[Solved] Sorting via macro in Calc

Creating a macro - Writing a Script - Using the API

[Solved] Sorting via macro in Calc

Postby martius » Wed Jul 18, 2018 2:42 pm

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
Last edited by martius on Thu Jul 19, 2018 1:06 am, edited 2 times in total.
LibreOffice 5.2.7.2 (x64), Windows 10 Home
martius
 
Posts: 50
Joined: Sat Feb 05, 2011 9:41 pm
Location: São Paulo, Brasil

Re: Sorting via macro in Calc

Postby FJCC » Wed Jul 18, 2018 5:04 pm

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.
Attachments
FourColSort.ods
(10.67 KiB) Downloaded 74 times
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7204
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Solved] Sorting via macro in Calc

Postby martius » Thu Jul 19, 2018 1:11 am

I tried the idea of FJCC (sorting first the last column and then the other three), and it works great.
Thanks a lot!
:super:
LibreOffice 5.2.7.2 (x64), Windows 10 Home
martius
 
Posts: 50
Joined: Sat Feb 05, 2011 9:41 pm
Location: São Paulo, Brasil


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 3 guests