4

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?

Zhang18
  • 4,800
  • 10
  • 50
  • 67
  • So, you are asking for a way to insert a sheet at a specific index? – rawwar May 11 '18 at 16:31
  • @kalyan - that's exactly right. – Zhang18 May 11 '18 at 17:02
  • could you give a logical reason of why sheet2 can't be the firse worksheet and sheet1 can't be the second worksheet? – rawwar May 11 '18 at 22:51
  • if the only logic is based on the number at the end, i mean 1 for sheet 1 and 2 for sheet2, you can first write all the data, and just modify the sheet names – rawwar May 11 '18 at 22:52
  • 5
    Yes, for example I have 50 sheets with raw data and 1 sheet with summary. I need the summary sheet to be at the front. But content of the summary sheet isn't known until I go over creating the 50 sheets, which I want to create along the way since going over each sheet / content is expensive. – Zhang18 May 12 '18 at 01:10
  • As a workaround for this specific issue, you could save each of your 50 sheets into a list of 50 mini dataframes, then write your summary to excel, and then write each of the 50 mini dataframes to its own sheet after. – Nesha25 Oct 05 '21 at 20:24

2 Answers2

4

As jmcnamara wrote, it's not advisable to change the order of sheets because of Excel's internal structure, but you can change which sheet is active when the Excel file opens using activate():

>>> writer = pd.ExcelWriter('output.xlsx')
>>> df1.to_excel(writer,'Sheet1')
>>> df2.to_excel(writer,'Summary')
>>> writer.sheets['Summary'].activate()
>>> writer.save()

When you open the Excel file, the sheet called "Summary" will be displayed, although "Sheet1" will still be the first sheet.

Nathaniel Jones
  • 1,829
  • 3
  • 29
  • 27
-1

Create a dictionary saving the sheets as values and sheet names as keys. Then select the order in which to write the sheets by creating a list of keys in the order desired.

writer = pd.ExcelWriter('output.xlsx')
sheet_dict = {}
sheet_dict.update({'Sheet1': df1})
sheet_dict.update({'Summary': df2})
for key in ['Summary', 'Sheet1']:
   sheet_dict[key].to_excel(writer, key)
writer.save()
Arty
  • 37
  • 3