Python利用openpyxl来操作Excel(一)

转载自公众号：python_shequ

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

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

from openpyxl import load_workbookfrom openpyxl.styles import colors, Font, Fill, NamedStylefrom openpyxl.styles import PatternFill, Border, Side, Alignment# 加载文件wb = load_workbook('./5a.xlsx')

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

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

• cell： 单元格，存储数据对象

文章所用表格为：

image

操作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)

<Cell ‘5a_’.A1> <Cell ‘5a_’.B1>
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# 注意！！ 这里是两层元组嵌套，每一行的单元格位于同一个元组里。

((<Cell ‘5a_’.A1>, <Cell ‘5a_’.B1>), (<Cell ‘5a_’.A2>, <Cell ‘5a_’.B2>))

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)1213wb.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 = ft2223# 设置文本对齐2425ali = Alignment(horizontal='center', vertical='center')26"""27horizontal:水平对齐('centerContinuous', 'general', 'distributed',28                    'left', 'fill', 'center', 'justify', 'right')29vertical:垂直对齐（'distributed', 'top', 'center', 'justify', 'bottom'）3031"""32a1.alignment = ali3334# 设置图案填充3536fill = PatternFill('solid', fgColor='FFA500')37# 颜色一般使用十六进制RGB38# 'solid'是图案填充类型，详细可查阅文档3940a1.fill = fill

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

 1# 设置边框 2bian = Side(style='medium', color='000000')    # 设置边框样式 3""" 4style:边框线的风格{'dotted','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: 右斜线 bool14diagonalDown: 左斜线 bool15"""1617# a1.border = border18for item in ws['A1:E1'][0]:   # 去元组中的每一个cell更改样式19    item.border = border2021wb.save('./5a.xlsx')  # 保存更改

再次注意！！！：

• 不能使用 a1.border = border，否则只会如下图情况，B1：E1单元格没有线。我个人认为是因为线框涉及到相邻单元格边框的改动所以需要单独对每个单元格修改才行。

• 不能使用ws['A1:E1'].border = border,由前面的内容可知，openpyxl的多个单元格其实是一个元组，而元组是没有style的方法的,所以必须一个一个改！！其实官方有其他办法，后面讲。

image

只有A1边框有加粗

按列或行设置样式

 1# 现在我们对整个表进行设置 2 3# 读取 4wb = load_workbook('./5a.xlsx') 5ws = wb['5a_'] 6 7# 读取数据表格范围 8rows = ws.max_row 9cols = ws.max_column1011# 字体12font1 = Font(name='微软雅黑', size=11, b=True)13font2 = Font(name='微软雅黑', size=11)1415# 边框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)2122# 填充23fill = PatternFill('solid', fgColor='CFCFCF')2425# 对齐26alignment = Alignment(horizontal='center', vertical='center')2728# 将样式打包命名29sty1 = NamedStyle(name='sty1', font=font1, fill=fill,30                  border=border1, alignment=alignment)31sty2 = NamedStyle(name='sty2', font=font2, border=border2, alignment=alignment)3233for r in range(2, rows+1):34    for c in range(1, cols):35        if r == 2:36            ws.cell(r, c).style = sty137        else:38            ws.cell(r, c).style = sty23940wb.save('./5a.xlsx')

image

对于，设置标题样式，其实官方也给出了一个自定义函数(链接阅读原文),设定范围后，范围内的单元格都会合并，并且应用样式，就像是单个cell一样。在这里就不多赘述了，有兴趣的可以看看。很实用。

原著是一个有趣的人,若有侵权,请通知删除