Enabling NaturalSort in Basic Macro?

Pez1 · August 29, 2019, 11:55am

I’ve gotten nowhere trying to get this to work, sorting style Natural - is there a way to specify NaturalSort without resolving to record a macro and use?

The below function works, but sorts “11:5” before “7:0”, by using NaturalSort it would sort “correctly”.

Function SortList
        Dim sPK as Object
        sPK=ThisComponent.getSheets().getbyName("Result One")

	Range = sPK.getCellrangeByName("A6:E100")

	Dim SortFields(0) as New com.sun.star.table.TableSortField

	SortFields(0).Field = 4   'Column E = TIME
	SortFields(0).FieldType = "AlphaNumeric" 'Sort as text
	SortFields(0).IsAscending = "True"

	SortDesc = Range.createSortDescriptor
	SortDesc(3).Value = SortFields()
	
	Range.sort(SortDesc)
End Function

Original data is CSV text with this field being MM:SS so using text instead of converting seemed “natural” to me

(Bump only)

newbie-02 · October 3, 2020, 3:52pm

either change the “7:0” values to “07:0”, or do some conversion from string to numbers, e.g. add a helper column with format “MM:SS” and formula “=VALUE(Ex)/60”

Lupp · December 6, 2021, 6:38pm

This is an old thread, but I didn’t find another one on the topic. Therefore revive.

The IsNaturalSortEnabled as a property for table SortDescriptor as a whole or (better) as a property of an enhanced com.sun.star.table.TableSortField2 would be a good idea.
Is there already something of the kind?
Are there collators comparing this way, and if, how can they be set?

erAck · December 8, 2021, 11:55pm

No such thing. The NaturalSort available in the dialog is handled Calc internally and the string is split into number prefixes/suffixes and string parts on which the selected collator algorithm is applied. This is not exposed to the UNO API and not available as separate collator algorithm(s) (in fact couldn’t be one algorithm but would have to be a flag to be used with existing algorithms). To do so, implementation would have to be moved from Calc to i18npool and its collators, which in general would be a good idea so also others could use it, and probably a css::table::TableSortField2 be introduced and uses of TableSortField adapted, while maintaining compatibility with existing API consumers. Overall not just a “let’s add a flag” task…

karolus · December 9, 2021, 10:34am

If you switch to a serious Programming-Language this source of information could be helpfull

erAck · October 3, 2020, 5:04pm

Do not force the field to type Text during import and the times will be imported as actual time values which would sort naturally with a normal sort. You can still convert the existing text content to numeric time content following this FAQ.

However, SortFields(0).FieldType = "AlphaNumeric" is wrong anyway, because values for FieldType are not string but of enum TableSortFieldType (see API docs), for which a TableSortField.CollatorAlgorithm could be defined.

Natural sort though is not available as CollatorAlgorithm but implemented only as a Calc sort algorithm, unfortunately without being accessible through the UNO API’s SortDescriptor.