Append dataframes to multiple Excel sheets-CodePudding

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?

CodePudding user response：

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