如何用Python保存Excel工作簿且不破坏动态溢出/数组公式

问题根源 #

openpyxl默认处理动态数组公式时，会将其溢出区域的公式转换为普通单元格公式（添加括号、丢失动态蓝线），导致失去自动更新能力——即使你完全没有修改包含动态公式的工作表。

解决方案 #

1. 确保使用兼容版本 #

必须使用openpyxl 3.0及以上版本，旧版本不支持Excel的动态数组公式特性。可以通过以下命令升级：

pip install --upgrade openpyxl

2. 正确加载工作簿 #

加载工作簿时显式指定data_only=False（默认值，但显式设置更稳妥），确保读取的是公式本身而非计算结果：

from openpyxl import load_workbook

# 加载工作簿，保留公式而非结果
wb = load_workbook('Original.xlsx', data_only=False)

3. 保存前恢复动态数组公式属性 #

即使未修改dynamic工作表，openpyxl仍可能在保存时破坏动态公式的属性，需要手动标记这些公式为数组公式：

# 获取dynamic工作表
ws_dynamic = wb['dynamic']

# 定义常见动态数组函数集合
dynamic_functions = {'FILTER', 'UNIQUE', 'BYROW', 'BYCOL', 'SORT', 'SORTBY'}

# 遍历工作表，标记含动态函数的公式为数组公式
for row in ws_dynamic.iter_rows():
    for cell in row:
        if cell.value and isinstance(cell.value, str) and cell.value.startswith('='):
            if any(func in cell.value for func in dynamic_functions):
                cell.is_array_formula = True

# 执行你的dump表数据写入逻辑（示例）
ws_dump = wb['dump']
# 这里写入Some_data工作簿的3列数据
# ...（你的原有数据写入代码）

# 保存工作簿
wb.save('Original_updated.xlsx')

4. 可选：避免直接覆盖原文件 #

建议先保存为新文件，确认公式功能正常后再替换原文件，防止原文件被意外损坏。

验证方法 #

打开保存后的工作簿，检查dynamic工作表：

• 公式单元格应显示动态数组专属的蓝线
• 修改nums工作表的数据，公式应自动更新溢出结果

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