使用Python3读写合并Excel文件
2019-12-06 本文已影响0人
君临天下夜未央
1、Python中处理Excel常用的包
- xlrd&xlwt
- xlsxwriter
- openpyxl
2、Python中几种常用包比较
image.png
3、安装python包
pip install xlrd
pip install xlwt
pip install xlsxwriter
pip install openpyxl
4、用xlrd包读取Excel文件
1)、用法
#1、引用包
import xlrd
#2、打开文件
xlrd.open_workbook(r'/root/excel/chat.xls')
#3、获取你要打开的sheet文件
#获取所有sheet
sheet_name = workbook.sheet_names()[0]
# 根据sheet索引或者名称获取sheet内容
sheet = workbook.sheet_by_index(0) # sheet索引从0开始
#4、获取指定单元格里面的值
sheet.cell_value(第几行,第几列)
#5、获取某行或者某列的值
# 获取整行和整列的值（数组）
rows = sheet.row_values(1) # 获取第2行内容
cols = sheet.col_values(2) # 获取第3列内容
#6、获取sheet的名称，行数，列数
# sheet的名称，行数，列数
print (sheet.name,sheet.nrows,sheet.ncols)
2)、源码示例
import xlrd
from datetime import date,datetime
arrayNum = 6
#array = {'L1':'','L2':'','L3':'','L4':'','Question':'','Answer':''}
tables = []
newTables = []
def read_excel():
# 打开文件
workbook = xlrd.open_workbook(r'/root/chat.xls')
# 获取所有sheet
sheet_name = workbook.sheet_names()[0]
# 根据sheet索引或者名称获取sheet内容
sheet = workbook.sheet_by_index(0) # sheet索引从0开始
# sheet = workbook.sheet_by_name('Sheet1')
#print (workboot.sheets()[0])
# sheet的名称，行数，列数
print (sheet.name,sheet.nrows,sheet.ncols)
# 获取整行和整列的值（数组）
rows = sheet.row_values(1) # 获取第2行内容
cols = sheet.col_values(2) # 获取第3列内容
print (rows)
print (cols)
for rown in range(sheet.nrows):
array = {'L1':'','L2':'','L3':'','L4':'','Question':'','Answer':''}
array['L1'] = sheet.cell_value(rown,0)
array['L2'] = sheet.cell_value(rown,1)
array['L3'] = sheet.cell_value(rown,2)
array['L4'] = sheet.cell_value(rown,3)
array['Question'] = sheet.cell_value(rown,4)
array['Answer'] = sheet.cell_value(rown,5)
tables.append(array)
print (len(tables))
print (tables)
print (tables[5])
if __name__ == '__main__':
# 读取Excel
read_excel();
print ('读取成功')
5、尽量不用xlwt包写入Excel文件
1)、原因
1、在xlwt中生成的xls文件最多能支持65536行数据。
2、如果写入过多，会报错,由于数据太多，会报这个错误:
ValueError: row index (65536)not an intin range(65536)错误
2)、源码示例
# 1. 导入模块
import xlwt
def write_excel():
# 2. 创建Excel工作薄
myWorkbook = xlwt.Workbook()
# 3. 添加Excel工作表
mySheet = myWorkbook.add_sheet('A Test Sheet')
# 4. 写入数据
myStyle = xlwt.easyxf('font: name Times New Roman, color-index red, bold on', num_format_str='#,##0.00') #数据格式
mySheet.write(i, j, 1234.56, myStyle)
mySheet.write(2, 0, 1) #写入A3，数值等于1
mySheet.write(2, 1, 1) #写入B3，数值等于1
mySheet.write(2, 2, xlwt.Formula("A3+B3")) #写入C3，数值等于2（A3+B3）
#5. 保存
myWorkbook.save('excelFile.xls')
if __name__ == '__main__':
# 写入Excel
write_excel();
print ('写入成功')
5、用openpyxl包写入Excel文件
1)、用法
#1、引用包
import openpyxl
#2、创建工作簿
f = openpyxl.Workbook() #创建工作簿
#3、创建sheet
sheet1 = f.create_sheet()
#4、设置每个单元格里面的值
for jkey in range(len(newTables)):
jk = 1
for cT in range(arrayNum):
jk = jkey + 1
if cT == 0:
sheet1.cell(row=jk,column=cT+1).value='1'
else:
sheet1.cell(row=jk,column=cT+1).value='2'
#5、保存文件
f.save("chatPy.xlsx") #保存文件
1)、源码示例
import openpyxl
#写excel
def write_excel():
f = openpyxl.Workbook() #创建工作簿
sheet1 = f.create_sheet()
#sheet1 = f.add_sheet(u'sheet1',cell_overwrite_ok=True) #创建sheet
row0 = [u'L1',u'L2',u'L3',u'L4',u'问题',u'答案']
#生成第一行
#for i in range(len(row0)):
# sheet1.cell(column=i,row=0).value='L1')
#生成后续
for jkey in range(len(newTables)):
jk = 1
for cT in range(arrayNum):
jk = jkey + 1
if cT == 0:
sheet1.cell(row=jk,column=cT+1).value='1'
else:
sheet1.cell(row=jk,column=cT+1).value='2'
f.save("chatPy.xlsx") #保存文件
if __name__ == '__main__':
# 写入Excel
write_excel();
print ('写入成功')
5、用xlsxwriter包写入Excel文件
1)、简介
xlsxwriter这个模块，它生成的文件后缀名为.xlsx，最大能够支持1048576行数据，16384列数据
2)、用法
#1、引用包
import xlsxwriter
#2、创建工作簿
workbook = xlsxwriter.Workbook('demo1.xlsx')#创建一个excel文件
#3、创建sheet
#在文件中创建一个名为TEST的sheet,不加名字默认为sheet1
worksheet = workbook.add_worksheet(u'sheet1')
#4、设置每个单元格里面的值
worksheet.write(3,0,35.5)#第4行的第1列设置值为35.5
#5、关闭工作簿
workbook.close()
3)、源码示例
import xlsxwriter
#写excel
def write_excel():
workbook = xlsxwriter.Workbook('chat.xlsx')#创建一个excel文件
worksheet = workbook.add_worksheet(u'sheet1')#在文件中创建一个名为TEST的sheet,不加名字默认为sheet1
worksheet.set_column('A:A',20)#设置第一列宽度为20像素
bold= workbook.add_format({'bold':True})#设置一个加粗的格式对象
worksheet.write('A1','HELLO')#在A1单元格写上HELLO
worksheet.write('A2','WORLD',bold)#在A2上写上WORLD,并且设置为加粗
worksheet.write('B2',U'中文测试',bold)#在B2上写上中文加粗
worksheet.write(2,0,32)#使用行列的方式写上数字32,35,5
worksheet.write(3,0,35.5)#使用行列的时候第一行起始为0,所以2,0代表着第三行的第一列,等价于A4
worksheet.write(4,0,'=SUM(A3:A4)')#写上excel公式
workbook.close()
if __name__ == '__main__':
# 写入Excel
write_excel();
print ('写入成功')
6、总结以及实际案例
1)、案例背景
公司财务人员给了我们一个15年8月到18年2月的报销明细表，包括34个sheet页，要求把所有年月的sheet页面的数据合并在一个sheet页中。截图如下：
image.png
于是，我们使用了openpyxl包读取并合并Excel的多个sheet页。
2)、源代码
import xlrd
import openpyxl
result ='result.xlsx'
#创建结果文件，并添加表头
wbResult =openpyxl.Workbook()
wsResult =wbResult.worksheets[0]
wsResult.append(['编号','名称','年','月','误差率','收入1','收入2'])
#打开源文件，
print('打开文件')
wb = openpyxl.load_workbook("1508-1802.xlsx",data_only=True)
#获取所有sheet页名字
xl_sheet_names=wb.get_sheet_names()
#打印所有sheet页名称
print(xl_sheet_names)
#定位到相应sheet页,[0]为sheet页索引
i =0
rowSum=0
while i< len(xl_sheet_names)-3:
xl_sheet=wb.get_sheet_by_name(xl_sheet_names[i])
i=i+1
#获取行列数
row=xl_sheet.max_row
rowSum =rowSum+row
column=xl_sheet.max_column
#print('row=',row)
#print('column=',column)
def getSheet(index,year,month):
ws =wb.worksheets[index]
for index, row in enumerate(ws.rows):
#跳过表头
if index ==0:
continue
row[2].value =year
row[3].value =month
wsResult.append(list(map(lambda cell:cell.value,row)))
d =0
month=['00','01','02','03','04','05','06','07','08','09','10','11','12']
while d< len(xl_sheet_names)-3:
if(d<5):
getSheet(d,'2015',month[12-4+d])
elif(d>4 and d<17):
getSheet(d,'2016',month[d-4])
elif(d>16 and d<29):
getSheet(d,'2017',month[d-16])
else:
getSheet(d,'2018',month[d-28])
d =d+1
#处理结果文件，合并第一列中合适的单元格。
rows =['']+list(wsResult.rows)
index1 =2
rowCount =len(rows)
print('rowSum=',rowSum)
print('rowCount=',rowCount)
#处理结果文件，合并第一列中合适的单元格
print('写入文件')
while index1< rowCount:
value=rows[index1][0].value
index1=index1+1
#保存结果文件
print('保存文件')
wbResult.save(result)
