Python处理Excel数据之Openpyxl

1.1 Openpyxl库的安装使用

openpyxl模块是一个读写Excel 2010文档的 Python 库，如果要处理更早格式的Excel文档，需要用到额外的库，openpyxl是一个比较综合的工具，能够同时读取和修改Excel文档。其他很多的与Excel相关的项目基本只支持读或者写Excel一种功能。新建、读取、保存工作簿。Openpyxl的安装和其它库一样。直接在PyCharm 中安装即可。
Openpyxl可以对Excel进入读、写操作，也可以进行单元格格式设置、图表、条件格式、数据透视表等设置。
由于本课程是为了巩固应用python基础知识，所以就只讲解对openpyxl对 Excel的读写操作。

1.2 Excel的新建、读取、保存

1.2.1新建保存工作簿

新建: openpyxl.Workbook()，注意这个的w是大写的（本人吃过亏)，可以设置write_only=True/False的读写方式，默认是可写。
保存: workbook.save(‘工作簿名.xlsx’)

python">1->from openpyxl import Workbook
2-> wb=Workbook()#新建工作簿
3->wb.save('我的工作簿.xlsx')#保存工作簿

每个workbook创建后，默认会存在一个worksheet。也可以自行创建新的worksheet。

1.2.2读取保存工作簿

读取工作簿: openpyxl.load_workbook(‘工作簿名.xlsx’)，注意以下相关参数的设置。read_only=False/True False表示可以读、写，True表示只能读、不能写。
guess_types=False/True False表示转换数据，True表示不能转换数据。
data_only=False/True False表示序单元格的真实信息，True表示只读取值。

python">1-> from openpyxl import load_workbook
2->wb=load_workbook("成绩表-1xsx')#读取工作簿3->wb.save("成绩表-2.xlsx')#保存工作簿

1.2.3实例(批量建新工作表)

python">1-> from openpyxl import Workbook
2-> for m in range(1,13):
3->wb = Workbook()#新建工作簿4->wb.save('9%d月.xlsx"%m)出保存工作簿

1.3工作表对象的获取方法

1.3.1工作表获取方式

获取当前活动工作表的: workbook.active
以索引值方式获取工作表: workbook.worksheets[索引值]
以工作表名获取:workbook['工作表名]，注意，此表达方式没有没有成员提示。循环工作表: workbook.worksheets
获取所有工作表名: workbook.sheetnames
获取指定工作表名: worksheet.tite，可以返回工作表名称，也可以修改工作表名称，如worksheet.title=‘工作表名’

1.3.2实例(批量修改工作表名)

python">1-> import openpyxl
2-> wb=openpyxLload_workbook('各年业绩表.xlx')
3-> for sh in wb.worksheets:
4-> sh.title=sh.title+'-芝华公司'5-> wb.save('各年业绩表（修改后）.xlsxe')

1.4工作表的新建、复制、删除

1.4.1新建工作表

可以在新建的工作簿中新建工作表（在新建工作簿时，会默认新建一个工作表)。也可在已经存在的工作簿中新建工作表。
新建工作表时的默认工作表名: workbook.create_sheet(),默认工作表名为Sheet1、Sheet2、Sheet3…-…-
新建工作表自定义工作表名: workbook.create_sheet(‘"工作表名’,指定位置)，如果不指定位置则默认将新建的工作表放置在最后。

1.4.2复制工作表

workbook.copy_worksheet(工作表)

1.4.3 删除工作表

workbook.remove(工作表)

1.5关于工作表的实例应用

1.5.1实例应用（批量新建工作表)

python">1-> import open pyxl
2-> wb = openpyxl.Workbook()#新建工作簿
3-> for m in range(1,13):
4->wb.create_sheet("%d月"%m)#新建月份工作表
5-> wb.remove(wb['Sheet'])#别除指定工作表
6-> wb.save("2019年计划表.xlsx')#保存工作簿。

1.5.2实例应用（删除不符合条件的工作表)

python">1-> import openpyxl
2-> wb=openpyxLload_workbook("2018年.xlsx")#读取工作簿
3-> for sh in wb:#活环工作簿中的工作表
4-> if sh.title.split("-")[0]!='北京":#判断工作表是否不等于北京
5->wb.remove(sh)#别除工作表
6->wb.save("北京.xlsx')#保存工作簿

1.5.3实例应用（批量复制工作表)

python">1-> import openpyxl
2-> wb=openpyxlload_workbook(模板.xlsx')
3-> for m in range(1,13):
4->wb.copy_worksheet(wb['demo'l).title='%d月'%m
5-> wb.remove(wb['demo'])
6->wb.save('2018年各月表格.xlsxe')

1.6单元格信息获取

1.6.1单元格数据获取

A1表示法:工作表[·A1]，R1C1表示法:工作表.cell(行号,列号)

1.6.2实例应用（汇总各表各单元格数据)

python">1-> import openpyxl
2->wb = openpyxl.load_workbook(各年业绩表.xlsx')
3->print(sum([s['b14'].value for s in wb]))
4-> print(sum([s.cell(14,2).value for s in wb]))

1.7单元格区域信息获取

1.7.1单元格区域数据获取

1.工作表[起始单元格∵’终止单元格’]或工作表[起始单元格:终止单元格"，如l ws[‘A1’:‘F3’]或ws[‘A1: F3’]。此方法是按行读取的数据。
2.工作表["起始行号:'结束行号]或者工作表['起始行号:结束行号]，如ws[‘1’:‘3’]或ws['1: 3。此方法是按行读取的数据。
3.工作表[起始列号:"结束列号]或者工作表[起始列号:结束列号]，如 ws[‘A’:'F]或ws['A:F]。此方法是按列读取的数据。
4.获取（按行〉指定工作表所有已用数据: list(workbook.worksheets[索引值J.values)

1.7.2实例应用

按行求和(方法1)

python">1->import open pyxl
2-> wb = openpyxlload_workbook('test.xlsxe')
3-> ws=wb['成绩表"
4->  rng=ws["2:71']
5-> rng=ws['A2:'E71']
6-> print(["%s:%d分1%(rn[O].value,sum([r.value for r in rn][1:])) for rn in rng])

按行求和(方法2)

python">1-> import openpyxl
2->wb=openpyxl.load_workbook( 'test.xlsx')
3->ws=wb.active
4-> for x in list(ws.values)[1:]:
5->print([x[O],sum(x[1:])

按列统计平均值

python">1->import openpyxl
2->wb=openpyxl.load_workbook( 'test.xlsx')
3->ws=wb.active
4-> for x in list(zip(*list(ws.values)))[1:]:
5->print([x[ 0],float("%.2f"%(sum(x[1:])/len(x)-1))])

1.8行列信息获取

1.8.1行列信息获取

按行获取工作表使用区域数据:worksheet.rows
按列获取工作表使用区域数据: worksheet.columns
获取工作表中最小行号: worksheet.min_row
获取工作表中最小列号: worksheet.min_column
获取工作表中最大行号: worksheet.max_row
获取工作表中最大列号: worksheet.max_column
获取单元格的行号: cell.row
获取单元格的列号: cell.column

iter方法获取指定区域：
1.按行获取指定工作表单元格区域: worksheet.iter_rows(……)
2.按列获取指定工作表单元格区域: worksheet.iter_cols(……)
可以通过min_row、min_col、max_col、max_row这几个参数进行单元格区域的控制。

1.8.2实例应用

按行求和

python">1-> import openpyxl
2-> wb=openpyxl.load_workbook('test.xlsx')
3->ws=wb.active
4-> for r in [row for row in ws.rows][1:]:
5-> l=[w.value for v inr]
6->print([I[O],sum(I[1:])])

按列求最大值

python">1->import openpyxl
2->wb=openpyxlload_workbook('test.xlsx')
3->ws=wb.active
4-> for cin [col for col in ws.columns][1:]:
5-> l=[w.value for v in c]
6->print([I[o],max(I[1:]))

按行求和

python">1-> import openpyxl
2->wb=openpyxLload_workbook('test.xlsx')
3->ws=wb.active
4->subtotal=[sum([w.value for v in row]) for row in
ws.iter_rows(min_row=2,min_col=2)]
5->name=[v.value for v in ws['a']][1:]
6->print(list(zip(name,subtotal))

按列求最大值

python">1->import openpyxl
2->wb=openpyxl.load_workbook('test.xlsx')
3->ws=wb.active
4->subtotal=[max([v.value for v in col]) for col in ws.iter_cols(min _row=2,min_col=2)]
5->name=[wvalue for v in ws['1']][1:]
6->print(list(zip(name,subtotal)))

动态获取单元格区域并汇总

python">1->import openpyxl
2-> wb=openpyxL.load_workbook( 'demo.xlsx')
3->ws=wb.active
4->minr=ws.min_row
5-> minc=ws.min_column
6->maxr=ws.max_row
7->maxc=ws.max_column
8->rngs=ws.iter_rows(min_row=minr+1,min_col=minc+2,max_row=maxr-1,max_col=
maxc-1)
9->subtotal=[min([v.value for v in row]) for row in rngs]
10-> co=[v for v in
ws.iter_cols(min_row=minr+1,min_col=minc+1,max_row=maxr-1,max_col=minc+1)
11-> chanping=[[w.value for v in r] for rin col][o]
12-> print(list(zip(chanping,subtotal)))

1.9单元格的写入

1.9.1单元格与区域数据写入

A1表示法:工作表['A1]=值，R1c1表示法:工作表.cell(行号,列号,值)

1.9.2实例应用（九九乘法表)

python">4->import open pyxl
5-> wb=openpyxLload_workbook('demo.xIsx')
6-> ws=wb.active
7-> for x in range(1,10):
8->for y in range(1,x+1):
9->ws.cell(x,y,1%dX%d=%d '9%(y,x,×*y)#方法1
10->ws.cell(x,y).value='%dX%d=%d'%(yxx*y)#方法2
11->wb.save('demo.xlsx')

1.10批量写入数据

1.10.1按行写入数据

在最后一行写入数据:工作表.append(列表)

1.10.2实例应用(九九乘法表)

python">1-> import openpyxl
2-> wb=openpyxL.load_workbook( 'demo.xlsx")
3->ws=wb.active
4->—[["'%d×%d=%d"%(y,x,x*y) for y in range(1,10) if y<=x] for x in range(1,10)]
5-> for r in l:
6->ws.ap pend(r)
7->wb.save('demo.xlsx')

1.11循环方式批量写入数据

1.11.1循环获取单元格对象，再写入

之前我们可以通过组合单元格来获取或者写入数据，但还有一种方法,就是直接循环单元格区域来写入数据。与循环读取的表示方式基本相同，只是多了一个赋值。

1.11.2实例应用(大于等于90分为优秀)

python">1-> import openpyxl
2-> wb=openpyxL.load_workbook( 'demo.xlsx")
3-> ws=wb.active
4->rngs=ws.iter_rows(min_row=2,min_col=2)
5-> for row in rngs:
6->for c in row:
7->if c.value>=90:
8->cvalue='%d(%s)%(c.value,'优秀)
9-> wb.save( 'demo1.xlsx')

1.11.3实例应用(每个人的总分大于等于300为优秀)

python">1. import openpyxl
2.wb=openpyxLl.load_workbook('demo.xlsx')
3. ws=wb.active
4.rngs=ws.iter_rows(min_row=2,min_col=2)
5.for row in rngs:
6.sm=sum( [c.value for cin row][0:4])
7.if sm>=300:
8.row[-1].value='优秀'
9. wb.save('demo2.xlsx')

1.12工作表行、列的插入与删除

1.12.1行列的插入与删除

插入列: worksheet.insert_cols(位置,列数)，其中位置是指在工作表的第几列前插入多少列。
插入行: worksheet.insert_rows(位置,行数),其中位置是指在工作表的第几行前插入多少行。
删除列: worksheet.delete_cols(位置,列数)，从指定位置开始向后删除指定的列数。
删除行: worksheet.delete_rows(位置,行数)，从指定位置开始向下删除指定的行数。

1.12.2实例应用(筛选小于300的记录)

python">12-> import openpyxl
13-> wb=openpyxLload_workbook("成绩表.xlsx")
14-> ws=wb.active
15-> for r in range(ws.max_row,1,-1):
16->s=sum([vvalue for v in ws[r][1:]])
17->if s>=300:
18->ws.delete_rows(r)
19-> wb.save('demo999.xlsx")

1.13实例应用（求和结果写入单元格)

python">1->import openpyxl
2-> wb=openpyxl.load_workbook('test.xlsx')
3->ws=wb['成绩表']
4->rng=ws[str(ws.min_row +1):str(ws.max_row)]#动态获取单元格区域
5->rngs=[[rn[0].value,sum([r.value for r in rn][1:])] for rn in rng]#汇总处理
6->ws1=wb.create_sheet("结果")#新建工作表
7->ws1.append([姓名,总分数]#写入表头
8->for line in rngs:
9->ws1.append(lin e)#活环写入求和分数
10-> wb.save('test1.xlsx")#保存工作簿

1.14实例应用(筛选成绩总分大于等于300分的记录)

python">1->import openpyxl
2->wb=openpyxlload_workbook('test.xlsxe')
3->ws=wb[成绩表];nws=wb.create_sheet("结果)
4->rng=list(ws.rows)[1:];nws.append([wvalue for v in ws[1':1']]+[总分'])
5-> for l in rng:
6-> l=[w.value for v in l]
7->if sum(Il[1:])>=300:
8->nws.append(ll+[sum(lI[1:]])
9-> wb.save('test2.xlsxe')

1.15实例应用（工资条制作)

python">1->import openpyxl
2-> wb=openpyxLload_workbook('工资表.xlsx",data_only=True)
3->ws=wb.active
4-> for r in range(ws.max_row,2,-1):
5->ws.insert_rows(r)
6->for c in range(1,8):
7->ws.cellr,c,ws.celI(1,c).value)
8-> wb.save('工资表结果.xkx')

1.16 实例应用(多工作表合并到单工作表)

python">1-> import openpyxl
2-> wb=openpyxl.load_workbook('各年业绩表.xlsx')#读取工作簿
3-> nwb=openpyxLWorkbook()#新建工作簿
4->nws=nwb.active#获取活动工作表
5->nws.append(['年份∵月份∵金额])#写入标题
6-> for sh in wb:
7->ll=[[sh.title] +[wvalue for v inl] for l in sh.rows][1:-1]#合并各表数据
8->for l in ll:
9->nws.append(ID)#3入到新表
10-> nwb.save('合并.xlsx')#保存工作表

1.17实例应用(单工作表拆分到多工作表)

python">1->import openpyxl
2-> wb=openpyxLload_workbook('各班成绩表.xkx')#读取工作簿
3->ws=wb.active#读取活动工作表
4->rngs=ws.iter_rows(min_row=2)#获取工作表中的数据
5->d=#创建空字典
6-> for r in rngs:
7-> l=[wvalue for v inr]
8->if I[0] in d.keys():#!将每行数据写入到字典中对应的班级
9.>d[1[0]]+=[]
10->else:
11->d[[0]]=[叮]
12-> nwb=openpyxL. Workbook()#新建工作簿
13-> for k,v in sorted(d.items()):
14->nws=nwb.create_sheet(k)#将字典中的键名做为班级名
15-> nws.append(['班级∵姓名∵'分数]#写入每个工作表的标题
16->forr in v:
17->nws.append(r)#将每个班的记录写入对应的工作表
18-> nwb.remove(nwb['Sheet'])#别除默认创建的工作表
19-> nwb.save("拆分到工作表.xlsxe')#保存工作簿

1.18实例应用（单工作簿拆分到多工作簿中(单表中))

python">1->import openpyxl
2-> wb=openpyxL.load_workbook('工资表.xlsx",data_only=True)3->rngs=wb.active.iter_rows(min_row=2)
4-> d=[]
5-> for row in rngs:
6->l=[w.value for v in row]
7->if I[2] in d.keys():
8->d[[2]]+=[l]
9->else:
10->d.update(l[2]:[0]
11-> for k,v in d.items():
12->nwb=open pyxl.Workbook()
13-> nws=nwb.active;nws.title=k
14->nws.append(['工号∵姓名,'应发工资"∵扣款∵奖金"∵;实发工资D)
15->for r in v:
16->del r[2]
17->nws.append(r)
18->nwb.save('各部门工资表W'+k+'.xlsx')

1.19实例应用(单工作簿拆分到多工作簿中(多表中))

python">1->import openpyxl
2->wb=openpyxl.load_workbook('工资表.xlsx",;data_only=True)
3->ws=wb.active
4->rngs=ws.iter_rows(min_row=2)
5->d=[]
6->for row in rngs:
7->l=[Lvalue for l in row]
8->if [2] in d.keys() and l[3] in d[II[2]].keys(:
9->d[I[2][[3]+=[
10->else:
11->if not lI[2] in d.keys():d[II[2]]=得
12->d[I[2]][[3]=[I
13-> for k,v in d.items():
14->nwb=open pyxl.Workbook()
15->for m,n in witems():
16->nws=nwb.create_sheet(m);nws.append([wvalue for v in ws['1'l1)
17->for f in n:
18->nws.append(f)
19->nwb.remove(nwb['Sheet'])
20->nwb.save('拆分结果W'+k+".xlsx')

1.20实例应用(二维表转一维表)

python">1->import openpyxl
2->wb=openpyxl.load_workbook("业绩表.xlsx")
3->ws=wb.active
4->if 转换表' in wb.sheetnames:
5->wb.remove(wb[转换表])
6->nws=wb.create_sheet('转换表')
7->nws.append([姓名∵;月份∵金额])
8-> for name,row in zip(ws['a'][1:],ws.iter_rows(min_col=2,min_row=2)):
9->for x,y in zip(ws[11'][1:],row):
10->nws.append([name.value,x.value,y.value])11-> wb.save("业绩表.xlsxe')

1.21实例应用（一维转二维)

python">1-> import openpyxl
2->wb=openpyxL.load_workbook("业绩表.xlsx")
3->ws=wb.active
4-> if not'二维表' in wb.sheetnames:
5->nws=wb.create_sheet('二维表')
6->rngs=[[r.value for r in row] for row in ws.iter_rows(min_row=2)]
7->mm=list({name.value:" for name in ws['b'][1:]}.keys0)
8->nws.ap pend(["姓名']+mm)
9->forx in {name.value:" for name in ws['a'][1:]}.keys():
10->=[(x,y) for yin mm]
11->nws.append([x]+[list(filter(lambda x:x[0]==s[0] and x[1]==s[1],rngs)[O][2]
for s in l])
12-> wb.save("业绩表.xlsx")

1.22实例应用（将入库单据数据写入工作表)

python">1-> import openpyxl
2-> wb=openpyxL.load_workbook('入库单.xlsx',data_only=True)
3->wb1=openpyxLload_workbook("数据库.xlsx')
4->ws=wb.active
5-> Hlist(ws.values)
6-> t=(I[2][1],I[2][3].I[2][5])
7->if not t[2] in [v.value for v in wb1.active[i]]:
8->for v in [5:]:
9->if not None in v:
10->wb1.worksheets[o].append(v+t)
11->wb1.save("数据库xlsx'")
12->print(保存成功!)
13-> else:
14->print('己保存)