Pandas / xlsxwriter writer.close() does not completely close the excel file

Question

I'm trying to modify manually an excel file after creating it with a python script. Unfortunately, if the script is still running, a sharing file violation error message appears when trying to save it with the same name.

Everything runs smoothly in the code. The file is created, filled and saved. I can open it and work on it but can't overwrite it under the same name if the script is still running.

outpath = filedialog.asksaveasfile(
    mode="wb",
    filetype=[("Excel", ("*.xls", "*.xlsx"))],
    defaultextension=".xlsx",
)
writer = pd.ExcelWriter(outpath, engine="xlsxwriter")
df1.to_excel(writer, sheet_name="Results")
writer.save()
writer.close()

I expect python to fully close the excel file and let me overwrite on it while the script is still running

Answer 1

I also had this issue. When trying to save changes in Excel I got "Sharing violation". Solved it adding writer.handles = None after writer.close().

writer = pd.ExcelWriter(workPath+'file.xlsx', engine='xlsxwriter')
# Add all your sheets and formatting here
...
# Save and release handle
writer.close()
writer.handles = None

Answer 2

I also ran into this. I couldn't save the file in Excel because of a "Sharing violation" because python.exe still had a handle on the file.

The accepted answer, to just use df.to_excel() is correct if all you want to do is save the excel file. But if you want to do more things, such as adding formatting to the excel file first, you will have to use pd.ExcelWriter().

The key is though, as Exho commented, that you use the form:

with pd.ExcelWriter(outpath, engine="xlsxwriter") as writer:
    # do stuff here

You don't use writer.save() or writer.close(), which are synonyms for the same call anyway. Instead the file is saved and closed and handles are released as soon as you leave the with scope.

Answer 3

Your code looks too complicated, you don't need to deal with the writer yourself df.to_excel() can do it for you. Just use the simpler code:df1.to_excel(outpath, sheet_name="Results", engine='xlsxwriter') as suggested in the docs.