Unlimited Sort Columns & Custom Sort Order Using Basic

Forum rules
No question in this forum please
For any question related to a topic, create a new thread in the relevant section.

Unlimited Sort Columns & Custom Sort Order Using Basic

Postby alsOO » Mon Oct 03, 2011 1:33 am

I hope the following helps others. I really wish the Basic documentation was better...

First, it is possible to sort on a custom sort order and this can be found in the documentation but it is NOT trivial to locate the information. I have included the TWO statements needed in the below code section.

It is also possible to sort on an unlimited number of columns. This is possible since the initial order of the data is preserved during a sort. If the desired order of sort is columns F, C, H, A, B, sort on columns A and B, then F, C, H. This can easily be tested by doing consecutive single sorts using the Data/Sort dialog box. More importantly, it works in Basic. The following sorts on four columns in the order: 1, 0, 4, 2

' allocate the variables
Dim sortField4(0) As New com.sun.star.util.SortField
Dim sortFields(2) As New com.sun.star.util.SortField
Dim sortProperties(1) As New com.sun.star.beans.PropertyValue

' get the range to sort (done by position but can be done by range)
dataCells = oSheet.getCellRangeByPosition(startCol, startRow, endCol, endRow)

' set the fourth field (column 2) to sort on, whether ascending, and type of field
sortField4(0).Field = 2
sortField4(0).SortAscending = TRUE
sortField4(0).FieldType = com.sun.star.util.SortFieldType.ALPHANUMERIC

' set the sort descriptor
sortProperties(0).Name = "SortFields"
sortProperties(0).Value = sortField4()

' sort on column 2
dataCells.Sort(sortProperties())

' set the sort fields for the first three sort columns
sortFields(0).Field = 1
sortFields(0).SortAscending = TRUE
sortFields(0).FieldType = com.sun.star.util.SortFieldType.ALPHANUMERIC
sortFields(1).Field = 0
sortFields(1).SortAscending = TRUE
sortFields(1).FieldType = com.sun.star.util.SortFieldType.ALPHANUMERIC
sortFields(2).Field = 4
sortFields(2).SortAscending = TRUE
sortFields(2).FieldType = com.sun.star.util.SortFieldType.NUMERIC

' set the sort descriptor
sortProperties(0).Name = "SortFields" ' here for clarity. Does not have to be reset here since it already has this value
sortProperties(0).Value = sortFields()
' the next two statements enable use of a custom sort order defined in Data/Sorts,,,/Options/Custom Sort Order
' note: I only have ONE custom sort order defined. I did not test with multiple custom sort orders
sortProperties(1).Name = "IsUserListEnabled"
sortProperties(1).Value = TRUE

' sort on columns 1, 0, 4
dataCells.Sort(sortProperties())

' access the data
data = dataCells.getDataArray()

print data(1)(0) ' print the contents of the cell at location column 1 row 0 of the selected (and sorted) data
OOo 4.0.1 on Ms Windows 8
alsOO
 
Posts: 5
Joined: Wed Jun 17, 2009 6:38 am

Re: Unlimited Sort Columns & Custom Sort Order Using Basic

Postby Villeroy » Fri May 29, 2015 2:19 pm

In simple terms and how I use to do it when I need to sort by more than 3 fields in columns A,B,C,D,E

Click a cell in E and then the sort button.
Click a cell in D and then the sort button.
Click a cell in C and then the sort button.
Click a cell in B and then the sort button.
Click a cell in A and then the sort button.

Works with any list on any sheet in any (reverse) order of columns. No need to write any macro. No need to adjust the macro to the current requirement.

Oh, and then we have the database component where we can define arbitrary complex sort and filter conditions even for spreadsheet lists. Yes, I know; nobody wants to process database data by a database component when we have a calculator component and Basic.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25767
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 1 guest