python3 openpyxl操作excel

Devops海洋的渔夫

发布于 2019-10-10 15:31:01

2.6K0

发布于 2019-10-10 15:31:01

文章被收录于专栏：Devops专栏Devops专栏

需求

在日常工作中，避免不了需要操作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中行和列的数据：

这个默认查询是一行一列的，如果需要插入第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

The following charts are available:

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

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）

介绍

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

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

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

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报表如下：

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2019.10.09 ，如有侵权请联系 cloudcommunity@tencent.com 删除

图像处理

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

图像处理