欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Python-openpyxl对excel取消/合并单元格,以及修改单元格值

程序员文章站 2022-06-14 09:39:17
...

获取已合并单元格的位置信息

# 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))

拆分单元格

    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 openpyxl
 wb = 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 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)
相关标签: openpyxl Python