wizards/source/scriptforge/SF_Array.xba | 17 ++++++++++++++---
wizards/source/scriptforge/SF_Session.xba | 12 ++++++++++--
wizards/source/scriptforge/python/scriptforge.py | 3 ++-
wizards/source/scriptforge/python/scriptforge.pyi | 9 +++++----
4 files changed, 31 insertions(+), 10 deletions(-)
New commits:
commit d28fcb3e26497ab1982ffcaee613e33821773c2a
Author: Jean-Pierre Ledure <j...@ledure.be>
AuthorDate: Tue Dec 3 17:06:16 2024 +0100
Commit: Jean-Pierre Ledure <j...@ledure.be>
CommitDate: Wed Dec 4 10:21:45 2024 +0100
ScriptForge (SF_Session) enhance ExecuteCalcFunction
The ExecuteCalcFunction() method is a wrapper
of the
com.sun.star.sheet.FunctionAccess::callFunction()
method.
This method accepts a broad variety of arguments
- scalars: numeric and strings only
- data arrays (= arrays os arrays)
- 2D arrays (1D arrays give errors)
- com.sun.star.table.XCellRange objects
depending on the called function and its
execution or not as an "array function".
The actual commit makes that the arguments passed to
session.ExecuteCalcFunction()
are checked as formally correct: scalars are filtered
as numeric or strings, dates and booleans are converted,
arrays are reshaped to 2D where necessary.
The actual change facilitates, as an example,
the use of complex array functions like XLOOKUP,
a powerful search engine on large data sets.
The implemented functionalities are valid in Basic
and Python.
The help documentation might be completed with
additional examples.
Change-Id: I6bba1e21828ef09c5c6f0463cbcfa1f5df695073
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/177744
Tested-by: Jenkins
Reviewed-by: Jean-Pierre Ledure <j...@ledure.be>
diff --git a/wizards/source/scriptforge/SF_Array.xba
b/wizards/source/scriptforge/SF_Array.xba
index 386b405bd89a..05c96c96d494 100644
--- a/wizards/source/scriptforge/SF_Array.xba
+++ b/wizards/source/scriptforge/SF_Array.xba
@@ -2673,19 +2673,30 @@ Private Function _ConvertToCellValue(ByVal pvItem As
Variant _
, ByVal pbIsCell As Boolean _
) As Variant
''' Convert the argument to a valid Calc cell content, i.e.
a string or a double.
-''' When the argument is not convertible, either
+''' When the argument is not immediately convertible, either
+''' - the cell range is returned unchanged if the
argument is a UNO com.sun.star.table.XCellRange object
+''' - the date is converted to a double when the
argument is a UNO com.sun.star.util.DateTime object
''' - the zero-length string is returned (pbIsCell
= True)
''' - the argument is returned unchanged (pbIsCell
= False)
-Dim vCell As Variant ' Return value
+Dim vCell As Variant ' Return value
+Dim oObjectDescriptor As Object ' Object descriptor (see SF_Utils)
Try:
+ ' Conversion takes place only when pbIsCell = True
If pbIsCell Then
Select Case SF_Utils._VarTypeExt(pvItem)
Case V_STRING :
vCell = pvItem
Case V_DATE
: vCell = CDbl(pvItem)
Case V_NUMERIC :
vCell = CDbl(pvItem)
Case V_BOOLEAN :
vCell = CDbl(Iif(pvItem, 1, 0))
+ Case V_OBJECT
+ Set oObjectDescriptor =
SF_Utils._VarTypeObj(pvItem)
+ Select Case oObjectDescriptor.sObjectType
+ Case "ScCellRangeObj"
: vCell = pvItem
+ Case
"com.sun.star.util.DateTime" : vCell =
CDbl(CDateFromUnoDateTime(pvItem)) ' Python date
+ Case Else
: vCell = ""
+ End Select
Case Else
: vCell = ""
End Select
Else ' Return the input item unchanged
@@ -2978,4 +2989,4 @@ Dim iCompare As Integer, iVarType1 As Integer, iVarType2
As Integer
End Function ' ScriptForge.SF_Array._ValCompare
REM ================================================= END OF
SCRIPTFORGE.SF_ARRAY
-</script:module>
+</script:module>
\ No newline at end of file
diff --git a/wizards/source/scriptforge/SF_Session.xba
b/wizards/source/scriptforge/SF_Session.xba
index ebde30256c7f..30d220d3be4c 100644
--- a/wizards/source/scriptforge/SF_Session.xba
+++ b/wizards/source/scriptforge/SF_Session.xba
@@ -190,8 +190,10 @@ Public Function ExecuteCalcFunction(Optional ByVal
CalcFunction As Variant _
''' Args:
''' CalcFunction: the english name of the function
to execute
''' pvArgs: the arguments of the called function
-''' Each argument must be either a string,
a numeric value
-''' or an array of arrays combining those
types
+''' Each argument must be either
+''' - a string, a numeric value, a date or
a boolean
+''' - a 1D array, a 2D array or an array of
arrays combining those types
+''' - a com.sun.star.table.XCellRange UNO
object
''' Returns:
''' The (string or numeric) value or the array of
arrays returned by the call to the function
''' When the arguments contain arrays, the function
is executed as an array function
@@ -205,6 +207,7 @@ Public Function ExecuteCalcFunction(Optional ByVal
CalcFunction As Variant _
Dim oCalc As Object ' Give access to the
com.sun.star.sheet.FunctionAccess service
Dim vReturn As Variant ' Returned value
+Dim i As Long
Const cstThisSub = "Session.ExecuteCalcFunction"
Const cstSubArgs = "CalcFunction, arg0[, arg1] ..."
@@ -223,6 +226,11 @@ Try:
If UBound(pvArgs) = 0 Then
If IsEmpty(pvArgs(0)) Then pvArgs = Array()
End If
+ ' Force suitable arguments
+ For i = 0 To UBound(pvArgs)
+ pvArgs(i) = SF_Array.ConvertToRange(pvArgs(i), Direction :=
"V")
+ Next i
+ ' Error trapping and execution
If SF_Utils._ErrorHandling() Then On Local Error GoTo CatchCall
vReturn = oCalc.callFunction(UCase(CalcFunction), pvArgs())
diff --git a/wizards/source/scriptforge/python/scriptforge.py
b/wizards/source/scriptforge/python/scriptforge.py
index 05a7fa557e74..9dd8a90d7b2f 100644
--- a/wizards/source/scriptforge/python/scriptforge.py
+++ b/wizards/source/scriptforge/python/scriptforge.py
@@ -1545,7 +1545,8 @@ class SFScriptForge:
# Arguments of Calc functions are strings or numbers. None ==
Empty is a good alias for no argument
args = (calcfunction,) + (None,)
else:
- args = (calcfunction,) + args
+ # Date arguments are converted on-the-fly to
com.sun.star.util.DateTime
+ args = (calcfunction,) +
tuple(map(SFScriptForge.SF_Basic.CDateToUnoDateTime, args))
# ExecuteCalcFunction method has a ParamArray parameter in Basic
return cls.SIMPLEEXEC('@SF_Session.ExecuteCalcFunction', args)
diff --git a/wizards/source/scriptforge/python/scriptforge.pyi
b/wizards/source/scriptforge/python/scriptforge.pyi
index 879db366e4a9..1f4bc8f8f5e9 100644
--- a/wizards/source/scriptforge/python/scriptforge.pyi
+++ b/wizards/source/scriptforge/python/scriptforge.pyi
@@ -1797,11 +1797,12 @@ class SFScriptForge:
Args
``calcfunction``: the name of the Calc function to be
called, in English.
- ``args``: the arguments to be passed to the called
Calc function.
- Each argument must be either a string, a numeric value
or an array of arrays combining
- those types.
+ ``args``: the arguments to be passed to the called
Calc function. Each argument may be either
+ - a string, a numeric value, a bool or a
datatime.datetime instance
+ - a tuple or a tuple of tuples combining those
types
+ - a com.sun.star.table.XCellRange UNO object
Returns
- The value returned by the function.
+ The value returned by the function as a scalar or a
tuple of tuples.
"""
...
