[Solved] Sorting via macro in Calc

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
martius
Posts: 56
Joined: Sat Feb 05, 2011 9:41 pm
Location: São Paulo, Brasil

[Solved] Sorting via macro in Calc

Post 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

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
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Sorting via macro in Calc

Post 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

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 449 times
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
martius
Posts: 56
Joined: Sat Feb 05, 2011 9:41 pm
Location: São Paulo, Brasil

Re: [Solved] Sorting via macro in Calc

Post 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:
LibreOffice 6.2.8.2 (x64), Windows 10 Home
KaduLeite
Posts: 5
Joined: Fri Apr 06, 2018 8:35 pm

Re: [Solved] Sorting via macro in Calc

Post by KaduLeite »

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

Code: Select all

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
Myosotis
Posts: 2
Joined: Mon Nov 04, 2019 12:10 pm

Re: [Solved] Sorting via macro in Calc

Post by Myosotis »

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
KaduLeite
Posts: 5
Joined: Fri Apr 06, 2018 8:35 pm

Re: [Solved] Sorting via macro in Calc

Post by KaduLeite »

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

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
Myosotis
Posts: 2
Joined: Mon Nov 04, 2019 12:10 pm

Re: [Solved] Sorting via macro in Calc

Post by Myosotis »

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
KaduLeite
Posts: 5
Joined: Fri Apr 06, 2018 8:35 pm

Re: [Solved] Sorting via macro in Calc

Post by KaduLeite »

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 503 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
Post Reply