Exporting n different dataframes into separate sheets of one xlsx file

I have a for loop that processes multiple different dataframes that I am trying to export into one .xlsx file but in separate sheets. The dataframes are listed as dfs[i] and a corresponding sheet name as sn[i]. save_file is obtained from tkinter asksaveasfilename.

In the following loop I am trying to export the dataframes to Excel sheet tabs but I am only getting a single tab with the final dataframe in the list:

for i in range(len(dfs)):

    writer = pd.ExcelWriter(save_file.get(), engine = 'xlsxwriter')
    dfs[i].to_excel(writer, sheet_name = sn[i], index=False)
    writer.close()

Similarly, I have tried this command with the same result:

for i in range(len(dfs)):
     with pd.ExcelWriter(save_file.get()) as writer:
          dfs[i].to_excel(writer, sheet_name = sn[i], index=False)

When I have tested out print(dfs[i]) and print(sn[i]) in that for loop it has been displaying exactly as intended, but am confused as to why only the last element in the list is getting exported to Excel. Any assistance would be much appreciated, thank you!

>Solution :

Try this:

 for i in range(len(dfs)):
    if not os.path.exists(save_file.get()):
        with pd.ExcelWriter(save_file.get(), engine="openpyxl", mode="w") as writer:
            dfs[i].to_excel(writer, sheet_name=sn[i])
    with pd.ExcelWriter(save_file.get(), engine="openpyxl", mode="a", if_sheet_exists="replace") as writer:
        dfs[i].to_excel(writer, sheet_name=sn[i])

You’ll need pip install openpyxl first.