真正的勇士，敢于直面银行卡上的余额，敢于正视磅秤上的数字。

表格数据是最常见的数据类型，Excel是日常办公中最常接触的文件。

上一章提过Python可以通过openpyxl模块处理Excel文件。

其实Python处理Excel的三方模块主要有这几个：

  1. xlrd、xlwt和xlutils，支持读写Excel文件。
  2. openpyxl，支持读写和修改OpenXML格式的Excel文件。
  3. xlswriter，支持写文件，以及更多Excel功能特性。
  4. pandas，底层依赖xlrd和xlwt读写Excel文件，处理数据神器。
  5. pywin32，调用Win32 API操作文件，只适用于Windows。
  6. xlwings，可理解为pywin32的多平台加强版，有商业版支持。

简单做个对比分析：

  • 第1类中3个模块，属于同一作者的开源项目，目前已停更，不建议直接使用。
  • pywin32和xlwings基于原生Excel软件提供服务，需提前安装软件，读写性能和功能最佳，xlwings还有商业版本支持。
  • pandas侧重数据分析，Excel文件读写只是其数据输入和输出部分。
  • openpyxl不需要依赖Excel功能，直接读写OpenXML内容，兼容性好。
  • xlswriter侧重写Excel文件，支持VBA等更多特性。

建议的使用场景：

  1. 如果企业内大量使用Excel，并用了很多如VBA宏的特性，建议用xlwings。一方面功能全，另一方面可以采买商业付费版支持。
  2. 对于普通Excel文件自动处理，建议用openpyxl，大部分情况下够用。
  3. GB级以下的数据分析，建议用pandas，后续会单独出一个数据分析系列来介绍它。

本文重点介绍openpyxl的使用，它足够应付大部分人日常的表格自动化处理场景。

日常处理Excel文件，最常见的应用场景有3类：

  • 读写Excel文件
  • 合并、分割文档数据
  • 报表自动化，批量生成图表

模块安装：pip install openpyxl

基本使用

Excel表格文件中主要元素有这几个：

  • sheet（工作表）：一个Excel文件可以有多个sheet。
  • row/col（行和列），iter_rows()和iter_cols()可以返回所有行和列。
  • cell（单元格）：和Excel一样用sheet['A1']表示，注意行列都以1开始。
  • formulae（公式）：可以为单元格设置计算公式，和Excel概念一致。
  • charts（图表）：比如饼图、柱状图等。
  • style（样式）：支持自定义样式，以及条件格式化等。
  • image（图片），支持pillow的图像处理。

打开Excel文件

  1. import pathlib
  2. import datetime
  3. from openpyxl import load_workbook
  5. path = list(pathlib.Path.cwd().parents)[1].joinpath('data/automate/004excel')
  6. in_path = path.joinpath('input.xlsx')
  7. wb = load_workbook(in_path, read_only=True)
  8. for name in wb.sheetnames:
  9.     ws = wb[name]
  10.     print(f'{name} 表有 {ws.max_row-ws.min_row+1} 行, {ws.max_column-ws.min_column+1} 列.')

读取Excel文件内的数据

  1. import pathlib
  2. import datetime
  3. from openpyxl import load_workbook
  5. path = list(pathlib.Path.cwd().parents)[1].joinpath('data/automate/004excel')
  6. in_path = path.joinpath('input.xlsx')
  7. wb = load_workbook(in_path)
  8. ws = wb[wb.sheetnames[0]] # 第一个工作表
  9. # 操作单列
  10. for cell in ws["A"]:
  11.     print(cell.value)
  12. # 操作单行
  13. for cell in ws["1"]:
  14.     print(cell.value, end='\t')
  15. # 操作多列
  16. for column in ws['A:C']:
  17.     for cell in column:
  18.         print(cell.value)
  19. # 操作多行
  20. for row in ws['1:3']:
  21.     for cell in row:
  22.         print(cell.value, end='\t')
  23.     print()
  24. # 指定范围
  25. for row in ws['A1:C3']:
  26.     for cell in row:
  27.         print(cell.value)
  28. # 所有行
  29. for row in ws.iter_rows():
  30.     for cell in row:
  31.         print(cell.value)
  32. # 所有列
  33. for column in ws.iter_cols():
  34.     for cell in column:
  35.         print(cell.value)

创建Excel文件

  1. import pathlib
  2. import datetime
  3. from openpyxl import Workbook
  5. path = list(pathlib.Path.cwd().parents)[1].joinpath('data/automate/004excel')
  6. out_path = path.joinpath('004excel_basic.xlsx')
  7. sheet_path = path.joinpath('004excel_sheet.xlsx')
  8. wb = Workbook()
  9. ws = wb.active # 找当前Sheet
  10. ws['A1'] = 42
  11. ws.append([1, 2, 3]) # 添加行
  12. ws['A2'] = datetime.datetime.now()
  13. wb.save(out_path)
  14. # 创建第二个sheet
  15. wb.create_sheet('sheet2')
  16. ws = wb['sheet2']
  17. ws.title = '第二个工作表'
  18. ws.sheet_properties.tabColor = "FF0000"
  19. # 增加一整行数据
  20. ws.append([1, 2, 3, datetime.datetime.now()])
  21. ws.cell(row=1, column=3, value=10)
  22. # 获取cell方式一
  23. d1 = ws.cell(row=1, column=4)
  24. # 获取cell方式二
  25. d2 = ws['D1']
  26. print(d1==d2)
  27. print(type(d2.value))
  28. wb.save(sheet_path)

设置单元格样式

单元格样式可以用NumberFormat（数据格式）、Alignment（对齐）、Font（字体）、Border（边框）、PatternFill（填充）、Protection（保护）等来设置。

也可以通过NamedStyle自定义样式，重复应用到单元格。

此外，通过row_dimensions和column_dimensions可以设置整行/整列的样式。

  1. import pathlib
  2. import datetime
  3. from openpyxl import Workbook
  4. from openpyxl.styles import numbers, Font, GradientFill, PatternFill, Border, Side, Alignment, Protection
  5. from openpyxl.styles import NamedStyle
  7. path = list(pathlib.Path.cwd().parents)[1].joinpath('data/automate/004excel')
  8. out_path = path.joinpath('004excel_style.xlsx')
  9. wb = Workbook()
  10. ws = wb.active
  11. ws.cell(row=1, column=1, value='微软雅黑').font = Font(name='微软雅黑', size=18, bold=True, color='123456')
  12. ws.cell(row=2, column=1, value='居中对齐').alignment = Alignment(horizontal='center', vertical='center')
  13. ws.cell(row=3, column=1, value='填充渐变色').fill = GradientFill(stop=['FF0000', '0000FF'])
  14. ws.cell(row=4, column=1, value='设置边线').border = Border(left=Side(border_style='thin', color='123456'), right= Side(border_style='thick', color='FF0000'))
  15. ws.cell(row=5, column=1, value='受保护的').protection = Protection(locked=True, hidden=True)
  16. ws.cell(row=6, column=1, value=0.88).number_format =numbers.FORMAT_PERCENTAGE
  17. ws.column_dimensions['A'].width = 80 # 修改列宽
  18. # 设置整行
  19. row = ws.row_dimensions[4]
  20. row.font = Font(name='宋体', size=30, italic=True, color='FF0000')
  21. # 设置整列
  22. column = ws.column_dimensions["B"]
  23. column.fill = PatternFill("solid", fgColor="1874CD")
  24. # 自定义样式
  25. my_style = NamedStyle(name='my_style',
  26.                            font=Font(name='宋体',size=12, color='FF000000'),
  27.                            fill=PatternFill(fill_type='solid', fgColor="FFDAB9"),
  28.                            border=Border(top=Side(border_style='thin', color='FF000000')),
  29.                            alignment=Alignment(horizontal='left',vertical='center'))
  30. ws['B4'] = '自定义样式'
  31. ws['B4'].style=my_style
  32. wb.save(out_path)

合并单元格

  • 合并单元格：merge_cells()
  • 解除合并：unmerge_cells()

注意：对于没有合并过单元格的位置调用unmerge_cells()会出错；此外合并的单元格顺序是从左上角到右下角。

  1. import pathlib
  2. import datetime
  3. from openpyxl import Workbook
  4. from openpyxl.styles import numbers, Font, GradientFill, Border, Side, Alignment, Protection
  6. path = list(pathlib.Path.cwd().parents)[1].joinpath('data/automate/004excel')
  7. out_path = path.joinpath('004excel_cell_merge.xlsx')
  9. wb = Workbook()
  10. ws = wb.active
  11. # 合并,必须是从左上角，到右下角的顺序
  12. ws.merge_cells('A3:D5')
  13. ws.merge_cells('A7:D10')
  14. # 合并后以起点为代表读写值，其他的cell都被设置为只读
  15. ws['A3']='test'
  16. # 解除合并
  17. ws.unmerge_cells('A7:D10')
  18. # 也可以用起止行列数字来选取合并区域
  19. ws.merge_cells(start_row=12,start_column=11,end_row=15,end_column=14)
  20. ws['K12']='合并过'
  21. ws.unmerge_cells(start_row=12,start_column=11,end_row=15,end_column=14)
  22. wb.save(out_path)

图表和公式应用

openpyxl支持的图表都在openpyxl.chart包中，比如直方图、饼图、折线图等。

可以直接把单元格的值设置为公式，在Excel软件中可以获得结果，但openpyxl并不支持公式的计算。

如果生成的xlsx文件被Excel软件打开过，这时公式的计算结果会缓存在文件中，这时用openpyxl打开文件时候，增加一个data_only=True参数，也可以读取到计算结果。

  1. import pathlib
  2. import datetime
  3. from openpyxl import Workbook
  4. from openpyxl.chart import BarChart, PieChart, LineChart, Reference
  6. path = list(pathlib.Path.cwd().parents)[1].joinpath('data/automate/004excel')
  7. out_path = path.joinpath('004excel_graph.xlsx')
  8. wb = Workbook()
  9. ws = wb.active
  10. # 装载数据
  11. data = [
  12.     ('月份', '产品A销量', '产品B销量'),
  13.     (1, 100, 200),
  14.     (2, 106, 100),
  15.     (3, 120, 200),
  16.     (4, 180, 100),
  17.     (5, 200, 300),
  18.     (6, 280, 400),
  19.     (7, 400, 200),
  20.     (8, 500, 800),
  21.     (9, 600, 1000),
  22.     (10, 800, 300),
  23. ]
  24. for d in data:
  25.     ws.append(d)
  26. # 用Python做些统计
  27. ws['A12'] = '汇总累加'
  28. ws['B12'] = sum([d[1] for i, d in enumerate(data) if i > 0])
  29. ws['C12'] = sum([d[2] for i, d in enumerate(data) if i > 0])
  30. # 用公式统计
  31. ws['A13'] = '公式累加'
  32. ws['B13'] = '=SUM(B2:B11)'
  33. ws['C13'] = '=SUM(C2:C11)'
  34. # 增加一列总销量
  35. ws['D1'] = '当月总销量'
  36. for i in range(2, 13):
  37.     ws[f'D{i}'] = ws[f'C{i}'].value + ws[f'B{i}'].value
  38. # 列状图
  39. ct_bar= BarChart()
  40. ct_bar.type = "col" # 列状图
  41. ct_bar.style = 10
  42. ct_bar.title = "销量柱状图"
  43. ct_bar.y_axis.title = '销量'
  44. ct_bar.x_axis.title = '月份'
  45. d_ref = Reference(ws, min_col=2, min_row=1, max_row=11, max_col=3)
  46. series = Reference(ws, min_col=1, min_row=2, max_row=11)
  47. ct_bar.add_data(d_ref, titles_from_data=True)
  48. ct_bar.set_categories(series)
  49. ws.add_chart(ct_bar, 'A15')
  50. # 饼图
  51. ct_pie = PieChart()
  52. ct_pie.title = "产品销量占比"
  53. d_ref = Reference(ws, min_col=2, min_row=12, max_col=3)
  54. series = Reference(ws, min_col=2, min_row=1, max_col=3)
  55. ct_pie.add_data(d_ref, titles_from_data=True)
  56. ct_pie.set_categories(series)
  57. ct_pie.splitType='val'
  58. ws.add_chart(ct_pie, 'I15')
  59. # 一个图两个轴
  60. ct_bar = BarChart()
  61. d_ref = Reference(ws, min_col=2, min_row=1, max_row=11, max_col=3)
  62. ct_bar.add_data(d_ref, titles_from_data=True)
  63. ct_bar.x_axis.title = '月份'
  64. ct_bar.y_axis.title = '产品AB销量'
  65. ct_bar.y_axis.majorGridlines = None
  66. ct_bar.title = '销量分析'
  67. ct_line = LineChart()
  68. d_ref = Reference(ws, min_col=4, min_row=1, max_row=11)
  69. ct_line.add_data(d_ref, titles_from_data=True)
  70. ct_line.y_axis.axId = 200 # 不为空即可
  71. ct_line.y_axis.title = '总销量'
  72. # 让线条和第一图的最大值相交
  73. ct_line.y_axis.crosses = 'max'
  74. ct_bar += ct_line # 只支持+=赋值，不能直接+
  75. ws.add_chart(ct_bar, 'A31')
  76. wb.save(out_path)

插入图像

openpyxl的图像处理基于PIL.Image。

PIL.Image的宽和高单位默认使用Pixel（像素），如果需要映射到Excel中的位置，可以用pixels_to_EMU()转换。

  1. import pathlib
  2. from openpyxl import Workbook, load_workbook
  3. from openpyxl.drawing.image import Image
  4. from openpyxl.drawing.spreadsheet_drawing import AbsoluteAnchor
  5. from openpyxl.drawing.xdr import XDRPoint2D, XDRPositiveSize2D
  6. from openpyxl.utils.units import pixels_to_EMU as p2e
  8. path = list(pathlib.Path.cwd().parents)[1].joinpath('data/automate/004excel')
  9. out_path = path.joinpath('004excel_image.xlsx')
  10. img_path = path.joinpath('image.jpg')
  11. wb = Workbook()
  12. ws = wb.active
  13. img = Image(img_path)
  14. img.width /= 2
  15. img.height /= 2
  16. h, w = img.height, img.width
  17. # 绝对定位图片
  18. position = XDRPoint2D(p2e(800), p2e(800))
  19. size = XDRPositiveSize2D(p2e(h), p2e(w))
  20. img.anchor = AbsoluteAnchor(pos=position, ext=size)
  21. ws.add_image(img)
  22. # 单元格定位
  23. img = Image(img_path)
  24. size = (90, 90)
  25. img.width, img.height = size
  26. ws.row_dimensions[3].height=90
  27. ws.column_dimensions['B'].width=90
  28. ws.add_image(img, 'B3')
  29. wb.save(out_path)

总结

本文主要介绍了使用openpyxl模块处理Excel文件，包括基本的Excel文件读写、单元格样式处理、图表生成和图像结合等。

用Excel应对日常工作中的小规模数据足以。

比如实践应用中有一个“多门店加盟品牌数据统计”的案例，其中正好用到了上面的各项知识点。

4、Excel 文件处理 - 图1

案例说明：

  • 以Python和openpyxl应用实践为主。
  • 提供解决问题思路，实战中可直接应用。
  • 后续“数据分析专题”会介绍Pandas等数据分析工具，一个实战应用更灵活的模块。

加入学习群

4、Excel 文件处理 - 图2