Menu
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,...
294
You can move a sheet to a different position, click and hold the sheet tab at the bottom of the screen with the mouse and drag it to its new position. select Edit → Sheet → Move/Copy from the main menu. Specify the new position of the sheet in the dialog.
Select the first sheet you want to move, while holding down the Shift key, click on the next sheet. This will indicate a range of sheets to move i.e. sheet1:sheet2. You can then drag the range of sheets to their new location.
If you are working with large files or are particularly concerned about speed then you may find XlsxWriter a better choice than OpenPyXL. XlsxWriter is a Python module that can be used to write text, numbers, formulas and hyperlinks to multiple worksheets in an Excel 2007+ XLSX file.
The with of 1 unit of the xlsxwriter columns is about equal to the width of one character. So, you can simulate autofit by setting each column to the max number of characters in that column.
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()
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
