如何使用Python修改Excel数据透视表的数据源并更新？

方法一：使用win32com.client（Windows环境，依赖本地Excel） #

该方法直接调用Excel的COM接口，能完整实现修改数据源+更新透视表的全流程，步骤如下：

1. 初始化Excel应用

   import win32com.client as win32
   import os
   
   # 启动后台Excel进程（不显示窗口）
   excel = win32.gencache.EnsureDispatch('Excel.Application')
   excel.Visible = False
   

2. 打开目标工作簿

   # 替换为你的透视表工作簿路径
   pivot_wb_path = r"C:\docs\pivot_file.xlsx"
   wb = excel.Workbooks.Open(pivot_wb_path)
   

3. 定位目标数据透视表
   通过工作表名+透视表名精准定位：

   # 替换为透视表所在工作表名
   pivot_sheet = wb.Worksheets["透视表页"]
   # 替换为你的透视表名称
   pivot_table = pivot_sheet.PivotTables["月度数据透视表"]
   

4. 修改数据源
   分两种场景处理：

   • 同一工作簿内数据源

     # 替换为数据源所在工作表和数据范围
     new_data_range = wb.Worksheets["最新数据源"].Range("A1:F2000")
     # 重新创建透视缓存并绑定
     new_cache = wb.PivotCaches().Create(SourceType=1, SourceData=new_data_range)
     pivot_table.ChangePivotCache(new_cache)
     

   • 外部工作簿数据源

     external_data_path = r"C:\docs\monthly_data.xlsx"
     # 格式：[文件名]工作表名!数据范围
     data_ref = f"[{os.path.basename(external_data_path)}]数据源页!A1:F2000"
     # 用完整路径格式创建缓存
     new_cache = wb.PivotCaches().Create(SourceType=1, SourceData=f"'{os.path.dirname(external_data_path)}'!{data_ref}")
     pivot_table.ChangePivotCache(new_cache)
     

5. 更新透视表并保存

   pivot_table.RefreshTable()
   wb.Save()
   # 关闭资源
   wb.Close()
   excel.Quit()
   

方法二：使用openpyxl（仅支持xlsx，功能有限） #

如果无需复杂操作，可尝试openpyxl，但它无法直接刷新透视表，修改后需手动打开Excel刷新，或结合win32com完成最后一步：

1. 安装依赖

   pip install openpyxl
   

2. 修改数据源范围

   from openpyxl import load_workbook
   
   wb = load_workbook(r"C:\docs\pivot_file.xlsx")
   pivot_sheet = wb["透视表页"]
   # 获取第一个透视表
   pivot_table = pivot_sheet._pivots[0]
   
   # 修改数据源引用
   pivot_table.cacheSource.worksheetSource.ref = "最新数据源!A1:F2000"
   # 设置打开时自动刷新
   pivot_table.cacheSource.refreshOnLoad = True
   
   wb.save(r"C:\docs\updated_pivot.xlsx")
   

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