Page 1 of 1

[Solved] Sorting via macro in Calc

Posted: 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

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

Posted: 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

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

Posted: 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:

Re: [Solved] Sorting via macro in Calc

Posted: Fri Nov 01, 2019 7:16 pm
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

Re: [Solved] Sorting via macro in Calc

Posted: Mon Nov 04, 2019 12:17 pm
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).

Re: [Solved] Sorting via macro in Calc

Posted: Tue Dec 03, 2019 4:18 pm
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

Re: [Solved] Sorting via macro in Calc

Posted: Tue Dec 03, 2019 4:41 pm
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.

Re: [Solved] Sorting via macro in Calc

Posted: Tue Dec 03, 2019 5:02 pm
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 501 times
There are two Subs executed by buttons to sort and unsort.

Good Luck,

Kadu Leite