I have read this answer:
Pandas / xlsxwriter writer.close() does not completely close the excel file
but this solution won't work for me as the solution for this was to not use an ExcelWriter as they are only writing one sheet to the file. They're also using xlsxwriter as their engine where I'm using openpyxl (if that makes a difference).
I have an object which uses a pandas ExcelWriter object to write a series of sheets to an xlsx file.
import pandas as pd
with pd.ExcelWriter("file.xlsx",mode='w') as stream:
for sheet, dataframe in dataframe_dictionary.items():
dataframe.to_excel(stream, sheet_name=sheet)
other_dataframe.to_excel(stream, sheet_name='other')
The problem is that sometimes, if an exception is raised while inside the context manager, pandas does not close the file properly. So when I try to delete it (outside my python script, after the python script has finished running) I get an error message saying
"The action can't be completed because the file is open in Python"
Is this a bug in pandas or am I doing something wrong? Do context managers need to be put inside try, except blocks? I thought the whole point of the context manager was that it took care of the clean up for you? Is there any way to check if any files are still open and close them if they are? I am running the script in Jupyter Notebook.
Hi I might be one year too late but I was facing the same issue but seem to have solved it. I deleted the ExcelWriter object in Python after my file has been saved. So you could try out the following: