Using excel from Python require to set all references to workbooks & excel app to None at the end to be sure it closes (using the API win32com.client).
At first I expected to be able to use the with keyword and include all the "clean up" code in the __exit__() function. But I still have references that needs to be cleaned up when I do it this way...
==> Is it possible to do it in a better way? Better = all the clean up code in packaged in the exit() function. (not using contextmanager has the same issue: Excel doesn't close if I use the "with" keyword instead of "inline" python )
example: I have to set wb & xlapp to None outside for the __exit__() function
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
thanks
