使用openpyxl 2.6.2更新Excel数据Sheet后无法保留数据透视表

我之前也碰到过类似的坑，openpyxl处理透视表时确实有不少细节要注意，核心是修改数据源后得保证透视表的缓存关联不被破坏。针对你的问题，我整理了两个经过验证的修正方案：

方案一：修正Pandas结合OpenPyXL的写法 #

你第一段代码的问题在于pd.ExcelWriter的使用逻辑会意外覆盖透视表结构，删除列后直接写入DataFrame也容易断裂数据源关联。试试下面的调整版本：

import pandas as pd
from openpyxl import load_workbook

sheet_name = 'Data'
file_path = local_path + 'file_name' + '.xlsx'

# 1. 加载工作簿并配置透视表自动刷新
book = load_workbook(file_path)
pivot_ws = book["my_pivot_table"]
if pivot_ws._pivots:
    pivot = pivot_ws._pivots[0]
    pivot.cache.refreshOnLoad = True  # 设置Excel打开时自动刷新透视表

# 2. 清空Data工作表的旧数据（保留表头可按需调整）
data_ws = book[sheet_name]
data_ws.delete_rows(2, data_ws.max_row)  # 假设第一行是表头，只清空数据行

# 3. 用Pandas安全写入数据，避免破坏工作表结构
writer = pd.ExcelWriter(file_path, engine='openpyxl', mode='a', if_sheet_exists='replace')
df_tb_exp.to_excel(writer, sheet_name=sheet_name, index=False)
writer.book = book
writer.sheets = {ws.title: ws for ws in book.worksheets}
writer.save()
writer.close()

关键调整点： #

• 用mode='a'+if_sheet_exists='replace'替换Data表数据，而非直接删列写入，保证透视表的数据源引用不中断
• 只清空数据行而非删除列，避免列结构变化导致透视表缓存失效
• 确保开启refreshOnLoad=True，让Excel打开时自动触发透视表刷新

方案二：纯OpenPyXL写法（不依赖Pandas） #

你第二段代码的问题是dataframe_to_rows+append会在旧数据后面追加新内容，而非替换，导致透视表找不到正确的数据源范围。试试这个修正版本：

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

sheet_name = 'Data'
file_path = local_path + 'file_name' + '.xlsx'

# 加载工作簿并设置透视表自动刷新
book = load_workbook(file_path)
pivot_ws = book["my_pivot_table"]
if pivot_ws._pivots:
    pivot = pivot_ws._pivots[0]
    pivot.cache.refreshOnLoad = True

# 完全清空Data工作表内容（如需保留表头，可改为delete_rows(2, data_ws.max_row)）
data_ws = book[sheet_name]
data_ws.delete_rows(1, data_ws.max_row)

# 将DataFrame数据逐单元格写入工作表
rows = dataframe_to_rows(df_tb_exp, index=False, header=True)
for r_idx, row in enumerate(rows, 1):
    for c_idx, value in enumerate(row, 1):
        data_ws.cell(row=r_idx, column=c_idx, value=value)

# 保存并关闭工作簿
book.save(file_path)
book.close()

关键调整点： #

• 先清空Data表所有内容，再逐单元格写入新数据，避免新旧数据混杂导致透视表数据源混乱
• 手动控制行列索引，确保数据写入到正确位置，匹配透视表的数据源范围

额外注意事项 #

1. 如果你的透视表数据源是固定单元格范围而非整个工作表，修改数据后需要手动更新透视表的数据源引用：

   # 假设Data表数据有N行，更新透视表数据源范围
   pivot.cacheSource.ref = f"{sheet_name}!A1:Z{df_tb_exp.shape[0]+1}"
   

2. openpyxl 2.6.2对透视表的支持有局限，若仍有问题可以尝试升级到3.x系列稳定版，兼容性会更好
3. 保存文件后打开Excel时，要允许系统刷新透视表，部分Excel版本会弹出确认提示，需手动确认

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