这个默认查询是一行一列的，如果需要插入第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:
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
>>> 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
>>> 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)
下面来设置一个字体颜色看看，如下：
访问转换网址，如下：https://www.sioe.cn/yingyong/yanse-rgb-16/
# 设置字体颜色以及主题
ws['A3'].font = Font(color="00807E")
ws['A3'] = '使用16进制格式设置颜色'
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报表 如下：
存在问题
可以从上面的截图看到，其实我在递进横向逐层多次合并单元格的时候也只是合并上下两行的单元格，并没有做到多行单元格合并。导致合并的效果并不好，可以看到文字内容合并后都在最上方的两格之间。
优化代码
在这里我也写好了优化的代码，直接贴出。
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()
具体要看效果，直接复制执行即可。
本文分享自微信公众号 - DevOps社群（DevOpsCommunity）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。