asp网页生成excel 多sheet_python3 openpyxl操作excel

需求

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

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

openxl官方文档

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

安装openpyxl

pip3 install openpyxl==3.0.0

创建excel文件的示例代码

if __name__ == '__main__':

from openpyxl import Workbook
    wb = Workbook()

# grab the active worksheet
    ws = wb.active

# Data can be assigned directly to cells
    ws['A1'] = 42

# Rows can also be appended
    ws.append([1, 2, 3])

# Python types will automatically be converted
import datetime

    ws['A2'] = datetime.datetime.now()

# Save the file
    wb.save("sample.xlsx")

执行生成的excel文件如下：

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

官网示例

创建编写excel ( Write a workbook )

def main():
from openpyxl import Workbook
from openpyxl.utils import get_column_letter # 引入获取列字母的方法

    wb = Workbook() # 创建Workbook
    dest_filename = 'empty_book.xlsx' # 设置保存的空xlsx文件名

# 创建第一个sheet
    ws1 = wb.active
    ws1.title = "range names" # 设置sheet name

# 往第一个sheet写入40行 区间0-600 的数据
for row in range(1, 40):
        ws1.append(range(600))

    ws1.append(['1','2','3']) # 在最后的行再增加一行1，2，3

# 创建第二个sheet，以及同时设置sheet name为 Pi
    ws2 = wb.create_sheet(title="Pi")
    ws2['F5'] = 3.14 # 设置F5的单元格数值为 3.14

# 创建第三个sheet，以及设置sheet name 为 Data
    ws3 = wb.create_sheet(title="Data")

for row in range(10, 20): # 设置10~20行数据
for col in range(10, 54): # 设置 10~54 列数据
            _ = ws3.cell(column=col, row=row, value="{0}".format(get_column_letter(col)))
            print('col = {}, row = {}, value = {}'.format( col, row, get_column_letter(col) ))

    print(ws3['AA10'].value) # 打印 AA10 单元格的数据， 为：AA

    wb.save(filename=dest_filename) # 保存excel文件

if __name__ == '__main__':
    main()

生成的excel如下：

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

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

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

读取已存在的excel文件数据 (Read an existing workbook)

def main():
from openpyxl import load_workbook
    wb = load_workbook(filename='empty_book.xlsx')
    sheet_ranges = wb['range names']
    print(sheet_ranges['D18'].value)

if __name__ == '__main__':
    main()

执行结果如下：

打开excel确认值如下：

使用格式化数据(Using number formats)

def main():
import datetime
from openpyxl import Workbook
    wb = Workbook()  # 创建Workbook
    dest_filename = 'format_book.xlsx'

    ws = wb.active
# set date using a Python datetime
    ws['A1'] = datetime.datetime.now()
    print(ws['A1'].number_format)

    wb.save(filename=dest_filename) # 保存excel文件

if __name__ == '__main__':
    main()

执行结果如下：

生成excel如下：

使用excel的公式(Using formulae)

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

def main():
from openpyxl import Workbook
    wb = Workbook()
    ws = wb.active
# add a simple formula
    ws["A1"] = "=SUM(1, 1)"
    wb.save("formula.xlsx")

if __name__ == '__main__':
    main()

执行生成excel如下：

合并/拆分单元格的示例 (Merge / Unmerge cells)

def main():
from openpyxl.workbook import Workbook

    wb = Workbook()
    ws = wb.active

# 合并A1至D1 四个单元格并赋值
    ws['A1'] = '合并A1至D1 四个单元格并赋值'
    ws.merge_cells('A1:D1')

# 合并A3至D3 四个单元格，然后再拆分，确认是否恢复
    ws['A3'] = '合并A3至D3 四个单元格，然后再拆分，确认是否恢复'
    ws.merge_cells('A3:D3')
    ws.unmerge_cells('A3:D3')

# 合并多行多列单元格,从第五行开始，合并至第九行，从第一列开始，合并至第四列
    ws['A5'] = '合并多行多列单元格,从第五行开始，合并至第九行，从第一列开始，合并至第四列'
    ws.merge_cells(start_row=5, start_column=1, end_row=9, end_column=4)

# 拆分多行多列
    ws['A13'] = '首先合并，然后再拆分'
    ws.merge_cells(start_row=13, start_column=1, end_row=16, end_column=4)
    ws.unmerge_cells(start_row=13, start_column=1, end_row=16, end_column=4)

    wb.save("merge_unmerge.xlsx")

if __name__ == '__main__':
    main()

执行生成excel如下：

插入图片示例 (Inserting an image)

def main():
from openpyxl import Workbook
from openpyxl.drawing.image import Image

    wb = Workbook()
    ws = wb.active
    ws['A1'] = 'You should see three logos below'

# create an image
    img = Image('logo.jpg')

# add to worksheet and anchor next to cells
    ws.add_image(img, 'A2')
    wb.save('logo.xlsx')

if __name__ == '__main__':
    main()

执行生成excel如下：

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

def main():
import openpyxl
   wb = openpyxl.Workbook()
   ws = wb.create_sheet()
   ws.column_dimensions.group('A', 'D', hidden=True)
   ws.row_dimensions.group(1, 10, hidden=True)
   wb.save('group.xlsx')

if __name__ == '__main__':
    main()

执行如下：

只读模式(Read-only mode)

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

from openpyxl import load_workbook
wb = load_workbook(filename='large_file.xlsx', read_only=True)
ws = wb['big_data']

for row in ws.rows:
for cell in row:
        print(cell.value)

只写模式(Write-only mode)

def main():
from openpyxl import Workbook
    wb = Workbook(write_only=True) # 设置只写模式
    ws = wb.create_sheet()

# now we'll fill it with 100 rows x 200 columns
for irow in range(100):
        ws.append(['%d' % i for i in range(200)])

# save the file
    wb.save('new_big_file.xlsx')  # doctest: +SKIP

if __name__ == '__main__':
    main()

生成的excel如下：

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

def main():
from openpyxl import Workbook
    wb = Workbook(write_only = True)
    ws = wb.create_sheet()

from openpyxl.cell import WriteOnlyCell
from openpyxl.comments import Comment
from openpyxl.styles import Font

    cell = WriteOnlyCell(ws, value="hello world")
    cell.font = Font(name='Courier', size=36)
    cell.comment = Comment(text="A comment", author="Author's Name")
    ws.append([cell, 3.14, None])
    wb.save('write_only_file.xlsx')

if __name__ == '__main__':
    main()

生成excel如下：

删除excel表格中的行和列

插入行和列数据(Inserting rows and columns)

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

• `openpyxl.worksheet.worksheet.Worksheet.insert_rows()`^[1]
• `openpyxl.worksheet.worksheet.Worksheet.insert_cols()`^[2]
• `openpyxl.worksheet.worksheet.Worksheet.delete_rows()`^[3]
• `openpyxl.worksheet.worksheet.Worksheet.delete_cols()`^[4]

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

>>> ws.insert_rows(7)

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

def main():
from openpyxl import Workbook
    wb = Workbook()
    ws = wb.create_sheet('test_insert')

# 往第一个sheet写入40行 区间0-600 的数据
for row in range(1, 40):
        ws.append(range(600))

    ws.insert_rows(7) # 在第7行插入空白的一行

    wb.save('write_only_file.xlsx')

if __name__ == '__main__':
    main()

查看生成的excel如下：

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

删除行和列 (Deletinng rows and columns)

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

ws.delete_rows(idx=1, amount=2) # 删除前两行
ws.delete_cols(idx=2,amount=1) # 删除第2列

示例如下：

def main():
from openpyxl import Workbook
    wb = Workbook()
    ws = wb.create_sheet('test_insert')

# 往第一个sheet写入40行 区间0-600 的数据
for row in range(1, 40):
        ws.append(range(600))

    ws.insert_rows(7) # 在第7行插入空白的一行

    ws.delete_rows(idx=1, amount=2) # 删除前两行
    ws.delete_cols(idx=2,amount=1) # 删除第2列

    wb.save('write_only_file.xlsx')

if __name__ == '__main__':
    main()

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

移动单元格(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^[5]

The following charts are available:

• Area Charts
  • 2D Area Charts
  • 3D Area Charts
• Bar and Column Charts
  • Vertical, Horizontal and Stacked Bar Charts
  • 3D Bar Charts
• Bubble Charts
• Line Charts
  • Line Charts
  • 3D Line Charts
• Scatter Charts
• Pie Charts
  • Pie Charts
  • Projected Pie Charts
  • 3D Pie Charts
  • Gradient Pie Charts
• Doughnut Charts
• Radar Charts
• Stock Charts
• Surface charts

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

def main():
from openpyxl import Workbook
    wb = Workbook()
    ws = wb.active
for i in range(10):
        ws.append([i])

from openpyxl.chart import BarChart, Reference, Series
    values = Reference(ws, min_col=1, min_row=1, max_col=1, max_row=10)
    chart = BarChart()
    chart.add_data(values)
    ws.add_chart(chart, "E15")
    wb.save("SampleChart.xlsx")

if __name__ == '__main__':
    main()

生成图表如下：

2D Area Charts

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

def main():
from openpyxl import Workbook
from openpyxl.chart import (
        AreaChart,
        Reference,
        Series,
    )

    wb = Workbook()
    ws = wb.active

    rows = [
        ['Number', 'Batch 1', 'Batch 2'],
        [2, 40, 30],
        [3, 40, 25],
        [4, 50, 30],
        [5, 30, 10],
        [6, 25, 5],
        [7, 50, 10],
    ]

for row in rows:
        ws.append(row)

    chart = AreaChart()
    chart.title = "Area Chart"
    chart.style = 13
    chart.x_axis.title = 'Test'
    chart.y_axis.title = 'Percentage'

    cats = Reference(ws, min_col=1, min_row=1, max_row=7)
    data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=7)
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(cats)

    ws.add_chart(chart, "A10")

    wb.save("area.xlsx")

if __name__ == '__main__':
    main()

生成excel如下：

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

设置excel的样式(Working with styles)

介绍^[6]

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 默认的样式参数

>>> from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
>>> font = Font(name='Calibri',
...                 size=11,
...                 bold=False,
...                 italic=False,
...                 vertAlign=None,
...                 underline='none',
...                 strike=False,
...                 color='FF000000')
>>> fill = PatternFill(fill_type=None,
...                 start_color='FFFFFFFF',
...                 end_color='FF000000')
>>> border = Border(left=Side(border_style=None,
...                           color='FF000000'),
...                 right=Side(border_style=None,
...                            color='FF000000'),
...                 top=Side(border_style=None,
...                          color='FF000000'),
...                 bottom=Side(border_style=None,
...                             color='FF000000'),
...                 diagonal=Side(border_style=None,
...                               color='FF000000'),
...                 diagonal_direction=0,
...                 outline=Side(border_style=None,
...                              color='FF000000'),
...                 vertical=Side(border_style=None,
...                               color='FF000000'),
...                 horizontal=Side(border_style=None,
...                                color='FF000000')
...                )
>>> alignment=Alignment(horizontal='general',
...                     vertical='bottom',
...                     text_rotation=0,
...                     wrap_text=False,
...                     shrink_to_fit=False,
...                     indent=0)
>>> number_format = 'General'
>>> protection = Protection(locked=True,
...                         hidden=False)
>>>

单元格样式和命名样式(Cell Styles and Named Styles)

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

Cell Styles^[7]

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.

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

def main():
from openpyxl.styles import colors
from openpyxl.styles import Font, Color
from openpyxl import Workbook
import datetime

    wb = Workbook()
    ws = wb.active

    a1 = ws['A1']
    d4 = ws['D4']

    ft = Font(color=colors.RED) # 设置字体

    a1.font = ft
    ws['A1'] = datetime.datetime.now()

    d4.font = ft
    ws['D4'] = 'd4内容'


# If you want to change the color of a Font, you need to reassign it::
    a1.font = Font(color=colors.RED, italic=True)  # the change only affects A1

    wb.save("cell_styles.xlsx")

if __name__ == '__main__':
    main()

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

Copying styles

Styles can also be copied

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

>>> from copy import copy
>>>
>>> ft1 = Font(name='Arial', size=14)
>>> ft2 = copy(ft1)
>>> ft2.name = "Tahoma"
>>> ft1.name
'Arial'
>>> ft2.name
'Tahoma'
>>> ft2.size # copied from the
14.0

Basic Font Colors^[8]

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

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

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

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

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

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

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

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

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

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

• 设置字体颜色代码如下：

    # 设置字体颜色以及主题
    ws['A3'].font = Font(color="00807E")
    ws['A3'] = '使用16进制格式设置颜色'

• 生成excel如下：

Applying Styles^[9]

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

def main():
from openpyxl.workbook import Workbook
from openpyxl.styles import Font, Fill
    wb = Workbook()
    ws = wb.active

    a1 = ws['A1']
    a1.font = Font(size=12, bold=True)
    ws['A1'] = '设置字体大小为12,粗体'

    a2 = ws['A2']
    a2.font = Font(size=24,underline="single")
    ws['A2'] = '设置字体大小24，下划线'

    wb.save("14_apply_styles.xlsx")

if __name__ == '__main__':
    main()

生成excel如下：

Styling Merged Cells^[10]

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.

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

def main():
from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment
from openpyxl import Workbook

    wb = Workbook()
    ws = wb.active
    ws.merge_cells('B2:F4') # 合并 B2:F4 的单元格

    top_left_cell = ws['B2'] # 设置合并单元格的左上角单元格
    top_left_cell.value = "My Cell"

    thin = Side(border_style="thin", color="000000")
    double = Side(border_style="double", color="ff0000")

# 设置左上角样式
    top_left_cell.border = Border(top=double, left=thin, right=thin, bottom=double) # 设置边框
    top_left_cell.fill = PatternFill("solid", fgColor="DDDDDD") # 设置背景色填充
    top_left_cell.fill = GradientFill(stop=("000000", "FFFFFF")) # 设置渐变色
    top_left_cell.font = Font(b=True, color="FF0000") # 设置字体的颜色
    top_left_cell.alignment = Alignment(horizontal="center", vertical="center") # 设置内容的居中

    wb.save("15_styles.xlsx")

if __name__ == '__main__':
    main()

生成的excel如下：

Named Styles^[11]

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

def main():
from openpyxl.styles import NamedStyle, Font, Border, Side
from openpyxl import Workbook

    wb = Workbook()
    ws = wb.active

# 创建命名样式highlight
    highlight = NamedStyle(name="highlight")
    highlight.font = Font(bold=True, size=20) # 设置粗体以及字体大小20
    bd = Side(style='thick', color="000000")  # 设置黑色粗体线条
    highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd) # 设置边框

    wb.add_named_style(highlight) # 将命名样式注册到wb

    ws['A1'].style = highlight # 设置命名样式
    ws['A1'] = 'A1内容'

    ws['D5'].style = 'highlight' # 也可以使用命名样式的name来设置样式
    ws['D5'] = 'D5内容'

    ws['D8'].style = highlight
    ws['D8'] = 'D8内容'

    wb.save("16_styles.xlsx")

if __name__ == '__main__':
    main()

生成excel如下：

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

def main():
from openpyxl.styles import NamedStyle, Font, Border, Side, Alignment
from openpyxl import Workbook

    wb = Workbook()
    ws = wb.active

# 创建命名样式highlight
    highlight = NamedStyle(name="highlight")
    highlight.font = Font(bold=True, size=20) # 设置粗体以及字体大小20
    bd = Side(style='thick', color="000000")  # 设置黑色粗体线条
    highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd) # 设置边框

    wb.add_named_style(highlight) # 将命名样式注册到wb

    ws['A1'].style = highlight # 设置命名样式
    ws['A1'] = 'A1内容'
    ws['A1'].alignment = Alignment(horizontal="center", vertical="center") # 设置内容的居中
    ws.row_dimensions[1].height = 70 # 设置行高
    ws.column_dimensions['A'].width = 20 # 设置宽度


    ws['D5'].style = 'highlight' # 也可以使用命名样式的name来设置样式
    ws['D5'] = 'D5内容'

    ws['D8'].style = highlight
    ws['D8'] = 'D8内容'

    wb.save("16_styles.xlsx")

if __name__ == '__main__':
    main()

excel如下：

创建报告excel

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

def main():
import datetime
from openpyxl.styles import NamedStyle, Font, Border, Side, Alignment
from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment
from openpyxl.utils import get_column_letter  # 引入获取列字母的方法
from openpyxl import Workbook

    wb = Workbook()
    ws = wb.active
    ws.title = 'report'

# 创建标题样式
    title_style = NamedStyle("title_style")
    title_style.font = Font(name='微软雅黑', bold=True, size=11, color="FFFFFF")
    title_style.fill = PatternFill("solid", fgColor="00807E")  # 设置背景色填充
# title_style.fill = GradientFill(stop=("00807E", "FFFFFF"))  # 设置渐变色
# bd = Side(style='thick', color="000000")  # 设置黑色粗体线条 线条的类型:{'mediumDashDotDot', 'mediumDashed', 'thin', 'dashDotDot', 'double', 'medium', 'thick', 'dashed', 'mediumDashDot', 'dotted', 'slantDashDot', 'dashDot', 'hair'}
    bd = Side(style='thin', color="000000")  # 设置黑色dashed线条
    title_style.border = Border(left=bd, top=bd, right=bd, bottom=bd)  # 设置边框
    title_style.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)  # 设置内容的居中以及自动换行 wrap_text=True

# 将命名样式注册到wb
    wb.add_named_style(title_style)

# 设置标题
    title_value = [
"编号",
"任务名称",
"脚本名称",
"测试项",
"接口",
"压测时长(秒)",
"并发用户数",
"每秒启动用户数",
"平均响应时间 Average(ms)",
"最短响应时间Min(ms)",
"90%响应时间(ms)",
"99%响应时间(ms)",
"最长响应时间Max(ms)",
"失败率Error(%)",
"服务器每秒处理请求数QPS(个)",
"服务器ServerName",
"CPU Avg 消耗(%)",
"CPU Max 消耗(%)",
"内存消耗(%)",
"磁盘I/O %Busy",
"网络IO Recv/Trans M/S", "",
"nmon文件",
"压测完成时间" ]

    row = 1 # 设置写标题的行

# 设置行高
    ws.row_dimensions[row].height = 60  # 设置第一行的行高

for i in range(0, len(title_value)):
        col = i + 1 # 设置写标题内容的列
        ws.column_dimensions[str(get_column_letter(col))].width = 15  # 设置列的宽度

# 设置单元格
        s = str(get_column_letter(col)) + str(row)
        ws[s].style = title_style  # 也可以使用命名样式的name来设置样式
        ws[s] = title_value[i]

    ws.merge_cells('U1:V1')  # 合并 U1:V1 的单元格

# 创建内容样式
    content_style_grey = NamedStyle("content_style_grey")
    content_style_grey.font = Font(name='Arial', size=10, color="000000")
    content_style_grey.fill = PatternFill("solid", fgColor="D9D9D9")  # 设置背景色填充
    content_style_grey.border = Border(left=bd, top=bd, right=bd, bottom=bd)  # 设置边框
    content_style_grey.alignment = Alignment(horizontal="center", vertical="center",wrap_text=True)  # 设置内容的居中以及自动换行 wrap_text=True

    content_style_white = NamedStyle("content_style_white")
    content_style_white.font = Font(name='Arial', size=10, color="000000")
    content_style_white.border = Border(left=bd, top=bd, right=bd, bottom=bd)  # 设置边框
    content_style_white.alignment = Alignment(horizontal="center", vertical="center",wrap_text=True)  # 设置内容的居中以及自动换行 wrap_text=True

# 注册内容样式
    wb.add_named_style(content_style_grey)
    wb.add_named_style(content_style_white)

# 设置内容
    content_values = [
        [ "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()],
        [ "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()],
        [ "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()],
        [ "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()],
        [ "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()],
        [ "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()],
        [ "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()],
        [ "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()],
        [ "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()],
        [ "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()],
        ["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()],
        ["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()],
        ["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()],
    ]

# 设置内容
for content_value in content_values:

        row = row + 1  # 设置行

# 设置行高
        ws.row_dimensions[row].height = 26.7  # 设置第一行的行高

for i in range(0, len(content_value)):
            col = i + 1  # 设置写标题内容的列

# 设置单元格
            s = str(get_column_letter(col)) + str(row)
            ws[s].style = content_style_white  # 也可以使用命名样式的name来设置样式
            ws[s] = content_value[i]

# 设置合并单元格 ws.merge_cells('A1:D1')
    row_list = []
for row in ws.iter_rows(): # 遍历所有行的
        row_list.append(row)

for i in range(0,len(row_list)):

        row = row_list[i]
        print('i = %d' % i)
        print('遍历 row = {0}'.format(row))

        task_name = row[1].value
        script_name = row[2].value
        test_case_name = row[3].value
        api_name = row[4].value
        run_time = row[5].value
        users = row[6].value
        rate = row[7].value
        average_response_time = row[8].value
        min_response_time = row[9].value
        percent_90_response_time = row[10].value
        percent_99_response_time = row[11].value
        max_response_time = row[12].value
        failures = row[13].value
        requests = row[14].value

        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' % (
            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
        ))

if i > 1:
            pre_row = row_list[i-1]

def merge_pre_cell(num):
for i in range(1,num):
if row[i].value == pre_row[i].value: # 逐级递进合并上下单元格，如果其中一项不同，则退出合并循环
                        pre_coord = str(get_column_letter(pre_row[i].column)) + str(pre_row[i].row)
                        now_coord = str(get_column_letter(row[i].column)) + str(row[i].row)
                        ws.merge_cells("{0}:{1}".format(pre_coord, now_coord))
else:
break

            merge_pre_cell(15)

    wb.save("17_styles.xlsx")

if __name__ == '__main__':
    main()

生成的excel报表 如下：

存在问题

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

优化代码

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

import datetime
import time
from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment, NamedStyle
from openpyxl.utils import get_column_letter  # 引入获取列字母的方法
from openpyxl import Workbook

class CreateReport():
"""创建压测结果excel报告"""

def __init__(self):
        self.wb = Workbook()
        self.excel = "report_{0}.xlsx".format(time.strftime('%Y%m%d%H%M%S',time.localtime()))
        self.ws = self.wb.active
        self.ws.title = 'report'
# 设置标题内容
        self.title_value = [
"编号",
"任务名称",
"脚本名称",
"测试项",
"接口",
"压测时长(秒)",
"并发用户数",
"每秒启动用户数",
"平均响应时间 Average(ms)",
"最短响应时间Min(ms)",
"90%响应时间(ms)",
"99%响应时间(ms)",
"最长响应时间Max(ms)",
"失败率Error(%)",
"服务器每秒处理请求数QPS(个)",
"服务器ServerName",
"CPU Avg 消耗(%)",
"CPU Max 消耗(%)",
"内存消耗(%)",
"磁盘I/O %Busy",
"网络IO Recv/Trans M/S", "",
"nmon文件",
"压测完成时间"
        ]
# 设置内容
        self.content_values = [
            ["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()],
            ["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()],
            ["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()],
            ["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()],
            ["4", "测试压测项目927", "2_locustfile_api_空业务.py", "测试接口1", "GET /apis2", "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()],
            ["4", "测试压测项目927", "2_locustfile_api_空业务.py", "测试接口1", "GET /apis2", "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()],
            ["4", "测试压测项目927", "2_locustfile_api_空业务.py", "测试接口1", "GET /apis2", "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()],
            ["5", "测试压测项目927", "2_locustfile_api_空业务.py", "测试接口2", "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()],
            ["6", "测试压测项目928", "2_locustfile_api_空业务.py", "测试接口2", "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()],
            ["7", "测试压测项目928", "2_locustfile_api_空业务.py", "测试接口2", "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()],
            ["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()],
            ["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()],
            ["9", "测试压测项目929", "2_locustfile_api_空业务.py", "测试接口3", "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()],
            ["9", "测试压测项目929", "2_locustfile_api_空业务.py", "测试接口4", "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()],
            ["9", "测试压测项目929", "2_locustfile_api_空业务.py", "测试接口5", "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()],
            ["9", "测试压测项目929", "2_locustfile_api_空业务.py", "测试接口5", "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()],
            ["9", "测试压测项目929", "2_locustfile_api_空业务.py", "测试接口5", "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()],
            ["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()],
        ]

def _create_title_style(self):
"""设置excel标题样式"""
        title_style = NamedStyle("title_style")
        title_style.font = Font(name='微软雅黑', bold=True, size=11, color="FFFFFF")
        title_style.fill = PatternFill("solid", fgColor="00807E")  # 设置背景色填充
        bd = Side(style='thin', color="000000")  # 设置黑色dashed线条
        title_style.border = Border(left=bd, top=bd, right=bd, bottom=bd)  # 设置边框
        title_style.alignment = Alignment(horizontal="center", vertical="center",wrap_text=True)  # 设置内容的居中以及自动换行 wrap_text=True
return title_style

def _set_title(self):
"""设置excel标题"""

# 设置标题内容
        title_value = self.title_value

# 设置标题样式
        title_style = self._create_title_style()

# 将命名样式注册到wb
        self.wb.add_named_style(title_style)

# 设置写标题的行
        row = 1

# 设置行高
        self.ws.row_dimensions[row].height = 60  # 设置第一行的行高

for i in range(0, len(title_value)):
            col = i + 1 # 设置写标题内容的列

# 设置列的宽度
if col == 1:
                self.ws.column_dimensions[str(get_column_letter(col))].width = 8
elif col == 3:
                self.ws.column_dimensions[str(get_column_letter(col))].width = 25
else:
                self.ws.column_dimensions[str(get_column_letter(col))].width = 15

# 设置单元格
            s = str(get_column_letter(col)) + str(row)
            self.ws[s].style = title_style  # 也可以使用命名样式的name来设置样式
            self.ws[s] = title_value[i]

        self.ws.merge_cells('U1:V1')  # 合并 U1:V1 的单元格

def _save(self):
"""保存excel报表"""
        self.wb.save(self.excel)

def _create_content_style(self):
"""设置excel内容样式"""

        bd = Side(style='thin', color="000000")  # 设置黑色dashed线条
        content_style = NamedStyle("content_style")
        content_style.font = Font(name='Arial', size=10, color="000000")
        content_style.border = Border(left=bd, top=bd, right=bd, bottom=bd)  # 设置边框
        content_style.alignment = Alignment(horizontal="center", vertical="center",wrap_text=True)  # 设置内容的居中以及自动换行 wrap_text=True
return content_style

def _set_content(self):
"""设置报表每一行的数据"""

# 注册内容样式
        content_style = self._create_content_style()
        self.wb.add_named_style(content_style)

# 设置内容
        row = 1
for content_value in self.content_values:
# 设置行
            row = row + 1
# 设置行高
            self.ws.row_dimensions[row].height = 26.7  # 设置第一行的行高

for i in range(0, len(content_value)):
                col = i + 1  # 设置写标题内容的列

# 设置单元格
                s = str(get_column_letter(col)) + str(row)
                self.ws[s].style = content_style  # 也可以使用命名样式的name来设置样式
                self.ws[s] = content_value[i]

def _content_merge_cells_col(self):
"""横向初次递进准备合并内容的单元格的下一行为空"""

# 设置合并单元格 ws.merge_cells('A1:D1')
        row_list = []
for row in self.ws.iter_rows():  # 遍历所有行的
            row_list.append(row)

for i in range(0, len(row_list)):

            row = row_list[i]

if i > 1:
                pre_row = row_list[i - 1]

# todo:采用多次覆盖合并的方式
def merge_pre_cell(layer): # layer为递进需要合并的标题数量
for i in range(1, layer):
if row[i].value == pre_row[i].value:  # 逐级递进合并上下单元格，如果其中一项不同，则退出合并循环
                            pre_coord = str(get_column_letter(pre_row[i].column)) + str(pre_row[i].row)
                            now_coord = str(get_column_letter(row[i].column)) + str(row[i].row)
                            self.ws.merge_cells("{0}:{1}".format(pre_coord, now_coord))
                            self.ws.unmerge_cells("{0}:{1}".format(pre_coord, now_coord))
else:
break

                merge_pre_cell(15)

def _is_merged(self,cell_coord):
"""输入单元格的坐标，判断是否被合并"""

# ex: print( 'B2单元格是否被合并 = ', _is_merged('B2') )
for merged_cell in self.ws.merged_cells.ranges:
# print('merged_cell = %s' % merged_cell)
if cell_coord in merged_cell:
return True
return False

def _content_merge_cells_row(self):
"""纵向二次合并单元格数据，解决初次合并不完整的问题"""
        sheet_report = self.wb[self.ws.title]
# print(sheet_report.merged_cells)
        max_row, max_column = sheet_report.max_row, sheet_report.max_column # 获取sheet表中的最大row column
# print('max_row = %s' % max_row)
# print('max_column = %s' % max_column)

# ws.unmerge_cells(start_row=13, start_column=1, end_row=16, end_column=4)
# ws.merge_cells(start_row=13, start_column=1, end_row=18, end_column=4)

# 遍历一列的每行数据
def traverse_column(col):
            start_row = 2
            end_row = 2
            start_column = col
            end_column = col
            column_letter = get_column_letter(col)

for i in range(2, max_row+1):

# 设置单个的单元格
                cell = sheet_report['{0}{1}'.format(column_letter,i)]
                print("cell.value = %s, cell.row = %s, cell.column = %s" % (cell.value, cell.row, cell.column) )

# 设置下一个单元格
                next_cell = None
if i < max_row: # 当前的单元格还不是最后一行的单元格，那么则需要判断下一个单元格
                    next_cell = sheet_report['{0}{1}'.format(column_letter,i+1)]
                    print("next_cell.value = %s, next_cell.row = %s, next_cell.column = %s" % (next_cell.value, next_cell.row, next_cell.column))
else:
                    next_cell = None
                    print("没有下一行的单元格数据了！")

# 记录第一行数据不为空的单元格为合并的起始
if cell.value is not None:
                    start_row = cell.row
                    print('合并单元格的起始行数 start_row = %s' % start_row)

# 记录最后一行空数据，并且下一行的单元格数据不为空的单元格为合并的终止
if cell.value is None:
if next_cell is not None:
if next_cell.value is not None:
                            end_row = cell.row
                            print('合并单元格的终止行数 end_row = {0}'.format(end_row))
                            print('start_row = %s, end_row = %s' % (start_row, end_row))

# 合并多行单元格
                            self.ws.merge_cells(start_row=start_row, start_column=start_column, end_row=end_row,
                                                end_column=end_column)

else: # 当到了最后一个单元格，则合并最后一次即可
                        end_row = cell.row
                        print('合并最后一个单元格的终止行数 end_row = {0}'.format(end_row))
                        print('start_row = %s, end_row = %s' % (start_row, end_row))
# 合并多行单元格
                        self.ws.merge_cells(start_row=start_row, start_column=start_column, end_row=end_row,end_column=end_column)

# 设置需要合并单元格的列
for col in range(2,16):
            traverse_column(col=col)

def report(self):
"""创建excel报表"""
        self._set_title()
        self._set_content()
        self._content_merge_cells_col()
        self._content_merge_cells_row()
        self._save()

return self.excel

def _test():
"""测试创建报表"""
    excel = CreateReport().report()
    print('excel = %s' % excel)

if __name__ == '__main__':
    _test()

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