Python写入Excel现有工作表时透视表损坏及输出文件被覆盖如何解决

问题根因 #

• 代码逻辑错误：原代码中load_workbook加载的是输入文件filename_in，并将该工作簿绑定到输出路径的ExcelWriter，保存时会直接覆盖filename_out的全部原有内容，仅保留输入文件的工作表结构+新写入的数据。同时原代码先将数据写入临时文件再读取的逻辑完全冗余，会增加不必要的IO开销。
• 依赖库默认配置问题：openpyxl默认不会保留Excel中的透视表、图表、交叉工作表引用等高级元素，写入时会直接丢弃这些结构，导致关联工作表损坏。

修复方案 #

首先确保openpyxl版本≥3.0，pandas版本≥1.4.0，对高级Excel特性的兼容性更好。

修正后代码 #

import pandas as pd
from openpyxl import load_workbook

# 配置参数
target_file = '你要修改的目标Excel文件路径（即原逻辑中的filename_out）'
sheet_to_update = 'Detail'
# 直接使用原始DataFrame，无需先写入临时文件再读取
write_df = pos_detail_data_df

# 加载目标工作簿，开启保留关联、公式的参数
book = load_workbook(
    filename=target_file,
    data_only=False,  # 保留公式而非仅读取计算结果
    keep_vba=True,  # 如果文件是带宏的.xlsm格式必须开启，.xlsx可省略
    keep_links=True  # 保留跨工作表引用关系
)

# 用上下文管理器创建ExcelWriter，避免资源泄漏
with pd.ExcelWriter(
    target_file,
    engine='openpyxl',
    mode='a',  # 追加模式，不覆盖原有文件内容
    if_sheet_exists='overlay'  # 已有工作表时叠加写入，不替换整张工作表
) as writer:
    writer.book = book
    writer.sheets = {ws.title: ws for ws in book.worksheets}
    # 写入数据，跳过前2行，不输出表头和索引
    write_df.to_excel(
        writer,
        sheet_name=sheet_to_update,
        startrow=2,
        header=False,
        index=False
    )
# 上下文管理器会自动保存，不需要手动调用save()

注意事项 #

• 如果使用的pandas版本低于1.4.0，移除mode='a'和if_sheet_exists='overlay'参数即可，原有写入逻辑不受影响。
• 写入完成后打开Excel，若透视表没有自动更新，右键透视表选择「刷新」即可，也可以在Excel透视表选项中开启「打开文件时刷新数据」，无需修改代码。
• 不要修改透视表依赖的数据源区域的表头字段名、列顺序，否则刷新透视表会出现结构错误。
• 若原文件是.xls格式，先转成.xlsx/.xlsm格式再操作，openpyxl不支持旧版.xls格式。

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