使用pd.ExcelWriter后动态公式变为数组公式的问题求助

问题原因 #

• 引擎冲突：代码外层用openpyxl初始化ExcelWriter，但to_excel中额外指定engine="io.excel.xlsx.writer"，两种引擎混合解析会破坏Excel文件原生的动态公式属性。
• openpyxl overlay模式缺陷：使用if_sheet_exists="overlay"覆盖工作表时，openpyxl会读取原模板中公式的当前溢出范围（396行），将FILTER这类动态溢出公式转换为固定区域的数组公式，固化输出范围后无法随数据源更新扩展。
• pandas to_excel局限性：pandas写入工作表时，不会保留Excel原生的动态溢出关联逻辑，反而会覆盖相关属性。

解决方案 #

1. 统一引擎，移除冲突参数 #

删除to_excel中的engine参数，全程使用openpyxl引擎，避免格式解析混乱：

writer = pd.ExcelWriter(excel_path, engine='openpyxl', mode='a', if_sheet_exists="overlay")
for config in excelUpdateConfigs:
    result = fetchSQL(db_conn, config["sql"])
    result = result.astype(config["dtype"])
    # 移除engine参数，与外层保持一致
    result.to_excel(writer, sheet_name="Raw Data", float_format="%.5f", startrow=2, startcol=config["startcol"], header=True, index=False)
writer.close()

2. 删除重建工作表（推荐） #

避免使用overlay模式，先删除旧的"Raw Data"工作表，再新建写入，彻底清除原工作表的格式残留：

from openpyxl import load_workbook
import os

# 加载原工作簿并删除旧工作表
wb = load_workbook(excel_path)
if "Raw Data" in wb.sheetnames:
    del wb["Raw Data"]
temp_path = f"{os.path.splitext(excel_path)[0]}_temp.xlsx"
wb.save(temp_path)

# 写入新的Raw Data工作表
writer = pd.ExcelWriter(temp_path, engine='openpyxl', mode='a', if_sheet_exists="new")
for config in excelUpdateConfigs:
    result = fetchSQL(db_conn, config["sql"])
    result = result.astype(config["dtype"])
    result.to_excel(writer, sheet_name="Raw Data", float_format="%.5f", startrow=2, startcol=config["startcol"], header=True, index=False)
writer.close()

# 替换原文件
os.replace(temp_path, excel_path)

3. 直接用openpyxl操作工作表（精细控制） #

跳过pandas的ExcelWriter，直接用openpyxl加载工作簿，清空数据后写入DataFrame，最大程度保留原公式特性：

from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows

wb = load_workbook(excel_path)
ws = wb["Raw Data"]

# 清空第3行及以后的数据（保留表头）
for row in ws.iter_rows(min_row=3, max_row=ws.max_row, min_col=1, max_col=ws.max_column):
    for cell in row:
        cell.value = None

# 写入新数据
rows = dataframe_to_rows(result, index=False, header=False)
for r_idx, row in enumerate(rows, start=3):  # 从第3行开始写入
    for c_idx, value in enumerate(row, start=config["startcol"] + 1):  # openpyxl列索引从1开始
        ws.cell(row=r_idx, column=c_idx, value=value)

# 设置浮点数格式
for row in ws.iter_rows(min_row=3, max_row=ws.max_row, min_col=config["startcol"] + 1):
    for cell in row:
        cell.number_format = "0.00000"

wb.save(excel_path)

4. Windows环境备选：用win32com刷新公式 #

如果上述方法无效，可通过win32com调用Excel刷新公式，恢复动态溢出特性：

import win32com.client as win32

excel = win32.gencache.EnsureDispatch('Excel.Application')
excel.Visible = False
wb = excel.Workbooks.Open(excel_path)
wb.RefreshAll()
wb.Save()
wb.Close()
excel.Quit()

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