Page 1 of 1

[Solved] Sorting via macro in Calc

PostPosted: Wed Jul 18, 2018 2:42 pm
by martius
How can I sort in Calc, at same time, four fields (columns) via macro. I need to sort, via macro, four columns, and they need to be coordenated.
It's easy via interface, but I need via macro.
I tried this macro, but it works just to three fiels.

Code: Select all   Expand viewCollapse view
Sub ordenaDados(oDoc As object, oPlan As Object, Ci%, Li%, OrdemCol1, OrdemCol2, OrdemCol3, OrdemCol4)
   Dim Despachante As Object, Controle As Object
   Despachante = createUnoService("com.sun.star.frame.DispatchHelper")
   Controle = oDoc.CurrentController
   Controle.Select( oPlan.GetCellByPosition(Ci,Li) )
                                        
   Dim args(13) As New com.sun.star.beans.PropertyValue
      args(0).Name   = "ByRows"      
      args(0).Value  = True
      args(1).Name   = "HasHeader"      
      args(1).Value  = False      
      args(2).Name   = "CaseSensitive"   
      args(2).Value  = False
      args(3).Name   = "NaturalSort"   
      args(3).Value  = False
      args(4).Name   = "IncludeAttribs"   
      args(4).Value  = True
      args(5).Name   = "UserDefIndex"   
      args(5).Value  = 0   
      args(6).Name   = "Col1"      
      args(6).Value  = OrdemCol1      
      args(7).Name   = "Ascending1"   
      args(7).Value  = True
      args(8).Name   = "Col2"      
      args(8).Value  = OrdemCol2      
      args(9).Name   = "Ascending2"   
      args(9).Value  = True
      args(10).Name  = "Col3"      
      args(10).Value = OrdemCol3      
      args(11).Name  = "Ascending3"   
      args(11).Value = True
      args(12).Name  = "Col4"      
      args(12).Value = OrdemCol4      
      args(13).Name  = "Ascending4"   
      args(13).Value = True
   Despachante.executeDispatch(Controle, ".uno:DataSort", "", 0, args())
End Sub

Re: Sorting via macro in Calc

PostPosted: Wed Jul 18, 2018 5:04 pm
by FJCC
One way to sort on four columns is to first sort on the one that would be the last in the sequence and then sort on the other three.
Code: Select all   Expand viewCollapse view
oSheet = ThisComponent.Sheets.getByName("Sheet1")
oCellrange = oSheet.getCellrangeByName("B1:E17")
'Want to sort the columns in the order B, D, E, C
'Do the first sort on the column that would be the fourth one to be sorted
Dim aSortFields(0) as New com.sun.star.table.TableSortField
aSortFields(0).Field = 1   'index = 1 of the CellRange = column C
aSortFields(0).IsAscending = "True"

SortDesc = oCellrange.createSortDescriptor
'A SortDescriptor is an array of 9 propertyvalues: 0 = IsSortColumns, 1 = ContainsHeader,
'2 = MaxFieldCount (set to 3), 3 = SortFields, 4 = BindFormatToContent, 5 = CopyOutputData,
'6 = OutputPosition, 7 = IsUserListEnabled, 8 = UserListIndex
SortDesc(1).Value = TRUE 'ContainsHeader
SortDesc(3).Value = aSortFields() 'SortFields
oCellrange.sort(SortDesc)

'Sort the remaining three columns in the order B, D, E
Dim aSortFields2(2) as New com.sun.star.table.TableSortField
aSortFields2(0).Field = 0   'index = 0 of the CellRange = column B
aSortFields2(0).IsAscending = "True"
aSortFields2(1).Field = 2  'index = 2 of the CellRange = column D
aSortFields2(1).IsAscending = "True"
aSortFields2(2).Field = 3   'index = 3 of the CellRange = columna E
aSortFields2(2).IsAscending = "True"

SortDesc(3).Value = aSortFields2() 'SortFields

oCellrange.sort(SortDesc)

After running the macro in the attached file, you can do Edit -> Undo twice to get back to the original order of the items.

Re: [Solved] Sorting via macro in Calc

PostPosted: Thu Jul 19, 2018 1:11 am
by martius
I tried the idea of FJCC (sorting first the last column and then the other three), and it works great.
Thanks a lot!
:super: