I am working with an Excel file containing multiple sheets and I want to append a new sheet to it when I am done with my code. It works perfectly fine on the python side.
But when I open the Excel file and try to save it after modifying it, I get a sharing violation from Excel. I am using Spyder and if I restart the kernel, I don't get the error anymore, so there is a workaround, so there is no urgency for an answer. But I think it would be nice to have a clean way to handle this issue.
I am using python 3.11, pandas 2.2.2, openpyxl 3.1.2
Here is a snippet of my code :
import pandas as pd
import os
"""I first open the file, which is, and was working perfectly fine before"""
path_to_excel = os.path.realpath(r"Something\Something\myfile.xlsx")
df_excel = pd.read_excel(io=path_to_excel ,sheet_name='Sheet 1', index_col=2)
"""Some code to build a new dataframe called df_new"""
"""Then I append df_new to myfile.xlsx"""
with pd.ExcelWriter(path=path_to_excel, mode='a',if_sheet_exists="replace",engine="openpyxl") as Writer:
df_new.to_excel(excel_writer=Writer,sheet_name="Sheet 2",float_format="%.2f",index_label="Cool index name")
"""I also tried to write it this way, but the result is the same"""
Writer = pd.ExcelWriter(path=path_to_excel, mode='a',if_sheet_exists="replace",engine="openpyxl")
df_new.to_excel(excel_writer=Writer,sheet_name="Sheet 2",float_format="%.2f",index_label="Cool index name")
Writer.close()
Writer.handles=None
Any hindsight would be appreciated thank you !
So... the issue vanished, both versions are working now, the only thing I did was restarting the kernel, so I guess the issue came from Spyder. I don't really know what happened so if anyone have an idea, I am curious to hear about it.