Append dataframes to multiple Excel sheets

I’m trying to append 3 dataframes to 3 existing sheets in an Excel file (one dataframe per sheet).

This is my code:

with pd.ExcelWriter(output_path, mode="a", if_sheet_exists="overlay") as writer:
    df_a.to_excel(writer, sheet_name="A", index=False)
    df_b.to_excel(writer, sheet_name="B", index=False)
    df_c.to_excel(writer, sheet_name="C", index=False)

However, the new data overwrites the old data rather than being appended at the end of the corresponding sheet. Note that I set mode="a" and if_sheet_exists="overlay", yet it overwrites and doesn’t append.

How should I fix it?

Answer

You have to find last row and land new dataframe after it. assuming you have some data in place and all headers, you can test like below:

with pd.ExcelWriter(output_path, mode="a", if_sheet_exists="overlay") as writer:
    # getting last row from Sheet "A" and adding 1 as a starting row
    lrow = pd.read_excel(output_path, sheet_name="A").shape[0]+1
    # startrow statement at the end of the code shows there to start placing new data
    dff.to_excel(writer, sheet_name="A", index=False,header=False,startrow=lrow)

function .shape gives the amount of rows and columns for example (1235,66), – using .shape[0], only amount of rows is taken