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

Question

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()

any luck with this issue? did you manage to solve this? can you help me? I also encounter the same issue. — The Great, Mar 18 '22 at 16:13
No solution yet @TheGreat. I made a comearound, reading the entire excel file, adding the new data and than overwritting the excel file. — Victor Faraon, Mar 21 '22 at 18:36
I have an issue with excel file reading and writing using python - Would you be able to help? https://stackoverflow.com/questions/71557677/xlwings-enter-data-in-a-excel-cell-only-when-it-matches-with-df-row-and-col — The Great, Mar 22 '22 at 05:14
see my answer, this is on purpose, you have to use startrow to insert below the existing data — jmd, Feb 22 '23 at 22:41

score 8 · Answer 1 · answered Apr 03 '22 at 10:29

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()

Tried this but got "Exception has occurred: IndexError At least one sheet must be visible" — MrT77, Dec 06 '22 at 10:03
This exception about visible sheet is risen when you try to close the file without actually writing data to it. You have something wrong in your code, maybe an exception before you get to the end. — jmd, Feb 22 '23 at 22:43

score 2 · Answer 2 · answered Mar 25 '22 at 05:07

2

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.

answered Mar 25 '22 at 05:07

Carlost

478
5
13

1

I've tried in pandas 1.4.0 and 1.4.1 and no luck, unfortunately. – TimO Apr 01 '22 at 13:08
can you share your code? – Carlost Apr 01 '22 at 14:26
1

Exactly, I've tried in pandas 1.4.0, and it's nor working. – Victor Faraon Apr 02 '22 at 12:32
tried with 1.4.0, 1.4.1, 1.4.2 .. but not working – Harsha Biyani Jun 22 '22 at 10:01
Guys, this only works with mode='a' – Carlost Aug 02 '22 at 15:03
I've tried with 1.5.1 (in MacOs) but got the same problem, it just writes on top, doesn't append. – MrT77 Dec 06 '22 at 10:04

score 1 · Answer 3 · answered Feb 20 '23 at 18:35

Nothing is missing. The intent of the overlay option is to write over the existing data, and not to append the new data below it.
The old data will in fact remain only for the part that was larger than the new one you're writing, or if you are explicitly setting startrow to a value high enough to write your new data below it.

As proposed already, you can do it like this :

df1.to_excel(writer, sheet_name='sheet1', startrow=writer.sheets['sheet1'].max_row, header=None)

This all becomes more obvious when you see that the initial name proposed for that option was overwrite_cells and not overlay.

score 0 · Answer 4 · edited Jun 22 '22 at 14:01

0

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.

edited Jun 22 '22 at 14:01

Gino Mempin

25,369
29
96
135

answered Apr 06 '22 at 01:56

oldstudent

25
6

3

It would be helpful to say to which pandas version you upgraded to and found working. – Gino Mempin Jun 22 '22 at 13:19

Lijo Abraham · Answer 5 · 2023-07-03T06:13:32.280

We can write to excel by creating a utility function.

def save_excel_sheet(df, excel_path, sheet_name):
    if not os.path.exists(excel_path):
        df.to_excel(excel_path, sheet_name=sheet_name, index=False)
    else:
        with pd.ExcelWriter(excel_path, engine='openpyxl', if_sheet_exists='overlay', mode='a') as writer:
            df.to_excel(writer, sheet_name=sheet_name, startrow=writer.sheets[sheet_name].max_row, header=None, index=False)

and call the function like below

save_excel_sheet(df1, excel_path, sheet_name='Núcleo de TRIAGEM')

save_excel_sheet(df3, excel_path, sheet_name='RE - Triagem')

This will create a file if not exists and append to the provided sheet if the file exists.

Pandas Version - 2.0.2

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

5 Answers5

Linked