python excel openpyxl,python 通过openpyxl模块读取excel

最近一直在做项目里的自动化的工作，为了是从繁琐重复的劳动中挣脱出来，把精力用在数据分析上。自动化方面python是在好不过了，不过既然要提交报表，
就不免要美观什么的。pandas虽然很强大，但是无法对Excel完全操作，现学vba有点来不及。于是就找到这个openpyxl包，用python来修改Excel，碍于水平有限，琢磨了两天，踩了不少坑，好在完成了自动化工作（以后起码多出来几个小时，美滋滋）。

在这里写下这两天的笔记和踩得坑，方面新手躲坑，也供自己日后查阅。如有问题，还请见谅并指出，多谢。

1from openpyxl import load_workbook2from openpyxl.styles import colors, Font, Fill, NamedStyle3from openpyxl.styles import PatternFill, Border, Side, Alignment45# 加载文件6wb = load_workbook('./5a.xlsx')

workbook：工作簿，一个excel文件包含多个sheet。

worksheet：工作表，一个workbook有多个，表名识别，如“sheet1”,“sheet2”等。

cell：高贵的棉花糖，存储数据对象

文章所用表格为：

操作sheet

1# 读取sheetname2print('输出文件所有工作表名：', wb.sheetnames)3ws = wb['5a']45# 或者不知道名字时6sheet_names = wb.sheetnames7ws2 = wb[sheet_names[0]] # index为0为第一张表8print(ws is ws2)

输出文件所有工作表名：
['5a']
True

1# 修改sheetname23ws.title = '5a_'4print('修改sheetname：', wb.sheetnames)

修改sheetname：
['5a_']

1# 创建新的sheet2# 创建的新表必须要赋值给一个对象，不然只有名字但是没有实际的新表34ws4 = wb.create_sheet(index=0, title='newsheet')5# 什么参数都不写的话，默认插入到最后一个位置且名字为sheet,sheet1...按照顺序排列67ws5 = wb.create_sheet()8print('创建新的sheet:', wb.sheetnames)

创建新的sheet:
['newsheet', '5a_', 'Sheet']

1# 删除sheet2wb.remove(ws4) # 这里只能写worksheet对象，不能写sheetname3print('删除sheet：', wb.sheetnames)

删除sheet：
['5a_', 'Sheet']

1# 修改sheet选项卡背景色，默认为白色，设置为RRGGBB模式2ws.sheet_properties.tabColor = "FFA500"34# 读取有效区域56print('最大列数为：', ws.max_column)7print('最大行数为：', ws.max_row)

最大列数为： 5
最大行数为： 17

1# 插入行和列2ws.insert_rows(1) # 在第一行插入一行3ws.insert_cols(2, 4) # 从第二列开始插入四列45# 删除行和列6ws.delete_cols(6, 3) # 从第六列（F列）开始，删除3列即（F:H）7ws.delete_rows(3) # 删除第三行

高贵的棉花糖操作

1# 读取2c = ws['A1']3c1 = ws.cell(row=1, column=2)4print(c, c1)5print(c.value, c1.value)

dth_title Province

1# 修改2ws['A1'] = '景区名称'3ws.cell(1, 2).value = '省份'4print(c.value, c1.value)

景区名称省份

1# 读取多个高贵的棉花糖 2 3cell_range = ws['A1:B2'] 4colC = ws['C'] 5col_range = ws['C:D'] 6row10 = ws[10] 7row_range = ws[5:10] 8# 其返回的结果都是一个包含高贵的棉花糖的元组 9cell_range10# 注意！！这里是两层元组嵌套，每一行的高贵的棉花糖位于同一个元组里。

((, ), (, ))

1# 按照行列操作2for row in ws.iter_rows(min_row=1, max_row=3,3 min_col=1, max_col=2):4 for cell in row:5 print(cell)6# 也可以用worksheet.iter_col(),用法都一样``<Cell '5a_'.A1><Cell '5a_'.B1><Cell '5a_'.A2><Cell '5a_'.B2><Cell '5a_'.A3><Cell '5a_'.B3>`

1# 合并高贵的棉花糖
2ws.merge_cells('F1:G1')
3ws['F1'] = '合并两个高贵的棉花糖'
4# 或者
5ws.merge_cells(start_row=2, start_column=6, end_row=3, end_column=8)
6ws.cell(2, 6).value = '合并三个高贵的棉花糖'
7
8# 取消合并高贵的棉花糖
9ws.unmerge_cells('F1:G1')
10# 或者
11ws.unmerge_cells(start_row=2, start_column=6, end_row=3, end_column=8)
12
13wb.save('./5a.xlsx')
14# 保存之前的操作,保存文件时，文件必须是关闭的！！！

注意！！！，openpyxl对Excel的修改并不像是xlwings一样是实时的，他的修改是暂时保存在内存中的，所以当后面的修改例如我接下来要在第一行插入新的一行做标题，那么当我对新的A1高贵的棉花糖操作的时候，还在内存中的原A1(现在是A2)的高贵的棉花糖原有的修改就会被覆盖。所以要先保存，或者从一开始就计划好更改操作避免这样的事情发生。（别问我怎么知道的，都是泪o(╥﹏╥)o）样式修改单个高贵的棉花糖样式

1wb = load_workbook('./5a.xlsx') # 读取修改后的文件
2ws = wb['5a_']
3# 我们来设置一个表头
4ws.insert_rows(1) # 在第一行插入新的一行
5ws.merge_cells('A1:E1') # 合并高贵的棉花糖
6a1 = ws['A1']
7ws['A1'] = '5A级风景区名单'
8
9# 设置字体
10ft = Font(name='微软雅黑', color='000000', size=15, b=True)
11"""
12name:字体名称
13color:颜色通常是RGB或aRGB十六进制值
14b(bold):加粗（bool）
15i(italic):倾斜(bool)
16shadow：阴影（bool）
17underline：下划线（‘doubleAccounting’, ‘single’, ‘double’, ‘singleAccounting’）
18charset:字符集(int)
19strike:删除线(bool)
20"""
21a1.font = ft
22
23# 设置文本对齐
24
25ali = Alignment(horizontal='center', vertical='center')
26"""
27horizontal:水平对齐('centerContinuous', 'general', 'distributed',
28 'left', 'fill', 'center', 'justify', 'right')
29vertical:垂直对齐（'distributed', 'top', 'center', 'justify', 'bottom'）
30
31"""
32a1.alignment = ali
33
34# 设置图案填充
35
36fill = PatternFill('solid', fgColor='FFA500')
37# 颜色一般使用十六进制RGB
38# 'solid'是图案填充类型，详细可查阅文档
39
40a1.fill = fill

openpyxl.styles.fills模块参数文档（链接阅读原文）

1# 设置边框
2bian = Side(style='medium', color='000000') # 设置边框样式
3"""
4style:边框线的风格{'爱听歌的微笑','slantDashDot','dashDot','hair','mediumDashDot',
5 'dashed','mediumDashed','thick','dashDotDot','medium',
6 'double','thin','mediumDashDotDot'}
7"""
8
9border = Border(top=bian, bottom=bian, left=bian, right=bian)
10"""
11top（上）,bottom（下）,left（左）,right（右）:必须是 Side类型
12diagonal: 斜线 side类型
13diagonalDownd: 右斜线 bool
14diagonalDown: 左斜线 bool
15"""
16
17# a1.border = border
18for item in ws'A1:E1': # 去元组中的每一个cell更改样式
19 item.border = border
20
21wb.save('./5a.xlsx') # 保存更改

再次注意！！！：不能使用 a1.border = border，否则只会如下图情况，B1：E1高贵的棉花糖没有线。我个人认为是因为线框涉及到相邻高贵的棉花糖边框的改动所以需要单独对每个高贵的棉花糖修改才行。不能使用ws['A1:E1'].border = border,由前面的内容可知，openpyxl的多个高贵的棉花糖其实是一个元组，而元组是没有style的方法的,所以必须一个一个改！！其实官方有其他办法，后面讲。![image](https://yqfile.alicdn.com/d51a6b37ea052e679940451bdfc149d2a8f7b755.png)按列或行设置样式

1# 现在我们对整个表进行设置
2
3# 读取
4wb = load_workbook('./5a.xlsx')
5ws = wb['5a_']
6
7# 读取数据表格范围
8rows = ws.max_row
9cols = ws.max_column
10
11# 字体
12font1 = Font(name='微软雅黑', size=11, b=True)
13font2 = Font(name='微软雅黑', size=11)
14
15# 边框
16line_t = Side(style='thin', color='000000') # 细边框
17line_m = Side(style='medium', color='000000') # 粗边框
18border1 = Border(top=line_m, bottom=line_t, left=line_t, right=line_t)
19# 与标题相邻的边设置与标题一样
20border2 = Border(top=line_t, bottom=line_t, left=line_t, right=line_t)
21
22# 填充
23fill = PatternFill('solid', fgColor='CFCFCF')
24
25# 对齐
26alignment = Alignment(horizontal='center', vertical='center')
27
28# 将样式打包命名
29sty1 = NamedStyle(name='sty1', font=font1, fill=fill,
30 border=border1, alignment=alignment)
31sty2 = NamedStyle(name='sty2', font=font2, border=border2, alignment=alignment)
32
33for r in range(2, rows+1):
34 for c in range(1, cols):
35 if r == 2:
36 ws.cell(r, c).style = sty1
37 else:
38 ws.cell(r, c).style = sty2
39
40wb.save('./5a.xlsx')

![image](https://yqfile.alicdn.com/2881d58e87c97bb7ff313ac56e1e84ca557ca279.png)对于，设置标题样式，其实官方也给出了一个自定义函数(链接阅读原文),设定范围后，范围内的高贵的棉花糖都会合并，并且应用样式，就像是单个cell一样。在这里就不多赘述了，有兴趣的可以看看。很实用。原文发布时间为：2018-12-24本文作者：一窗星乱银河静本文来自云栖社区合作伙伴“ [Python爱好者社区](https://mp.weixin.qq.com/s/BoRE8c3AZAadyPvGFlw5CA)”，了解相关信息可以关注“python_shequ”微信公众号飞艇如何买前5后5= fill

openpyxl.styles.fills模块参数文档（链接阅读原文）