14

I have a script which creates a number of the following pairs of worksheets in order:

WorkSheet (holds data) -> ChartSheet using WorkSheet

After the script is finished, I am left with worksheets ordered as such:

Data1, Chart1, Data2, Chart2, Data3, Chart3, ...

Is it possible to re-order the worksheets at the end of the script (i.e. before workbook.close()) to obtain the following worksheet order in the final .xlsx file?

Chart1, Chart2, Chart3,...,ChartN, Data1, Data2, Data3,...
alecxe
  • 462,703
  • 120
  • 1,088
  • 1,195
Ben Southgate
  • 3,388
  • 3
  • 22
  • 31

3 Answers3

20

Just sort workbook.worksheets_objs list:

import xlsxwriter


workbook = xlsxwriter.Workbook('test.xlsx')

sheet_names = ['Data1', 'Chart1', 'Data2', 'Chart2', 'Data3', 'Chart3']
for sheet_name in sheet_names:
    workbook.add_worksheet(sheet_name)

# sort sheets based on name
workbook.worksheets_objs.sort(key=lambda x: x.name)
workbook.close()
alecxe
  • 462,703
  • 120
  • 1,088
  • 1,195
18

I'm the author of XlsxWriter. It is possible to do it but it isn't advisable.

Each worksheet has an internal index which is used by Excel to track the relationship of different workbook objects.

Sorting the worksheets might work in some simple cases but for more complex workbooks it could lead to problems.

jmcnamara
  • 38,196
  • 6
  • 90
  • 108
  • Good to know, I had an inkling that the order in which a sheet was added had significance in the overall .xlsx structure. – Ben Southgate Jan 14 '14 at 22:08
  • Is it fair to say that "more complex" means worksheets with dependencies in other worksheets? If each worksheet is completely standalone and has no formulas, would there be any risk? – elPastor Feb 24 '20 at 21:22
  • @elPastor The truth is that I don't know where it will work and where it won't work, which of itself, is a problem since I wrote the code. It isn't intended behaviour so users shouldn't depend on it. – jmcnamara Mar 01 '20 at 19:14
  • So basically if you want sheets in a certain order you should create them in that order in the first place? – Nick Feb 14 '23 at 22:08
7

You can create dummy sheets in the order you want and them fill them up with real data in any order:

import pandas as pd
dummy = pd.DataFrame()
writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')
# Here go the dummy sheets in the order you want
dummy.to_excel(writer, sheet_name='Chart1')
dummy.to_excel(writer, sheet_name='Chart2')
dummy.to_excel(writer, sheet_name='Data1')
dummy.to_excel(writer, sheet_name='Data2')
# Then you fill-in the placeholders in whatever order:
data1.to_excel(writer, sheet_name='Data1')
chart1.to_excel(writer, sheet_name='Chart1')
data2.to_excel(writer, sheet_name='Data2')
chart2.to_excel(writer, sheet_name='Chart2')
writer.close()
Pluto
  • 816
  • 10
  • 9