python-自动化篇-办公-Excel-Openpyxl库

文章目录 1.1 Openpyxl库的安装使用1.2 Excel的新建、读取、保存1.2.1新建保存工作簿1.2.2读取保存工作簿1.2.3实例(批量建新工作表) 1.3工作表对象的获取方法1.3.1工作表获取方式1.3.2实例(批量修改工作表名) 1.4工作表的新建、复制、删除1.4.1新建工作表1.4.2复制工作表1.4.3删除工作表 1.5关于工作表的实例应用1.5.1实例应用(批量新建工作表)1.5.2实例应用（删除不符合条件的工作表)1.5.3实例应用（批量复制工作表) 1.6单元格信息获取1.6.1单元格数据获取1.6.2实例应用（汇总各表各单元格数据) 1.7单元格区域信息获取1.7.1单元格区域数据获取1.7.2实例应用 1.8行列信息获取1.8.1行列信息获取1.8.2实例应用 1.9单元格的写入1.9.1单元格与区域数据写入

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’)

from openpyxl import Workbook2 wb=Workbook()#新建工作簿 wb.save('我的工作簿xlsx')#t保存工作簿

每个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表示只读取值。

from openpyxl import load_workbook wb=load_workbook("成绩表-1.xlsx')#读取工作簿 wb.save('成绩表-2.xlsx')#保存工作簿 1.2.3实例(批量建新工作表) from openpyxl import Workbook for m in range(1,13): wb = Workbook()#新建工作簿 wb.save("%d月.xlsx'%m)#保存工作簿 1.3工作表对象的获取方法 1.3.1工作表获取方式

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

1.3.2实例(批量修改工作表名) import openpyxl wb=openpyxl.load_workbook('各年业绩表.xlsx') for sh in wb.worksheets: sh.title=sh.title+'-芝华公司' wb.save('各年业绩表（修改后）.xlsx') 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实例应用(批量新建工作表) import openpyxl wb = openpyxl.Workbook()#新建工作簿 for m in range(1,13): wb.create_sheet('%d月"%m)#t新建月份工作表 wb.remove(wb[ 'Sheet'])#鹏除指定工作表 wb.save( '2019年计划表.xlsx')# 保存工作簿。 1.5.2实例应用（删除不符合条件的工作表) import openpyxl wb=openpyxl.load_workbook('2018年.xlsx') #读取工作簿 for sh in wb:#循环工作簿中的工作表 if sh.title.split('-')[O]!='北京":#判断工作表是否不等于北京 wb.remove(sh)#棚除工作表 wb.save('北京.xlsx')#保存工作簿 1.5.3实例应用（批量复制工作表) import openpyxl wb=openpyxl.load_workbook('模板xlsx') for m in range(1,13): wb.copy_worksheet(wb['demo']).title='%d月'%m wb.remove(wb[ 'demo']) wb.save('2018年各月表格.xlsx') 1.6单元格信息获取 1.6.1单元格数据获取

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

1.6.2实例应用（汇总各表各单元格数据) import openpyxl wb = openpyxl.load_workbook('各年业绩表.xlsx') print(sum([s['b14'].value for s in wb]) print(sum([s.cell(14,2).value for s in wb])) 1.7单元格区域信息获取 1.7.1单元格区域数据获取

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

1.7.2实例应用

按行求和（方法1)

import openpyxl wb = openpyxl.load_workbook( 'test.xlsx') ws=wb['成绩表' # rng=ws['2:71'] rng=ws['A2'∵:'E71'] print(['%s:%d分1%(rn[0].value,sum([r.value for r in rn][1:]) for rn in rng])

按行求和(方法2)

import openpyxl wb=openpyxl.load_workbook('test.xlsx') ws=wb.active for xin list(ws.values)[1:]: print([x[0], sum(x[1:]))

按列统计平均值

import openpyxl wb=openpyxl.load_workbook('test.xlsx') ws=wb.active for xin list(zip(*list(ws.values)))[1:]: print([x[O],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实例应用

按行求和

import openpyxl wb=openpyxl.load_workbook('test.xlsx')ws=wb.active for r in [row for row in ws.rows][1:]: l=[wvalue for v in r] print([l[O],sum(I[1:]))

按列求最大值

import openpyxl wb=openpyxl.load_workbook('test.xlsx') ws=wb.active for c in [col for col in ws.columns][1:]: l=[vvalue for v in c] print([l[O],max(l[1:])])

按行求和

import openpyxl wb=openpyxl.load_workbook('test.xlsx')wsewb.active subtotal=[sum([v.value for v in row]) for row inws.iter_rows(min_row=2,min_col=2])] namemv.value for v in ws[ "a']][1:]print( list(zip(name ,subtotal

按列求最大值

import openpyxcl wb=openpyxil.load_workbook('test.xlsx")wsmwb.active subtotal=[m ax([v.value for vin coll) for col in ws.iter_cols(min_row 2,min_col=2)]name=[v.value for v in ws['1]][1:] print( list(zip(name ,subtotal))

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

import openpyxl wb=openpyxl.load_workbook(" demo.xlsxe') ws=wb.active minr=ws.min_row minc=ws.min_column maxr=ws.max_row maxc=ws.max_column mgs=ws.iter_rows(min_row=minrt1,min_col minc+2,max_row=maxr-1,max_colemaxc-1) subtotal=[min([v.value for v in row]) for row in rngs] col=[v for v in ws.iter_coks(min_row=minr+1,min_col=minc+1,max_row=maxr-1,max_col=minc+1)] chanping=l[v.value for v in r] for r in col][0] print( list(zip(chanping,subtotal)) 1.9单元格的写入 1.9.1单元格与区域数据写入

A1表示法:工作表[‘A1’]=值，R1C1表示法:工作表.celI(行号,列号,值)