使用Xlwings保存Excel工作簿同名覆盖失败问题求助

问题描述 #

使用Python的Xlwings包打开现有Excel文件，写入内容后尝试以相同名称覆盖原文件保存时崩溃，报错Cannot access 'my_file.xlsx'，但换不同名称保存正常，使用os.path.normpath()也无效。

代码片段：

import xlwings as xw

excel_file = "C:\Working Drive\Automation Scripts\my_file.xlsx"
with xw.App(visible=True) as app:
    wb = app.books.open(excel_file)
    ws = wb.sheets['MySheet']
    # write into some cells...
    wb.save(excel_file)
    wb.close()

报错信息：

Traceback (most recent call last):
  File "test.py", line 30, in <module>
    wb.save(excel_file)
  File "C:\Users\XXX\AppData\Local\Programs\Python\Python38\lib\site-packages\xlwings\main.py", line 1147, in save
    self.impl.save(path, password=password)
  File "C:\Users\XXX\AppData\Local\Programs\Python\Python38\lib\site-packages\xlwings\_xlwindows.py", line 847, in save
    self.xl.SaveAs(
  File "C:\Users\XXX\AppData\Local\Programs\Python\Python38\lib\site-packages\xlwings\_xlwindows.py", line 1-9, in __call__
    v = self.__method(*args, **kwargs)
  File "C:\Users\XXX\AppData\Local\Temp\gen_py\3.8\00020813-0000-0000-C000-000000000046x0x1x9.py", line 45271, in SaveAs
    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
pywintypes.com_error: (-214735267, 'Exception Occured', (0, "Microsoft Excel", "Cannot access 'my_file.xlsx'.", 'xlmain11.chm', 0, -2146827284), None)

解决方案 #

1. 直接调用无参数wb.save() #

对于已打开的工作簿，无需指定路径，直接调用wb.save()即可覆盖原文件，Xlwings会自动使用工作簿的原始路径：

import xlwings as xw

# 使用原始字符串避免路径转义问题
excel_file = r"C:\Working Drive\Automation Scripts\my_file.xlsx"
with xw.App(visible=True) as app:
    wb = app.books.open(excel_file)
    ws = wb.sheets['MySheet']
    # write into some cells...
    wb.save()  # 无需指定路径
    wb.close()

2. 临时文件替换法 #

如果第一种方法无效，可先保存到临时文件，再替换原文件：

import xlwings as xw
import shutil
import os

excel_file = r"C:\Working Drive\Automation Scripts\my_file.xlsx"
temp_file = r"C:\Working Drive\Automation Scripts\my_file_temp.xlsx"

with xw.App(visible=True) as app:
    app.display_alerts = False  # 关闭保存弹窗提示
    wb = app.books.open(excel_file)
    ws = wb.sheets['MySheet']
    # write into some cells...
    wb.save(temp_file)
    wb.close()

# 替换原文件
if os.path.exists(excel_file):
    os.remove(excel_file)
shutil.move(temp_file, excel_file)

3. 排查文件占用问题 #

• 关闭资源管理器的预览窗格，避免文件被锁定
• 确保Excel文件未被其他程序（如杀毒软件、同步工具）占用
• 以管理员身份运行Python脚本，避免权限问题

4. 优化路径处理 #

使用原始字符串或双反斜杠避免路径转义错误：

# 原始字符串
excel_file = r"C:\Working Drive\Automation Scripts\my_file.xlsx"
# 或双反斜杠
excel_file = "C:\\Working Drive\\Automation Scripts\\my_file.xlsx"

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