wizards/source/scriptforge/python/scriptforge.py | 5
wizards/source/scriptforge/python/scriptforge.pyi | 30 +++
wizards/source/sfdocuments/SF_Calc.xba | 178 ++++++++++++++++++++--
3 files changed, 199 insertions(+), 14 deletions(-)
New commits:
commit b7e84bec01e060f805820d812fe16930c52b05f1
Author: Jean-Pierre Ledure <j...@ledure.be>
AuthorDate: Mon May 5 17:50:17 2025 +0200
Commit: Jean-Pierre Ledure <j...@ledure.be>
CommitDate: Tue May 6 14:31:56 2025 +0200
ScriptForge (Calc) new DefineName() method
Introduction of
1. the DefinedNames property
Returns the full sorted list of all
named ranges in the document.
The names defined in a single sheet
are qualified with the sheet name.
2. the DefineName() method
calc.DefineName(definedname, value [,sheetname])
Define a new name in the worksheet, at global
or sheet level. If the name existed already,
it is overwritten without warning.
The Value of the new name may be a range,
a scalar value or a formula.
When a range, the top-left cell is set
as reference cell.
After creation, the new name can be used
in formulaes.
The functions are available both for Basic
and Python user scripts.
The documentation will need an update.
Change-Id: I184e97b734a57ecba2e8a8eae583050116f40296
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/184963
Reviewed-by: Jean-Pierre Ledure <j...@ledure.be>
Tested-by: Jenkins
diff --git a/wizards/source/scriptforge/python/scriptforge.py
b/wizards/source/scriptforge/python/scriptforge.py
index 807d8462b5fb..ba710a2d4354 100644
--- a/wizards/source/scriptforge/python/scriptforge.py
+++ b/wizards/source/scriptforge/python/scriptforge.py
@@ -2472,7 +2472,7 @@ class SFDocuments:
serviceimplementation = 'basic'
servicename = 'SFDocuments.Calc'
servicesynonyms = ('calc', 'sfdocuments.calc')
- serviceproperties = dict(CurrentSelection = 3, CustomProperties = 3,
Description = 3,
+ serviceproperties = dict(CurrentSelection = 3, CustomProperties = 3,
DefinedNames = 1, Description = 3,
DocumentProperties = 1, DocumentType = 0,
ExportFilters = 0,
FileSystem = 0, ImportFilters = 0, IsAlive =
1, IsBase = 0, IsCalc = 0,
IsDraw = 0, IsFormDocument = 0, IsImpress =
0, IsMath = 0,
@@ -2619,6 +2619,9 @@ class SFDocuments:
return self.ExecMethod(self.vbMethod, 'DecorateFont', targetrange,
fontname, fontsize, decoration,
filterformula, filterscope)
+ def DefineName(self, definedname, value, sheetname = ''):
+ return self.ExecMethod(self.vbMethod, 'DefineName', definedname,
value, sheetname)
+
def ExportRangeToFile(self, range, filename, imagetype = 'pdf',
overwrite = False):
return self.ExecMethod(self.vbMethod, 'ExportRangeToFile', range,
filename, imagetype, overwrite)
diff --git a/wizards/source/scriptforge/python/scriptforge.pyi
b/wizards/source/scriptforge/python/scriptforge.pyi
index fc30510a0f51..d399f98e5b18 100644
--- a/wizards/source/scriptforge/python/scriptforge.pyi
+++ b/wizards/source/scriptforge/python/scriptforge.pyi
@@ -98,7 +98,7 @@ SERVICE = Union[ARRAY, BASIC, DICTIONARY, EXCEPTION,
FILESYSTEM, L10N, PLATFORM,
DOCUMENT, BASE, CALC, CALCREFERENCE, CHART, FORM, FORMCONTROL,
FORMDOCUMENT, WRITER,
MENU, CONTEXTMENU, POPUPMENU, TOOLBAR, TOOLBARBUTTON]
# UNO
-UNO = TypeVar('UNO')
+UNO = TypeVar('UNO', Any, Any)
# Other
FILE = TypeVar('FILE', str, str)
""" File or folder name expressed in accordance with the
``FileSystem.FileNaming`` notation. """
@@ -5096,6 +5096,12 @@ class SFDocuments:
CurrentSelection: Union[RANGE, Tuple[RANGE, ...]]
""" Get/set the single selected range as a string or the list of
selected ranges as a tuple of strings. """
+ DefinedNames: Tuple[str, ...]
+ """ The list of all names defined in the document, at global and sheet
levels. """
+
+ Sheets: Tuple[str, ...]
+ """ The list with the names of all existing sheets. """
+
def FirstCell(self, rangename: RANGE) -> RANGE:
"""
Returns the First used cell in a given range or sheet. When
the argument is a sheet it will always
@@ -5727,6 +5733,28 @@ class SFDocuments:
"""
...
+ def DefineName(self,
+ definedname: str,
+ value: Union[str, int, float],
+ sheetname: SHEETNAME = ...
+ ) -> bool:
+ """
+ Define a new name in the worksheet, at global or sheet level.
+ The Value of the new name may be a range, a scalar value or a
formula.
+ Args
+ ``definedname``: The name as a string. If it already
exists, it is overwritten without warning.
+ Note that homonyms might exist, but not in the same
sheet and not at global level.
+
+ ``value``: Either a range reference as a string, a
scalar value as a number or a string,
+ or a formula starting with the "=" sign.
+
+ ``Sheetname``: when present, the sheet where the name
is applicable to. The "~" shortcut
+ is accepted.
+ Returns
+ ``True`` when successful.
+ """
+ ...
+
def ExportRangeToFile(self,
range: RANGE,
filename: FILE,
diff --git a/wizards/source/sfdocuments/SF_Calc.xba
b/wizards/source/sfdocuments/SF_Calc.xba
index 3e0de474bd48..171fa7c3cc56 100644
--- a/wizards/source/sfdocuments/SF_Calc.xba
+++ b/wizards/source/sfdocuments/SF_Calc.xba
@@ -225,6 +225,12 @@ Catch:
GoTo Finally
End Property ' SFDocuments.SF_Calc.CurrentSelection (let)
+REM
-----------------------------------------------------------------------------
+Property Get DefinedNames() As Variant
+''' Returns the full sorted list of all named ranges in the
document
+ DefinedNames = _PropertyGet("DefinedNames")
+End Property ' SFDocuments.SF_Calc.DefinedNames
+
REM
-----------------------------------------------------------------------------
Property Get FirstCell(Optional ByVal RangeName As Variant) As String
''' Returns the First used cell in a given range or sheet
@@ -1771,10 +1777,10 @@ Public Function DecorateFont(Optional ByVal TargetRange
As Variant _
''' Returns:
''' A string representing the updated range
''' Examples:
-''' oDoc.DecorateRange("SheetX.A1:J30",
FontSize := 15, Decoration := "BU", FilterFormula :=
"IsNumeric(A1), FilterScope := "CELL")
+''' oDoc.DecorateFont("SheetX.A1:J30",
FontSize := 15, Decoration := "BU", FilterFormula :=
"IsNumeric(A1), FilterScope := "CELL")
''' ' Modify the font size and apply
bold and underline properties to the numeric cells
-Dim sDecorate As String ' Return value
+Dim sDecorate As String ' Return value
Dim oRange As Object ' Alias of TargetRange
Dim vRanges() As Variant ' Array of subranges resulting
from the application of the filter
Dim oDRange As Object ' A single element of vRanges
@@ -1835,6 +1841,95 @@ Catch:
GoTo Finally
End Function ' SFDocuments.SF_Calc.DecorateFont
+REM
-----------------------------------------------------------------------------
+Public Function DefineName(Optional ByVal DefinedName As Variant _
+ , Optional ByVal Value
As Variant _
+ , Optional ByVal
SheetName As Variant _
+ ) As Boolean
+''' Define a new name in the worksheet, at global or sheet
level.
+''' The Value of the new name may be a range, a scalar
value or a formula.
+''' Args:
+''' DefinedName: The name as a string. If it
already exists, it is overwritten without warning.
+''' Note that homonyms might exist, but not
in the same sheet.
+''' Value: Either
+''' a range reference as a string, often a
single cell
+''' a scalar value as a number or a string
+''' a formula starting with the
"=" sign
+''' SheetName: when present, the sheet where the
name is applicable to. The "~" shortcut is accepted.
+''' Returns:
+''' True when successful.
+''' Examples:
+''' oDoc.DefineName("NewName", Sheet :=
"Sheet3", Value := "H2")
+
+Dim bDefine As Boolean ' Return value
+Dim oContainer As Object ' com.sun.star.lang.XComponent or
com.sun.star.sheet.XSpreadsheet
+Dim oRange As Object ' Range after parsing
+Dim oCell As Object '
com.sun.star.table.CellAddress
+Dim sContent As String ' The content to store in the name
+
+Const cstThisSub = "SFDocuments.Calc.DefineName"
+Const cstSubArgs = "DefinedName, Value,
[SheetName=""""]"
+
+ If ScriptForge.SF_Utils._ErrorHandling() Then On Local Error GoTo Catch
+ bDefine = False
+
+Check:
+ If IsMissing(SheetName) Or IsEmpty(SheetName) Then SheetName =
""
+ If ScriptForge.SF_Utils._EnterFunction(cstThisSub, cstSubArgs) Then
+ If Not _IsStillAlive() Then GoTo Finally
+ If Not ScriptForge.SF_Utils._Validate(DefinedName,
"DefinedName", V_STRING) Then GoTo Finally
+ If Not ScriptForge.SF_Utils._Validate(Value, "Value",
Array(V_STRING, ScriptForge.V_NUMERIC)) Then GoTo Finally
+ If Not _ValidateSheet(SheetName, "SheetName", False,
True, True) Then GoTo Finally
+ End If
+
+Try:
+ ' Set the default reference cell
+ Set oCell = New com.sun.star.table.CellAddress
+ With oCell
+ .Sheet = 0
+ .Column = 0
+ .Row = 0
+ End With
+
+ ' Determine the content as a string, and the reference cell when
Value is a range
+ Select Case VarType(Value)
+ Case V_STRING
+ ' Is Value a range ?
+ Set oRange = _ParseAddress(Value, pbError := False)
+ If IsNull(oRange) Then
+ sContent = Value
+ Else
+ With oRange
+ sContent = .RangeName
+ oCell.Sheet = .SheetIndex
+ oCell.Column =
.XCellRange.RangeAddress.StartColumn
+ oCell.Row =
.XCellRange.RangeAddress.StartRow
+ End With
+ End If
+ Case Else
+ ' Use a locale-independent conversion function
+ sContent = Str(Value)
+ End Select
+
+ ' Determine the named ranges container, either at document or at
sheet level
+ If Len(SheetName) = 0 Then Set oContainer = _Component Else Set
oContainer = _Component.Sheets.getByName(SheetName)
+
+ ' Set the new name or replace it if it exists
+ With oContainer.NamedRanges
+ If .hasByName(DefinedName) Then .removeByName(DefinedName)
+ .addNewByName(DefinedName, sContent, oCell, 0)
+ End With
+
+ bDefine = True
+
+Finally:
+ DefineName = bDefine
+ ScriptForge.SF_Utils._ExitFunction(cstThisSub)
+ Exit Function
+Catch:
+ GoTo Finally
+End Function ' SFDocuments.SF_Calc.DefineName
+
REM
-----------------------------------------------------------------------------
Public Function ExportRangeToFile(Optional ByVal Range As Variant _
,
Optional ByVal FileName As Variant _
@@ -2601,6 +2696,7 @@ Public Function Methods() As Variant
, "DAvg" _
, "DCount" _
, "DecorateFont" _
+ , "DefineName" _
, "DMax" _
, "DMin" _
, "DSum" _
@@ -3054,6 +3150,7 @@ Public Function Properties() As Variant
Properties = Array( _
"CurrentSelection" _
, "CustomProperties" _
+ , "DefinedNames" _
, "Description" _
, "DocumentProperties" _
, "DocumentType" _
@@ -4806,6 +4903,54 @@ Finally:
_LastCell = vCoordinates
End Function ' SFDocuments.SF_Calc._LastCell
+REM
-----------------------------------------------------------------------------
+Private Function _NamedRangesList(Optional ByVal pbAll As Boolean) As Variant
+''' Return the list, as an array of all the named ranges of
the document.
+''' The list may, optionally, include the named ranges
owned by sheets. Such names are qualified with the sheet name.
+''' Note that synonyms across sheet named ranges may exist.
+''' Args:
+''' pbAll: When true (default), returns the full
list of names
+''' Returns:
+''' The resulting list is sorted.
+
+Dim vNamedRanges As Variant ' Return value
+Dim lTop As Long ' Upper bound of
vNamedRanges
+Dim oSheets As Object '
com.sun.star.sheet.XSpreadsheets
+Dim vSheetNames As Variant ' Array of sheet names
+Dim sSheetName As String ' A single sheet name
+Dim vSheetNamedRanges As Variant ' Array of named ranges found in
a sheet
+Dim sSheetNamedRange As String ' A single named range
+Dim arr As Object : Set arr =
CreateScriptService("Array")
+
+ If IsMissing(pbAll) Then pbAll = True
+ vNamedRanges = Array()
+
+Try:
+ vNamedRanges = _Component.NamedRanges.getElementNames()
+ If pbAll Then
+ Set oSheets = _Component.Sheets
+ With oSheets
+ vSheetNames = .getElementNames()
+ ' Explore sheet by sheet
+ For Each sSheetName In vSheetNames
+ vSheetNamedRanges =
.GetByName(sSheetName).NamedRanges.getElementNames()
+ For Each sSheetNamedRange In vSheetNamedRanges
+ lTop = UBound(vNamedRanges) + 1
+ ReDim Preserve vNamedRanges(0 To lTop)
+ ' The named range is coded
'sheet'.range
+ vNamedRanges(lTop) =
"$'" & sSheetName & "'." &
sSheetNamedRange
+ Next sSheetNamedRange
+ Next sSheetName
+ End With
+ End If
+
+ vNamedRanges = arr.Sort(vNamedRanges, SortOrder := "ASC",
CaseSensitive := True)
+
+Finally:
+ _NamedRangesList = vNamedRanges
+ Exit Function
+End Function ' SFDocuments.SF_Calc._NamedRangesList
+
REM
-----------------------------------------------------------------------------
Public Function _Offset(ByRef pvRange As Variant _
, ByVal plRows
As Long _
@@ -4890,14 +5035,15 @@ CatchAddress:
End Function ' SFDocuments.SF_Calc._Offset
REM
-----------------------------------------------------------------------------
-Private Function _ParseAddress(ByVal psAddress As String) As Object
+Private Function _ParseAddress(ByVal psAddress As String, Optional ByVal
pbError As Boolean) As Object
''' Parse and validate a sheet or range reference
''' Syntax to parse:
''' [Sheet].[Range]
''' Sheet => [$][']sheet[']
or document named range or ~
''' Range => A1:D10, A1, A:D, 10:10 ($
ignored), or sheet named range or ~ or *
''' Returns:
-''' An object of type _Address
+''' An object of type _Address.
+''' When parsing failed, an error is generated,
except if pbError = False => Nothing is returned.
''' Exceptions:
''' CALCADDRESSERROR ' Address
could not be parsed to a valid address
@@ -4913,6 +5059,8 @@ Dim oRangeAddress As Object '
Alias for rangeaddress
Dim vLastCell As Variant ' Result of _LastCell()
method
Dim oSelect As Object ' Current selection
+ If IsMissing(pbError) Then pbError = True
+
' If psAddress has already been parsed, get the result back
If Not IsNull(_LastParsedAddress) Then
' Given argument must contain an explicit reference to a
sheet
@@ -5020,8 +5168,12 @@ Finally:
CatchError:
ScriptForge.SF_Exception.Clear()
CatchAddress:
- ScriptForge.SF_Exception.RaiseFatal(CALCADDRESSERROR,
"Range", psAddress _
+ If pbError Then
+ ScriptForge.SF_Exception.RaiseFatal(CALCADDRESSERROR,
"Range", psAddress _
, "Document", [_Super]._FileIdent())
+ Else
+ Set oAddress = Nothing
+ End If
GoTo Finally
End Function ' SFDocuments.SF_Calc._ParseAddress
@@ -5063,13 +5215,8 @@ Const cstSubArgs = ""
Else
_PropertyGet = oSelect.AbsoluteName
End If
- Case UCase("Height")
- If IsMissing(pvArg) Or IsEmpty(pvArg) Then
- _PropertyGet = 0
- Else
- If Not ScriptForge.SF_Utils._Validate(pvArg,
"Range", V_STRING) Then GoTo Finally
- _PropertyGet = _ParseAddress(pvArg).Height
- End If
+ Case UCase("DefinedNames")
+ _PropertyGet = _NamedRangesList(True)
Case UCase("FirstCell"), UCase("FirstRow"),
UCase("FirstColumn") _
, UCase("LastCell"),
UCase("LastColumn"), UCase("LastRow") _
, UCase("SheetName")
@@ -5092,6 +5239,13 @@ Const cstSubArgs = ""
End Select
End With
End If
+ Case UCase("Height")
+ If IsMissing(pvArg) Or IsEmpty(pvArg) Then
+ _PropertyGet = 0
+ Else
+ If Not ScriptForge.SF_Utils._Validate(pvArg,
"Range", V_STRING) Then GoTo Finally
+ _PropertyGet = _ParseAddress(pvArg).Height
+ End If
Case UCase("Range")
If IsMissing(pvArg) Or IsEmpty(pvArg) Then
Set _PropertyGet = Nothing
