专栏首页Devops专栏python3 openpyxl操作excel

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中行和列的数据:

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

Basic Font Colors

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

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

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

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 首个镜子分割网络问世,大连理工、鹏城实验室、香港城大出品 | ICCV 2019

    镜子作为日常生活中非常重要的物体无处不在,不仅能够反射光线,能呈现出周围物体或者场景的镜像。

    量子位
  • 恢复带宽与时延的平衡

    随着数据集规模的不断增长,数据中心负载对内存、存储的性能和容量的要求也在不断提高。与此同时,每单位时间需要处理更多数据,组成计算系统的组件性能也在不断提高。所谓...

    冬瓜哥
  • 9.18 VR扫描:VINCI获美空百万美元订单;高通展示无线化XR头显设计方案

    据悉,品牌体验设计公司Accomplice已完成对体验式设计机构Design & Manufacturing、VR/AR技术开发商Spatial Cinemat...

    VRPinea
  • Macbook风扇声音大怎么办?

    Macbook风扇声音大怎么办?好多使用 MacBook 笔记本的朋友,都会遇到风扇声音大的时候。有的可能是硬件问题,有的可能是软件问题,也有的是属于正常情况。...

    MAC先森
  • 如何实现真正安全的RTMP防盗播整体加解密方案

    好多企业或开发者联系大牛直播SDK,希望我们能够提供一套整体的音视频加密、解密方案,能通过公网标准服务器,高效、安全可靠的实现加密数据的传输、播放。

    daniulive
  • 虚拟现实前传-Three.js实现管壳式换热器3D模型在线查看

    使用threejs实现3D模型加载和显示。3D编程就像拍大片,几个关键东西如下所列:

    周星星9527
  • 类器官:肿瘤研究的新高地

    每年肿瘤相关研究的学术论文发表量数以万计,但能真正转化到临床应用的治疗方案却极少。新药研发进入临床试验后,有85%在早期就被证明没有效果,而那些成功通过三期临床...

    用户6317549
  • OpenCV实战应用必备技能 | 模块裁剪

    OpenCV是一个大而全的完整的计算机视觉库,有时候我们项目只是用到了一些基础功能,并没有必要用OPenCV的官方编译好的版本,那个对我们来说太大啦,这个时候其...

    OpenCV学堂
  • 2D照片秒变3D,Adobe新方法让你只用一张图像轻松做特效

    Ken Burns 特效,是一种通过虚拟摄像机扫描和缩放使静止图片动态化的特效。添加视差是创建 3D Ken Burns 的重要因素,带来了很多不可思议的画面。...

    机器之心
  • Python验证码识别

    最近在做爬虫的时候发现手动输入验证码算是比较烦了,就网上搜了一下,结果发现真的有现成的,作者:老板丶鱼丸粗面,写的很完整,看一下。所有源码点击阅读原文。

    我被狗咬了

作者介绍

Devops海洋的渔夫

TCL集团python全栈开发工程师

TCL集团 · python全栈开发工程师 (已认证)

精选专题

活动推荐

扫码关注云+社区

领取腾讯云代金券