使用Python openpyxl批量复制Excel公式并自动更新单元格引用

需求说明 #

• 处理大体量Excel文件时，需将GD列所有单元格存储的公式，复制到右侧相邻GE列
• 复制效果需和Excel原生粘贴完全一致：公式内的相对引用自动偏移，绝对引用保持不变
• 规避Excel内直接操作大文件卡顿、耗时过长的问题，基于openpyxl的Translator类实现

原有代码的逻辑错误 #

之前编写的代码存在几个核心问题，导致无法实现预期效果：

• Translator参数传反：origin参数需要传入公式原始所在单元格的坐标，原代码把目标单元格坐标传给了origin，翻译方向完全错误
• 遍历范围错误：GD列是第186列、GE列是第187列，原代码设置的min_col=187, max_col=188遍历范围完全没有覆盖到存储原公式的GD列
• 计算结果未写入：翻译得到新公式后，没有赋值给GE列对应的单元格，操作无实际输出
• 无异常判断：没有跳过GD列的纯文本、数值、表头类非公式单元格，容易触发翻译报错

正确实现代码 #

from openpyxl import load_workbook
from openpyxl.formula.translate import Translator

# 加载工作簿，大文件场景关闭不必要的加载项降低内存占用
workbook = load_workbook(
    'file.xlsx',
    data_only=False,  # 必须设为False才能读取到单元格公式，而非公式计算值
    keep_links=False,
    keep_vba=False
)
sheet = workbook.worksheets[8]

# 插入GE列（第187列），原有187列及之后的列自动右移
sheet.insert_cols(187)
# 写入GE列表头
sheet['GE2'] = '20220531'

# 遍历GD列（第186列）所有行，跳过表头行（从第3行开始处理数据，可根据实际表头位置调整起始行号）
for row in range(3, sheet.max_row + 1):
    gd_cell = sheet.cell(row=row, column=186)
    ge_cell = sheet.cell(row=row, column=187)
    cell_value = gd_cell.value
    # 仅处理公式单元格（公式均以=开头）
    if isinstance(cell_value, str) and cell_value.startswith('='):
        # 按原坐标到目标坐标平移公式引用
        translated_formula = Translator(
            formula=cell_value,
            origin=gd_cell.coordinate
        ).translate_formula(ge_cell.coordinate)
        # 将翻译后的公式写入GE列对应单元格
        ge_cell.value = translated_formula

# 保存文件
workbook.save('file_updated.xlsx')

注意事项 #

• 该实现的公式平移逻辑和Excel原生复制粘贴完全一致：带$的绝对引用不会偏移，相对引用自动按列偏移1位、行不变的规则调整
• 不要用pandas处理公式平移逻辑，pandas不识别Excel公式的引用规则，会直接丢失公式结构
• 大文件处理时建议保存为新文件名，避免写入失败损坏原文件

内容的提问来源于stack exchange，提问作者Bridget Bozman