Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I'm in the process of automating a monthly report. The full report contains multiple sheets and a combination of dataframes and notes. For some reason, when I attempt to write an Excel file with pd.ExcelWriter, the file created remains 'open' in Python. Excel claims that the file is 'in use'. The only way that I'm able to access it on my device is by restarting my Python kernel, at which point the Excel file loses the connection and becomes available to me to open. I've tried multiple combinations of writer.save(), writer.close(), wb.save(), wb.close() etc, from the code example below, with no success. I was under the impression the code below should work:

import pandas as pd
writer = pd.ExcelWriter('notes_test.xlsx', engine="xlsxwriter")
notes="""Definitions:
1. Pure premium trends are calculated as the product of severity and frequency trends.
Notes:
1. The most recent month of available data is excluded due to the variability of frequency measurements on immature dates."""
wb  = writer.book    # Get the xlsxwriter workbook and worksheet objects.
ws=wb.add_worksheet('Notes')
ws.write(0,0,'Notes')
row=2
for i in notes.split('\n'):
    ws.write(row,0,i)
    row+=1
factors=pd.DataFrame(index=range(3),data={'fact':[1,0.98,0.99]})
factors.to_excel(writer, sheet_name='factors',startrow=4,startcol=0,float_format='%.3f')
ws = writer.sheets['factors']
ws.write(0,0,'Factors')
writer.save()
But that exact code still gives me a file that's perpetually 'sync pending' on my device until I kill the kernel. Thank you for any recommendations or insights.
                What OS are you using and how are running the program: from the command line, from an IDE, etc?
– jmcnamara
                Aug 22, 2022 at 23:03
                @jmcnamara: I'm running this through Jupyter Lab version 3.4 on Windows 10, with Python 3.7.3
– OldKingCole
                Aug 23, 2022 at 13:30
If you would follow a proper with structure, you would not need to close the file manually. The kernel would do it automatically be the end of execution.
import pandas as pd
with pd.ExcelWriter('notes_test.xlsx', engine='xlsxwriter') as writer:
  notes="""Definitions:
1. Pure premium trends are calculated as the product of severity and frequency trends.
Notes:
1. The most recent month of available data is excluded due to the variability of frequency measurements on immature dates."""
  wb = writer.book    # Get the xlsxwriter workbook and worksheet objects.
  ws = wb.add_worksheet('Notes')
  ws.write(0,0,'Notes')
  row = 2
  for i in notes.split('\n'):
      ws.write(row,0,i)
      row += 1
  factors = pd.DataFrame(index=range(3), data={'fact':[1,0.98,0.99]})
  factors.to_excel(writer, sheet_name='factors', startrow=4, startcol=0, float_format='%.3f')
  ws = writer.sheets['factors']
  ws.write(0,0,'Factors')
  #writer.save() no need for this anymore
Looking at the documentation, the simplest (and recommended) way is to open the file using a with context manager.
with pd.ExcelWriter('notes_test.xlsx', engine="xlsxwriter") as writer:
    # do processing
# context manager automatically closes the file
# after the "with" block is exited
The alternative, however, would be to reset the handles property for the writer object.
writer = pd.ExcelWriter('notes_test.xlsx', engine="xlsxwriter")
# ... do work
writer.save()
writer.close()
writer.handles = None
Both solutions should ideally work, but I'd recommend the first option as it's the standard (pythonic) way of using files in Python.
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.