xlwings调用Book.save()报错：SaveAs Method of Workbook class failed

问题场景 #

使用xlwings打开并操作Excel文件后，调用wb.save()时偶发报错，报错提示SaveAs method of Workbook class failed，核心代码及报错信息如下：

核心代码 #

Some_data = r"C:\Users\FrodeWilkensen\OneDrive - Norfund\Frode Wilkensen\Some_data.xlsx"
Original = r"C:\Users\FrodeWilkensen\OneDrive - Norfund\Frode Wilkensen\Original.xlsx"

df = pd.read_excel(Some_data, engine = "openpyxl")

wb = xw.Book(Original)
ws = wb.sheets["dump"]
ws["B2"].options(pd.DataFrame, index=False, header=False, expand="table").value = df

wb.save(r"C:\Users\FrodeWilkensen\OneDrive - Norfund\Frode Wilkensen\dumped.xlsx")
wb.app.kill()

报错信息 #

---------------------------------------------------------------------------
com_error                                 Traceback (most recent call last)
Input In [49], in <cell line: 11>()
      7 ws = wb.sheets["dump"] #the sheet called "dump" assigned as ws
      8 ws["B2"].options(pd.DataFrame, index=False, header=False, expand="table").value = df #some operations
---> 11 wb.save(r"C:\Users\FrodeWilkensen\OneDrive - Norfund\Frode Wilkensen\dumped.xlsx") #want to save it in same directoy
     12 #gets thrown an error, shown below.
     13 wb.app.kill()

File ~\anaconda3\lib\site-packages\xlwings\main.py:874, in Book.save(self, path)
    872     path = utils.fspath(path)
    873 with self.app.properties(display_alerts=False):
---> 874     self.impl.save(path)

File ~\anaconda3\lib\site-packages\xlwings\_xlwindows.py:582, in Book.save(self, path)
    580 alerts_state = self.xl.Application.DisplayAlerts
    581 self.xl.Application.DisplayAlerts = False
---> 582 self.xl.SaveAs(os.path.realpath(path), FileFormat=file_format)
    583 self.xl.Application.DisplayAlerts = alerts_state

File ~\anaconda3\lib\site-packages\xlwings\_xlwindows.py:70, in COMRetryMethodWrapper.__call__(self, *args, **kwargs)
     68 while True:
     69     try:
---> 70         v = self.__method(*args, **kwargs)
     71         if isinstance(v, (CDispatch, CoClassBaseClass, DispatchBaseClass)):
     72             return COMRetryObjectWrapper(v)

File C:\Users\FRODEW~1\AppData\Local\Temp\gen_py\3.9\00020813-0000-0000-C000-000000000046x0x1x9.py:46807, in _Workbook.SaveAs(self, Filename, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local, WorkIdentity)
  46804 def SaveAs(self, Filename=defaultNamedNotOptArg, FileFormat=defaultNamedNotOptArg, Password=defaultNamedNotOptArg, WriteResPassword=defaultNamedNotOptArg
  46805         , ReadOnlyRecommended=defaultNamedNotOptArg, CreateBackup=defaultNamedNotOptArg, AccessMode=1, ConflictResolution=defaultNamedOptArg, AddToMru=defaultNamedOptArg
  46806         , TextCodepage=defaultNamedOptArg, TextVisualLayout=defaultNamedOptArg, Local=defaultNamedOptArg, WorkIdentity=defaultNamedOptArg):
> 46807     return self._oleobj_.InvokeTypes(3174, LCID, 1, (24, 0), ((12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (3, 49), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17)),Filename
  46808         , FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup
  46809         , AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout
  46810         , Local, WorkIdentity)

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

报错原因分析 #

这个错误确实属于VBA层面的调用失败——xlwings在Windows平台底层通过COM接口调用Excel的SaveAs方法，报错直接来自Excel本身而非xlwings框架。偶发出现大概率和OneDrive同步锁定、文件格式自动判断错误、文件占用冲突有关。

解决方案 #

1. 规避OneDrive同步锁定问题 #

OneDrive的实时同步可能在文件写入时锁定文件，导致SaveAs失败：

• 临时方案：先保存到本地非OneDrive目录，再移动到目标路径：

  import shutil
  temp_path = r"C:\Temp\dumped_temp.xlsx"
  wb.save(temp_path)
  shutil.move(temp_path, r"C:\Users\FrodeWilkensen\OneDrive - Norfund\Frode Wilkensen\dumped.xlsx")
  

• 长期方案：关闭OneDrive对该文件夹的实时同步，或手动触发同步。

2. 显式指定文件格式 #

xlwings自动判断格式时可能出错，显式指定格式可避免问题：

# xlsx格式对应FileFormat=51，xls对应56
wb.save(r"C:\Users\FrodeWilkensen\OneDrive - Norfund\Frode Wilkensen\dumped.xlsx", file_format=51)

3. 添加重试机制 #

针对偶发的资源冲突，用重试逻辑覆盖：

import time

max_retries = 3
retry_count = 0
save_success = False

while retry_count < max_retries and not save_success:
    try:
        wb.save(r"C:\Users\FrodeWilkensen\OneDrive - Norfund\Frode Wilkensen\dumped.xlsx", file_format=51)
        save_success = True
    except Exception as e:
        retry_count += 1
        time.sleep(1)  # 等待1秒重试
        if retry_count == max_retries:
            raise e

4. 确保文件未被占用 #

• 检查目标文件是否被Excel或其他程序打开，提前关闭；
• 使用with语句管理工作簿，确保资源及时释放：

  with xw.Book(Original) as wb:
      ws = wb.sheets["dump"]
      ws["B2"].options(pd.DataFrame, index=False, header=False, expand="table").value = df
      wb.save(r"C:\Users\FrodeWilkensen\OneDrive - Norfund\Frode Wilkensen\dumped.xlsx", file_format=51)
  

5. 避免解析OneDrive虚拟路径 #

xlwings默认用os.path.realpath解析路径，但OneDrive虚拟路径和真实路径可能不一致，直接传入原始路径或用xlwings内置工具：

from xlwings import utils

save_path = utils.fspath(r"C:\Users\FrodeWilkensen\OneDrive - Norfund\Frode Wilkensen\dumped.xlsx")
wb.save(save_path, file_format=51)

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