哈尔滨市建设工程质量安全站,月熊志网站,江苏天德建设工程有限公司网站,苏州工程网站建设Python处理Excel数据之Openpyxl
1.1 Openpyxl库的安装使用
openpyxl模块是一个读写Excel 2010文档的 Python 库#xff0c;如果要处理更早格式的Excel文档#xff0c;需要用到额外的库#xff0c;openpyxl是一个比较综合的工具#xff0c;能够同时读取和修改Excel文档。其…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_onlyTrue/False的读写方式默认是可写。 保存: workbook.save(‘工作簿名.xlsx’)
1-from openpyxl import Workbook
2- wbWorkbook()#新建工作簿
3-wb.save(我的工作簿.xlsx)#保存工作簿
每个workbook创建后默认会存在一个worksheet。也可以自行创建新的worksheet。
1.2.2读取保存工作簿
读取工作簿: openpyxl.load_workbook(‘工作簿名.xlsx’)注意以下相关参数的设置。read_onlyFalse/True False表示可以读、写True表示只能读、不能写。 guess_typesFalse/True False表示转换数据True表示不能转换数据。 data_onlyFalse/True False表示序单元格的真实信息True表示只读取值。
1- from openpyxl import load_workbook
2-wbload_workbook(成绩表-1xsx)#读取工作簿3-wb.save(成绩表-2.xlsx)#保存工作簿1.2.3实例(批量建新工作表)
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实例(批量修改工作表名)
1- import openpyxl
2- wbopenpyxLload_workbook(各年业绩表.xlx)
3- for sh in wb.worksheets:
4- sh.titlesh.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实例应用批量新建工作表)
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实例应用删除不符合条件的工作表)
1- import openpyxl
2- wbopenpyxLload_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实例应用批量复制工作表)
1- import openpyxl
2- wbopenpyxlload_workbook(模板.xlsx)
3- for m in range(1,13):
4-wb.copy_worksheet(wb[demol).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实例应用汇总各表各单元格数据)
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)
1-import open pyxl
2- wb openpyxlload_workbook(test.xlsxe)
3- wswb[成绩表
4- rngws[2:71]
5- rngws[A2:E71]
6- print([%s:%d分1%(rn[O].value,sum([r.value for r in rn][1:])) for rn in rng])按行求和(方法2)
1- import openpyxl
2-wbopenpyxl.load_workbook( test.xlsx)
3-wswb.active
4- for x in list(ws.values)[1:]:
5-print([x[O],sum(x[1:])按列统计平均值
1-import openpyxl
2-wbopenpyxl.load_workbook( test.xlsx)
3-wswb.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实例应用
按行求和
1- import openpyxl
2- wbopenpyxl.load_workbook(test.xlsx)
3-wswb.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:])])按列求最大值
1-import openpyxl
2-wbopenpyxlload_workbook(test.xlsx)
3-wswb.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:]))
按行求和
1- import openpyxl
2-wbopenpyxLload_workbook(test.xlsx)
3-wswb.active
4-subtotal[sum([w.value for v in row]) for row in
ws.iter_rows(min_row2,min_col2)]
5-name[v.value for v in ws[a]][1:]
6-print(list(zip(name,subtotal))按列求最大值
1-import openpyxl
2-wbopenpyxl.load_workbook(test.xlsx)
3-wswb.active
4-subtotal[max([v.value for v in col]) for col in ws.iter_cols(min _row2,min_col2)]
5-name[wvalue for v in ws[1]][1:]
6-print(list(zip(name,subtotal)))动态获取单元格区域并汇总
1-import openpyxl
2- wbopenpyxL.load_workbook( demo.xlsx)
3-wswb.active
4-minrws.min_row
5- mincws.min_column
6-maxrws.max_row
7-maxcws.max_column
8-rngsws.iter_rows(min_rowminr1,min_colminc2,max_rowmaxr-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_rowminr1,min_colminc1,max_rowmaxr-1,max_colminc1)
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实例应用九九乘法表)
4-import open pyxl
5- wbopenpyxLload_workbook(demo.xIsx)
6- wswb.active
7- for x in range(1,10):
8-for y in range(1,x1):
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实例应用(九九乘法表)
1- import openpyxl
2- wbopenpyxL.load_workbook( demo.xlsx)
3-wswb.active
4-—[[%d×%d%d%(y,x,x*y) for y in range(1,10) if yx] 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分为优秀)
1- import openpyxl
2- wbopenpyxL.load_workbook( demo.xlsx)
3- wswb.active
4-rngsws.iter_rows(min_row2,min_col2)
5- for row in rngs:
6-for c in row:
7-if c.value90:
8-cvalue%d(%s)%(c.value,优秀)
9- wb.save( demo1.xlsx)1.11.3实例应用(每个人的总分大于等于300为优秀)
1. import openpyxl
2.wbopenpyxLl.load_workbook(demo.xlsx)
3. wswb.active
4.rngsws.iter_rows(min_row2,min_col2)
5.for row in rngs:
6.smsum( [c.value for cin row][0:4])
7.if sm300:
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的记录)
12- import openpyxl
13- wbopenpyxLload_workbook(成绩表.xlsx)
14- wswb.active
15- for r in range(ws.max_row,1,-1):
16-ssum([vvalue for v in ws[r][1:]])
17-if s300:
18-ws.delete_rows(r)
19- wb.save(demo999.xlsx)1.13实例应用求和结果写入单元格)
1-import openpyxl
2- wbopenpyxl.load_workbook(test.xlsx)
3-wswb[成绩表]
4-rngws[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-ws1wb.create_sheet(结果)#新建工作表
7-ws1.append([姓名,总分数]#写入表头
8-for line in rngs:
9-ws1.append(lin e)#活环写入求和分数
10- wb.save(test1.xlsx)#保存工作簿1.14实例应用(筛选成绩总分大于等于300分的记录)
1-import openpyxl
2-wbopenpyxlload_workbook(test.xlsxe)
3-wswb[成绩表];nwswb.create_sheet(结果)
4-rnglist(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实例应用工资条制作)
1-import openpyxl
2- wbopenpyxLload_workbook(工资表.xlsx,data_onlyTrue)
3-wswb.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 实例应用(多工作表合并到单工作表)
1- import openpyxl
2- wbopenpyxl.load_workbook(各年业绩表.xlsx)#读取工作簿
3- nwbopenpyxLWorkbook()#新建工作簿
4-nwsnwb.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实例应用(单工作表拆分到多工作表)
1-import openpyxl
2- wbopenpyxLload_workbook(各班成绩表.xkx)#读取工作簿
3-wswb.active#读取活动工作表
4-rngsws.iter_rows(min_row2)#获取工作表中的数据
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- nwbopenpyxL. Workbook()#新建工作簿
13- for k,v in sorted(d.items()):
14-nwsnwb.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实例应用单工作簿拆分到多工作簿中(单表中))
1-import openpyxl
2- wbopenpyxL.load_workbook(工资表.xlsx,data_onlyTrue)3-rngswb.active.iter_rows(min_row2)
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-nwbopen pyxl.Workbook()
13- nwsnwb.active;nws.titlek
14-nws.append([工号∵姓名,应发工资∵扣款∵奖金∵;实发工资D)
15-for r in v:
16-del r[2]
17-nws.append(r)
18-nwb.save(各部门工资表Wk.xlsx)1.19实例应用(单工作簿拆分到多工作簿中(多表中))
1-import openpyxl
2-wbopenpyxl.load_workbook(工资表.xlsx,;data_onlyTrue)
3-wswb.active
4-rngsws.iter_rows(min_row2)
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-nwbopen pyxl.Workbook()
15-for m,n in witems():
16-nwsnwb.create_sheet(m);nws.append([wvalue for v in ws[1l1)
17-for f in n:
18-nws.append(f)
19-nwb.remove(nwb[Sheet])
20-nwb.save(拆分结果Wk.xlsx)1.20实例应用(二维表转一维表)
1-import openpyxl
2-wbopenpyxl.load_workbook(业绩表.xlsx)
3-wswb.active
4-if 转换表 in wb.sheetnames:
5-wb.remove(wb[转换表])
6-nwswb.create_sheet(转换表)
7-nws.append([姓名∵;月份∵金额])
8- for name,row in zip(ws[a][1:],ws.iter_rows(min_col2,min_row2)):
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实例应用一维转二维)
1- import openpyxl
2-wbopenpyxL.load_workbook(业绩表.xlsx)
3-wswb.active
4- if not二维表 in wb.sheetnames:
5-nwswb.create_sheet(二维表)
6-rngs[[r.value for r in row] for row in ws.iter_rows(min_row2)]
7-mmlist({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实例应用将入库单据数据写入工作表)
1- import openpyxl
2- wbopenpyxL.load_workbook(入库单.xlsx,data_onlyTrue)
3-wb1openpyxLload_workbook(数据库.xlsx)
4-wswb.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(vt)
11-wb1.save(数据库xlsx)
12-print(保存成功!)
13- else:
14-print(己保存)
