def write_report(url, excel_name, sheet_name):
import re
import ast
import pandas as pd
import openpyxl
from copy import deepcopy
resp = requests.get(url)
contents = re.findall('myTable=(.*?);', resp.text)[0]
all_nums = []
for content in ast.literal_eval(contents):
nums = {}
title = content["title"]
str = "".join(content["test_result"].split())
# 实际测试过程中的并发数
mean_request_sec = float(re.findall('meanrequests/sec(.*?)\(', str)[0])
mean_request_sec = int(mean_request_sec)
# 最大响应事件
max_response_time = int(re.findall('maxresponsetime(.*?)\(', str)[0])
# 平均响应事件
mean_response_time = int(re.findall('meanresponsetime(.*?)\(', str)[0])
nums.update({title: [mean_request_sec, mean_response_time, max_response_time]})
all_nums.append(nums)
df = pd.read_excel(excel_name, sheet_name=sheet_name)
wb = openpyxl.load_workbook(excel_name)
ws = wb[sheet_name]
"""
Book = load_workbook(Input_Xlsx_FileName)
Worksheet = Book['Hoja1']
Worksheet.unmerge_cells(start_row=1, start_column=1, end_row=2, end_column=2)
Worksheet.cell(2,2).value = "TOTO"
"""
# 获取所有的已合并的单元格信息
# worksheet.merged_cells获取已经合并单元格的信息；再使用worksheet.unmerge_cells()拆分单元格；
m_list = ws.merged_cells
cr = []
for m_area in m_list:
# 合并单元格的起始行坐标、终止行坐标。。。。，
r1, r2, c1, c2 = m_area.min_row, m_area.max_row, m_area.min_col, m_area.max_col
# 纵向合并单元格的位置信息提取出
if r2 - r1 > 0:
cr.append((r1, r2, c1, c2))
# 这里注意需要把合并单元格的信息提取出再拆分
merge_cr = deepcopy(cr)
for r in cr:
ws.unmerge_cells(start_row=r[0], end_row=r[1], start_column=r[2], end_column=r[3])
wb.save(excel_name)
# row是行， column是列 清除excel中合并的单元格，下标是从1开始
# ws.unmerge_cells(start_row=1, start_column=1, end_row=3, end_column=1)
# row是行， column是列 合并excel中合并的单元格，下标是从1开始
# ws.merge_cells(start_row=1, start_column=1, end_row=3, end_column=1)
# 获取最大行，最大列
ncols = df.columns.size
nrows = df.shape[0]
# 遍历逐行逐列
for iRow in range(nrows):
for iCol in range(ncols):
for num in all_nums:
value = list(num.values())[0]
if df.iloc[iRow, iCol] == list(num.keys())[0]:
# 往excel原有数值处写入数据。行，列下标从1开始
ws.cell(iRow + 2, iCol).value = value[2]
ws.cell(iRow + 2, iCol-1).value = value[1]
ws.cell(iRow + 2, iCol-2).value = value[0]
break
# 合并已拆分得单元格
for r in merge_cr:
# worksheet.merge_cells()合并单元格
ws.merge_cells(start_row=r[0], end_row=r[1], start_column=r[2], end_column=r[3])
wb.save(excel_name)
if __name__ == '__main__':
# 测试集报告链接
url = "https://sep.sensetime.com/common/report/performance/set_2985/2020_05_19_16_12_20_191934.html"
# excel表名称
excel_name = "qps.xlsx"
# excel_sheet分页名称
sheet_name = "senselink"
write_report(url, excel_name, sheet_name)
