Python 和 Excel:覆盖现有文件总是提示，尽管 XlSaveConflictResolution 值

2022-01-14 00:00:00 python excel com activex excel-2007

问题描述

我正在使用 Python 程序中的 Excel.Application COM 对象打开 CSV 文件并将其保存为 Excel 工作簿.如果目标文件已存在，则会提示我以下消息:此位置已存在名为 '...' 的文件.您要替换它吗?"尽管我已经设置了 XlSaveConflictResolution 值到 xlLocalSessionChanges，它应该会自动覆盖更改而不提示 - 或者我是这么认为的.

I'm using the Excel.Application COM object from a Python program to open a CSV file and save it as an Excel workbook. If the target file already exists, then I am prompted with this message: "A file named '...' already exists in this location. Do you want to replace it?" That message comes up despite the fact that I have set the XlSaveConflictResolution value to xlLocalSessionChanges, which is supposed to automatically overwrite the changes without prompting -- or so I thought.

我使用的是 Microsoft Office Excel 2007 (12.0.6535.5002) SP2 MSO 和 ActivePython 2.6.5.14.我已经使用常量和整数尝试了所有三个 XlSaveConflictResolution 值.我没有尝试过不同版本的 Excel.

I'm using Microsoft Office Excel 2007 (12.0.6535.5002) SP2 MSO and ActivePython 2.6.5.14. I have tried all three of the XlSaveConflictResolution values using both constants and integers. I have not tried different versions of Excel.

这是一个代码片段:

import win32com.client
xl = win32com.client.gencache.EnsureDispatch("Excel.Application")
wb = xl.Workbooks.Open(r"C:somefile.csv")
wb.SaveAs(r"C:somefile.xls", win32com.client.constants.xlWorkbookNormal, 
    None, None, False, False, win32com.client.constants.xlNoChange, 
    win32com.client.constants.xlLocalSessionChanges)

以下是 Microsoft 关于 Excel 工作簿对象的 SaveAs 方法的规范:http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook.saveas(VS.80).aspx

And here's the spec from Microsoft about the SaveAs method for an Excel workbook object: http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook.saveas(VS.80).aspx

这可能是 Excel 2007 中的一个新功能"，还是我做错了什么?

Could this be a new "feature" in Excel 2007, or did I just do something wrong?

解决方案

在保存文件前设置 DisplayAlerts 为 False 以抑制警告对话框:

Before saving the file set DisplayAlerts to False to suppress the warning dialog:

xl.DisplayAlerts = False

文件保存后，最好将DisplayAlerts设置回True:

After the file is saved it is usually a good idea to set DisplayAlerts back to True: 

 xl.DisplayAlerts = True

相关文章