Use Pandas to write to excel sheets in specified order

Question

At the end of Pandas .to_excel documentation it shows how to write sheets in order:

>>> writer = pd.ExcelWriter('output.xlsx')
>>> df1.to_excel(writer,'Sheet1')
>>> df2.to_excel(writer,'Sheet2')
>>> writer.save()

Is there a way for me to write to sheet2 first, then sheet1, in my python program. However, I still need the sheet1 to appear before sheet2 in the final excel file?

Answer 1

Writing an empty sheet and then re-writing on the same sheet after computing your summary works:

writer = pd.ExcelWriter(filepath)
stats = []
pd.DataFrame.from_dict([],orient='columns').to_excel(writer, sheet_name="Stats", index=None, index_label=None)
for page in pages:
    # calculate and fill in stats
    # append my page data in it's own sheet
pd.DataFrame.from_dict(stats,orient='columns').to_excel(writer, sheet_name="Stats", index=None, index_label=None)

Result:

I am using pandas 2.2.1 on python 3.10.12 with openpyxl 3.1.2 as the engine.