无法用xlwings/win32com打开Excel，pandas读取公式值为NaN求助

问题情况 #

• 程序逻辑：编辑Excel首工作表数据 → 第二工作表基于首表做数学运算与拟合 → 第三工作表提取参数到一行
• 读取异常：用pandas.read_excel读第三表时，公式生成的数值显示为NaN，手动输入的数值读取正常
• 需求：把第三表的参数存入DataFrame后导入其他Excel
• 尝试过的操作：用load_workbook保存、xlwings/win32com打开刷新保存，均触发COM错误

触发的错误信息 #

(-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2147352565), None)
...
com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft Excel', 'Open method of workbooks class failed', 'xlmain11.chm', 0, -2146827284), None)

之前尝试的代码 #

xlwings 示例 #

import xlwings as xl
import win32com.client as win32
def refresh_data(path):
    wb = load_workbook(path)
    ex_app = xl.App(visible=False)
    ex_book = ex_app.books.open(path)
    ex_book.save()
    ex_book.close()
    ex_app.quit()

win32com 示例 #

import win32com.client as win32
XLAPP = win32.DispatchEx("Excel.Application")
book= XLAPP.Workbooks.Open(path)
book.RefreshAll()
XLAPP.CalculateUntilAsyncQueriesDone()
book.save()
XLAPP.Quit()

解决办法 #

办法1：修复xlwings代码，强制刷新公式 #

原代码未触发公式计算，还混用了openpyxl的load_workbook导致冲突，修改后：

import xlwings as xl

def refresh_excel_formulas(path):
    # 启动后台Excel实例，自动管理生命周期
    with xl.App(visible=False, add_book=False) as app:
        wb = app.books.open(path)
        # 强制计算所有公式
        wb.app.calculate()
        wb.save()
        wb.close()

运行此函数后，再用pandas.read_excel读取第三表，公式生成的数值即可正常读取。

办法2：用openpyxl计算公式（无需Excel客户端） #

如果不想依赖本地Excel客户端，可直接用openpyxl计算公式：

from openpyxl import load_workbook
import pandas as pd

# 加载工作簿，保留公式（data_only=False）
wb = load_workbook(filename=path, data_only=False)
# 强制计算所有工作表的公式
wb.calculate()
# 保存计算结果
wb.save(path)
# 读取第三工作表（索引从0开始，2对应第三表）
df = pd.read_excel(path, sheet_name=2)

注意：openpyxl对复杂自定义公式支持有限，Excel内置函数可正常处理。

办法3：修复win32com代码的路径与权限问题 #

COM错误多由路径格式错误或Excel进程残留导致，修改后代码：

import win32com.client as win32
import os

def refresh_win32com(path):
    abs_path = os.path.abspath(path)
    XLAPP = win32.DispatchEx("Excel.Application")
    # 关闭警告提示，避免程序被阻断
    XLAPP.DisplayAlerts = False
    XLAPP.AskToUpdateLinks = False
    try:
        book = XLAPP.Workbooks.Open(abs_path)
        # 先全量计算公式，再刷新外部连接
        XLAPP.CalculateFull()
        book.RefreshAll()
        XLAPP.CalculateUntilAsyncQueriesDone()
        book.Save()
    finally:
        book.Close()
        XLAPP.Quit()
        # 释放COM对象，防止Excel进程残留
        del XLAPP

执行后再读取Excel即可获取正确的公式计算值。

内容的提问来源于stack exchange，提问作者Jalaj Sanjay Mehta