获取已合并单元格的位置信息
# worksheet.merged_cells获取已经合并单元格的信息；再使用worksheet.unmerge_cells()拆分单元格；m_list = ws.merged_cellscr = []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))
拆分单元格
for r in cr:ws.unmerge_cells(start_row=r[0], end_row=r[1], start_column=r[2], end_column=r[3])
根据原拆分的位置信息进行单元格合并
merge_cr = deepcopy(cr)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])
根据指定的返回进行单元格合并或拆分
import openpyxlwb = openpyxl.load_workbook(excel_name)ws = wb[sheet_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)
修改单元格的值
ws.cell(iRow, iCol).value = "新的值"
详细代码如下:
def write_report(url, excel_name, sheet_name):import reimport astimport pandas as pdimport openpyxlfrom copy import deepcopyresp = 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_cellscr = []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.sizenrows = 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 = "/common/report/performance/set_2985/_05_19_16_12_20_191934.html"# excel表名称excel_name = "qps.xlsx"# excel_sheet分页名称sheet_name = "senselink"write_report(url, excel_name, sheet_name)
