[Solved] Sorting via macro in Calc

[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

[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.

[martius]

I tried the idea of FJCC (sorting first the last column and then the other three), and it works great.
Thanks a lot!

[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

[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).

[KaduLeite]

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

[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.

[KaduLeite]

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 616 times

There are two Subs executed by buttons to sort and unsort.

Good Luck,

Kadu Leite