Unlimited Sort Columns & Custom Sort Order Using Basic

Creating Extension - 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 forum is not for asking questions about writing your own macros.

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

' 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

' 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
Posts: 6
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, no OpenOffice, LibreOffice 6.4
User avatar
Posts: 29296
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Unlimited Sort Columns & Custom Sort Order Using Basic

Postby alsOO » Sun Feb 17, 2019 2:38 am

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
Posts: 6
Joined: Wed Jun 17, 2009 6:38 am

Return to Code Snippets

Who is online

Users browsing this forum: No registered users and 2 guests