[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 6.2.8.2 (x64), Windows 10 Home
martius
 
Posts: 53
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 114 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: 7384
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 6.2.8.2 (x64), Windows 10 Home
martius
 
Posts: 53
Joined: Sat Feb 05, 2011 9:41 pm
Location: São Paulo, Brasil

Re: [Solved] Sorting via macro in Calc

Postby KaduLeite » Fri Nov 01, 2019 7:16 pm

I did a little alteration in the code to sort any number of columns.

Code: Select all   Expand viewCollapse view
Sub SortCols()
   Dim sSheetName as String, sRange as String
   Dim Order as Variant, isAscendent as Variant
   Dim oSheet, oCellRange, i as Integer
   Dim aSortFields(0) as New com.sun.star.table.TableSortField, SortDesc
   sSheetName = "Sheet1" 'set the sheet name
   sRange = "B1:E17" 'set the range to sort
   'For setting the order to sort, first column is 1,
   'so if the order is B, D, E, C the array should be 1,3,4,2
   'If there's a column in the range you don't use to sort, ignore it
   'For exemple: sRange = "A1:F17" and Order = Array(2,4,5,3)
   Order = Array(1,3,4,2) 'set the order for sorting: B(1), D(3), E(4), C(2)
   isAscendent = Array(True,True,True,True) 'set True=1 or False=0 for each column to sort
   
   oSheet = ThisComponent.Sheets.getByName(sSheetName)
   oCellRange = oSheet.getCellRangeByName(sRange)
   SortDesc = oCellRange.CreateSortDescriptor
   SortDesc(1).Value = True 'ContainsHeader
   
   For i = Ubound(Order) to 0 Step -1
      aSortFields(0).Field = Order(i)-1   'index = 0 is first column
      aSortFields(0).IsAscending = isAscendent(i)
      SortDesc(3).Value = aSortFields()
      oCellrange.Sort(SortDesc)
   Next
End Sub


I hope it helps,

Kadu Leite
LibreOffice 5.2 on Windows 10 / LibreOffice 6.2 on Ubuntu 19.10
KaduLeite
 
Posts: 5
Joined: Fri Apr 06, 2018 8:35 pm

Re: [Solved] Sorting via macro in Calc

Postby Myosotis » Mon Nov 04, 2019 12:17 pm

Hello,

First, thanks for posting this.
I understand that the columns begin at 0, so you define them like this :

Order = Array(1,3,4,2) 'set the order for sorting: B(1), D(3), E(4), C(2)

But why do you substract 1 again in the loop ?

aSortFields(0).Field = Order(i)-1 'index = 0 is first column

Did you test your code ? This sorts nothing for me (and I only try to sort one column).
OpenOffice 4.1.5
Windows 10
Myosotis
 
Posts: 2
Joined: Mon Nov 04, 2019 12:10 pm

Re: [Solved] Sorting via macro in Calc

Postby KaduLeite » Tue Dec 03, 2019 4:18 pm

Myosotis wrote:Hello,

First, thanks for posting this.
I understand that the columns begin at 0, so you define them like this :

Order = Array(1,3,4,2) 'set the order for sorting: B(1), D(3), E(4), C(2)

But why do you substract 1 again in the loop ?

aSortFields(0).Field = Order(i)-1 'index = 0 is first column

Did you test your code ? This sorts nothing for me (and I only try to sort one column).


Myosotis,

Sorry for delay.

I just thought it's simplier to sort using the first column as 1, but you can change this.
So, in my code, if you use the range "A9:G87", to sort using the column C and after the column F you shoud set Order = Array(3,6).
The column number is based on the range, not its index. That's the reason I subtract one in the loop, because it uses the index of the range.
If you want to use 0 for the first column in range, just delete the "-1" and use 0 for the first column in Order variable.
My example in the code, using B(1), was terrible. Let me change it.

Code: Select all   Expand viewCollapse view
Sub SortCols()
   Dim sSheetName as String, sRange as String
   Dim Order as Variant, isAscendent as Variant
   Dim oSheet, oCellRange, i as Integer
   Dim aSortFields(0) as New com.sun.star.table.TableSortField, SortDesc
   sSheetName = "Sheet1" 'set the sheet name
   sRange = "G1:M17" 'set the range to sort
   'For setting the order to sort, first column is 1,
   'so if the order is G, J, M, K the array should be 1,4,7,5
   'If there's a column in the range you don't use to sort, ignore it
   'For exemple: sRange = "C1:J17" and Order = Array(2,4,5,3)
   Order = Array(1,3,4,2) 'set the order for sorting: G(1), J(4), M(7), K(5)
   isAscendent = Array(True,True,True,True) 'set True=1 or False=0 for each column to sort
   
   oSheet = ThisComponent.Sheets.getByName(sSheetName)
   oCellRange = oSheet.getCellRangeByName(sRange)
   SortDesc = oCellRange.CreateSortDescriptor
   SortDesc(1).Value = True 'ContainsHeader
   
   For i = Ubound(Order) to 0 Step -1
      aSortFields(0).Field = Order(i)-1   'index = 0 is first column
      aSortFields(0).IsAscending = isAscendent(i)
      SortDesc(3).Value = aSortFields()
      oCellrange.Sort(SortDesc)
   Next
End Sub



Kadu Leite
Last edited by KaduLeite on Tue Dec 03, 2019 4:45 pm, edited 1 time in total.
LibreOffice 5.2 on Windows 10 / LibreOffice 6.2 on Ubuntu 19.10
KaduLeite
 
Posts: 5
Joined: Fri Apr 06, 2018 8:35 pm

Re: [Solved] Sorting via macro in Calc

Postby Myosotis » Tue Dec 03, 2019 4:41 pm

Indeed, the indices begin at 0, I know that. But what I said is that you substracted 1 twice : Order = Array(1,3,4,2) so 'B' is already 1. 'A' would be 0 and would become -1 with your code. That's why I asked if you had tested that code. And also because I couldn't make it work, even after this correction.
OpenOffice 4.1.5
Windows 10
Myosotis
 
Posts: 2
Joined: Mon Nov 04, 2019 12:10 pm

Re: [Solved] Sorting via macro in Calc

Postby KaduLeite » Tue Dec 03, 2019 5:02 pm

Myosotis wrote:Indeed, the indices begin at 0, I know that. But what I said is that you substracted 1 twice : Order = Array(1,3,4,2) so 'B' is already 1. 'A' would be 0 and would become -1 with your code. That's why I asked if you had tested that code. And also because I couldn't make it work, even after this correction.


Myosotis,

Yes, I had tested the code and it is working well.
Try to understand that the code does not subtract twice. When you specify a range, doesn't matter what columns you use, the first in the range in my code uses the number 1.
See the attached example:
Sorting.ods
(11.66 KiB) Downloaded 3 times
There are two Subs executed by buttons to sort and unsort.

Good Luck,

Kadu Leite
LibreOffice 5.2 on Windows 10 / LibreOffice 6.2 on Ubuntu 19.10
KaduLeite
 
Posts: 5
Joined: Fri Apr 06, 2018 8:35 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 4 guests