wizards/source/scriptforge/python/scriptforge.py | 3
wizards/source/sfdocuments/SF_Calc.xba | 81 ++++++++++++++++++++++-
2 files changed, 83 insertions(+), 1 deletion(-)
New commits:
commit 6a1feb264479f0b223305882636eed1b58c80966
Author: Jean-Pierre Ledure <j...@ledure.be>
AuthorDate: Sat Oct 23 18:31:21 2021 +0200
Commit: Jean-Pierre Ledure <j...@ledure.be>
CommitDate: Mon Oct 25 11:06:54 2021 +0200
ScriptForge - (SF_Calc) new A1Style() method
Returns a range expressed in A1-style as defined by its coordinates
If only one pair of coordinates is given, the range will embrace only a
single cell
Arguments:
Row1 : the row number of the first coordinate
Column1 : the column number of the first coordinates
Row2 : optional, the row number of the second coordinate
Column2 : optional, the column number of the second coordinates
SheetName: Default = the current sheet. If present, the sheet must exist
Is complementary to the Offset() method to compute ranges easily
Available both from Basic and Python user scripts
Change-Id: Ib9323441bbd579beb867329c8b0930653462d00e
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/124093
Tested-by: Jenkins
Tested-by: Jean-Pierre Ledure <j...@ledure.be>
Reviewed-by: Jean-Pierre Ledure <j...@ledure.be>
diff --git a/wizards/source/scriptforge/python/scriptforge.py
b/wizards/source/scriptforge/python/scriptforge.py
index 7dd9c62a6da7..e15c63e1e4a8 100644
--- a/wizards/source/scriptforge/python/scriptforge.py
+++ b/wizards/source/scriptforge/python/scriptforge.py
@@ -1904,6 +1904,9 @@ class SFDocuments:
return self.ExecMethod(self.vbGet + self.flgUno, 'XSpreadsheet',
sheetname)
# Usual methods
+ def A1Style(self, row1, column1, row2 = 0, column2 = 0, sheetname =
'~'):
+ return self.ExecMethod(self.vbMethod, 'A1Style', row1, column1,
row2, column2, sheetname)
+
def Activate(self, sheetname = ''):
return self.ExecMethod(self.vbMethod, 'Activate', sheetname)
diff --git a/wizards/source/sfdocuments/SF_Calc.xba
b/wizards/source/sfdocuments/SF_Calc.xba
index 390e4b274165..2e651651dc1b 100644
--- a/wizards/source/sfdocuments/SF_Calc.xba
+++ b/wizards/source/sfdocuments/SF_Calc.xba
@@ -264,6 +264,77 @@ End Property ' SFDocuments.SF_Calc.XSpreadsheet
REM =====================================================================
METHODS
+REM
-----------------------------------------------------------------------------
+Public Function A1Style(Optional ByVal Row1 As Variant _
+ , Optional
ByVal Column1 As Variant _
+ , Optional
ByVal Row2 As Variant _
+ , Optional
ByVal Column2 As Variant _
+ , Optional
ByVal SheetName As Variant _
+ ) As String
+''' Returns a range expressed in A1-style as defined by its
coordinates
+''' If only one pair of coordinates is given, the range
will embrace only a single cell
+''' Args:
+''' Row1 : the row number of the first coordinate
+''' Column1 : the column number of the first
coordinates
+''' Row2 : the row number of the second coordinate
+''' Column2 : the column number of the second
coordinates
+''' SheetName: Default = the current sheet. If
present, the sheet must exist.
+''' Returns:
+''' A range as a string
+''' Exceptions:
+''' Examples:
+''' range = oDoc.A1Style(5, 2, 10, 4,
"SheetX") ' "'$SheetX'.E2:J4"
+
+Dim sA1Style As String ' Return value
+Dim vSheetName As Variant ' Alias of SheetName -
necessary see [Bug 145279]
+Dim lTemp As Long ' To switch 2
values
+Const cstThisSub = "SFDocuments.Calc.A1Style"
+Const cstSubArgs = "Row1, Column1, [Row2], [Column2],
[SheetName]="""""
+
+ If ScriptForge.SF_Utils._ErrorHandling() Then On Local Error GoTo Catch
+ sA1Style = ""
+
+Check:
+ If IsMissing(Row2) Or IsEmpty(Row2) Then Row2 = 0
+ If IsMissing(Column2) Or IsEmpty(Column2) Then Column2 = 0
+ If IsMissing(SheetName) Or IsEmpty(SheetName) Then SheetName =
"~"
+ vSheetName = SheetName
+
+ If ScriptForge.SF_Utils._EnterFunction(cstThisSub, cstSubArgs) Then
+ If Not _IsStillAlive() Then GoTo Finally
+ If Not ScriptForge.SF_Utils._Validate(Row1, "Row1",
ScriptForge.V_NUMERIC) Then GoTo Finally
+ If Not ScriptForge.SF_Utils._Validate(Column1,
"Column1", ScriptForge.V_NUMERIC) Then GoTo Finally
+ If Not ScriptForge.SF_Utils._Validate(Row2, "Row2",
ScriptForge.V_NUMERIC) Then GoTo Finally
+ If Not ScriptForge.SF_Utils._Validate(Column2,
"Column2", ScriptForge.V_NUMERIC) Then GoTo Finally
+ If Not _ValidateSheet(vSheetName, "SheetName", ,
True, True, , , True) Then GoTo Finally
+ End If
+
+ If Row1 > MAXROWS Then Row1 = MAXROWS
+ If Row2 > MAXROWS Then Row2 = MAXROWS
+ If Column1 > MAXCOLS Then Column1 = MAXCOLS
+ If Column2 > MAXCOLS Then Column2 = MAXCOLS
+
+ If Row2 > 0 And Row2 < Row1 Then
+ lTemp = Row2 : Row2 = Row1 : Row1 = lTemp
+ End If
+ If Column2 > 0 And Column2 < Column1 Then
+ lTemp = Column2 : Column2 = Column1 : Column1
= lTemp
+ End If
+
+Try:
+ ' Define the new range string
+ sA1Style = "'$" & vSheetName &
"'." _
+ & "$" &
_GetColumnName(Column1) & "$" & CLng(Row1) _
+ & Iif(Row2 > 0 And Column2 >
0, ":$" & _GetColumnName(Column2) & "$" &
CLng(Row2), "")
+
+Finally:
+ A1Style = sA1Style
+ ScriptForge.SF_Utils._ExitFunction(cstThisSub)
+ Exit Function
+Catch:
+ GoTo Finally
+End Function ' SF_Documents.SF_Calc.A1Style
+
REM
-----------------------------------------------------------------------------
Public Function Activate(Optional ByVal SheetName As Variant) As Boolean
''' Make the current document or the given sheet active
@@ -667,7 +738,7 @@ Try:
If Not FSO.FileExists(FileName) Then GoTo CatchNotExists
sFileName = FSO._ConvertToUrl(FileName)
- ' Insert a blank new sheet and import sheet from file va link
setting and deletion
+ ' Insert a blank new sheet and import sheet from file via link
setting and deletion
If Not InsertSheet(Newname, BeforeSheet) Then GoTo Finally
Set oSheet = _Component.getSheets.getByName(NewName)
With oSheet
@@ -3166,6 +3237,7 @@ Private Function _ValidateSheet(Optional ByRef
pvSheetName As Variant _
,
Optional ByVal pvOptional as Variant _
,
Optional ByVal pvNumeric As Variant _
,
Optional ByVal pvReference As Variant _
+ ,
Optional ByVal pvResetSheet As Variant _
) As
Boolean
''' Sheet designation validation function similar to the
SF_Utils._ValidateXXX functions
''' Args:
@@ -3177,12 +3249,14 @@ Private Function _ValidateSheet(Optional ByRef
pvSheetName As Variant _
''' pvNumeric: if True, the sheet position is
accepted (default = False)
''' pvReference: if True, a sheet reference is
acceptable (default = False)
''' pvNumeric and pvReference must not both
be = True
+''' pvResetSheet: if True, return in pvSheetName
the correct (case-sensitive) sheet name (default = False)
''' Returns
''' True if valid. SheetName is reset to current
value if = "~"
''' Exceptions
''' DUPLICATESHEETERROR A sheet with
the given name exists already
Dim vSheets As Variant ' List of sheets
+Dim lSheet As Long ' Index in list
of sheets
Dim vTypes As Variant ' Array of accepted
variable types
Dim bValid As Boolean ' Return value
@@ -3192,6 +3266,7 @@ Check:
If IsMissing(pvOptional) Or IsEmpty(pvOptional) Then pvOptional = False
If IsMissing(pvNumeric) Or IsEmpty(pvNumeric) Then pvNumeric = False
If IsMissing(pvReference) Or IsEmpty(pvReference) Then pvReference =
False
+ If IsMissing(pvResetSheet) Or IsEmpty(pvResetSheet) Then pvResetSheet =
False
' Define the acceptable variable types
If pvNumeric Then
@@ -3215,6 +3290,10 @@ Try:
If ScriptForge.SF_Array.Contains(vSheets,
pvSheetName) Then GoTo CatchDuplicate
Else
If Not
ScriptForge.SF_Utils._Validate(pvSheetName, psArgName, V_STRING, vSheets) Then
GoTo Finally
+ If pvResetSheet Then
+ lSheet =
ScriptForge.SF_Array.IndexOf(vSheets, pvSheetName, CaseSensitive := False)
+ pvSheetName = vSheets(lSheet)
+ End If
End If
End If
End If
