A helper function(link below) is not working with the code below. It gives error Sheet1 is existing. Tested with Pandas 1.3.2, openpyxl 3.0.7. I am not sute the function is able to append df to existing sheet, it seems ExcelWriter open a Sheet1 and not able to write to the sheet_name.
https://stackoverflow.com/a/66599924/16857467
if __name__=="__main__":
filename_out = 'excel_output.xlsx'
list_1 = [1,2,3,4,5]
list_2 = [6,7,8,9,10]
df = pd.DataFrame(list_1)
append_df_to_excel(filename_out, df, header=None, index=False)
df = pd.DataFrame(list_2)
append_df_to_excel(filename_out, df, header=None, index=False)
the error message in debug terminal is:
Traceback (most recent call last):
File "c:\Users\user1\userspace\exceltest3.py", line 10, in <module>
append_df_to_excel(filename_out, df, header=None, index=False)
File "c:\Users\user1\userspace\exceltest2.py", line 102, in append_df_to_excel
df.to_excel(writer, sheet_name, startrow=startrow, startcol=startcol, na_rep=na_rep, **to_excel_kwargs)
File "C:\Users\user1\Miniconda3\lib\site-packages\pandas\core\generic.py", line 2284, in to_excel
formatter.write(
File "C:\Users\user1\Miniconda3\lib\site-packages\pandas\io\formats\excel.py", line 840, in write
writer.write_cells(
File "C:\Users\user1\Miniconda3\lib\site-packages\pandas\io\excel\_openpyxl.py", line 436, in write_cells
raise ValueError(
ValueError: Sheet 'Sheet1' already exists and if_sheet_exists is set to 'error'.
and also this try gives same error. writing to another sheet. Workaround is to set if_sheet_exists='replace', since the book already has list_1 sheetname and we can not append to it but replace.
if __name__=="__main__":
filename_out = 'excel_output.xlsx'
list_1 = [1,2,3,4,5]
list_2 = [6,7,8,9,10]
df = pd.DataFrame(list_1)
append_df_to_excel(filename_out, df, sheet_name='list1', header=None, index=False)
df = pd.DataFrame(list_2)
append_df_to_excel(filename_out, df, sheet_name='list2', header=None, index=False)
from documentation of pandas.ExcelWriter, I understand it no longer allow appending df to same sheet.
any workaround or suggestion appreciated.
https://pandas.pydata.org/docs/reference/api/pandas.ExcelWriter.html
if_sheet_exists{‘error’, ‘new’, ‘replace’}, default ‘error’
How to behave when trying to write to a sheet that already exists (append mode only).
error: raise a ValueError.
new: Create a new sheet, with a name determined by the engine.
replace: Delete the contents of the sheet before writing to it.
New in version 1.3.0.
