Unlimited Sort Columns & Custom Sort Order Using Basic

Shared Libraries
Forum rules
For sharing working examples of macros / scripts. These can be in any script language supported by OpenOffice.org [Basic, Python, Netbean] or as source code files in Java or C# even - but requires the actual source code listing. This section is not for asking questions about writing your own macros.
Post Reply
alsOO
Posts: 7
Joined: Wed Jun 17, 2009 6:38 am

Unlimited Sort Columns & Custom Sort Order Using Basic

Post by alsOO »

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
Last edited by MrProgrammer on Thu Jul 02, 2020 5:34 pm, edited 1 time in total.
Reason: Moved from Tutorials → Calc forum to Code Sippets forum
OOo 4.0.1 on Ms Windows 8
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Unlimited Sort Columns & Custom Sort Order Using Basic

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
alsOO
Posts: 7
Joined: Wed Jun 17, 2009 6:38 am

Re: Unlimited Sort Columns & Custom Sort Order Using Basic

Post by alsOO »

Appreciate the additional options you have provided for those who frequently sort more than 3 fields.

Your suggestion of manually sorting works fine for a spreadsheet with 15 to 20 short-width columns. For 70 columns which vary from 10 to 150 characters per column? A well named macro is probably a better solution in this case.

Oh, if you are using custom sort orders, your suggestion won't work. Custom sort orders can be specified in a macro.

Databases also have the ability to group data but they are not a panacia. Just because you can use a hammer to put a screw into board does not mean that you should. I could have used a database for what I was doing but that would be doing it the hard way. Sigh...
OOo 4.0.1 on Ms Windows 8
Post Reply