Python:使用 Win32 COM Api 打开 Excel 工作簿 [英] Python: Open Excel Workbook using Win32 COM Api

问题描述

我正在使用以下代码在 Excel 中打开和显示工作簿:

I'm using the following code to open and display a workbook within Excel:

import win32com.client as win32
excel = win32.gencache.EnsureDispatch('Excel.Application')
wb = excel.Workbooks.Open('my_sheet.xlsm')
ws = wb.Worksheets('blaaaa') 
excel.Visible = True

当文件my_sheet.xlsm"已经打开时，Excel 会询问我是否要重新打开而不保存.

When the File 'my_sheet.xlsm' is already opened, Excel asks me whether I want to re-open it without saving.

如何提前查看工作簿是否已打开，如果已打开，只需将其放在最前面?

How could I check in advance whether the workbook is already opened up and in case it is, just bring it to the front?

编辑:现在发现:

if excel.Workbooks.Count > 0:
    for i in range(1, excel.Workbooks.Count+1):
        if excel.Workbooks.Item(i).Name is 'my_sheet.xlsm':
            wb = excel.Workbooks.Item(i)
            break

还有一个问题:我的工作表包含一些标题，我在其中启用了一些过滤.所以当设置过滤器时，当我从 Python 打开工作簿时，它有时会要求我输入一个唯一的名称来保存过滤器.这是为什么?这是对话:

And one more question: My worksheet contains some headers where I enabled some filtering. So when the filters are set, and when I open the Workbook from Python, it sometimes asks me to enter a unique name to save the filter. Why is that? This is the dialogue:

编辑 好的，这里说(用德语)后一个问题是 2007 和 2010 文件中的一个已知错误:https://social.msdn.microsoft.com/Forums/de-DE/3dce9f06-2262-4e22-a8ff-5c0d83166e73/excel-api-interne-namen 如果您以编程方式打开 Excel 文件，它似乎存在.不知道有没有解决办法.

EDIT Ok here it says (in german language) that the latter problem is a known bug in 2007 and 2010 files: https://social.msdn.microsoft.com/Forums/de-DE/3dce9f06-2262-4e22-a8ff-5c0d83166e73/excel-api-interne-namen and it seems to exist if you open Excel-Files programmatically. Don't know whether there is a workaround.

推荐答案

当您找到解决方案时，请考虑使用 try/except/finally 块.目前，即使您关闭了可见的工作表，您的代码也会让 Excel.exe 进程在后台运行(如果使用 Windows，请检查任务管理器).顺便说一句，在 Python 或任何其他语言(如 VBA)中，应始终在应用程序代码期间干净地发布任何外部 API(例如此 COM 接口).

While you found a solution, consider using try/except/finally block. Currently your code will leave the Excel.exe process running in background (check Task Manager if using Windows) even if you close the visible worksheet. As an aside, in Python or any other language like VBA, any external API such as this COM interface should always be released cleanly during application code.

下面的解决方案使用一个定义的函数，openWorkbook() 走两条可能的路线:1) 首先尝试重新启动指定的工作簿，假设它已打开，2) 如果当前未打开，则启动一个新的该位置的工作簿对象.最后一个嵌套的 try/except 用于防止 Workbooks.Open() 方法失败，例如文件名不正确.

Below solution uses a defined function, openWorkbook() to go two potential routes: 1) first attempts to relaunch specified workbook assuming it is opened and 2) if it is not currently opened launches a new workbook object of that location. The last nested try/except is used in case the Workbooks.Open() method fails such as incorrect file name.

import win32com.client as win32

def openWorkbook(xlapp, xlfile):
    try:        
        xlwb = xlapp.Workbooks(xlfile)            
    except Exception as e:
        try:
            xlwb = xlapp.Workbooks.Open(xlfile)
        except Exception as e:
            print(e)
            xlwb = None                    
    return(xlwb)

try:
    excel = win32.gencache.EnsureDispatch('Excel.Application')
    wb = openWorkbook(excel, 'my_sheet.xlsm')
    ws = wb.Worksheets('blaaaa') 
    excel.Visible = True

except Exception as e:
    print(e)

finally:
    # RELEASES RESOURCES
    ws = None
    wb = None
    excel = None

这篇关于Python:使用 Win32 COM Api 打开 Excel 工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！