[Solved] LibreOffice Calc Sort via AU3, VBA/VBS
Posted: Sun Mar 17, 2024 9:08 pm
Good day everyone,
I’m not sure exactly how to introduce this post, and I’m not fluent in appropriate scripting terms and what not, so please forgive the poor usage.
I am in the process of creating a User-Defined Function (UDF) for users to automate LibreOffice using AutoIt. The process has worked great so far, and I am currently working on making functions to work with Calc, but I have run into a problem while trying to use the Sort function. I have followed Andrew Pitonyak’s book, and double checked all of my code to ensure I haven’t missed anything. It seems to be all correct.
The problem I am encountering is as follows: I have a Range, say A1:A5, with the values 5, 4, 3, 1, 2. I run my AutoIt code that is supposed to sort the range (A1:A5), and have the sort copy the results to a separate cell, cell C3, so that I know the sort function is actually being called. What occurs is the values are output to cell C3 and down, but remain in the order they were found, in this case, 5, 4, 3, 1, 2. In case it matters, if I don’t copy the results, the sort occurs, but the values aren’t sorted that way either. No COM Errors result from calling the script, in either case.
To begin with I thought it was perhaps a problem with an incompatibility with AutoIt, but I wanted to make sure, so I attempted to convert the code to work in VBA/VBS (the only other BASIC language I can figure out how to use to automate LibreOffice). The result is the same, the sort is processed, but the output is not sorted.
I have also converted the code to work in LibreOffice Basic. That, of course, works fine. I am thinking that this may be a bug in LibreOffice itself, when an external language (or BASIC language at least) is used to call a sort function? But before I report it, I wanted to see if anyone here has any input on why this wouldn’t be working correctly. Perhaps I have missed some obvious limitation? As a side note, if I can explain this correctly, I think the main issue at hand is the SortField Struct or Sortfield Array is not being recognized in the array of sort descriptor settings. The reason for this conclusion is as follows: I can write a simple macro and store it in the LibreOffice document, such as the following:
After creating the above macro in the document, I can go to AutoIt, and using the SortField Struct array, the Sort Descriptor Array, and the range Object that I tried to call the sort command with directly from AutoIt (which failed, as noted above), I can instead pass those as parameters to the Macro in the document, and then have the macro insert the SortField array and call the sort, and the sort works just fine. If I pass only the Range and the SortDescriptor array with the SortField Array inserted already, the sort fails the same as when I tried to sort directly from AutoIt.
I would appreciate any input on this issue, on whether you think this is worth reporting? Or if I have missed something obvious? If you need me to clarify, or try to re-word anything, let me know. Thank you very much for your help.
The scripts I have written are below. In all of them I have made it automatically fill in the values 5, 4, 3, 1, 2 in cells A1:A5 to ease the demonstration.
Code for AutoIt,
And VBA
And VBS
And the LibreOffice Macro.
I’m not sure exactly how to introduce this post, and I’m not fluent in appropriate scripting terms and what not, so please forgive the poor usage.
I am in the process of creating a User-Defined Function (UDF) for users to automate LibreOffice using AutoIt. The process has worked great so far, and I am currently working on making functions to work with Calc, but I have run into a problem while trying to use the Sort function. I have followed Andrew Pitonyak’s book, and double checked all of my code to ensure I haven’t missed anything. It seems to be all correct.
The problem I am encountering is as follows: I have a Range, say A1:A5, with the values 5, 4, 3, 1, 2. I run my AutoIt code that is supposed to sort the range (A1:A5), and have the sort copy the results to a separate cell, cell C3, so that I know the sort function is actually being called. What occurs is the values are output to cell C3 and down, but remain in the order they were found, in this case, 5, 4, 3, 1, 2. In case it matters, if I don’t copy the results, the sort occurs, but the values aren’t sorted that way either. No COM Errors result from calling the script, in either case.
To begin with I thought it was perhaps a problem with an incompatibility with AutoIt, but I wanted to make sure, so I attempted to convert the code to work in VBA/VBS (the only other BASIC language I can figure out how to use to automate LibreOffice). The result is the same, the sort is processed, but the output is not sorted.
I have also converted the code to work in LibreOffice Basic. That, of course, works fine. I am thinking that this may be a bug in LibreOffice itself, when an external language (or BASIC language at least) is used to call a sort function? But before I report it, I wanted to see if anyone here has any input on why this wouldn’t be working correctly. Perhaps I have missed some obvious limitation? As a side note, if I can explain this correctly, I think the main issue at hand is the SortField Struct or Sortfield Array is not being recognized in the array of sort descriptor settings. The reason for this conclusion is as follows: I can write a simple macro and store it in the LibreOffice document, such as the following:
Code: Select all
REM Macro for Performing a Sort Function.
Sub AU3LibreOffice_Sort(oRange, avSortDesc, atField)
For i = LBound(avSortDesc) To UBound(avSortDesc)
If (avSortDesc(i).Name() = ""SortFields"") Then avSortDesc(i).Value = atField
Next
oRange.Sort(avSortDesc())
End Sub
I would appreciate any input on this issue, on whether you think this is worth reporting? Or if I have missed something obvious? If you need me to clarify, or try to re-word anything, let me know. Thank you very much for your help.
The scripts I have written are below. In all of them I have made it automatically fill in the values 5, 4, 3, 1, 2 in cells A1:A5 to ease the demonstration.
Code for AutoIt,
Code: Select all
Global $oCOM_ErrorHandler = ObjEvent("AutoIt.Error", __COM_ERROR)
Global $oServiceManager = ObjCreate("com.sun.star.ServiceManager")
If Not IsObj($oServiceManager) Then Exit ConsoleWrite("! Error: " & @ScriptLineNumber & @CRLF)
Global $oDesktop = $oServiceManager.createInstance("com.sun.star.frame.Desktop")
If Not IsObj($oDesktop) Then Exit ConsoleWrite("! Error: " & @ScriptLineNumber & @CRLF)
Global Const $iURLFrameCreate = 8 ;frame will be created if not found
Global $aArgs[0]
; Create a new Calc document.
Global $oDoc = $oDesktop.loadComponentFromURL("private:factory/scalc", "_blank", $iURLFrameCreate, $aArgs)
If Not IsObj($oDoc) Then Exit ConsoleWrite("! Error: " & @ScriptLineNumber & @CRLF)
; Retrieve Active Sheet
Global $oSheet = $oDoc.CurrentController.getActiveSheet()
If Not IsObj($oSheet) Then Exit ConsoleWrite("! Error: " & @ScriptLineNumber & @CRLF)
; Retrieve Cell Range A1 to A5
Global $oRange = $oSheet.getCellRangeByName("A1:A5")
If Not IsObj($oRange) Then Exit ConsoleWrite("! Error: " & @ScriptLineNumber & @CRLF)
; Fill Arrays with numbers
Global $aaiArray[5]
Global $aiFill[1]
$aiFill[0] = 5
$aaiArray[0] = $aiFill
$aiFill[0] = 4
$aaiArray[1] = $aiFill
$aiFill[0] = 3
$aaiArray[2] = $aiFill
$aiFill[0] = 1
$aaiArray[3] = $aiFill
$aiFill[0] = 2
$aaiArray[4] = $aiFill
; Fill the Range with numbers.
$oRange.setData($aaiArray)
Global Const _
$LOC_SORT_DATA_TYPE_AUTO = 0, _ ; Automatically determine Sort Data type.
$LOC_SORT_DATA_TYPE_NUMERIC = 1, _ ; Sort Data type is Numerical.
$LOC_SORT_DATA_TYPE_ALPHANUMERIC = 2 ; Sort Data type is Text.
; Create a Sort Descriptor,
Global $tSortField = $oServiceManager.Bridge_GetStruct("com.sun.star.table.TableSortField")
If Not IsObj($tSortField) Then Exit ConsoleWrite("! Error: " & @ScriptLineNumber & @CRLF)
With $tSortField
.Field = 0 ; 0 = first column in the range A1-A5.
.FieldType = $LOC_SORT_DATA_TYPE_NUMERIC ; Numerical values being sorted
.IsAscending = False ; Descending order
.IsCaseSensitive = False
EndWith
Global $atSortField[1] = [$tSortField]
$avSortDesc = $oRange.createSortDescriptor()
; Create a Cell Address to indicate where to copy output to. Cell C3
Global $tCellAddr = $oServiceManager.Bridge_GetStruct("com.sun.star.table.CellAddress")
If Not IsObj($tCellAddr) Then Exit ConsoleWrite("! Error: " & @ScriptLineNumber & @CRLF)
$tCellAddr.Sheet = 0 ; 0 = first sheet.
$tCellAddr.Column = 2 ; 2 = Column C
$tCellAddr.Row = 2 ; 2 = Row 3
; Apply Sort settings
For $i = 0 To UBound($avSortDesc) - 1
Switch $avSortDesc[$i].Name()
Case "IsSortColumns"
$avSortDesc[$i].Value = False ; False = Sort rows top to bottom.
Case "ContainsHeader"
$avSortDesc[$i].Value = False ; False = Range has no headers to ignore.
Case "SortFields"
$avSortDesc[$i].Value = $atSortField
Case "BindFormatsToContent"
$avSortDesc[$i].Value = False ; False = Dont bind any formatting to the data when sorted.
Case "CopyOutputData"
$avSortDesc[$i].Value = True ; True = Copy the sort results instead of modifying the cell range itself.
Case "OutputPosition"
$avSortDesc[$i].Value = $tCellAddr
EndSwitch
Next
; Perform the sort
$oRange.Sort($avSortDesc)
Func __COM_ERROR(ByRef $oComError)
ConsoleWrite("!--COM Error-Begin--" & @CRLF & _
"Number: 0x" & Hex($oComError.number, 8) & @CRLF & _
"WinDescription: " & $oComError.windescription & @CRLF & _
"Source: " & $oComError.source & @CRLF & _
"Error Description: " & $oComError.description & @CRLF & _
"HelpFile: " & $oComError.helpfile & @CRLF & _
"HelpContext: " & $oComError.helpcontext & @CRLF & _
"LastDLLError: " & $oComError.lastdllerror & @CRLF & _
"At line: " & $oComError.scriptline & @CRLF & _
"!--COM-Error-End--" & @CRLF)
EndFunc ;==>__COM_ERROR
Code: Select all
Sub SortLibre()
' Create the Service Manager
Set oServiceManager = CreateObject("com.sun.star.ServiceManager")
' Create the desktop.
Set oDesktop = oServiceManager.createInstance("com.sun.star.frame.Desktop")
' Open a new empty Calc document.
Dim args()
Dim s As String
s = "private:factory/scalc"
Set oDoc = oDesktop.loadComponentFromURL(s, "_blank", 0, args())
Dim oSheet As Object
' Retrieve Active Sheet
Set oSheet = oDoc.CurrentController.getActiveSheet()
' Retrieve Cell Range A1 to A5
Dim oRange As Object
Set oRange = oSheet.getCellRangeByName("A1:A5")
' Fill the Range with numbers.
oRange.setData (Array(Array(5), Array(4), Array(3), Array(1), Array(2)))
' Dim atSortFields(0) As New com.sun.star.Table.TableSortField
Dim atSortFields As Variant
Dim tSortField As Object
Set tSortField = oServiceManager.Bridge_GetStruct("com.sun.star.table.TableSortField")
With tSortField
.Field = 0
.FieldType = 1 ' Numeric
.IsAscending = False ' Descending Sort order
.IsCaseSensitive = False
End With
atSortFields = Array(tSortField)
Dim avSortDesc As Variant
avSortDesc = oRange.createSortDescriptor()
' Create a Cell Address to indicate where to copy output to. Cell C3
Dim tCellAddr As Object
Set tCellAddr = oSheet.getCellRangeByName("C3").CellAddress()
tCellAddr.Sheet = 0 ' 0 = first sheet.
tCellAddr.Column = 2 ' 2 = Column C
tCellAddr.Row = 2 ' 2 = Row 3
' Apply Sort settings
For i = LBound(avSortDesc) To UBound(avSortDesc)
Select Case avSortDesc(i).Name()
Case "IsSortColumns"
avSortDesc(i).Value = False ' False = Sort rows top to bottom.
Case "ContainsHeader"
avSortDesc(i).Value = False ' False = Range has no headers to ignore.
Case "SortFields"
avSortDesc(i).Value = atSortFields
Case "BindFormatsToContent"
avSortDesc(i).Value = False ' False = Dont bind any formatting to the data when sorted.
Case "CopyOutputData"
avSortDesc(i).Value = True ' True = Copy the sort results instead of modifying the cell range itself.
Case "OutputPosition"
avSortDesc(i).Value = tCellAddr ' Copy to Cell C3
End Select
Next
' Perform the sort
oRange.Sort (avSortDesc)
End Sub
Code: Select all
' Create the Service Manager
Set oServiceManager = WScript.CreateObject("com.sun.star.ServiceManager")
' Create the desktop.
Set oDesktop = oServiceManager.createInstance("com.sun.star.frame.Desktop")
' Open a new empty Calc document.
Dim args()
Set oDoc = oDesktop.loadComponentFromURL("private:factory/scalc", "_blank", 0, args)
' Retrieve Active Sheet
Set oSheet = oDoc.CurrentController.getActiveSheet()
' Retrieve Cell Range A1 to A5
Set oRange = oSheet.getCellRangeByName("A1:A5")
' Fill the Range with numbers.
oRange.setData (Array(Array(5), Array(4), Array(3), Array(1), Array(2)))
Set tSortField = oServiceManager.Bridge_GetStruct("com.sun.star.table.TableSortField")
With tSortField
.Field = 0
.FieldType = 1 ' Numeric
.IsAscending = False ' Descending Sort order
.IsCaseSensitive = False
End With
atSortFields = Array(tSortField)
avSortDesc = oRange.createSortDescriptor()
' Create a Cell Address to indicate where to copy output to. Cell C3
Set tCellAddr = oSheet.getCellRangeByName("C3").CellAddress
tCellAddr.Sheet = 0 ' 0 = first sheet.
tCellAddr.Column = 2 ' 2 = Column C
tCellAddr.Row = 2 ' 2 = Row 3
' Apply Sort settings
For i = LBound(avSortDesc) To UBound(avSortDesc)
Select Case avSortDesc(i).Name
Case "IsSortColumns"
avSortDesc(i).Value = False ' False = Sort rows top to bottom.
Case "ContainsHeader"
avSortDesc(i).Value = False ' False = Range has no headers to ignore.
Case "SortFields"
avSortDesc(i).Value = atSortFields
Case "BindFormatsToContent"
avSortDesc(i).Value = False ' False = Dont bind any formatting to the data when sorted.
Case "CopyOutputData"
avSortDesc(i).Value = True ' True = Copy the sort results instead of modifying the cell range itself.
Case "OutputPosition"
avSortDesc(i).Value = tCellAddr ' Copy to Cell C3
End Select
Next
' Perform the sort
oRange.Sort (avSortDesc)
Code: Select all
Sub Main
' Retrieve Active Sheet
Dim oSheet
oSheet = ThisComponent.CurrentController.getActiveSheet()
' Retrieve Cell Range A1 to A5
Dim oRange
oRange = oSheet.getCellRangeByName("A1:A5")
' Fill the Range with numbers.
oRange.setData(Array(Array(5), Array(4), Array(3), Array(1), Array(2)))
Dim atSortFields(0) as new com.sun.star.table.TableSortField
atSortFields(0).Field = 0 ' 0 = first column in the range.
atSortFields(0).FieldType =com.sun.star.util.SortFieldType.NUMERIC ' = Numerical values being sorted
atSortFields(0).IsAscending = False ' Descending order
atSortFields(0).IsCaseSensitive = False
DIm avSortDesc
avSortDesc = oRange.createSortDescriptor()
' Create a Cell Address to indicate where to copy output to. Cell C3
Dim tCellAddr As New com.sun.star.table.CellAddress
tCellAddr.Sheet = 0 ' 0 = first sheet.
tCellAddr.Column = 2 ' 2 = Column C
tCellAddr.Row = 2 ' 2 = Row 3
' Apply Sort settings
For i = LBound(avSortDesc) To UBound(avSortDesc)
Select Case avSortDesc(i).Name()
Case "IsSortColumns"
avSortDesc(i).Value = False ' False = Sort rows top to bottom.
Case "ContainsHeader"
avSortDesc(i).Value = False ' False = Range has no headers to ignore.
Case "SortFields"
avSortDesc(i).Value = atSortFields
Case "BindFormatsToContent"
avSortDesc(i).Value = False ' False = Dont bind any formatting to the data when sorted.
Case "CopyOutputData"
avSortDesc(i).Value = True ' True = Copy the sort results instead of modifying the cell range itself.
Case "OutputPosition"
avSortDesc(i).Value = tCellAddr
End Select
Next
' Perform the sort
oRange.Sort(avSortDesc)
End Sub