B4J Question [XLUtils] Reorder sheets

[Alessandro71]

I've found no methods to get the sheets list from an excel workbook and move them around, for example, for sorting them in ascending order, or inserting a new one in a specific position based on the existing sheets names.

 

[Erel]

You can change the order with:

Workbook.jWorkbook.RunMethod("setSheetOrder", Array("Sheet2", 0)) 'make Sheet2 the first sheet
Workbook.jWorkbook.RunMethod("setSheetOrder", Array("Sheet3", 1)) 'make Sheet3 the second sheet

 

[Alessandro71]

Gratefully, I contribute back with a sorting Sub

Private Sub SortSheets(workbook As XLWorkbookWriter)
    Dim sheets() As String = workbook.PoiWorkbook.GetSheetNames
    If (sheets.Length > 1) Then
        Dim sheetslist As List
        sheetslist.Initialize2(sheets)
        sheetslist.Sort(True)
        
        For i = 0 To (sheetslist.Size - 1)
            workbook.jWorkbook.RunMethod("setSheetOrder", Array(sheetslist.Get(i), i))
        Next
    End If
End Sub

 

[Erel]

Tip - List.Initialize2 is never really needed.
It is the same as:

 Dim sheetslist As List = sheets

 

[Alessandro71]

time for a little optimization then...

Private Sub SortSheets(workbook As XLWorkbookWriter)
    Dim sheets As List = workbook.PoiWorkbook.GetSheetNames
    If (sheets.Size > 1) Then
        sheets.Sort(True)
        
        For i = 0 To (sheets.Size - 2)    'the last one will fall into place automatically
            workbook.jWorkbook.RunMethod("setSheetOrder", Array(sheets.Get(i), i))
        Next
    End If
End Sub