wizards/source/scriptforge/SF_PythonHelper.xba | 4 +
wizards/source/scriptforge/SF_Session.xba | 85 ++++++++++++++++++++++
wizards/source/scriptforge/python/scriptforge.py | 3
wizards/source/scriptforge/python/scriptforge.pyi | 26 ++++++
wizards/source/scriptforge/script.xlb | 32 ++++----
5 files changed, 133 insertions(+), 17 deletions(-)
New commits:
commit e72f7d6dd36bafebe085ed91e38602158ac561f0
Author: Jean-Pierre Ledure <j...@ledure.be>
AuthorDate: Wed Aug 6 12:51:15 2025 +0200
Commit: Jean-Pierre Ledure <j...@ledure.be>
CommitDate: Wed Aug 6 14:34:55 2025 +0200
ScriptForge new session.GetRangeFromCalc method
The
GetRangeFromCalc(filename: str, range: str)
method (stored in the Session service)
reads the values contained in the given
range located in the given Calc file.
The file does not need to be open. This
makes this shortcut particularly useful.
The content of the range is returned either
- as a scalar for a single cell
- as a 1D-array or a tuple when the range
is contained in a single row or column
- as a 2D-array or a tuple of tuples for
a rectangular range of adjacent cells.
Clean error management is included in the code.
The method is available for Basic and Python
user scripts.
The user documentation needs to be updated.
Change-Id: I39cd218f0bf3a6a21e3ada42826069eeee451c6e
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/189000
Reviewed-by: Jean-Pierre Ledure <j...@ledure.be>
Tested-by: Jenkins
diff --git a/wizards/source/scriptforge/SF_PythonHelper.xba
b/wizards/source/scriptforge/SF_PythonHelper.xba
index 8328af61c1b4..4de5bf74d44e 100644
--- a/wizards/source/scriptforge/SF_PythonHelper.xba
+++ b/wizards/source/scriptforge/SF_PythonHelper.xba
@@ -808,6 +808,10 @@ Try:
vReturn = vBasicObject.Properties()
Else
Select Case sServiceName
+ Case
"ScriptForge.SF_Session"
+ Select Case Script
+ Case
"GetRangeFromCalc" : vReturn =
vBasicObject.GetRangeFromCalc(vArgs(0), vArgs(1))
+ End Select
Case
"SFDatabases.Database"
If Script =
"GetRows" Then vReturn = vBasicObject.GetRows(vArgs(0),
vArgs(1), vArgs(2), vArgs(3))
Case
"SFDatabases.Dataset"
diff --git a/wizards/source/scriptforge/SF_Session.xba
b/wizards/source/scriptforge/SF_Session.xba
index 30d220d3be4c..281ff301727a 100644
--- a/wizards/source/scriptforge/SF_Session.xba
+++ b/wizards/source/scriptforge/SF_Session.xba
@@ -19,6 +19,7 @@ Option Explicit
''' - UNO introspection utilities
''' - clipboard management
''' - invocation of external scripts or
programs
+''' - access to the content of Calc files
'''
''' Service invocation example:
''' Dim session As Variant
@@ -31,6 +32,7 @@ Option Explicit
REM ==================================================================
EXCEPTIONS
Const CALCFUNCERROR =
"CALCFUNCERROR" ' Calc function execution failed
+Const CALCADDRESSERROR = "CALCADDRESSERROR"
' Invalid Calc range
Const NOSCRIPTERROR =
"NOSCRIPTERROR" ' Script could not be located
Const SCRIPTEXECERROR = "SCRIPTEXECERROR"
' Exception during script execution
Const WRONGEMAILERROR = "WRONGEMAILERROR"
' Wrong email address
@@ -326,6 +328,88 @@ Catch:
GoTo Finally
End Function ' ScriptForge.SF_Session.ExecutePythonScript
+REM
-----------------------------------------------------------------------------
+Public Function GetRangeFromCalc(Optional ByVal FileName As Variant _
+ , Optional
ByVal Range As Variant _
+ ) As Variant
+''' Extract the content of a range of adjacent cells stored
in a Calc file.
+''' The file may be open or closed. If the file is open in
the actual LibreOffice
+''' session, its actual content will be read. Otherwise the
content will be
+''' read from the file on disk.
+''' Inspired by
https://bugs.documentfoundation.org/show_bug.cgi?id=148040
+''' Args:
+''' FileName: the full file name, given in
FileSystem.FileNaming notation.
+''' The file must exist.
+''' Range: either
+''' "sheet.cells", where
"sheet" is the mandatory sheet name, eventually
+''' surrounded with single wuotes
when it contains special characters,
+''' and "cells" is the
single cell or the range of cells
+''' to be considered, in A1
notation with or without dollar signs
+''' a defined name at document-global level.
+''' Current selection shortcuts
("~") are forbidden in this context.
+''' Returns:
+''' Either a scalar, or a 1D array when the range
is located in a single row or column,
+''' or a 2D array.
+''' The returned values are either strings or
numbers. No dates, booleans, ...
+''' To convert doubles to dates, use the CDate
builtin function.
+''' Empty cells are returned as empty strings.
+''' Exceptions:
+''' UNKNOWNFILEERROR The Calc file
does not exist
+''' CALCADDRESSERROR Invalid Calc
range
+''' Example:
+''' value =
session.GetFromFileName("C:\myFile.ods", "Sheet1.A1:J10")
+
+Dim vReturn As Variant ' Returned value
+Dim oCalc As Object ' Give access to the
com.sun.star.sheet.FunctionAccess service
+Dim sArgument As String ' The argument given to
the "Indirect" Calc function
+
+Const cstThisSub = "Session.GetRangeFromCalc"
+Const cstSubArgs = "FileName, Range"
+
+ If SF_Utils._ErrorHandling() Then On Local Error GoTo Catch
+ vReturn = Empty
+
+Check:
+ If SF_Utils._EnterFunction(cstThisSub, cstSubArgs) Then
+ If Not SF_Utils._ValidateFile(FileName, "FileName")
Then GoTo Finally
+ If Not SF_Utils._Validate(Range, "Range", V_STRING)
Then GoTo Finally
+ End If
+
+ If Not SF_FileSystem.FileExists(FileName) Then GoTo CatchNotExists
+
+Try:
+ ' The execution results from the
com.sun.star.sheet.FunctionAccess service
+ Set oCalc = SF_Utils._GetUNOService("FunctionAccess")
+
+ ' Error trapping, execution, error reset
+ If SF_Utils._ErrorHandling() Then On Local Error GoTo CatchCall
+ sArgument = "'" &
SF_FileSystem._ConvertToUrl(FileName) & "'#" & Range
+ vReturn = oCalc.callFunction("INDIRECT", Array(sArgument))
+ If SF_Utils._ErrorHandling() Then On Local Error GoTo Catch
+
+ ' Transform the arrays of subarrays => 2D arrays, only in Basic
+ Select Case SF_Array.CountDims(vReturn)
+ Case -1, 0, 2
+ ' Do nothing: scalar or empty array or 2D array
(should not happen)
+ Case 1
+ If IsArray(vReturn(0)) And Not _SF_.TriggeredByPython
Then vReturn = SF_Array.ConvertFromDataArray(vReturn, IsRange := True)
+ Case Else
+ End Select
+
+Finally:
+ GetRangeFromCalc = vReturn
+ SF_Utils._ExitFunction(cstThisSub)
+ Exit Function
+Catch:
+ GoTo Finally
+CatchNotExists:
+ SF_Exception.RaiseFatal(UNKNOWNFILEERROR, "FileName",
FileName)
+ GoTo Finally
+CatchCall:
+ ScriptForge.SF_Exception.RaiseFatal(CALCADDRESSERROR,
"Range", Range, "FileName", FileName)
+ GoTo Finally
+End Function ' ScriptForge.SF_Session.GetRangeFromCalc
+
REM
-----------------------------------------------------------------------------
Public Function GetPDFExportOptions() As Variant
''' Return the actual values of the PDF export options
@@ -506,6 +590,7 @@ Public Function Methods() As Variant
"ExecuteBasicScript" _
, "ExecuteCalcFunction" _
, "ExecutePythonScript" _
+ , "GetRangeFromCalc" _
, "HasUnoMethod" _
, "HasUnoProperty" _
, "OpenURLInBrowser" _
diff --git a/wizards/source/scriptforge/python/scriptforge.py
b/wizards/source/scriptforge/python/scriptforge.py
index ddad6364009c..eb1a39ad6809 100644
--- a/wizards/source/scriptforge/python/scriptforge.py
+++ b/wizards/source/scriptforge/python/scriptforge.py
@@ -1571,6 +1571,9 @@ class SFScriptForge:
def ExecutePythonScript(cls, scope = '', script = '', *args):
return cls.SIMPLEEXEC(scope + '#' + script, *args)
+ def GetRangeFromCalc(self, filename, range):
+ return self.ExecMethod(self.vbMethod + self.flgArrayRet,
'GetRangeFromCalc', filename, range)
+
def GetPDFExportOptions(self):
return self.ExecMethod(self.vbMethod, 'GetPDFExportOptions')
diff --git a/wizards/source/scriptforge/python/scriptforge.pyi
b/wizards/source/scriptforge/python/scriptforge.pyi
index 637422ecce57..1b7067bba499 100644
--- a/wizards/source/scriptforge/python/scriptforge.pyi
+++ b/wizards/source/scriptforge/python/scriptforge.pyi
@@ -1872,6 +1872,30 @@ class SFScriptForge:
"""
...
+ def GetRangeFromCalc(self, filename: FILE, range: RANGE):
+ """
+ Extract the content of a range of adjacent cells stored in
a Calc file. The file may be open
+ or closed. If the file is open in the actual LibreOffice
session, its actual content will be read.
+ Otherwise, the content will be read from the file on disk.
+ Args
+ ``filename``: the full file name, given in
``FileSystem.FileNaming`` notation.
+
+ ``range``: either
+ - "sheet.cells", where "sheet" is the mandatory
sheet name, eventually surrounded with
+ single quotes when it contains special
characters, and "cells" is the single cell
+ or the range of cells to be considered, in A1
notation with or without dollar signs
+
+ - a defined name at document-global level.
+ Note
+ Current selection shortcuts ("~") are forbidden in
this context.
+ Returns
+ Either a scalar, or a tuple when the range is
located in a single row or column,
+ or a tuple of tuples. The returned values are
either strings or numbers. No dates, booleans, ...
+ To convert doubles to dates, use the
``basic.CDate()`` method.
+ Empty cells are returned as empty strings.
+ """
+ ...
+
def GetPDFExportOptions(self) -> DICTIONARY:
"""
Returns the current PDF export settings defined in the ``PDF
Options`` dialog, which can be accessed
@@ -5938,7 +5962,7 @@ class SFDocuments:
or quotes to improve its readability.
``directsql``: when ``True``, the SQL command is sent
to the database engine without
- pre-analysis. Default is ``False``. The argument is
ignored for tables.For queries,
+ pre-analysis. Default is ``False``. The argument is
ignored for tables. For queries,
the applied option is the one set when the query was
defined.
"""
...
diff --git a/wizards/source/scriptforge/script.xlb
b/wizards/source/scriptforge/script.xlb
index f78d358ee606..98570642b8e2 100644
--- a/wizards/source/scriptforge/script.xlb
+++ b/wizards/source/scriptforge/script.xlb
@@ -1,23 +1,23 @@
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE library:library PUBLIC "-//OpenOffice.org//DTD OfficeDocument
1.0//EN" "library.dtd">
<library:library xmlns:library="http://openoffice.org/2000/library";
library:name="ScriptForge" library:readonly="false"
library:passwordprotected="false">
- <library:element library:name="SF_Timer"/>
+ <library:element library:name="SF_Root"/>
+ <library:element library:name="__License"/>
+ <library:element library:name="SF_Region"/>
+ <library:element library:name="SF_L10N"/>
+ <library:element library:name="SF_String"/>
+ <library:element library:name="_ModuleModel"/>
+ <library:element library:name="SF_UI"/>
+ <library:element library:name="SF_Services"/>
+ <library:element library:name="SF_PythonHelper"/>
+ <library:element library:name="SF_Platform"/>
+ <library:element library:name="SF_Array"/>
+ <library:element library:name="SF_TextStream"/>
+ <library:element library:name="SF_Utils"/>
<library:element library:name="SF_FileSystem"/>
- <library:element library:name="SF_Dictionary"/>
- <library:element library:name="SF_Exception"/>
+ <library:element library:name="SF_Timer"/>
<library:element library:name="SF_Session"/>
- <library:element library:name="SF_Services"/>
+ <library:element library:name="SF_Exception"/>
+ <library:element library:name="SF_Dictionary"/>
<library:element library:name="_CodingConventions"/>
- <library:element library:name="_ModuleModel"/>
- <library:element library:name="SF_String"/>
- <library:element library:name="SF_L10N"/>
- <library:element library:name="SF_Region"/>
- <library:element library:name="__License"/>
- <library:element library:name="SF_Root"/>
- <library:element library:name="SF_Utils"/>
- <library:element library:name="SF_TextStream"/>
- <library:element library:name="SF_Array"/>
- <library:element library:name="SF_Platform"/>
- <library:element library:name="SF_PythonHelper"/>
- <library:element library:name="SF_UI"/>
</library:library>
\ No newline at end of file
