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!
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:
There are two Subs executed by buttons to sort and unsort.
Good Luck,
Kadu Leite