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

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

每个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

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

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

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
2
3
4
5

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
2
3
4
5
6

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
2
3
4
5
6
7

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
2
3
4
5
6

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
2
3
4

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])
1
2
3
4
5
6

按行求和(方法2)

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

按列统计平均值

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
2
3
4
5

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:]))
1
2
3
4
5

按列求最大值

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:])])
1
2
3
4
5
6

按行求和

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
1
2
3
4

按列求最大值

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))
1
2
3
4

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

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
2
3
4
5
6
7
8
9
10
11
12
13

1.9单元格的写入

1.9.1单元格与区域数据写入

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