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

Georgena

Could you post the exception and the traceback? – Lefthanded

As I said in my post, it is an excel error message : 'Your changes could not be saved to ' FileName ' because of a sharing violation. Try saving to a different file' – Georgena

documentation of pandas does not mention the use of save and close, what if you do not use them? And are you sure you need Writer? Couldn't you pass directly the outpah? – Lefthanded

You should use a context manager instead of an explicit save() and close(). with pd.ExcelWriter(outpath, engine="xlsxwriter") as writer: – Brisket

Answer 1

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.

Sexless

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.

Burble

I'm using this but it's hanging still :( – Clayson

Answer 3

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

Roid

The command writer.handles = None give me the error: AttributeError: 'NoneType' object has no attribute 'handle' This is despite the writer working otherwise. Sharing conflicts persist. Ideas? – Diluvial

That error indicates that writer is null when you try to set handles. – Roid

@Cfrietas with Pandas 1.5.3 ExcelWriter.handles has no setter. In 2.1.3, there's no handles. – Enamour

Answer 4

I was facing a similar situation. The suggestion given by alec_djinn didn't work for multiple sheets, as I was working. So I just ignored .close() method and it worked just fine.

Hansom

Answer 5

For the writer.handles = None error, AttributeError: 'NoneType' object has no attribute 'handle'

I got around it by writing it like this:

try:
    writer.handles = None
except:
    ''

Vicinity

But... if writer is None, setting handles on it isn't going to have any effect – Enamour