Pandas mode='a', if_sheet_exists='overlay' not working

user_14357963 0
Viewed 2760

Even using the code below, the content on each sheet of the .xlsx file is overwritten, not appended. What is missing?

writer = pd.ExcelWriter(excelfilepath, engine='openpyxl', mode='a', if_sheet_exists='overlay')

df1.to_excel(writer, sheet_name='Núcleo de TRIAGEM')

df2.to_excel(writer, sheet_name='Núcleo de FALÊNCIAS')

df3.to_excel(writer, sheet_name='RE - Triagem')

df4.to_excel(writer, sheet_name='RE - Falências')

writer.save()
pandas xlsx excel python
4 Answers

I know this is not typical, and most likely will be fixed in future versions of Pandas, but using the startrow with version 1.4.2 worked for me. Try the following code:

writer = pd.ExcelWriter(excelfilepath, engine='openpyxl', mode='a', if_sheet_exists='overlay')

df1.to_excel(writer, sheet_name='Núcleo de TRIAGEM', startrow=writer.sheets['Núcleo de TRIAGEM'].max_row, header=None)

df2.to_excel(writer, sheet_name='Núcleo de FALÊNCIAS', startrow=writer.sheets['Núcleo de FALÊNCIAS'].max_row, header=None)

df3.to_excel(writer, sheet_name='RE - Triagem', startrow=writer.sheets['RE - Triagem'].max_row, header=None)

df4.to_excel(writer, sheet_name='RE - Falências', startrow=writer.sheets['RE - Falências'].max_row, header=None)

writer.save()
user_136111560

For anyone in the future. Check your Pandas version if if_sheet_exists='overlay' is not working. That was added to pandas in the version 1.4.0. Just try updating it.

user_145256630

I was able to fix this by upgrading my version of pandas.

This requires you to first verify your version of pandas. You can do this by

print(pandas.__version__)

In my case, I was using pandas 1.3.5. I then ran

pip install --upgrade pandas

It then uninstalled the old version and installed the new version. Also, this did not work when I ran it as sudo or admin user.

user_103712380

I just upgraded to pandas 1.4.4 and still not work, but Omar Zaki's solution is working like a charm.

user_155694160
Related