使用openpyxl覆盖数据工作表并保留数据透视表的技术问题

你遇到的核心问题是旧版OpenPyXL配合Pandas写入数据时，破坏了Excel中数据透视表的关联结构——透视表所在工作表虽保留，但失去了动态刷新能力，只剩静态数值。这主要源于两个点：一是你使用的OpenPyXL 2.4.10版本对透视表元数据的支持不完善；二是Pandas的ExcelWriter在覆盖工作表内容时，可能擦除了透视表依赖的关键结构信息。

下面是具体的解决方案：

1. 优先升级OpenPyXL版本 #

OpenPyXL 2.4.x是较老旧的版本，2.5+及后续版本大幅优化了对数据透视表、公式关联等复杂Excel结构的支持。先执行升级命令：

pip install --upgrade openpyxl

2. 改用OpenPyXL直接操作单元格写入数据 #

避免用Pandas的ExcelWriter直接覆盖工作表，转而用OpenPyXL清空目标工作表的旧数据后逐单元格写入新内容。这种方式能保留原有工作表的结构，确保透视表与数据源的关联不被破坏。修改后的代码如下：

import pandas as pd
import openpyxl as xls
from shutil import copyfile

template_file = 'openpy_test.xlsx'
output_file = 'openpy_output.xlsx'
# 复制模板文件
copyfile(template_file, output_file)

# 加载工作簿，保留公式和关联链接（新版本支持keep_links参数）
book = xls.load_workbook(output_file, data_only=False, keep_links=True)
ws_data = book['data']

# 清空data工作表中除表头外的旧数据（假设表头在第1行）
for row in ws_data.iter_rows(min_row=2):
    for cell in row:
        cell.value = None

# 将Pandas数据逐行写入工作表
for row_idx, row_data in enumerate(df.itertuples(index=False), start=2):
    for col_idx, cell_value in enumerate(row_data, start=1):
        ws_data.cell(row=row_idx, column=col_idx, value=cell_value)

# 保存工作簿
book.save(output_file)

3. 关键参数说明 #

• data_only=False：必须设置该参数，确保加载工作簿时保留公式和透视表的计算逻辑，而非仅读取静态数值。
• keep_links=True：新版本OpenPyXL的参数，用于保留文件中的各类关联链接，包括透视表与数据源的绑定关系。

额外注意事项 #

如果写入数据后透视表未自动刷新，打开Excel文件后右键点击透视表选择「刷新」即可恢复动态功能——这是Excel默认不会自动刷新外部修改后的透视表，但只要结构未被破坏，刷新后就能正常工作。

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