Unable to close pd.ExcelWriter() file

Question

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.

Answer 1

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