Macros/BasicMacrosSortingInCalc
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
This article addresses developers of Basic macros.
Sorting in Basic macros
There are two ways to sort using a macro. You can use the .uno:DataSort command with the Dispatcher or use the css.util.XSortable interface together with the SheetSortDescriptor2 service.
Sorting with Dispatcher
Example Basic code
First you need the dispatcher, for example
Dim oDoc as Object
oDoc = ThisComponent.CurrentController.Frame
Dim oDispatcher as Object
oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
To sort with the dispatcher you have to first select the cell range, which you want to sort and then let the Dispatcher sort that area.
In the following example we sort the range C3:E12 ascending by column C, whereby row 3 contains labels.
To select the cell range, you can e.g. use the dispatcher with .uno:GoToCell command or if you know the name of the database range, with command .uno:SelectDB.
Dim args1(0) as New com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$C$3:$E$12"
oDispatcher.executeDispatch(oDoc, ".uno:GoToCell", "", 0, args1())
If the range was named via ▸ ▸ you can use that name instead of the direct range address as well.
Or if the database range is named "myData"
Dim args1(0) as New com.sun.star.beans.PropertyValue
args1(0).Name = "DbName"
args1(0).Value = "myData"
oDispatcher.executeDispatch(oDoc, ".uno:SelectDB", "", 0, args1())
The command for sorting is .uno:DataSort. It has a lot of parameters, see the list below. They correspond to settings in the Sort dialog. However, not all the settings of the dialog are available for the Dispatcher.
Sorting using the Dispatcher is always "in place". If you need the sort results on a different place, you have to copy the cell range before you then sort the copy.
Since version 26.2 the parameter list has the new parameter NumberBehavior to determine the sort algorithm for text with embedded numbers. The parameter NaturalSort is still available for old, existing macros, but it will be overwritten, if parameter NumberBehavior exists.
Dim args2(3) as New com.sun.star.beans.PropertyValue
args2(0).Name = "NumberBehavior"
args2(0).Value = 1 'UI: Use decimal numbers as a whole
args2(1).Name = "Col1"
args2(1).Value = 3 'command .uno:DataSort uses 1-based column index
args2(2).Name = "Ascending1"
args2(2).Value = True
args2(3).Name = "HasHeader"
args2(3).Value = True
oDispatcher.executeDispatch(oDoc, ".uno:DataSort", "", 0, args2())
Do not forget to set the parameter Col1, otherwise it has the default value 0 and that means, that this field is not used for sorting.
Parameters of command .uno:DataSort
The parameters are listed here as given in the source. To each parameter the relationship to the Sort dialog is added.
- SfxBoolItem ByRows SID_SORT_BYROW
- Options
Top to bottom (sort rows)andLeft to right (sort columns), respectively, of item Direction: on tab Sort Criteria
- SfxBoolItem HasHeader SID_SORT_HASHEADER
- Checkbox
Range contains column labels(orRange contains row labels, depending on Direction) on tab Sort Criteria
- SfxBoolItem CaseSensitive SID_SORT_CASESENS
- Checkbox
case-sensitiveon tab Options
- SfxBoolItem NaturalSort SID_SORT_NATURALSORT
- No longer available in the Sort dialog, only kept for backward compatibility. Its value TRUE is mapped to NumberBehavior value 1, value FALSE is mapped to NumberBehavior value 0.
- SfxBoolItem IncludeAttribs SID_SORT_ATTRIBS
- Checkbox
Include Formatson tab Options
- SfxUInt16Item UserDefIndex SID_SORT_USERDEF
- Index of chosen entry in drop-down list associated with checkbox
Custom sort order
- SfxInt32Item Col1 FN_PARAM_1
- Index of chosen column/row in
Sort key 1on tab Sort Criteria
- SfxBoolItem Ascending1 FN_PARAM_2
- Radio button in
Sort key 1
- SfxInt32Item Col2 FN_PARAM_3
- Index of chosen column/row in
Sort key 2on tab Sort Criteria
- SfxBoolItem Ascending2 FN_PARAM_4
- Radio button in
Sort key 2
- SfxInt32Item Col3 FN_PARAM_5
- Index of chosen column/row in
Sort key 3on tab Sort Criteria
- SfxBoolItem Ascending3 FN_PARAM_6
- Radio button in
Sort key 3
- SfxBoolItem IncludeComments SID_SORT_INCCOMMENTS
- Checkbox
Include boundary row(s) containing only commentson tab Options
- SfxBoolItem IncludeImages SID_SORT_INCIMAGES
- Checkbox
Include boundary row(s) containing only imageson tab Options
- SfxInt32Item NumberBehavior SID_SORT_NUMBERBEHAVIOR
- Checkbox
Recognize numbers within stringson tab Options and the associated radio buttonsUse decimal numbers as a wholeandSplit in integer and fraction. Possible values are 0 for checkbox not checked, 1 for option Use decimal numbers as a whole and 2 for option Split in integer and fraction. The values are defined in constant groupcss.sheet.SortNumberBehavior.
Sorting with interface XSortable
The interface css.util.XSortable is e.g. available from a cell range. It has the methods createSortDescriptor and sort. For use in spreadsheets the sort descriptor SheetSortDescriptor2 service should be used.
Attributes of SheetSortDescriptor2
The following list contains for each attribute of SheetSortDescriptor2 the corresponding attributes and elements in ODF (ODF:) and the corresponding settings in the Sort dialog (LO:).
For using TableSortDescriptor2 (the parent of SheetSortDescriptor2) see also Sorting in Development Guide.
- BindFormatsToContent
- ODF: Attribute
table:bind-styles-to-contentof element<table:sort> - LO: Checkbox
Include formatson tab Options. Initial value is from the definition of the DatabaseRange, there checkbox Keep formatting in part Options.
- ODF: Attribute
- IsUserListEnabled
- ODF: LibreOffice uses the attribute
table:data-typeof element<table:sort-by>. On loading of a document, if the value of this attribute starts with "UserList", then this property is set to true. On saving a document, LibreOffice writes e.g. "UserList3" to determine the third one is used of the user lists set in ▸ ▸ ▸ .
- ODF: LibreOffice uses the attribute
- LO: Checkbox
Custom sort order. Initial value is FALSE.
- LO: Checkbox
- UserListIndex
- ODF: The index is encoded into the string of attribute
table:data-typeof element<table:sort-by>, see above.
- ODF: The index is encoded into the string of attribute
- LO: The index of the entry in the drop-down list.
- CopyOutputData
- ODF: The existance of the attribute
table:target-range-addressof the element<table:sort>corresponds to value TRUE of property CopyOutputData.
- ODF: The existance of the attribute
- LO: Checkbox
Copy sort results to:. Initial value is FALSE.
- LO: Checkbox
- OutputPosition
- ODF: The value of the attribute
table:target-range-addressof the element<table:sort>. It is a string of kind "Sheet1.G3". LibreOffice does not write a range address but only the cell address of the top-left cell. Caution, do not confuse this attribute with the same named attribute of the element<table:database-range>
- ODF: The value of the attribute
- LO: It contains the cell address of the top-left cell of the area, where the sort result will be written. It is not a string in the API, but a struct
css.table.CellAddresswith components Sheet, Column and Row. All numbers are 0-based. In the Options tab of the Sort dialog, the cell address can be entered directly as string like$G$3into the right field below the checkboxCopy sort results to:. Alternatively a named area can be chosen from the left drop-down list, and then its top-left cell is automatically set in the right field.
- LO: It contains the cell address of the top-left cell of the area, where the sort result will be written. It is not a string in the API, but a struct
- ContainsHeader
- ODF: It is stored in attribute
table:contains-headerof the element<table:database-range>. LibreOffice takes it from the definition of the database range.
- ODF: It is stored in attribute
- LO: The attribute is initially set in the Options dialog part when defining a database range via ▸ . It can temporarily be overwritten with checkbox
Range contains column labels(orRange contains row labels, depending on Direction) on tab Sort Criteria of the Sort dialog. But the property from the database range definition is written to file and used on reload.
- LO: The attribute is initially set in the Options dialog part when defining a database range via ▸ . It can temporarily be overwritten with checkbox
- NumberBehavior since LO 26.2
- ODF: It corresponds to attribute
table:embedded-number-behaviorin element<table:sort>. It has valuesalpha-numeric,doubleandinteger.
- ODF: It corresponds to attribute
- LO: If the checkbox
Recognize numbers within stringson tab Options of the Sort dialog is not checked, then this attribute has value 0, that is constantcss.sheet.SortNumberBehavior.ALPHA_NUMERIC. If the checkbox is checked, the value depends on the radio buttons. The buttonUse decimal numbers as a wholecorresponds to value 1, that is constantcss.sheet.SortNumberBehavior.DOUBLE. The buttonSplit in integer and fractioncorresponds to value 2, that is constantcss.sheet.SortNumberBehavior.INTEGER. Initial value is 0.
- LO: If the checkbox
- MaxSortFieldsCount (from TableSortDescriptor2)
- ODF: There exists no directly corresponding attribute. The sorting levels are given by a sequence of elements
<table:sort-by>
- ODF: There exists no directly corresponding attribute. The sorting levels are given by a sequence of elements
- LO: The current count of Sort Key entries on tab Sort Criteria of the Sort dialog. The initial count is 3, but when the third entry is activated, further levels can be added. This value is the maximal size of property
<SortFields>.
- LO: The current count of Sort Key entries on tab Sort Criteria of the Sort dialog. The initial count is 3, but when the third entry is activated, further levels can be added. This value is the maximal size of property
- IsSortColumns (from TableSortDescriptor2)
- ODF: It corresponds to attribute
table:orientationof the element<table:database-range>with valuescolumnandrow(default). It determines whether a database record is written as row or as column.
- ODF: It corresponds to attribute
- LO: Value True means, that each column represents a database record. LibreOffice uses it not as attribute of the database range but as attribute of the sort descriptor. It is determined by the radio button
Direction:on tab Sort Criteria of the Sort dialog. The buttonTop to bottem (sort rows)corresponds to value False, the buttonLeft to right (sort columns)to value True. Initial value is False.
- LO: Value True means, that each column represents a database record. LibreOffice uses it not as attribute of the database range but as attribute of the sort descriptor. It is determined by the radio button
- SortFields (from TableSortDescriptor2)
- ODF: It is not a single attribute or element, but a sequence of elements
<table:sort-by>.
- ODF: It is not a single attribute or element, but a sequence of elements
- LO: It is a sequence of struct css.table.TableSortField. The sequence is initially empty.
- Caution, neither the Sort Key entries in the Sort dialog nor the
<table:sort-by>element in ODF correspond directly to the struct TableSortField. Details see below.
- Caution, neither the Sort Key entries in the Sort dialog nor the
Details for TableSortField
The struct TableSortField has these components:
- Field
- ODF: Attribute
table:field-numberof element<table:sort-by>. The values are relative to the cell range specified in attributetable:target-range-addressof the element<table:database-range>. Counting is 0-based.
- ODF: Attribute
- LO: The absolute column or row index of the chosen entry in the drop-down list Column (or Row, depending on Direction) of the Sort key item. The index is 0-based, that is column "C" has index 2, for example.
- IsAscending
- ODF: Attribute
table:orderof element<table:sort-by>.
- ODF: Attribute
- LO: Radiobutton
Ascendingof the Sort key item on tab Sort Criteria.
- LO: Radiobutton
- IsCaseSensitive
- ODF: Attribute
table:case-sensitiveof element<table:sort>
- ODF: Attribute
- LO: Because the attribute is common to all child elements
<table:sort-by>in ODF, the value in LO has to be the same for all SortFields items. That corresponds to the UI, were the attribute is not set at the Sort key but with checkboxCase sensitiveon tab Options. The option Case sensitive in ▸ ▸ ▸ ▸ is ignored in context of sorting.
- LO: Because the attribute is common to all child elements
- FieldType
- ODF: Attribute
table:data-typeof element<table:sort>. It can be one of the keywordsautomatic,numberortextor an implementation-dependent string for a user-defined sort order.
- ODF: Attribute
- LO: LibreOffice writes a string of kind "UserList3" in file markup, if a user-defined sort list was chosen in the Sort dialog. Thereby the part "3" in the example specifies that the third list from ▸ ▸ ▸ is used. If no user-defined sort list was chosen LibreOffice always writes automatic.
For macros in Calc, LibreOffice always setscss.table.TableSortFieldType.AUTOMATIC. To use a user defined list, not the FieldType is used but the attributesIsUserListEnabledandUserListIndexof SheetSortDescriptor2.
- LO: LibreOffice writes a string of kind "UserList3" in file markup, if a user-defined sort list was chosen in the Sort dialog. Thereby the part "3" in the example specifies that the third list from ▸ ▸ ▸ is used. If no user-defined sort list was chosen LibreOffice always writes automatic.
- CollatorLocal
- ODF: The local is not given by a single attribute or element but by the three attributes
table:country,table:language,table:scriptor by the attributetable:rfc-language-tag. They are attributes of element<table:sort>. These attributes are optional. If they are missing an implementation-dependent default is used.
- ODF: The local is not given by a single attribute or element but by the three attributes
- LO: Drop-down list
Localeon tab Options. If nothing was chosen there, that is the item Default- … is active, then LibreOffice uses the global locale setting that the current user has chosen in ▸ ▸ .
The value of this property is a structcss.lang.Localewith components Country, Language and Variant. The initial value is an emptry string for each component.
Because the attribute is common to all child elements<table:sort-by>in ODF, the value in LO has to be the same for all SortFields items.
- LO: Drop-down list
- CollatorAlgorithm
- ODF: Attribute
table:algorithmin element<table:sort>. It is an implementation-dependent string.
- ODF: Attribute
- LO: The default algorithm alphanumeric or a string corresponding to the chosen algorithm in drop-down
Optionsright fromLocaleon tab Options. The strings are essentially ICU keywords.
To get the string, select the algorithm in the UI. Then you can examine the document with an object inspection tool e.g. by ▸ or with the MRI extension. Or save the document and examine the file markup.
Because the attribute is common to all child elements<table:sort-by>in ODF, the value in LO has to be the same for all SortFields items.
- LO: The default algorithm alphanumeric or a string corresponding to the chosen algorithm in drop-down
Example Basic code
In this example we assume that the cell range was defined as database range "myData", data records are in rows and the range starts with a row of labels. We want to sort the first column of the range ascending, use natural sort type Split integer and fraction, and sort by the German Phone book algorithm.
Caution, the code snippets do not include any error handling or checks for existances.
First get access to the database range
Dim oDoc as Object
oDoc = ThisComponent
Dim oDatabaseRange as Object
oDatabaseRange = oDoc.DatabaseRanges.getByName("myData")
Then we retrieve the sort descriptor from the selected database range. This way, our changes to the sort descriptor will be reflected in the database range after actually sorting and thus stored in the document when saving. The service SheetSortDescriptor2 has the ten attributes listed above (state of LibreOffice version 26.2), albeit they can have default values and thus not all of them need to be set explicitly. The service is implemented as sequence of css.bean.ProperValue and thus becomes an array in BASIC.
Dim aSortDescriptor as Variant
aSortDescriptor = oDatabaseRange.SortDescriptor
Unfortunately the service SheetSortDescriptor2 does not support a name access to its attributes. Thus we first generate the desired settings and then iterate over its attributes.
Generating the struct collatorLocale. For details about the language codes see its API reference [2].
Dim aCollatorLocale as New "com.sun.star.lang.Locale"
aCollatorLocale.Language = "de" 'lower case
aCollatorLocale.Country = "DE" 'upper case
aCollatorLocale.Variant = "" 'not used here, therefore empty
Generate a SortField. We use struct css.table.TableSortField. The older, similar css.util.SortField does not have the needed language settings and it is deprecated. Nevertheless, you might still see it in existing Basic macro examples.
Dim aSortFields(0) as New "com.sun.star.table.TableSortField"
With aSortFields(0)
.Field = oDatabaseRange.DataArea.StartColumn 'sorting the first column of the range
.IsAscending = True
.IsCaseSensitive = False
Rem .FieldType is not evaluated in Calc
.CollatorLocale = aCollatorLocale
.CollatorAlgorithm = "phonebook"
End With
Now we iterate over the attributes of the sort descriptor and set those we want to change.
Dim i as Integer
For i = 0 To UBound(aSortDescriptor)
If aSortDescriptor(i).Name = "ContainsHeader" Then
aSortDescriptor(i).Value = True
ElseIf aSortDescriptor(i).Name = "NumberBehavior" Then
aSortDescriptor(i).Value = com.sun.star.sheet.SortNumberBehavior.INTEGER
ElseIf aSortDescriptor(i).Name = "SortFields" Then
aSortDescriptor(i).Value = aSortFields
End If
Next i
Creating a sort descriptor does not have any effect in itself. We need to actually use it in a sort. For sorting we need the to be sorted cell range (service css.sheet.SheetCellRange), as that supports the interface css.util.XSortable.
Dim oSheet as Object
oSheet = oDoc.Sheets(oDatabaseRange.DataArea.Sheet)
Dim oCellRange as Object
oCellRange = oSheet.getCellRangeByName("myData")
oCellRange.sort(aSortDescriptor)