Jump to content

Macros/BasicMacrosSortingInCalc

From The Document Foundation Wiki
This page contains changes which are not marked for translation.


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")

Tip:
All commands in Calc for the Dispatcher are defined in file /core/sc/sdi/scalc.sdi in the source [1]. There you find for each command a list of possible parameters and their expected datatype.


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 Sheet ▸ Named Ranges and Expressions ▸ Define… 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) and Left to right (sort columns), respectively, of item Direction: on tab Sort Criteria
  • SfxBoolItem HasHeader SID_SORT_HASHEADER
Checkbox Range contains column labels (or Range contains row labels, depending on Direction) on tab Sort Criteria
  • SfxBoolItem CaseSensitive SID_SORT_CASESENS
Checkbox case-sensitive on 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 Formats on 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 1 on 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 2 on 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 3 on 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 comments on tab Options
  • SfxBoolItem IncludeImages SID_SORT_INCIMAGES
Checkbox Include boundary row(s) containing only images on tab Options
  • SfxInt32Item NumberBehavior SID_SORT_NUMBERBEHAVIOR
Checkbox Recognize numbers within strings on tab Options and the associated radio buttons Use decimal numbers as a whole and Split 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 group css.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-content of element <table:sort>
LO: Checkbox Include formats on tab Options. Initial value is from the definition of the DatabaseRange, there checkbox Keep formatting in part Options.
IsUserListEnabled
ODF: LibreOffice uses the attribute table:data-type of 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 Tools ▸ Options ▸ Calc ▸ Sort Lists.
LO: Checkbox Custom sort order. Initial value is FALSE.
UserListIndex
ODF: The index is encoded into the string of attribute table:data-type of element <table:sort-by>, see above.
LO: The index of the entry in the drop-down list.
CopyOutputData
ODF: The existance of the attribute table:target-range-address of the element <table:sort> corresponds to value TRUE of property CopyOutputData.
LO: Checkbox Copy sort results to:. Initial value is FALSE.
OutputPosition
ODF: The value of the attribute table:target-range-address of 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>
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.CellAddress with 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$3 into the right field below the checkbox Copy 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.
ContainsHeader
ODF: It is stored in attribute table:contains-header of the element <table:database-range>. LibreOffice takes it from the definition of the database range.
LO: The attribute is initially set in the Options dialog part when defining a database range via Data ▸ Define…. It can temporarily be overwritten with checkbox Range contains column labels (or Range 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.
NumberBehavior since LO 26.2
ODF: It corresponds to attribute table:embedded-number-behavior in element <table:sort>. It has values alpha-numeric, double and integer.
LO: If the checkbox Recognize numbers within strings on tab Options of the Sort dialog is not checked, then this attribute has value 0, that is constant css.sheet.SortNumberBehavior.ALPHA_NUMERIC. If the checkbox is checked, the value depends on the radio buttons. The button Use decimal numbers as a whole corresponds to value 1, that is constant css.sheet.SortNumberBehavior.DOUBLE. The button Split in integer and fraction corresponds to value 2, that is constant css.sheet.SortNumberBehavior.INTEGER. Initial value is 0.
MaxSortFieldsCount (from TableSortDescriptor2)
ODF: There exists no directly corresponding attribute. The sorting levels are given by a sequence of elements <table:sort-by>
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>.
IsSortColumns (from TableSortDescriptor2)
ODF: It corresponds to attribute table:orientation of the element <table:database-range> with values column and row (default). It determines whether a database record is written as row or as column.
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 button Top to bottem (sort rows) corresponds to value False, the button Left to right (sort columns) to value True. Initial value is False.
SortFields (from TableSortDescriptor2)
ODF: It is not a single attribute or element, but a sequence of elements <table:sort-by>.
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.

Details for TableSortField

The struct TableSortField has these components:

Field
ODF: Attribute table:field-number of element <table:sort-by>. The values are relative to the cell range specified in attribute table:target-range-address of the element <table:database-range>. Counting is 0-based.
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:order of element <table:sort-by>.
LO: Radiobutton Ascending of the Sort key item on tab Sort Criteria.
IsCaseSensitive
ODF: Attribute table:case-sensitive of element <table:sort>
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 checkbox Case sensitive on tab Options. The option Case sensitive in Tools ▸ Options ▸ LibreOffice Calc ▸ Calculations ▸ General Calculations is ignored in context of sorting.
FieldType
ODF: Attribute table:data-type of element <table:sort>. It can be one of the keywords automatic, number or text or an implementation-dependent string for a user-defined sort order.
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 Tools ▸ Options ▸ Calc ▸ Sort Lists is used. If no user-defined sort list was chosen LibreOffice always writes automatic.
For macros in Calc, LibreOffice always sets css.table.TableSortFieldType.AUTOMATIC. To use a user defined list, not the FieldType is used but the attributes IsUserListEnabled and UserListIndex of SheetSortDescriptor2.
CollatorLocal
ODF: The local is not given by a single attribute or element but by the three attributes table:country, table:language, table:script or by the attribute table:rfc-language-tag. They are attributes of element <table:sort>. These attributes are optional. If they are missing an implementation-dependent default is used.
LO: Drop-down list Locale on 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 Tools ▸ Options ▸ Languages and Locales.
The value of this property is a struct css.lang.Locale with 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.
CollatorAlgorithm
ODF: Attribute table:algorithm in element <table:sort>. It is an implementation-dependent string.
LO: The default algorithm alphanumeric or a string corresponding to the chosen algorithm in drop-down Options right from Locale on 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 Tools ▸ Development Tools 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.

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)