已关联Data Model的Excel数据透视表更新时文件损坏问题求助

我有一个包含「Data」工作表和「Pivot Table」工作表的Excel文件，其中数据透视表已添加至Data Model。当我用Python代码更新「Data」工作表的数据时，文件会损坏且数据透视表失效；但如果没把数据透视表添加至Data Model，代码就能正常运行。现在需要找到能安全更新数据且不破坏文件的方法。

import pandas as pd

# 用于在Excel中创建透视表的初始数据
data = {
    'Country': ['USA', 'Canada'],
    'Population': [328200000, 37590000],
    'Capital': ['华盛顿特区', '渥太华']
}
df = pd.DataFrame(data)


# 用于替换初始数据的新数据
data2 = {
    'Country': ['USA', 'Canada', 'UK', 'Australia', 'Finland'],
    'Population': [328200000, 37590000, 66650000, 25360000, 5000000],
    'Capital': ['华盛顿特区', '渥太华', '伦敦', '堪培拉', '赫尔辛基']
}
# 注：原代码存在错误，此处应传入data2而非data，否则写入的仍是初始数据
df2 = pd.DataFrame(data)


# 将新数据追加到现有数据并写入Excel文件
with pd.ExcelWriter('Excel.xlsx', mode='a', engine='openpyxl', if_sheet_exists='replace') as writer:
    print("打开Excel文件")

    # 将合并后的数据写入工作表
    print("写入Data工作表")
    df2.to_excel(writer, sheet_name='Data', index=False, startrow=0)

问题核心是openpyxl引擎无法正确识别Excel的Data Model（Power Pivot）关联结构，替换工作表时会破坏模型依赖。以下是两种可靠的解决方法：

方法一：用win32com.client直接操控Excel（Windows专属） #

通过Windows COM接口调用本地Excel程序，完整保留文件结构，还能直接刷新透视表：

import pandas as pd
import win32com.client as win32

# 准备要写入的正确数据
data2 = {
    'Country': ['USA', 'Canada', 'UK', 'Australia', 'Finland'],
    'Population': [328200000, 37590000, 66650000, 25360000, 5000000],
    'Capital': ['华盛顿特区', '渥太华', '伦敦', '堪培拉', '赫尔辛基']
}
df2 = pd.DataFrame(data2)

# 启动Excel后台进程
excel = win32.gencache.EnsureDispatch('Excel.Application')
excel.Visible = False  # 如需可视化操作可改为True
wb = excel.Workbooks.Open('Excel.xlsx')

# 清空Data工作表原有内容
ws_data = wb.Worksheets('Data')
ws_data.Cells.ClearContents()

# 写入表头和数据
for c_idx, col in enumerate(df2.columns, start=1):
    ws_data.Cells(1, c_idx).Value = col
for r_idx, row in enumerate(df2.values, start=2):
    for c_idx, val in enumerate(row, start=1):
        ws_data.Cells(r_idx, c_idx).Value = val

# 刷新关联Data Model的透视表
ws_pivot = wb.Worksheets('Pivot Table')
for pivot in ws_pivot.PivotTables():
    pivot.PivotCache().Refresh()

# 保存并关闭文件
wb.Save()
wb.Close()
excel.Quit()

方法二：用xlwings跨平台操作Excel（需额外安装） #

xlwings支持直接与Excel交互，完美兼容Data Model，代码更简洁：

import pandas as pd
import xlwings as xw

# 准备正确的更新数据
data2 = {
    'Country': ['USA', 'Canada', 'UK', 'Australia', 'Finland'],
    'Population': [328200000, 37590000, 66650000, 25360000, 5000000],
    'Capital': ['华盛顿特区', '渥太华', '伦敦', '堪培拉', '赫尔辛基']
}
df2 = pd.DataFrame(data2)

# 打开Excel文件并操作
with xw.Book('Excel.xlsx') as wb:
    # 清空Data表并写入新数据
    ws_data = wb.sheets['Data']
    ws_data.clear_contents()
    ws_data.range('A1').options(index=False).value = df2

    # 刷新透视表
    ws_pivot = wb.sheets['Pivot Table']
    for pivot in ws_pivot.api.PivotTables():
        pivot.PivotCache().Refresh()

关键注意事项 #

• 必须修正原代码中df2 = pd.DataFrame(data)的错误，改为df2 = pd.DataFrame(data2)，否则无法写入新数据。
• 避免在含Data Model的Excel文件中使用openpyxl的if_sheet_exists='replace'逻辑，该操作会破坏模型关联。

内容的提问来源于stack exchange，提问作者Pauli H.