python openpyxl 操作excel ，使用 conditional_formatting，insert_rows

场景

python 使用 openpyxl 操作 excel 对于简单的格式来说还是挺方便的，但是对于复杂的格式，操作没有手动来的简单，如果对于复杂的格式也不是用python来做的。对于一个原有的报表，在其中添加一行，会出现很多问题。

• 后面的行公式没有响应的发生变化
• 合并的行还保留在原来的位置
• 原有的条件格式还在原来的位置

记录下解决方案

公式问题和合并单元格问题

这里记录的不是通用方法，因为我这里的单元格列数较少，所以采用的匹配方式是re.findall(r'[A-Z]\d+',val)前面的只需要A-Z，公式中有两种情况，单元格运算，还有就是单元格范围运算。

1. 对于单元格运算，包含一个或者多个，那么直接将插入行的后面行所有的数字+1;若是范围运算，则需要把插入行后面的单元格向下移动一样，对于前面的则保持不动；若都在后面，则范围整体向下移动一行。
2. 对于合并单元格，需要先清除插入行后面的单元格，然后记录位置，将所有的行都+1，然后重新合并单元格，使用unmerge_cells 清除合并的单元格，merge_cells合并单元格。这里需要注意的是临界值问题，因为插入行复制的是上一行的格式，则需要将上一行合并的单元格复制到插入行，并且上一行的合并行不能发生变化。

# 在指定位置添加一行，复制上一行的格式
def insert_row_up_merges(sheet, add_idx):
    # 统计需要移动的合并单元格信息
    cellmeges = []
    orgmeges = []
    merged_cells = sheet.merged_cells
    for merged_cell in merged_cells:
        if merged_cell.min_row >= add_idx:
            cellmeges.append(
                [merged_cell.min_row + 1, merged_cell.min_col, merged_cell.max_row + 1, merged_cell.max_col])
            orgmeges.append(merged_cell.coord)
        elif merged_cell.min_row >= add_idx-1:
            cellmeges.append(
                [merged_cell.min_row + 1, merged_cell.min_col, merged_cell.max_row + 1, merged_cell.max_col])
    for x in orgmeges:
        sheet.unmerge_cells(x)

    # 插入新行
    sheet.insert_rows(add_idx)

    # 复制上一行的样式和公式
    cells = sheet[add_idx - 1]
    cells2 = sheet[add_idx]
    for i, c in enumerate(cells):
        cells2[i]._style = copy(c._style)
        if c.value:
            val = f"{c.value}"
            if val.startswith("="):
                cells2[i].value = val


    # 合并单元格
    for x in cellmeges:
        sheet.merge_cells(start_row=x[0], start_column=x[1],
                          end_row=x[2], end_column=x[3])
    # 查看后面的单元格是否有 = 开头的，如果有，则把公式向下移动一行或者直接向下扩展一行
    for i in range(add_idx,sheet.max_row+1):
        row = sheet[i]
        for cell in row:
            if cell.value:
                val = f"{cell.value}"
                if val.startswith("="):
                    # print(cell.row,val,'==>',)
                    match = re.findall(r'[A-Z]\d+',val)
                    for r in match:
                        v = int(r[1:])
                        if v >= add_idx - 2:
                            val = val.replace(r,f"{r[0]}{v+1}")
                    # print(val)
                    cell.value = val

条件格式

可以看官方文档 https://openpyxl-chinese-docs.readthedocs.io/zh_CN/latest/formatting.html
这里需要注意的是，我这里是先清除格式，然后按照自己的逻辑自己生成格式

font_up = Font(size=9, color="FF0000", name="微软雅黑")
font_down = Font(size=9, color="548235", name="微软雅黑")
# 添加自己的逻辑
def format_view(sheet):
    # 清除单元格格式
    sheet.conditional_formatting = ConditionalFormattingList()
    # 单元格里面的值 小于0 展示绿色，大于0展示红色
    rd = CellIsRule(operator="lessThan",formula=['0'],stopIfTrue=True,font=font_down)
    ru = CellIsRule(operator="greaterThan",formula=['0'],stopIfTrue=True,font=font_up)

	# 对于部分数据，“-”开始的展示绿色 “+”开始的展示红色
    ju = Rule(type='beginsWith', operator='beginsWith', text="+", dxf=DifferentialStyle(font=font_up))
    jd = Rule(type='beginsWith', operator='beginsWith', text="-", dxf=DifferentialStyle(font=font_down))
    # 对部分位置进行标色

    # 第一个表格  B,D
    wk = week_format_row(sheet)
    if wk:
        for x in ['B','D']:
            sheet.conditional_formatting.add(f'{x}{wk[0]}:{x}{wk[1]}', rd)
            sheet.conditional_formatting.add(f'{x}{wk[0]}:{x}{wk[1]}', ru)
    # 第二个表格
    wp = wap_format_row(sheet)
    if wp:
        for x in ['F', 'G','I']:
            sheet.conditional_formatting.add(f'{x}{wp[0]}:{x}{wp[1]+1}', rd)
            sheet.conditional_formatting.add(f'{x}{wp[0]}:{x}{wp[1]+1}', ru)
        sheet.conditional_formatting.add(f'J{wp[0]}:J{wp[1] + 1}', jd)
        sheet.conditional_formatting.add(f'J{wp[0]}:J{wp[1] + 1}', ju)

	# 第三个格式
    t0 = t0_format_row(sheet)
    if t0:
        for x in ['F', 'G','I','L']:
            sheet.conditional_formatting.add(f'{x}{t0[0]}:{x}{t0[1]}', rd)
            sheet.conditional_formatting.add(f'{x}{t0[0]}:{x}{t0[1]}', ru)
        sheet.conditional_formatting.add(f'J{t0[0]}:J{t0[1] + 1}', jd)
        sheet.conditional_formatting.add(f'J{t0[0]}:J{t0[1] + 1}', ju)

效果

由于是生产的数据，不能展示，可以看到部分样式，这里实际上是三个图表