我使用 python 使用 win32com.client 库刷新 Excel 中的一些电源查询表。
- 如果我将所有内容都写为“内联”(第一个版本):Excel 正确关闭
- 如果我使用“with”语句打开/关闭 Excel 和工作簿(第二版):Excel 进程不会关闭
==> 为什么它不做同样的事情?
==> 如何使“with”版本正常工作?
代码“内联”:Excel正确关闭:
# Start excel app
xlapp = win32.gencache.EnsureDispatch('Excel.Application')
xlapp.Visible = False
xlapp.Interactive = False
excel_constant = win32.constants
# Open workbook
file_name = str(file)
wb = xlapp.Workbooks.Open(file_name)
#Refresh power query
wb.RefreshAll()
xlapp.CalculateUntilAsyncQueriesDone()
# Close workbook
wb.Close(SaveChanges=True)
wb = None
gc.collect()
# Quit excel app
xlapp.Quit()
xlapp = None
gc.collect()
使用“with”使 Excel 进程保持活动状态
class start_excel(object):
def __init__(self):
pass
def __enter__(self):
self.xlapp = win32.gencache.EnsureDispatch('Excel.Application')
self.xlapp.Visible = False
self.xlapp.Interactive = False
return self.xlapp
def __exit__(self, *args):
self.xlapp.Quit()
self.xlapp = None
gc.collect()
class open_workbook(object):
def __init__(self, xlapp, file_name):
self.file_name = file_name
self.xlapp = xlapp
def __enter__(self):
self.chemin_fichier = str(self.file_name)
self.wb = self.xlapp.Workbooks.Open(self.chemin_fichier)
return self.wb
def __exit__(self, *args):
self.wb.Close(SaveChanges=True)
self.wb = None
gc.collect()
with start_excel() as xlapp:
# excel_constant = win32.constants
with open_workbook(xlapp, file) as wb:
wb.RefreshAll()
xlapp.CalculateUntilAsyncQueriesDone()
编辑:第三个示例,带有 contextlib.contextmanager (同样的问题)
import contextlib
@contextlib.contextmanager
def start_excel():
try:
xlapp = win32.gencache.EnsureDispatch('Excel.Application')
xlapp.Visible = False
xlapp.Interactive = False
yield xlapp
finally:
xlapp.Quit()
xlapp = None
gc.collect()
@contextlib.contextmanager
def open_workbook(xlapp, file_name):
try:
chemin_fichier = str(file_name)
wb = xlapp.Workbooks.Open(chemin_fichier)
yield wb
finally:
wb.Close(SaveChanges=True)
wb = None
gc.collect()
with start_excel() as xlapp:
# excel_constant = win32.constants
with open_workbook(xlapp, file) as wb:
wb.RefreshAll()
xlapp.CalculateUntilAsyncQueriesDone()
编辑:下面的代码可以工作但不令人满意我添加了最后两行:wb = None
& xlapp = None
,Excel 正确关闭。
但感觉不安全,有没有办法确保 with 语句干净地清除其变量?我觉得每次使用 with 时很容易忘记包含这些附加行。
class start_excel(object):
def __init__(self):
pass
def __enter__(self):
self.xlapp = win32.gencache.EnsureDispatch('Excel.Application')
self.xlapp.Visible = False
self.xlapp.Interactive = False
return self.xlapp
def __exit__(self, *args):
self.xlapp.Quit()
self.xlapp = None
gc.collect()
class open_workbook(object):
def __init__(self, xlapp, file_name):
self.file_name = file_name
self.xlapp = xlapp
def __enter__(self):
self.chemin_fichier = str(self.file_name)
self.wb = self.xlapp.Workbooks.Open(self.chemin_fichier)
return self.wb
def __exit__(self, *args):
self.wb.Close(SaveChanges=True)
self.wb = None
gc.collect()
with start_excel() as xlapp:
# excel_constant = win32.constants
with open_workbook(xlapp, file) as wb:
wb.RefreshAll()
xlapp.CalculateUntilAsyncQueriesDone()
wb = None
xlapp = None