Possible to alter worksheet order in xlsxwriter?

Question

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,...

Chaunce

Answer 1

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()

Yovonnda

Answer 2

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.

Edouard

Good to know, I had an inkling that the order in which a sheet was added had significance in the overall .xlsx structure. – Chaunce

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? – Cowgirl

@Cowgirl 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. – Edouard

So basically if you want sheets in a certain order you should create them in that order in the first place? – Samhita

Answer 3

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()

Copybook