Apache OpenOffice Community Forum

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

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.

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