11

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

AKX
  • 152,115
  • 15
  • 115
  • 172
Nicolas Peille
  • 149
  • 1
  • 2
  • 10
  • Could you post the exception and the traceback? – olinox14 Jun 25 '19 at 09:50
  • 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' – Nicolas Peille Jun 25 '19 at 09:52
  • [documentation of pandas](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.ExcelWriter.html) 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? – olinox14 Jun 25 '19 at 09:54
  • 1
    You should use a context manager instead of an explicit `save()` and `close()`. `with pd.ExcelWriter(outpath, engine="xlsxwriter") as writer:` – Exho Jun 25 '19 at 09:54

5 Answers5

11

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
CFreitas
  • 1,647
  • 20
  • 29
  • 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? – fact_finder Dec 15 '21 at 16:38
  • That error indicates that `writer` is null when you try to set `handles`. – CFreitas Dec 16 '21 at 18:16
11

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.

J.B.
  • 111
  • 1
  • 2
2

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.

alec_djinn
  • 10,104
  • 8
  • 46
  • 71
0

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.

Dênnyson Eliseu
  • 1
0

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:
    ''
OscarG
  • 1
  • 1