Python更新Excel数据工作表时VB RefreshAll事件未触发，如何解决？

核心原因 #

你使用的openpyxl库（load_workbook是它的方法）仅直接操作Excel文件的底层格式，不会启动Excel应用程序，因此：

• 绑定在data工作表的Worksheet_Change事件完全不会触发，自然无法执行RefreshAll操作
• 数据透视表依赖Excel的计算引擎完成刷新，openpyxl无法触发该逻辑，导致Sheet1的透视表保留旧数据，pd.read_excel读取的也还是旧值

解决方案：用win32com.client调用Excel实例 #

通过Python调用真实的Excel应用程序，模拟人工操作流程，既能触发VBA事件，也能完成透视表刷新，步骤如下：

1. 安装依赖 #

先安装pywin32库以实现Excel调用：

pip install pywin32

2. 修改后的Python脚本 #

import pandas as pd
import win32com.client as win32
import os

excel_filepath = "test_excel_file2.xlsm"
txt_filepath = "txt_excel.txt"
data_sheet = "data"
calc_sheet = "Sheet1"

# 读取txt数据
df = pd.read_csv(txt_filepath, sep="|")
print("读取的txt数据：")
print(df)

# 启动Excel应用并打开目标文件
excel = win32.gencache.EnsureDispatch('Excel.Application')
excel.Visible = False  # 调试时可设为True，直观查看Excel操作过程
wb = excel.Workbooks.Open(os.path.abspath(excel_filepath))

# 清空data工作表并写入新数据
ws_data = wb.Worksheets(data_sheet)
ws_data.Cells.ClearContents()  # 保留格式仅清空内容，如需删除行可改用ws_data.Rows.Delete
# 写入表头
for col_num, header in enumerate(df.columns, 1):
    ws_data.Cells(1, col_num).Value = header
# 写入数据行
for row_num, row_data in enumerate(df.values, 2):
    for col_num, value in enumerate(row_data, 1):
        ws_data.Cells(row_num, col_num).Value = value

# 因通过Excel实例修改数据，会自动触发Worksheet_Change事件执行RefreshAll
# 若事件触发条件严格或失效，可手动调用刷新
wb.RefreshAll()
excel.CalculateUntilAsyncQueriesDone()  # 等待所有刷新、计算操作完成

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

# 读取更新后的Sheet1数据
new_df = pd.read_excel(excel_filepath, sheet_name=calc_sheet)
print("\n更新后的Sheet1数据：")
print(new_df)

补充说明 #

• 若Worksheet_Change事件有特定触发条件（如仅修改某列时触发），需确保脚本的写入操作符合条件；若不确定，直接手动调用wb.RefreshAll()更稳妥
• 操作完成后必须调用wb.Close()和excel.Quit()，避免后台残留Excel进程
• 非Windows环境无法使用win32com，可尝试用openpyxl标记透视表缓存需刷新（但仅在手动打开Excel时才会生效，无法通过pd.read_excel直接获取更新后数据）：

  from openpyxl import load_workbook
  
  wb = load_workbook(excel_filepath, keep_vba=True, data_only=False)
  # 此处保留你原有的data工作表修改代码...
  
  # 标记Sheet1的透视表缓存需刷新
  ws_calc = wb[calc_sheet]
  for pt in ws_calc._pivots:
      pt.cache.refreshOnLoad = True
  
  wb.save(excel_filepath)
  

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