devbox
你好赵伟
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

python3 openpyxl操作excel

作者：你好赵伟 | 2024-07-31 21:17:29

赞

踩

pip install openpyxl==3.0.0

需求

在日常工作中，避免不了需要操作excel文件的情况，如果还带有需要对excel的内容进行格式设定、合并单元格等需求，那么可以使用openxl来解决处理。

例如：本次的需求需要生成如下的一份压测excel数据报告如下：

13423234-247f595a6b32eaa3.png

openxl官方文档

https://openpyxl.readthedocs.io/en/stable/
https://openpyxl.readthedocs.io/en/stable/usage.html

安装openpyxl

pip3 install openpyxl==3.0.0

创建excel文件的示例代码

  1. if __name__ == '__main__':
  2.  
  3.     from openpyxl import Workbook
  4.     wb = Workbook()
  5.  
  6.     # grab the active worksheet
  7.     ws = wb.active
  8.  
  9.     # Data can be assigned directly to cells
  10.     ws['A1'] = 42
  11.  
  12.     # Rows can also be appended
  13.     ws.append([1, 2, 3])
  14.  
  15.     # Python types will automatically be converted
  16.     import datetime
  17.  
  18.     ws['A2'] = datetime.datetime.now()
  19.  
  20.     # Save the file
  21.     wb.save("sample.xlsx")

执行生成的excel文件如下：

13423234-e7c6ac40a53c934d.png

下面不着急，逐个执行一下官网教程中的示例看看。

官网示例

创建编写excel ( Write a workbook )

  1.  
  2. def main():
  3.     from openpyxl import Workbook
  4.     from openpyxl.utils import get_column_letter # 引入获取列字母的方法
  5.  
  6.     wb = Workbook() # 创建Workbook
  7.     dest_filename = 'empty_book.xlsx' # 设置保存的空xlsx文件名
  8.  
  9.     # 创建第一个sheet
  10.     ws1 = wb.active
  11.     ws1.title = "range names" # 设置sheet name
  12.  
  13.     # 往第一个sheet写入40行 区间0-600 的数据
  14.     for row in range(1, 40):
  15.         ws1.append(range(600))
  16.  
  17.     ws1.append(['1','2','3']) # 在最后的行再增加一行1，2，3
  18.  
  19.     # 创建第二个sheet，以及同时设置sheet name为 Pi
  20.     ws2 = wb.create_sheet(title="Pi")
  21.     ws2['F5'] = 3.14 # 设置F5的单元格数值为 3.14
  22.  
  23.     # 创建第三个sheet，以及设置sheet name 为 Data
  24.     ws3 = wb.create_sheet(title="Data")
  25.  
  26.     for row in range(10, 20): # 设置10~20行数据
  27.         for col in range(10, 54): # 设置 10~54 列数据
  28.             _ = ws3.cell(column=col, row=row, value="{0}".format(get_column_letter(col)))
  29.             print('col = {}, row = {}, value = {}'.format( col, row, get_column_letter(col) ))
  30.  
  31.     print(ws3['AA10'].value) # 打印 AA10 单元格的数据， 为：AA
  32.  
  33.     wb.save(filename=dest_filename) # 保存excel文件
  34.  
  35. if __name__ == '__main__':
  36.     main()
  37.

生成的excel如下：

13423234-bc29f0a7caaff470.png

在第一张sheet表中，从左向右设置0-599的数字。
在这里可以看出append()方法可以在最下面的一行开始增加数据，而数据的填充可以使用range或者list

13423234-433e321bd2ee325e.png

在第二张sheet表中，特定的F5单元格设置了3.14
在在这里可以看到特定的单元格值的设置可以使用 ws[单元格编号] 来进行设置。

13423234-f8c68e31692c49e1.png

第三张sheet表中，批量设置对应行数以及列数的值。

读取已存在的excel文件数据 （Read an existing workbook）

  1. def main():
  2.     from openpyxl import load_workbook
  3.     wb = load_workbook(filename='empty_book.xlsx')
  4.     sheet_ranges = wb['range names']
  5.     print(sheet_ranges['D18'].value)
  6.  
  7. if __name__ == '__main__':
  8.     main()

执行结果如下：

13423234-c41620238276b683.png

打开excel确认值如下：

13423234-4edcb5e70ddbc90b.png

使用格式化数据（Using number formats）

  1. def main():
  2.     import datetime
  3.     from openpyxl import Workbook
  4.     wb = Workbook()  # 创建Workbook
  5.     dest_filename = 'format_book.xlsx'
  6.  
  7.     ws = wb.active
  8.      # set date using a Python datetime
  9.     ws['A1'] = datetime.datetime.now()
  10.     print(ws['A1'].number_format)
  11.  
  12.     wb.save(filename=dest_filename) # 保存excel文件
  13.  
  14. if __name__ == '__main__':
  15.     main()

执行结果如下：

13423234-b9a28986239a6c44.png

生成excel如下：

13423234-e054b2fce30c0f80.png

使用excel的公式（Using formulae）

这里再来一个使用excel公式的方法。

  1. def main():
  2.     from openpyxl import Workbook
  3.     wb = Workbook()
  4.     ws = wb.active
  5.      # add a simple formula
  6.     ws["A1"] = "=SUM(1, 1)"
  7.     wb.save("formula.xlsx")
  8.  
  9. if __name__ == '__main__':
  10.     main()

执行生成excel如下：

13423234-32042e75dd7c2a22.png

合并/拆分单元格的示例 （Merge / Unmerge cells）

  1. def main():
  2.     from openpyxl.workbook import Workbook
  3.  
  4.     wb = Workbook()
  5.     ws = wb.active
  6.  
  7.     # 合并A1至D1 四个单元格并赋值
  8.     ws['A1'] = '合并A1至D1 四个单元格并赋值'
  9.     ws.merge_cells('A1:D1')
  10.  
  11.     # 合并A3至D3 四个单元格，然后再拆分，确认是否恢复
  12.     ws['A3'] = '合并A3至D3 四个单元格，然后再拆分，确认是否恢复'
  13.     ws.merge_cells('A3:D3')
  14.     ws.unmerge_cells('A3:D3')
  15.  
  16.     # 合并多行多列单元格,从第五行开始，合并至第九行，从第一列开始，合并至第四列
  17.     ws['A5'] = '合并多行多列单元格,从第五行开始，合并至第九行，从第一列开始，合并至第四列'
  18.     ws.merge_cells(start_row=5, start_column=1, end_row=9, end_column=4)
  19.  
  20.     # 拆分多行多列
  21.     ws['A13'] = '首先合并，然后再拆分'
  22.     ws.merge_cells(start_row=13, start_column=1, end_row=16, end_column=4)
  23.     ws.unmerge_cells(start_row=13, start_column=1, end_row=16, end_column=4)
  24.  
  25.     wb.save("merge_unmerge.xlsx")
  26.  
  27. if __name__ == '__main__':
  28.     main()

执行生成excel如下：

13423234-4370feab45bad4ed.png

插入图片示例 （Inserting an image）

  1. def main():
  2.     from openpyxl import Workbook
  3.     from openpyxl.drawing.image import Image
  4.  
  5.     wb = Workbook()
  6.     ws = wb.active
  7.     ws['A1'] = 'You should see three logos below'
  8.  
  9.     # create an image
  10.     img = Image('logo.jpg')
  11.  
  12.     # add to worksheet and anchor next to cells
  13.     ws.add_image(img, 'A2')
  14.     wb.save('logo.xlsx')
  15.  
  16. if __name__ == '__main__':
  17.     main()

执行生成excel如下：

13423234-b5ec4f6980150501.png

设置excel中的group （Fold (outline)）

  1. def main():
  2.    import openpyxl
  3.    wb = openpyxl.Workbook()
  4.    ws = wb.create_sheet()
  5.    ws.column_dimensions.group('A', 'D', hidden=True)
  6.    ws.row_dimensions.group(1, 10, hidden=True)
  7.    wb.save('group.xlsx')
  8.  
  9. if __name__ == '__main__':
  10.     main()

执行如下：

13423234-3c9d5c166bc2066d.png

只读模式（Read-only mode）

通过在创建workbook的时候，设置read_only=True的参数，设置为只读模式的状态。

  1. from openpyxl import load_workbook
  2. wb = load_workbook(filename='large_file.xlsx', read_only=True)
  3. ws = wb['big_data']
  4.  
  5. for row in ws.rows:
  6.     for cell in row:
  7.         print(cell.value)

只写模式（Write-only mode）

  1. def main():
  2.     from openpyxl import Workbook
  3.     wb = Workbook(write_only=True) # 设置只写模式
  4.     ws = wb.create_sheet()
  5.  
  6.     # now we'll fill it with 100 rows x 200 columns
  7.     for irow in range(100):
  8.         ws.append(['%d' % i for i in range(200)])
  9.  
  10.     # save the file
  11.     wb.save('new_big_file.xlsx')  # doctest: +SKIP
  12.  
  13. if __name__ == '__main__':
  14.     main()

生成的excel如下：

13423234-db8ab8b0b1450bcc.png

在只写模式下，设置Cell单元格的字体以及备注说明

  1. def main():
  2.     from openpyxl import Workbook
  3.     wb = Workbook(write_only = True)
  4.     ws = wb.create_sheet()
  5.  
  6.     from openpyxl.cell import WriteOnlyCell
  7.     from openpyxl.comments import Comment
  8.     from openpyxl.styles import Font
  9.  
  10.     cell = WriteOnlyCell(ws, value="hello world")
  11.     cell.font = Font(name='Courier', size=36)
  12.     cell.comment = Comment(text="A comment", author="Author's Name")
  13.     ws.append([cell, 3.14, None])
  14.     wb.save('write_only_file.xlsx')
  15.  
  16. if __name__ == '__main__':
  17.     main()

生成excel如下：

13423234-0aa2f9d1c5b8f0e0.png

删除excel表格中的行和列

插入行和列数据（Inserting rows and columns）

可以使用以下方法插入excel中行和列的数据：

这个默认查询是一行一列的，如果需要插入第7行，示例如下：

>>> ws.insert_rows(7)

直接这样不直观，下面来写一个示例：

  1. def main():
  2.     from openpyxl import Workbook
  3.     wb = Workbook()
  4.     ws = wb.create_sheet('test_insert')
  5.  
  6.     # 往第一个sheet写入40行 区间0-600 的数据
  7.     for row in range(1, 40):
  8.         ws.append(range(600))
  9.  
  10.     ws.insert_rows(7) # 在第7行插入空白的一行
  11.  
  12.     wb.save('write_only_file.xlsx')
  13.  
  14. if __name__ == '__main__':
  15.     main()

查看生成的excel如下：

13423234-be22018292197c8f.png

可以看到其实就是excel中插入一行的效果。

删除行和列 （Deletinng rows and columns）

  1. delete_rows(self, idx, amount=1) # 删除某行 idx为开始删除的行数，amount为后续需要继续删除的行数
  2. delete_cols(self, idx, amount=1) # 删除某列  idx为开始删除的列数，amount为后续需要继续删除的行数
  1. ws.delete_rows(idx=1, amount=2) # 删除前两行
  2. ws.delete_cols(idx=2,amount=1) # 删除第2列

示例如下：

  1. def main():
  2.     from openpyxl import Workbook
  3.     wb = Workbook()
  4.     ws = wb.create_sheet('test_insert')
  5.  
  6.     # 往第一个sheet写入40行 区间0-600 的数据
  7.     for row in range(1, 40):
  8.         ws.append(range(600))
  9.  
  10.     ws.insert_rows(7) # 在第7行插入空白的一行
  11.  
  12.     ws.delete_rows(idx=1, amount=2) # 删除前两行
  13.     ws.delete_cols(idx=2,amount=1) # 删除第2列
  14.  
  15.     wb.save('write_only_file.xlsx')
  16.  
  17. if __name__ == '__main__':
  18.     main()

在前面插入第7行的示例excel中，删除前两行，那么插入的7行就会变到5行，然后再删除第2列。下面来看看效果：

13423234-eba3848c6afaa014.png

移动单元格（Moving ranges of cells）

>>> ws.move_range("D4:F10", rows=-1, cols=2)

这将会将D4:F10的单元格上升一行，然后向左两列。这些单元格的数据将会覆盖旧数据。

>>> ws.move_range("G4:H10", rows=1, cols=1, translate=True)

如果移动的单元格数据还要带上公式，则可以加上translate=True的参数，默认都是false的。

图表（Charts）

Chart types

The following charts are available:

可以看到图表的类型挺多的，下面我就执行其中一个示例，如下：

  1. def main():
  2.     from openpyxl import Workbook
  3.     wb = Workbook()
  4.     ws = wb.active
  5.     for i in range(10):
  6.         ws.append([i])
  7.  
  8.     from openpyxl.chart import BarChart, Reference, Series
  9.     values = Reference(ws, min_col=1, min_row=1, max_col=1, max_row=10)
  10.     chart = BarChart()
  11.     chart.add_data(values)
  12.     ws.add_chart(chart, "E15")
  13.     wb.save("SampleChart.xlsx")
  14.  
  15. if __name__ == '__main__':
  16.     main()

生成图表如下：

13423234-7ca5fbcf8ab572e0.png

2D Area Charts

再来一个2D的区域图表如下：

  1. def main():
  2.     from openpyxl import Workbook
  3.     from openpyxl.chart import (
  4.         AreaChart,
  5.         Reference,
  6.         Series,
  7.     )
  8.  
  9.     wb = Workbook()
  10.     ws = wb.active
  11.  
  12.     rows = [
  13.         ['Number', 'Batch 1', 'Batch 2'],
  14.         [2, 40, 30],
  15.         [3, 40, 25],
  16.         [4, 50, 30],
  17.         [5, 30, 10],
  18.         [6, 25, 5],
  19.         [7, 50, 10],
  20.     ]
  21.  
  22.     for row in rows:
  23.         ws.append(row)
  24.  
  25.     chart = AreaChart()
  26.     chart.title = "Area Chart"
  27.     chart.style = 13
  28.     chart.x_axis.title = 'Test'
  29.     chart.y_axis.title = 'Percentage'
  30.  
  31.     cats = Reference(ws, min_col=1, min_row=1, max_row=7)
  32.     data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=7)
  33.     chart.add_data(data, titles_from_data=True)
  34.     chart.set_categories(cats)
  35.  
  36.     ws.add_chart(chart, "A10")
  37.  
  38.     wb.save("area.xlsx")
  39.  
  40. if __name__ == '__main__':
  41.     main()

生成excel如下：

13423234-03d9ca801465e0ca.png

其他的图表就根据官网的示例执行即可。值得注意的是有些3D图表有些问题，不过我个人2D的图表已经可以满足需求了。

设置excel的样式（Working with styles）

介绍

Styles are used to change the look of your data while displayed on screen. They are also used to determine the formatting for numbers.

Styles can be applied to the following aspects:

  • font to set font size, color, underlining, etc. 设置字符大小、颜色、下划线
  • fill to set a pattern or color gradient 填充图案和网格的颜色
  • border to set borders on a cell 设置单元格的边框
  • cell alignment 设置单元格居中、居上等位置
  • protection

The following are the default values 默认的样式参数

  1. >>> from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
  2. >>> font = Font(name='Calibri',
  3. ...                 size=11,
  4. ...                 bold=False,
  5. ...                 italic=False,
  6. ...                 vertAlign=None,
  7. ...                 underline='none',
  8. ...                 strike=False,
  9. ...                 color='FF000000')
  10. >>> fill = PatternFill(fill_type=None,
  11. ...                 start_color='FFFFFFFF',
  12. ...                 end_color='FF000000')
  13. >>> border = Border(left=Side(border_style=None,
  14. ...                           color='FF000000'),
  15. ...                 right=Side(border_style=None,
  16. ...                            color='FF000000'),
  17. ...                 top=Side(border_style=None,
  18. ...                          color='FF000000'),
  19. ...                 bottom=Side(border_style=None,
  20. ...                             color='FF000000'),
  21. ...                 diagonal=Side(border_style=None,
  22. ...                               color='FF000000'),
  23. ...                 diagonal_direction=0,
  24. ...                 outline=Side(border_style=None,
  25. ...                              color='FF000000'),
  26. ...                 vertical=Side(border_style=None,
  27. ...                               color='FF000000'),
  28. ...                 horizontal=Side(border_style=None,
  29. ...                                color='FF000000')
  30. ...                )
  31. >>> alignment=Alignment(horizontal='general',
  32. ...                     vertical='bottom',
  33. ...                     text_rotation=0,
  34. ...                     wrap_text=False,
  35. ...                     shrink_to_fit=False,
  36. ...                     indent=0)
  37. >>> number_format = 'General'
  38. >>> protection = Protection(locked=True,
  39. ...                         hidden=False)
  40. >>>

单元格样式和命名样式（Cell Styles and Named Styles）

在openpyxl中有单元格样式以及命名样式两种区分。

Cell Styles

Cell styles are shared between objects and once they have been assigned they cannot be changed. This stops unwanted side-effects such as changing the style for lots of cells when instead of only one.

单元格的样式在对象之间共享，一旦指定，就不能更改。这样可以避免不必要的副作用，例如在只改变一个单元格而改变许多单元格样式的情况。

  1. def main():
  2.     from openpyxl.styles import colors
  3.     from openpyxl.styles import Font, Color
  4.     from openpyxl import Workbook
  5.     import datetime
  6.  
  7.     wb = Workbook()
  8.     ws = wb.active
  9.  
  10.     a1 = ws['A1']
  11.     d4 = ws['D4']
  12.  
  13.     ft = Font(color=colors.RED) # 设置字体
  14.  
  15.     a1.font = ft 
  16.     ws['A1'] = datetime.datetime.now()
  17.  
  18.     d4.font = ft
  19.     ws['D4'] = 'd4内容'
  20.  
  21.  
  22.     # If you want to change the color of a Font, you need to reassign it::
  23.     a1.font = Font(color=colors.RED, italic=True)  # the change only affects A1
  24.  
  25.     wb.save("cell_styles.xlsx")
  26.  
  27. if __name__ == '__main__':
  28.     main()

如果要更改字体样式，可以看到就需要重新设置一个Font()类，生成的excel如下：

13423234-98c524083fe802cc.png

Copying styles

Styles can also be copied

Styles样式是可以使用copy()方法进行拷贝的。

  1. >>> from copy import copy
  2. >>>
  3. >>> ft1 = Font(name='Arial', size=14)
  4. >>> ft2 = copy(ft1)
  5. >>> ft2.name = "Tahoma"
  6. >>> ft1.name
  7. 'Arial'
  8. >>> ft2.name
  9. 'Tahoma'
  10. >>> ft2.size # copied from the
  11. 14.0

Basic Font Colors

Colors are usually RGB or aRGB hexvalues. The colors module contains some handy constants

字体的样式可以使用RGB或者aRGB 16进制的设置方式

  1. >>> from openpyxl.styles import Font
  2. >>> from openpyxl.styles.colors import RED
  3. >>> font = Font(color=RED)
  4. >>> font = Font(color="FFBB00")

There is also support for legacy indexed colors as well as themes and tints

另外还支持设置传统的索引颜色以及主题和色调

  1. >>> from openpyxl.styles.colors import Color
  2. >>> c = Color(indexed=32)
  3. >>> c = Color(theme=6, tint=0.5)

下面来设置一个字体颜色看看，如下：

  • 首先找到excel中一个背景色的颜色RGB数值


    13423234-c5900ae375fc3309.png

  • 然后将RGB数值转为16进制

访问转换网址，如下：
https://www.sioe.cn/yingyong/yanse-rgb-16/

13423234-81841d5e4db79b4e.png
  • 设置字体颜色代码如下：
  1.     # 设置字体颜色以及主题
  2.     ws['A3'].font = Font(color="00807E")
  3.     ws['A3'] = '使用16进制格式设置颜色'
  • 生成excel如下：
13423234-05432cd5034c5b8e.png

Applying Styles

应用样式设置字体大小、粗体、下划线

  1. def main():
  2.     from openpyxl.workbook import Workbook
  3.     from openpyxl.styles import Font, Fill
  4.     wb = Workbook()
  5.     ws = wb.active
  6.  
  7.     a1 = ws['A1']
  8.     a1.font = Font(size=12, bold=True)
  9.     ws['A1'] = '设置字体大小为12,粗体'
  10.  
  11.     a2 = ws['A2']
  12.     a2.font = Font(size=24,underline="single")
  13.     ws['A2'] = '设置字体大小24，下划线'
  14.  
  15.     wb.save("14_apply_styles.xlsx")
  16.  
  17. if __name__ == '__main__':
  18.     main()

生成excel如下：

13423234-6e33a32169688328.png

Styling Merged Cells

The merged cell behaves similar to other cell ojects. Its value and format is defined in its top-left cell. In order to change the border of the whole merged cell, change the border of its top-left cell. The formatting is generated for the purpose of writing.

合并单元格的样式类似于单个单元格的设置方式。合并单元格的值以及格式取决于左上角的单元格。

  1. def main():
  2.     from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment
  3.     from openpyxl import Workbook
  4.  
  5.     wb = Workbook()
  6.     ws = wb.active
  7.     ws.merge_cells('B2:F4') # 合并 B2:F4 的单元格
  8.  
  9.     top_left_cell = ws['B2'] # 设置合并单元格的左上角单元格
  10.     top_left_cell.value = "My Cell"
  11.  
  12.     thin = Side(border_style="thin", color="000000") 
  13.     double = Side(border_style="double", color="ff0000")
  14.  
  15.     # 设置左上角样式
  16.     top_left_cell.border = Border(top=double, left=thin, right=thin, bottom=double) # 设置边框 
  17.     top_left_cell.fill = PatternFill("solid", fgColor="DDDDDD") # 设置背景色填充
  18.     top_left_cell.fill = GradientFill(stop=("000000", "FFFFFF")) # 设置渐变色
  19.     top_left_cell.font = Font(b=True, color="FF0000") # 设置字体的颜色
  20.     top_left_cell.alignment = Alignment(horizontal="center", vertical="center") # 设置内容的居中
  21.  
  22.     wb.save("15_styles.xlsx")
  23.  
  24. if __name__ == '__main__':
  25.     main()

生成的excel如下：

13423234-27f0192f7f54dae9.png

Named Styles

In contrast to Cell Styles, Named Styles are mutable. They make sense when you want to apply formatting to lots of different cells at once. NB. once you have assigned a named style to a cell, additional changes to the style will not affect the cell.

与单元格样式相反，命名样式是可变的。当你想同时对许多不同的单元格应用格式时，它们是有意义的。注意。将命名样式指定给单元格后，对样式所做的其他更改将不会影响单元格。

Once a named style has been registered with a workbook, it can be referred to simply by name.

一旦命名样式注册到工作簿中，就可以简单地按名称引用它。

Creating a Named Style

  1. def main():
  2.     from openpyxl.styles import NamedStyle, Font, Border, Side
  3.     from openpyxl import Workbook
  4.  
  5.     wb = Workbook()
  6.     ws = wb.active
  7.  
  8.     # 创建命名样式highlight
  9.     highlight = NamedStyle(name="highlight")
  10.     highlight.font = Font(bold=True, size=20) # 设置粗体以及字体大小20
  11.     bd = Side(style='thick', color="000000")  # 设置黑色粗体线条
  12.     highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd) # 设置边框
  13.  
  14.     wb.add_named_style(highlight) # 将命名样式注册到wb
  15.  
  16.     ws['A1'].style = highlight # 设置命名样式
  17.     ws['A1'] = 'A1内容'
  18.  
  19.     ws['D5'].style = 'highlight' # 也可以使用命名样式的name来设置样式
  20.     ws['D5'] = 'D5内容'
  21.  
  22.     ws['D8'].style = highlight
  23.     ws['D8'] = 'D8内容'
  24.  
  25.     wb.save("16_styles.xlsx")
  26.  
  27. if __name__ == '__main__':
  28.     main()

生成excel如下：

13423234-9d10896a592752ec.png

设置单元格的行高、宽度、居中

  1. def main():
  2.     from openpyxl.styles import NamedStyle, Font, Border, Side, Alignment
  3.     from openpyxl import Workbook
  4.  
  5.     wb = Workbook()
  6.     ws = wb.active
  7.  
  8.     # 创建命名样式highlight
  9.     highlight = NamedStyle(name="highlight")
  10.     highlight.font = Font(bold=True, size=20) # 设置粗体以及字体大小20
  11.     bd = Side(style='thick', color="000000")  # 设置黑色粗体线条
  12.     highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd) # 设置边框
  13.  
  14.     wb.add_named_style(highlight) # 将命名样式注册到wb
  15.  
  16.     ws['A1'].style = highlight # 设置命名样式
  17.     ws['A1'] = 'A1内容'
  18.     ws['A1'].alignment = Alignment(horizontal="center", vertical="center") # 设置内容的居中
  19.     ws.row_dimensions[1].height = 70 # 设置行高
  20.     ws.column_dimensions['A'].width = 20 # 设置宽度
  21.  
  22.  
  23.     ws['D5'].style = 'highlight' # 也可以使用命名样式的name来设置样式
  24.     ws['D5'] = 'D5内容'
  25.  
  26.     ws['D8'].style = highlight
  27.     ws['D8'] = 'D8内容'
  28.  
  29.     wb.save("16_styles.xlsx")
  30.  
  31. if __name__ == '__main__':
  32.     main()

excel如下：

13423234-a22a73b7bbed4d88.png

创建报告excel

在上面写了那么多示例之后，下面主要来一个综合性的示例，创建一个报告excel如下：

  1. def main():
  2.     import datetime
  3.     from openpyxl.styles import NamedStyle, Font, Border, Side, Alignment
  4.     from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment
  5.     from openpyxl.utils import get_column_letter  # 引入获取列字母的方法
  6.     from openpyxl import Workbook
  7.  
  8.     wb = Workbook()
  9.     ws = wb.active
  10.     ws.title = 'report'
  11.  
  12.     # 创建标题样式
  13.     title_style = NamedStyle("title_style")
  14.     title_style.font = Font(name='微软雅黑', bold=True, size=11, color="FFFFFF")
  15.     title_style.fill = PatternFill("solid", fgColor="00807E")  # 设置背景色填充
  16.     # title_style.fill = GradientFill(stop=("00807E", "FFFFFF"))  # 设置渐变色
  17.     # bd = Side(style='thick', color="000000")  # 设置黑色粗体线条 线条的类型:{'mediumDashDotDot', 'mediumDashed', 'thin', 'dashDotDot', 'double', 'medium', 'thick', 'dashed', 'mediumDashDot', 'dotted', 'slantDashDot', 'dashDot', 'hair'}
  18.     bd = Side(style='thin', color="000000")  # 设置黑色dashed线条
  19.     title_style.border = Border(left=bd, top=bd, right=bd, bottom=bd)  # 设置边框
  20.     title_style.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)  # 设置内容的居中以及自动换行 wrap_text=True
  21.  
  22.     # 将命名样式注册到wb
  23.     wb.add_named_style(title_style)
  24.  
  25.     # 设置标题
  26.     title_value = [
  27.         "编号",
  28.         "任务名称",
  29.         "脚本名称",
  30.         "测试项",
  31.         "接口",
  32.         "压测时长（秒）",
  33.         "并发用户数",
  34.         "每秒启动用户数",
  35.         "平均响应时间 Average(ms)",
  36.         "最短响应时间Min(ms)",
  37.         "90%响应时间(ms)",
  38.         "99%响应时间(ms)",
  39.         "最长响应时间Max(ms)",
  40.         "失败率Error(%)",
  41.         "服务器每秒处理请求数QPS(个)",
  42.         "服务器ServerName",
  43.         "CPU Avg 消耗(%)",
  44.         "CPU Max 消耗(%)",
  45.         "内存消耗(%)",
  46.         "磁盘I/O %Busy",
  47.         "网络IO Recv/Trans M/S", "",
  48.         "nmon文件",
  49.         "压测完成时间" ]
  50.  
  51.     row = 1 # 设置写标题的行
  52.  
  53.     # 设置行高
  54.     ws.row_dimensions[row].height = 60  # 设置第一行的行高
  55.  
  56.     for i in range(0, len(title_value)):
  57.         col = i + 1 # 设置写标题内容的列
  58.         ws.column_dimensions[str(get_column_letter(col))].width = 15  # 设置列的宽度
  59.  
  60.         # 设置单元格
  61.         s = str(get_column_letter(col)) + str(row)
  62.         ws[s].style = title_style  # 也可以使用命名样式的name来设置样式
  63.         ws[s] = title_value[i]
  64.  
  65.     ws.merge_cells('U1:V1')  # 合并 U1:V1 的单元格
  66.  
  67.     # 创建内容样式
  68.     content_style_grey = NamedStyle("content_style_grey")
  69.     content_style_grey.font = Font(name='Arial', size=10, color="000000")
  70.     content_style_grey.fill = PatternFill("solid", fgColor="D9D9D9")  # 设置背景色填充
  71.     content_style_grey.border = Border(left=bd, top=bd, right=bd, bottom=bd)  # 设置边框
  72.     content_style_grey.alignment = Alignment(horizontal="center", vertical="center",wrap_text=True)  # 设置内容的居中以及自动换行 wrap_text=True
  73.  
  74.     content_style_white = NamedStyle("content_style_white")
  75.     content_style_white.font = Font(name='Arial', size=10, color="000000")
  76.     content_style_white.border = Border(left=bd, top=bd, right=bd, bottom=bd)  # 设置边框
  77.     content_style_white.alignment = Alignment(horizontal="center", vertical="center",wrap_text=True)  # 设置内容的居中以及自动换行 wrap_text=True
  78.  
  79.     # 注册内容样式
  80.     wb.add_named_style(content_style_grey)
  81.     wb.add_named_style(content_style_white)
  82.  
  83.     # 设置内容
  84.     content_values = [
  85.         [ "1", "测试压测项目927", "2_locustfile_api_空业务.py", "测试接口1", "GET /apis", "180秒", "1000", "300", "63", "6", "250", "250", "3047", "0(0.00%)", "935.80", "server_api01", "33.83", "46.6", "23.41%", "1.3", "6.41", "1.82", "server_api01_190603_1125.nmon",datetime.datetime.now()],
  86.         [ "2", "测试压测项目927", "2_locustfile_api_空业务.py", "测试接口1", "GET /apis", "180秒", "1000", "300", "63", "6", "250", "250", "3047", "0(0.00%)", "935.80", "server_api02", "33.83", "46.6", "23.41%", "1.3", "6.41", "1.82", "server_api02_190603_1125.nmon",datetime.datetime.now()],
  87.         [ "3", "测试压测项目927", "2_locustfile_api_空业务.py", "测试接口1", "GET /apis", "180秒", "1000", "300", "63", "6", "250", "250", "3047", "0(0.00%)", "935.80", "server_api03", "33.83", "46.6", "23.41%", "1.3", "6.41", "1.82", "server_api03_190603_1125.nmon",datetime.datetime.now()],
  88.         [ "4", "测试压测项目927", "2_locustfile_api_空业务.py", "测试接口1", "GET /apis", "180秒", "1250", "500", "88", "6", "320", "320", "7251", "0(0.00%)", "1130.70", "server_api01", "39.93", "53.7", "23.41%", "1.3", "8.06", "2.27", "server_api01_190603_1131.nmon",datetime.datetime.now()],
  89.         [ "5", "测试压测项目927", "2_locustfile_api_空业务.py", "测试接口1", "GET /apis", "180秒", "1500", "500", "124", "6", "430", "430", "3120", "1(0.00%)", "1292.40", "server_api02", "45.02", "63.0", "23.41%", "2.3", "9.47", "2.66", "server_api01_190603_1137.nmon",datetime.datetime.now()],
  90.         [ "5", "测试压测项目927", "3_locustfile_api_2.py", "测试接口2", "GET /apis2", "180秒", "1500", "500", "124", "6", "430", "430", "3120", "1(0.00%)", "1292.40", "server_api02", "45.02", "63.0", "23.41%", "2.3", "9.47", "2.66", "server_api01_190603_1137.nmon",datetime.datetime.now()],
  91.         [ "6", "测试压测项目928", "2_locustfile_api_空业务.py", "测试接口1", "GET /apis", "180秒", "2000", "500", "248", "6", "610", "610", "3404", "31(0.01%)", "1539.60", "server_api03", "53.68", "75.1", "23.43%", "2", "12.1", "3.41", "server_api01_190603_1143.nmon",datetime.datetime.now()],
  92.         [ "7", "测试压测项目928", "2_locustfile_api_空业务.py", "测试接口1", "GET /apis", "180秒", "2300", "500", "321", "6", "810", "810", "3247", "63(0.02%)", "1618.90", "server_api04", "55.15", "80.2", "23.43%", "2", "13.57", "3.83", "server_api01_190603_1150.nmon",datetime.datetime.now()],
  93.         [ "8", "测试压测项目929", "2_locustfile_api_空业务.py", "测试接口1", "GET /apis", "180秒", "2500", "500", "369", "6", "900", "900", "3733", "41(0.01%)", "1717.90", "server_api05", "57.72", "81.0", "23.41%", "1.7", "13.5", "3.79", "server_api01_190603_1156.nmon",datetime.datetime.now()],
  94.         [ "9", "测试压测项目929", "2_locustfile_api_空业务.py", "测试接口1", "GET /apis", "180秒", "2800", "500", "464", "6", "900", "900", "2333", "98(0.03%)", "1801.30", "server_api06", "61.1", "81.2", "23.45%", "1.7", "13.05", "3.68", "server_api01_190603_1202.nmon",datetime.datetime.now()],
  95.         ["10", "测试压测项目927", "2_locustfile_api_空业务.py", "测试接口2", "GET /apis2", "180秒", "1000", "300", "63", "6", "250","250", "3047", "0(0.00%)", "935.80", "server_api01", "33.83", "46.6", "23.41%", "1.3", "6.41", "1.82","server_api01_190603_1125.nmon", datetime.datetime.now()],
  96.         ["11", "测试压测项目927", "2_locustfile_api_空业务.py", "测试接口2", "GET /apis2", "180秒", "1000", "300", "63", "6", "250","250", "3047", "0(0.00%)", "935.80", "server_api01", "33.83", "46.6", "23.41%", "1.3", "6.41", "1.82","server_api01_190603_1125.nmon", datetime.datetime.now()],
  97.         ["12", "测试压测项目927", "2_locustfile_api_空业务.py", "测试接口2", "GET /apis2", "180秒", "1000", "300", "63", "6", "250","250", "3047", "0(0.00%)", "935.80", "server_api01", "33.83", "46.6", "23.41%", "1.3", "6.41", "1.82","server_api01_190603_1125.nmon", datetime.datetime.now()],
  98.     ]
  99.  
  100.     # 设置内容
  101.     for content_value in content_values:
  102.  
  103.         row = row + 1  # 设置行
  104.  
  105.         # 设置行高
  106.         ws.row_dimensions[row].height = 26.7  # 设置第一行的行高
  107.  
  108.         for i in range(0, len(content_value)):
  109.             col = i + 1  # 设置写标题内容的列
  110.  
  111.             # 设置单元格
  112.             s = str(get_column_letter(col)) + str(row)
  113.             ws[s].style = content_style_white  # 也可以使用命名样式的name来设置样式
  114.             ws[s] = content_value[i]
  115.  
  116.     # 设置合并单元格 ws.merge_cells('A1:D1')
  117.     row_list = []
  118.     for row in ws.iter_rows(): # 遍历所有行的
  119.         row_list.append(row)
  120.  
  121.     for i in range(0,len(row_list)):
  122.  
  123.         row = row_list[i]
  124.         print('i = %d' % i)
  125.         print('遍历 row = {0}'.format(row))
  126.  
  127.         task_name = row[1].value
  128.         script_name = row[2].value
  129.         test_case_name = row[3].value
  130.         api_name = row[4].value
  131.         run_time = row[5].value
  132.         users = row[6].value
  133.         rate = row[7].value
  134.         average_response_time = row[8].value
  135.         min_response_time = row[9].value
  136.         percent_90_response_time = row[10].value
  137.         percent_99_response_time = row[11].value
  138.         max_response_time = row[12].value
  139.         failures = row[13].value
  140.         requests = row[14].value
  141.  
  142.         print('task_name = %s, script_name = %s, test_case_name = %s, api_name = %s, run_time = %s, users = %s, rate = %s, average_response_time = %s, min_response_time = %s, percent_90_response_time = %s, percent_99_response_time = %s, max_response_time = %s, failures = %s, requests = %s' % (
  143.             task_name, script_name, test_case_name, api_name, run_time, users, rate, average_response_time, min_response_time, percent_90_response_time, percent_99_response_time, max_response_time, failures, requests
  144.         ))
  145.  
  146.         if i > 1:
  147.             pre_row = row_list[i-1]
  148.  
  149.             def merge_pre_cell(num):
  150.                 for i in range(1,num):
  151.                     if row[i].value == pre_row[i].value: # 逐级递进合并上下单元格，如果其中一项不同，则退出合并循环
  152.                         pre_coord = str(get_column_letter(pre_row[i].column)) + str(pre_row[i].row)
  153.                         now_coord = str(get_column_letter(row[i].column)) + str(row[i].row)
  154.                         ws.merge_cells("{0}:{1}".format(pre_coord, now_coord))
  155.                     else:
  156.                         break
  157.  
  158.             merge_pre_cell(15)
  159.  
  160.     wb.save("17_styles.xlsx")
  161.  
  162. if __name__ == '__main__':
  163.     main()

生成的excel报表 如下：

13423234-b2dea8b08504f2b0.png
13423234-ae2b405d73d43b52.png

存在问题

可以从上面的截图看到，其实我在递进横向逐层多次合并单元格的时候也只是合并上下两行的单元格，并没有做到多行单元格合并。
导致合并的效果并不好，可以看到文字内容合并后都在最上方的两格之间。

优化代码

在这里我也写好了优化的代码，直接贴出。

  1. import datetime
  2. import time
  3. from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment, NamedStyle
  4. from openpyxl.utils import get_column_letter  # 引入获取列字母的方法
  5. from openpyxl import Workbook
  6.  
  7. class CreateReport():
  8.     """创建压测结果excel报告"""
  9.  
  10.     def __init__(self):
  11.         self.wb = Workbook()
  12.         self.excel = "report_{0}.xlsx".format(time.strftime('%Y%m%d%H%M%S',time.localtime()))
  13.         self.ws = self.wb.active
  14.         self.ws.title = 'report'
  15.         # 设置标题内容
  16.         self.title_value = [
  17.             "编号",
  18.             "任务名称",
  19.             "脚本名称",
  20.             "测试项",
  21.             "接口",
  22.             "压测时长（秒）",
  23.             "并发用户数",
  24.             "每秒启动用户数",
  25.             "平均响应时间 Average(ms)",
  26.             "最短响应时间Min(ms)",
  27.             "90%响应时间(ms)",
  28.             "99%响应时间(ms)",
  29.             "最长响应时间Max(ms)",
  30.             "失败率Error(%)",
  31.             "服务器每秒处理请求数QPS(个)",
  32.             "服务器ServerName",
  33.             "CPU Avg 消耗(%)",
  34.             "CPU Max 消耗(%)",
  35.             "内存消耗(%)",
  36.             "磁盘I/O %Busy",
  37.             "网络IO Recv/Trans M/S", "",
  38.             "nmon文件",
  39.             "压测完成时间"
  40.         ]
  41.         # 设置内容
  42.         self.content_values = [
  43.             ["1", "测试压测项目927", "2_locustfile_api_空业务.py", "测试接口1", "GET /apis", "180秒", "1000", "300", "63", "6", "250",
  44.              "250", "3047", "0(0.00%)", "935.80", "server_api01", "33.83", "46.6", "23.41%", "1.3", "6.41", "1.82",
  45.              "server_api01_190603_1125.nmon", datetime.datetime.now()],
  46.             ["2", "测试压测项目927", "2_locustfile_api_空业务.py", "测试接口1", "GET /apis", "180秒", "1000", "300", "63", "6", "250",
  47.              "250", "3047", "0(0.00%)", "935.80", "server_api02", "33.83", "46.6", "23.41%", "1.3", "6.41", "1.82",
  48.              "server_api02_190603_1125.nmon", datetime.datetime.now()],
  49.             ["3", "测试压测项目927", "2_locustfile_api_空业务.py", "测试接口1", "GET /apis", "180秒", "1000", "300", "63", "6", "250",
  50.              "250", "3047", "0(0.00%)", "935.80", "server_api03", "33.83", "46.6", "23.41%", "1.3", "6.41", "1.82",
  51.              "server_api03_190603_1125.nmon", datetime.datetime.now()],
  52.             ["4", "测试压测项目927", "2_locustfile_api_空业务.py", "测试接口1", "GET /apis", "180秒", "1250", "500", "88", "6", "320",
  53.              "320", "7251", "0(0.00%)", "1130.70", "server_api01", "39.93", "53.7", "23.41%", "1.3", "8.06", "2.27",
  54.              "server_api01_190603_1131.nmon", datetime.datetime.now()],
  55.             ["4", "测试压测项目927", "2_locustfile_api_空业务.py", "测试接口1", "GET /apis2", "180秒", "1250", "500", "88", "6",
  56.              "320", "320", "7251", "0(0.00%)", "1130.70", "server_api01", "39.93", "53.7", "23.41%", "1.3", "8.06",
  57.              "2.27", "server_api01_190603_1131.nmon", datetime.datetime.now()],
  58.             ["4", "测试压测项目927", "2_locustfile_api_空业务.py", "测试接口1", "GET /apis2", "180秒", "1250", "500", "88", "6",
  59.              "320", "320", "7251", "0(0.00%)", "1130.70", "server_api01", "39.93", "53.7", "23.41%", "1.3", "8.06",
  60.              "2.27", "server_api01_190603_1131.nmon", datetime.datetime.now()],
  61.             ["4", "测试压测项目927", "2_locustfile_api_空业务.py", "测试接口1", "GET /apis2", "180秒", "1250", "500", "88", "6",
  62.              "320", "320", "7251", "0(0.00%)", "1130.70", "server_api01", "39.93", "53.7", "23.41%", "1.3", "8.06",
  63.              "2.27", "server_api01_190603_1131.nmon", datetime.datetime.now()],
  64.             ["5", "测试压测项目927", "2_locustfile_api_空业务.py", "测试接口2", "GET /apis", "180秒", "1500", "500", "124", "6",
  65.              "430", "430", "3120", "1(0.00%)", "1292.40", "server_api02", "45.02", "63.0", "23.41%", "2.3", "9.47",
  66.              "2.66", "server_api01_190603_1137.nmon", datetime.datetime.now()],
  67.             ["6", "测试压测项目928", "2_locustfile_api_空业务.py", "测试接口2", "GET /apis", "180秒", "2000", "500", "248", "6",
  68.              "610", "610", "3404", "31(0.01%)", "1539.60", "server_api03", "53.68", "75.1", "23.43%", "2", "12.1",
  69.              "3.41", "server_api01_190603_1143.nmon", datetime.datetime.now()],
  70.             ["7", "测试压测项目928", "2_locustfile_api_空业务.py", "测试接口2", "GET /apis", "180秒", "2300", "500", "321", "6",
  71.              "810", "810", "3247", "63(0.02%)", "1618.90", "server_api04", "55.15", "80.2", "23.43%", "2", "13.57",
  72.              "3.83", "server_api01_190603_1150.nmon", datetime.datetime.now()],
  73.             ["8", "测试压测项目929", "2_locustfile_api_空业务.py", "测试接口1", "GET /apis", "180秒", "2500", "500", "369", "6",
  74.              "900", "900", "3733", "41(0.01%)", "1717.90", "server_api05", "57.72", "81.0", "23.41%", "1.7", "13.5",
  75.              "3.79", "server_api01_190603_1156.nmon", datetime.datetime.now()],
  76.             ["9", "测试压测项目929", "2_locustfile_api_空业务.py", "测试接口1", "GET /apis", "180秒", "2800", "500", "464", "6",
  77.              "900", "900", "2333", "98(0.03%)", "1801.30", "server_api06", "61.1", "81.2", "23.45%", "1.7", "13.05",
  78.              "3.68", "server_api01_190603_1202.nmon", datetime.datetime.now()],
  79.             ["9", "测试压测项目929", "2_locustfile_api_空业务.py", "测试接口3", "GET /apis", "180秒", "2800", "500", "464", "6",
  80.              "900", "900", "2333", "98(0.03%)", "1801.30", "server_api06", "61.1", "81.2", "23.45%", "1.7", "13.05",
  81.              "3.68", "server_api01_190603_1202.nmon", datetime.datetime.now()],
  82.             ["9", "测试压测项目929", "2_locustfile_api_空业务.py", "测试接口4", "GET /apis", "180秒", "2800", "500", "464", "6",
  83.              "900", "900", "2333", "98(0.03%)", "1801.30", "server_api06", "61.1", "81.2", "23.45%", "1.7", "13.05",
  84.              "3.68", "server_api01_190603_1202.nmon", datetime.datetime.now()],
  85.             ["9", "测试压测项目929", "2_locustfile_api_空业务.py", "测试接口5", "GET /apis", "180秒", "2800", "500", "464", "6",
  86.              "900", "900", "2333", "98(0.03%)", "1801.30", "server_api06", "61.1", "81.2", "23.45%", "1.7", "13.05",
  87.              "3.68", "server_api01_190603_1202.nmon", datetime.datetime.now()],
  88.             ["9", "测试压测项目929", "2_locustfile_api_空业务.py", "测试接口5", "GET /apis", "180秒", "2800", "500", "464", "6",
  89.              "900", "900", "2333", "98(0.03%)", "1801.30", "server_api06", "61.1", "81.2", "23.45%", "1.7", "13.05",
  90.              "3.68", "server_api01_190603_1202.nmon", datetime.datetime.now()],
  91.             ["9", "测试压测项目929", "2_locustfile_api_空业务.py", "测试接口5", "GET /apis", "180秒", "2800", "500", "464", "6",
  92.              "900", "900", "2333", "98(0.03%)", "1801.30", "server_api06", "61.1", "81.2", "23.45%", "1.7", "13.05",
  93.              "3.68", "server_api01_190603_1202.nmon", datetime.datetime.now()],
  94.             ["9", "测试压测项目929", "2_locustfile_api_空业务.py", "测试接口1", "GET /apis", "180秒", "2800", "500", "464", "6",
  95.              "900", "900", "2333", "98(0.03%)", "1801.30", "server_api06", "61.1", "81.2", "23.45%", "1.7", "13.05",
  96.              "3.68", "server_api01_190603_1202.nmon", datetime.datetime.now()],
  97.         ]
  98.  
  99.     def _create_title_style(self):
  100.         """设置excel标题样式"""
  101.         title_style = NamedStyle("title_style")
  102.         title_style.font = Font(name='微软雅黑', bold=True, size=11, color="FFFFFF")
  103.         title_style.fill = PatternFill("solid", fgColor="00807E")  # 设置背景色填充
  104.         bd = Side(style='thin', color="000000")  # 设置黑色dashed线条
  105.         title_style.border = Border(left=bd, top=bd, right=bd, bottom=bd)  # 设置边框
  106.         title_style.alignment = Alignment(horizontal="center", vertical="center",wrap_text=True)  # 设置内容的居中以及自动换行 wrap_text=True
  107.         return title_style
  108.  
  109.     def _set_title(self):
  110.         """设置excel标题"""
  111.  
  112.         # 设置标题内容
  113.         title_value = self.title_value
  114.  
  115.         # 设置标题样式
  116.         title_style = self._create_title_style()
  117.  
  118.         # 将命名样式注册到wb
  119.         self.wb.add_named_style(title_style)
  120.  
  121.         # 设置写标题的行
  122.         row = 1
  123.  
  124.         # 设置行高
  125.         self.ws.row_dimensions[row].height = 60  # 设置第一行的行高
  126.  
  127.         for i in range(0, len(title_value)):
  128.             col = i + 1 # 设置写标题内容的列
  129.  
  130.             # 设置列的宽度
  131.             if col == 1:
  132.                 self.ws.column_dimensions[str(get_column_letter(col))].width = 8
  133.             elif col == 3:
  134.                 self.ws.column_dimensions[str(get_column_letter(col))].width = 25
  135.             else:
  136.                 self.ws.column_dimensions[str(get_column_letter(col))].width = 15
  137.  
  138.             # 设置单元格
  139.             s = str(get_column_letter(col)) + str(row)
  140.             self.ws[s].style = title_style  # 也可以使用命名样式的name来设置样式
  141.             self.ws[s] = title_value[i]
  142.  
  143.         self.ws.merge_cells('U1:V1')  # 合并 U1:V1 的单元格
  144.  
  145.     def _save(self):
  146.         """保存excel报表"""
  147.         self.wb.save(self.excel)
  148.  
  149.     def _create_content_style(self):
  150.         """设置excel内容样式"""
  151.  
  152.         bd = Side(style='thin', color="000000")  # 设置黑色dashed线条
  153.         content_style = NamedStyle("content_style")
  154.         content_style.font = Font(name='Arial', size=10, color="000000")
  155.         content_style.border = Border(left=bd, top=bd, right=bd, bottom=bd)  # 设置边框
  156.         content_style.alignment = Alignment(horizontal="center", vertical="center",wrap_text=True)  # 设置内容的居中以及自动换行 wrap_text=True
  157.         return content_style
  158.  
  159.     def _set_content(self):
  160.         """设置报表每一行的数据"""
  161.  
  162.         # 注册内容样式
  163.         content_style = self._create_content_style()
  164.         self.wb.add_named_style(content_style)
  165.  
  166.         # 设置内容
  167.         row = 1
  168.         for content_value in self.content_values:
  169.             # 设置行
  170.             row = row + 1
  171.             # 设置行高
  172.             self.ws.row_dimensions[row].height = 26.7  # 设置第一行的行高
  173.  
  174.             for i in range(0, len(content_value)):
  175.                 col = i + 1  # 设置写标题内容的列
  176.  
  177.                 # 设置单元格
  178.                 s = str(get_column_letter(col)) + str(row)
  179.                 self.ws[s].style = content_style  # 也可以使用命名样式的name来设置样式
  180.                 self.ws[s] = content_value[i]
  181.  
  182.     def _content_merge_cells_col(self):
  183.         """横向初次递进准备合并内容的单元格的下一行为空"""
  184.  
  185.         # 设置合并单元格 ws.merge_cells('A1:D1')
  186.         row_list = []
  187.         for row in self.ws.iter_rows():  # 遍历所有行的
  188.             row_list.append(row)
  189.  
  190.         for i in range(0, len(row_list)):
  191.  
  192.             row = row_list[i]
  193.  
  194.             if i > 1:
  195.                 pre_row = row_list[i - 1]
  196.  
  197.                 # todo:采用多次覆盖合并的方式
  198.                 def merge_pre_cell(layer): # layer为递进需要合并的标题数量
  199.                     for i in range(1, layer):
  200.                         if row[i].value == pre_row[i].value:  # 逐级递进合并上下单元格，如果其中一项不同，则退出合并循环
  201.                             pre_coord = str(get_column_letter(pre_row[i].column)) + str(pre_row[i].row)
  202.                             now_coord = str(get_column_letter(row[i].column)) + str(row[i].row)
  203.                             self.ws.merge_cells("{0}:{1}".format(pre_coord, now_coord))
  204.                             self.ws.unmerge_cells("{0}:{1}".format(pre_coord, now_coord))
  205.                         else:
  206.                             break
  207.  
  208.                 merge_pre_cell(15)
  209.  
  210.     def _is_merged(self,cell_coord):
  211.         """输入单元格的坐标，判断是否被合并"""
  212.  
  213.         # ex: print( 'B2单元格是否被合并 = ', _is_merged('B2') )
  214.         for merged_cell in self.ws.merged_cells.ranges:
  215.             # print('merged_cell = %s' % merged_cell)
  216.             if cell_coord in merged_cell:
  217.                 return True
  218.         return False
  219.  
  220.     def _content_merge_cells_row(self):
  221.         """纵向二次合并单元格数据，解决初次合并不完整的问题"""
  222.         sheet_report = self.wb[self.ws.title]
  223.         # print(sheet_report.merged_cells)
  224.         max_row, max_column = sheet_report.max_row, sheet_report.max_column # 获取sheet表中的最大row column
  225.         # print('max_row = %s' % max_row)
  226.         # print('max_column = %s' % max_column)
  227.  
  228.         # ws.unmerge_cells(start_row=13, start_column=1, end_row=16, end_column=4)
  229.         # ws.merge_cells(start_row=13, start_column=1, end_row=18, end_column=4)
  230.  
  231.         # 遍历一列的每行数据
  232.         def traverse_column(col):
  233.             start_row = 2
  234.             end_row = 2
  235.             start_column = col
  236.             end_column = col
  237.             column_letter = get_column_letter(col)
  238.  
  239.             for i in range(2, max_row+1):
  240.  
  241.                 # 设置单个的单元格
  242.                 cell = sheet_report['{0}{1}'.format(column_letter,i)]
  243.                 print("cell.value = %s, cell.row = %s, cell.column = %s" % (cell.value, cell.row, cell.column) )
  244.  
  245.                 # 设置下一个单元格
  246.                 next_cell = None
  247.                 if i < max_row: # 当前的单元格还不是最后一行的单元格，那么则需要判断下一个单元格
  248.                     next_cell = sheet_report['{0}{1}'.format(column_letter,i+1)]
  249.                     print("next_cell.value = %s, next_cell.row = %s, next_cell.column = %s" % (next_cell.value, next_cell.row, next_cell.column))
  250.                 else:
  251.                     next_cell = None
  252.                     print("没有下一行的单元格数据了！")
  253.  
  254.                 # 记录第一行数据不为空的单元格为合并的起始
  255.                 if cell.value is not None:
  256.                     start_row = cell.row
  257.                     print('合并单元格的起始行数 start_row = %s' % start_row)
  258.  
  259.                 # 记录最后一行空数据，并且下一行的单元格数据不为空的单元格为合并的终止
  260.                 if cell.value is None:
  261.                     if next_cell is not None:
  262.                         if next_cell.value is not None:
  263.                             end_row = cell.row
  264.                             print('合并单元格的终止行数 end_row = {0}'.format(end_row))
  265.                             print('start_row = %s, end_row = %s' % (start_row, end_row))
  266.  
  267.                             # 合并多行单元格
  268.                             self.ws.merge_cells(start_row=start_row, start_column=start_column, end_row=end_row,
  269.                                                 end_column=end_column)
  270.  
  271.                     else: # 当到了最后一个单元格，则合并最后一次即可
  272.                         end_row = cell.row
  273.                         print('合并最后一个单元格的终止行数 end_row = {0}'.format(end_row))
  274.                         print('start_row = %s, end_row = %s' % (start_row, end_row))
  275.                         # 合并多行单元格
  276.                         self.ws.merge_cells(start_row=start_row, start_column=start_column, end_row=end_row,end_column=end_column)
  277.  
  278.         # 设置需要合并单元格的列
  279.         for col in range(2,16):
  280.             traverse_column(col=col)
  281.  
  282.     def report(self):
  283.         """创建excel报表"""
  284.         self._set_title()
  285.         self._set_content()
  286.         self._content_merge_cells_col()
  287.         self._content_merge_cells_row()
  288.         self._save()
  289.  
  290.         return self.excel
  291.  
  292. def _test():
  293.     """测试创建报表"""
  294.     excel = CreateReport().report()
  295.     print('excel = %s' % excel)
  296.  
  297. if __name__ == '__main__':
  298.     _test()

具体要看效果，直接复制执行即可。

13423234-0e3934319aa622f6.png
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/你好赵伟/article/detail/910758
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号