最近一直在做项目里的自动化的工作, 为了是从繁琐重复的劳动中挣脱出来, 把精力用在数据分析上. 自动化方面 python 是在好不过了, 不过既然要提交报表,
就不免要美观什么的. pandas 虽然很强大, 但是无法对 Excel 完全操作, 现学 vba 有点来不及. 于是就找到这个 openpyxl 包, 用 python 来修改 Excel, 碍于水平有限, 琢磨了两天, 踩了不少坑, 好在完成了自动化工作(以后起码多出来几个小时, 美滋滋).
在这里写下这两天的笔记和踩得坑, 方面新手躲坑, 也供自己日后查阅. 如有问题, 还请见谅并指出, 多谢.
1fromopenpyxlimportload_workbook
2fromopenpyxl.stylesimportcolors, Font, Fill, NamedStyle
3fromopenpyxl.stylesimportPatternFill, Border, Side, Alignment
- # 加载文件
- wb = load_workbook('./5a.xlsx')
workbook: 工作簿, 一个 Excel 文件包含多个 sheet.
worksheet: 工作表, 一个 workbook 有多个, 表名识别, 如 "sheet1","sheet2" 等.
cell: 单元格, 存储数据对象
文章所用表格为:
操作 sheet
- # 读取 sheetname
- print('输出文件所有工作表名:', wb.sheetnames)
- ws = wb['5a']
- # 或者不知道名字时
- sheet_names = wb.sheetnames
- ws2 = wb[sheet_names[0]]# index 为 0 为第一张表
- print(wsisws2)
输出文件所有工作表名:
- ['5a']
- True
- 1# 修改 sheetname
- 2
- 3ws.title ='5a_'
- 4print('修改 sheetname:', wb.sheetnames)
修改 sheetname:
- ['5a_']
- 1# 创建新的 sheet
- 2# 创建的新表必须要赋值给一个对象, 不然只有名字但是没有实际的新表
- 3
- 4ws4 = wb.create_sheet(index=0, title='newsheet')
- 5# 什么参数都不写的话, 默认插入到最后一个位置且名字为 sheet,sheet1... 按照顺序排列
- 6
- 7ws5 = wb.create_sheet()
- 8print('创建新的 sheet:', wb.sheetnames)
创建新的 sheet:
- ['newsheet', '5a_', 'Sheet']
- 1# 删除 sheet
- 2wb.remove(ws4)# 这里只能写 worksheet 对象, 不能写 sheetname
- 3print('删除 sheet:', wb.sheetnames)
删除 sheet:
- ['5a_', 'Sheet']
- 1# 修改 sheet 选项卡背景色, 默认为白色, 设置为 RRGGBB 模式
- 2ws.sheet_properties.tabColor ="FFA500"
- 3
- 4# 读取有效区域
- 5
- 6print('最大列数为:', ws.max_column)
- 7print('最大行数为:', ws.max_row)
最大列数为: 5
最大行数为: 17
- # 插入行和列
- ws.insert_rows(1)# 在第一行插入一行
- ws.insert_cols(2,4)# 从第二列开始插入四列
- # 删除行和列
- ws.delete_cols(6,3)# 从第六列 (F 列) 开始, 删除 3 列即(F:H)
- ws.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_range
- 10# 注意!! 这里是两层元组嵌套, 每一行的单元格位于同一个元组里.
- ((<Cell '5a_'.A1>, <Cell '5a_'.B1>), (<Cell '5a_'.A2>, <Cell '5a_'.B2>))
- 1# 按照行列操作
- 2forrowinws.iter_rows(min_row=1, max_row=3,
- 3min_col=1, max_col=2):
- 4forcellinrow:
- 5print(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)
样式修改
单个单元格样式
- wb = load_workbook('./5a.xlsx')# 读取修改后的文件
- ws = wb['5a_']
- # 我们来设置一个表头
- ws.insert_rows(1)# 在第一行插入新的一行
- ws.merge_cells('A1:E1')# 合并单元格
- a1 = ws['A1']
- ws['A1'] ='5A 级风景区名单'
- # 设置字体
- ft = Font(name='微软雅黑', color='000000', size=15, b=True)
- """
- name: 字体名称
- color: 颜色通常是 RGB 或 aRGB 十六进制值
- b(bold): 加粗(bool)
- i(italic): 倾斜(bool)
- shadow: 阴影(bool)
- underline: 下划线('doubleAccounting', 'single', 'double', 'singleAccounting')
- charset: 字符集(int)
- strike: 删除线(bool)
- """
- a1.font = ft
- # 设置文本对齐
- ali = Alignment(horizontal='center', vertical='center')
- """27horizontal: 水平对齐('centerContinuous','general','distributed',
- 'left', 'fill', 'center', 'justify', 'right')
- vertical: 垂直对齐('distributed', 'top', 'center', 'justify', 'bottom')
- """
- a1.alignment = ali
- # 设置图案填充
- fill = PatternFill('solid', fgColor='FFA500')
- # 颜色一般使用十六进制 RGB
- # 'solid'是图案填充类型, 详细可查阅文档
- a1.fill = fill
openpyxl.styles.fills 模块参数文档(链接阅读原文)
- # 设置边框
- bian = Side(style='medium', color='000000')# 设置边框样式
- """4style: 边框线的风格{'dotted','slantDashDot','dashDot','hair','mediumDashDot',
- 'dashed','mediumDashed','thick','dashDotDot','medium',
- 'double','thin','mediumDashDotDot'}
- """
- border = Border(top=bian, bottom=bian, left=bian, right=bian)
- """
- top(上),bottom(下),left(左),right(右): 必须是 Side 类型
- diagonal: 斜线 side 类型
- diagonalDownd: 右斜线 bool
- diagonalDown: 左斜线 bool
- """
- # a1.border = border
- foriteminws['A1:E1'][0]:# 去元组中的每一个 cell 更改样式
- item.border = border
- wb.save('./5a.xlsx')# 保存更改
再次注意!!!:
- # 现在我们对整个表进行设置
- # 读取
- wb = load_workbook('./5a.xlsx')
- ws = wb['5a_']
- # 读取数据表格范围
- rows = ws.max_row
- cols = ws.max_column
- # 字体
- font1 = Font(name='微软雅黑', size=11, b=True)
- font2 = Font(name='微软雅黑', size=11)
- # 边框
- line_t = Side(style='thin', color='000000')# 细边框
- line_m = Side(style='medium', color='000000')# 粗边框
- border1 = Border(top=line_m, bottom=line_t, left=line_t, right=line_t)
- # 与标题相邻的边设置与标题一样
- border2 = Border(top=line_t, bottom=line_t, left=line_t, right=line_t)
- # 填充
- fill = PatternFill('solid', fgColor='CFCFCF')
- # 对齐
- alignment = Alignment(horizontal='center', vertical='center')
- # 将样式打包命名
- sty1 = NamedStyle(name='sty1', font=font1, fill=fill,
- border=border1, alignment=alignment)
- sty2 = NamedStyle(name='sty2', font=font2, border=border2, alignment=alignment)
- forrinrange(2, rows+1):
- forcinrange(1, cols):
- ifr ==2:
- ws.cell(r, c).style = sty1
- else:
- ws.cell(r, c).style = sty2
- wb.save('./5a.xlsx')
来源: http://www.bubuko.com/infodetail-2984185.html