wizards/source/scriptforge/SF_PythonHelper.xba | 1
wizards/source/scriptforge/python/scriptforge.py | 27 ++-
wizards/source/sfdocuments/SF_Calc.xba | 168 ++++++++++-------------
3 files changed, 95 insertions(+), 101 deletions(-)
New commits:
commit 754247fe3fb1c8baa8f37f7ea1eb74aa3f0bcd57
Author: Jean-Pierre Ledure <j...@ledure.be>
AuthorDate: Tue Nov 9 11:00:04 2021 +0100
Commit: Jean-Pierre Ledure <j...@ledure.be>
CommitDate: Tue Nov 9 13:09:24 2021 +0100
ScriptForge - (SF_Calc) First/Last * Row/Column/Cell accept sheets and
ranges
The methods
FirstCell
FirstColumn
FirstRow
LastCell
LastColumn
LastRow
SheetName
accept uniformly either a sheet name or a range name.
They return a single cell as an absolute address (First/LastCell),
a String (SheetName) or a Long.
The range name is to be understood as the extended notation for ranges.
FirstCell/Column/Row accept sheet names. However they will then always
return
either the "A1" cell or the value 1.
The LastCell/Column/Row return the real last xxx for ranges, and
the last USED xxx when the argument is a sheet.
The A1Style() method surrounds from now on sheet names
with single quotes only when necessary.
The GetRangeAddress() method has been dropped as redundant with the actual
methods. (Was new in 7.3 => no effect on upward compatibiity)
Change-Id: Iff1ce437ee27dfb8481361f648b3f7a33ae2c4b1
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/124904
Tested-by: Jean-Pierre Ledure <j...@ledure.be>
Tested-by: Jenkins
Reviewed-by: Jean-Pierre Ledure <j...@ledure.be>
diff --git a/wizards/source/scriptforge/SF_PythonHelper.xba
b/wizards/source/scriptforge/SF_PythonHelper.xba
index f32e077d83d0..5b919ba5abe1 100644
--- a/wizards/source/scriptforge/SF_PythonHelper.xba
+++ b/wizards/source/scriptforge/SF_PythonHelper.xba
@@ -777,7 +777,6 @@ Try:
Select Case Script
Case "Charts"
: vReturn = vBasicObject.Charts(vArgs(0), vArgs(1))
Case "Forms"
: vReturn = vBasicObject.Forms(vArgs(0), vArgs(1))
- Case
"GetRangeAddress" : vReturn =
vBasicObject.GetRangeAddress(vArgs(0), vArgs(1))
Case
"GetFormula" : vReturn =
vBasicObject.GetFormula(vArgs(0))
Case
"GetValue" : vReturn =
vBasicObject.GetValue(vArgs(0))
Case
"SetArray" : vReturn =
vBasicObject.SetArray(vArgs(0), vArgs(1))
diff --git a/wizards/source/scriptforge/python/scriptforge.py
b/wizards/source/scriptforge/python/scriptforge.py
index ebd33ab9a7f2..31a3f29ee799 100644
--- a/wizards/source/scriptforge/python/scriptforge.py
+++ b/wizards/source/scriptforge/python/scriptforge.py
@@ -1876,17 +1876,26 @@ class SFDocuments:
return windowname,
# Next functions are implemented in Basic as read-only properties with
1 argument
+ def FirstCell(self, rangename):
+ return self.GetProperty('FirstCell', rangename)
+
+ def FirstColumn(self, rangename):
+ return self.GetProperty('FirstColumn', rangename)
+
+ def FirstRow(self, rangename):
+ return self.GetProperty('FirstRow', rangename)
+
def Height(self, rangename):
return self.GetProperty('Height', rangename)
- def LastCell(self, sheetname):
- return self.GetProperty('LastCell', sheetname)
+ def LastCell(self, rangename):
+ return self.GetProperty('LastCell', rangename)
- def LastColumn(self, sheetname):
- return self.GetProperty('LastColumn', sheetname)
+ def LastColumn(self, rangename):
+ return self.GetProperty('LastColumn', rangename)
- def LastRow(self, sheetname):
- return self.GetProperty('LastRow', sheetname)
+ def LastRow(self, rangename):
+ return self.GetProperty('LastRow', rangename)
def Range(self, rangename):
return self.GetProperty('Range', rangename)
@@ -1897,6 +1906,9 @@ class SFDocuments:
def Sheet(self, sheetname):
return self.GetProperty('Sheet', sheetname)
+ def SheetName(self, rangename):
+ return self.GetProperty('SheetName', rangename)
+
def Width(self, rangename):
return self.GetProperty('Width', rangename)
@@ -1974,9 +1986,6 @@ class SFDocuments:
def GetFormula(self, range):
return self.ExecMethod(self.vbMethod + self.flgArrayRet,
'GetFormula', range)
- def GetRangeAddress(self, range, addressitem = ''):
- return self.ExecMethod(self.vbMethod + self.flgArrayArg,
'GetRangeAddress', range, addressitem)
-
def GetValue(self, range):
return self.ExecMethod(self.vbMethod + self.flgArrayRet,
'GetValue', range)
diff --git a/wizards/source/sfdocuments/SF_Calc.xba
b/wizards/source/sfdocuments/SF_Calc.xba
index 96dd08d5562c..87c111a64c96 100644
--- a/wizards/source/sfdocuments/SF_Calc.xba
+++ b/wizards/source/sfdocuments/SF_Calc.xba
@@ -127,6 +127,10 @@ Private Const SERVICEREFERENCE =
"SFDocuments.CalcReference"
Private Const ISCALCFORM = 2
' Form is stored in a Calc document
+Private Const cstSPECIALCHARS = "
`~!@#$%^&()-_=+{}|;:,<.>"""
+ ' Presence of a special character forces
surrounding the sheet name with single quotes in absolute addresses
+
+
REM ======================================================
CONSTRUCTOR/DESTRUCTOR
REM
-----------------------------------------------------------------------------
@@ -205,6 +209,27 @@ Catch:
GoTo Finally
End Property ' SFDocuments.SF_Calc.CurrentSelection (let)
+REM
-----------------------------------------------------------------------------
+Property Get FirstCell(Optional ByVal RangeName As Variant) As String
+''' Returns the First used cell in a given range or sheet
+''' When the argument is a sheet it will always return the
"sheet.$A$1" cell
+ FirstCell = _PropertyGet("FirstCell", RangeName)
+End Property ' SFDocuments.SF_Calc.FirstCell
+
+REM
-----------------------------------------------------------------------------
+Property Get FirstColumn(Optional ByVal RangeName As Variant) As Long
+''' Returns the leftmost column in a given sheet or range
+''' When the argument is a sheet it will always return 1
+ FirstColumn = _PropertyGet("FirstColumn", RangeName)
+End Property ' SFDocuments.SF_Calc.FirstColumn
+
+REM
-----------------------------------------------------------------------------
+Property Get FirstRow(Optional ByVal RangeName As Variant) As Long
+''' Returns the First used column in a given range
+''' When the argument is a sheet it will always return 1
+ FirstRow = _PropertyGet("FirstRow", RangeName)
+End Property ' SFDocuments.SF_Calc.FirstRow
+
REM
-----------------------------------------------------------------------------
Property Get Height(Optional ByVal RangeName As Variant) As Long
''' Returns the height in # of rows of the given range
@@ -212,21 +237,21 @@ Property Get Height(Optional ByVal RangeName As Variant)
As Long
End Property ' SFDocuments.SF_Calc.Height
REM
-----------------------------------------------------------------------------
-Property Get LastCell(Optional ByVal SheetName As Variant) As String
-''' Returns the last used cell in a given sheet
- LastCell = _PropertyGet("LastCell", SheetName)
+Property Get LastCell(Optional ByVal RangeName As Variant) As String
+''' Returns the last used cell in a given sheet or range
+ LastCell = _PropertyGet("LastCell", RangeName)
End Property ' SFDocuments.SF_Calc.LastCell
REM
-----------------------------------------------------------------------------
-Property Get LastColumn(Optional ByVal SheetName As Variant) As Long
+Property Get LastColumn(Optional ByVal RangeName As Variant) As Long
''' Returns the last used column in a given sheet
- LastColumn = _PropertyGet("LastColumn", SheetName)
+ LastColumn = _PropertyGet("LastColumn", RangeName)
End Property ' SFDocuments.SF_Calc.LastColumn
REM
-----------------------------------------------------------------------------
-Property Get LastRow(Optional ByVal SheetName As Variant) As Long
+Property Get LastRow(Optional ByVal RangeName As Variant) As Long
''' Returns the last used column in a given sheet
- LastRow = _PropertyGet("LastRow", SheetName)
+ LastRow = _PropertyGet("LastRow", RangeName)
End Property ' SFDocuments.SF_Calc.LastRow
REM
-----------------------------------------------------------------------------
@@ -248,6 +273,12 @@ Property Get Sheet(Optional ByVal SheetName As Variant) As
Variant
Sheet = _PropertyGet("Sheet", SheetName)
End Property ' SFDocuments.SF_Calc.Sheet
+REM
-----------------------------------------------------------------------------
+Property Get SheetName(Optional ByVal RangeName As Variant) As String
+''' Returns the sheet name part of a range
+ SheetName = _PropertyGet("SheetName", RangeName)
+End Property ' SFDocuments.SF_Calc.SheetName
+
REM
-----------------------------------------------------------------------------
Property Get Sheets() As Variant
''' Returns an array listing the existing sheet names
@@ -301,11 +332,14 @@ Public Function A1Style(Optional ByVal Row1 As Variant _
''' A range as a string
''' Exceptions:
''' Examples:
-''' range = oDoc.A1Style(5, 2, 10, 4,
"SheetX") ' "'$SheetX'.E2:J4"
+''' range = oDoc.A1Style(5, 2, 10, 4,
"SheetX") ' "'$SheetX'.$E$2:$J$4"
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
+Dim lInStr As Long ' Position of
special character in sheet name
+Dim i As Long
+
Const cstThisSub = "SFDocuments.Calc.A1Style"
Const cstSubArgs = "Row1, Column1, [Row2], [Column2],
[SheetName]="""""
@@ -340,8 +374,16 @@ Check:
End If
Try:
+ ' Surround the sheet name with single quotes when required by the
presence of special characters
+ If InStr(vSheetName, "'") > 0 Then vSheetName =
"'" & Replace(vSheetName, "'",
"''") & "'"
+ For i = 1 To Len(cstSPECIALCHARS)
+ If InStr(vSheetName, Mid(cstSPECIALCHARS, i, 1)) > 0 Then
+ vSheetName = "'" & vSheetName &
"'"
+ Exit For
+ End If
+ Next i
' Define the new range string
- sA1Style = "'$" & vSheetName &
"'." _
+ sA1Style = "$" & vSheetName & "." _
& "$" &
_GetColumnName(Column1) & "$" & CLng(Row1) _
& Iif(Row2 > 0 And Column2 >
0, ":$" & _GetColumnName(Column2) & "$" &
CLng(Row2), "")
@@ -1317,74 +1359,6 @@ Catch:
GoTo Finally
End Function ' SFDocuments.SF_Calc.GetProperty
-REM
-----------------------------------------------------------------------------
-Public Function GetRangeAddress(Optional ByVal Range As Variant _
- , Optional ByVal
AddressItem As Variant _
- ) As Variant
-''' Extracts from a range expressed in A1-style one or more
of its coordinates
-''' Args:
-''' Range: the range as a string from which to
extract the coordinates
-''' AddressItem: either "firstrow",
"firstcol" (topleft cell), "lastrow", "lastcol"
(bottom right cell), "sheet" or omitted
-''' Returns:
-''' Either:
-''' - one of the cell coordinates as a Long
(coordinates start with 1)
-''' when Range is a single cell,
row2 and row1 return the same value
-''' - the sheet name as a string
-''' - all of them as a zero-based array in
the order firstrow, firstcol, lastrow, lastcol, sheet
-''' Exceptions:
-''' Examples:
-''' row2 =
oDoc.GetRangeAddress("'$SheetX'.E2:J4", "row2")
' 4
-''' arr =
oDoc.GetRangeAddress("'$SheetX'.E2:J4")
' (2, 5, 4, 10, "SheetX")
-
-Dim vRangeAddress As Variant ' Return value
-Dim oAddress As Object ' Alias of input range as
_Address
-Dim oCellRange As Object '
com.sun.star.table.XCellRange
-Const cstThisSub = "SFDocuments.Calc.GetRangeAddress"
-Const cstSubArgs = "Range,
[AddressItem=""firstrow""|""firstcol""|""lastrow""|""lastcol""|""sheet""]"
-
- If ScriptForge.SF_Utils._ErrorHandling() Then On Local Error GoTo Catch
- vRangeAddress = 0
-
-Check:
- If IsMissing(AddressItem) Or IsEmpty(AddressItem) Then AddressItem =
""
-
- If ScriptForge.SF_Utils._EnterFunction(cstThisSub, cstSubArgs) Then
- If Not _IsStillAlive() Then GoTo Finally
- If Not ScriptForge.SF_Utils._Validate(Range, "Range",
V_STRING) Then GoTo Finally
- If Not ScriptForge.SF_Utils._Validate(AddressItem,
"AddressItem", V_STRING _
- , Array("firstrow",
"firstcol", "firstcolumn", "lastrow",
"lastcol", "lastcolumn", "sheet",
"sheetname", "")) Then GoTo Finally
- End If
-
-Try:
- Set oAddress = _ParseAddress(Range)
- Set oCellRange = oAddress.XCellRange
-
- With oCellRange
- Select Case UCase(AddressItem)
- Case "FIRSTROW"
: vRangeAddress = CLng(.RangeAddress.StartRow + 1)
- Case "FIRSTCOL", "FIRSTCOLUMN"
: vRangeAddress = CLng(.RangeAddress.StartColumn + 1)
- Case "LASTROW"
: vRangeAddress = CLng(.RangeAddress.EndRow + 1)
- Case "LASTCOL", "LASTCOLUMN"
: vRangeAddress = CLng(.RangeAddress.EndColumn + 1)
- Case "SHEET", "SHEETNAME"
: vRangeAddress = .Spreadsheet.Name
- Case ""
- vRangeAddress = Array( _
-
CLng(.RangeAddress.StartRow + 1) _
- ,
CLng(.RangeAddress.StartColumn + 1) _
- ,
CLng(.RangeAddress.EndRow + 1) _
- ,
CLng(.RangeAddress.EndColumn + 1) _
- ,
.Spreadsheet.Name _
- )
- End Select
- End With
-
-Finally:
- GetRangeAddress = vRangeAddress
- ScriptForge.SF_Utils._ExitFunction(cstThisSub)
- Exit Function
-Catch:
- GoTo Finally
-End Function ' SF_Documents.SF_Calc.GetRangeAddress
-
REM
-----------------------------------------------------------------------------
Public Function GetValue(Optional ByVal Range As Variant) As Variant
''' Get the value(s) stored in the given range of cells
@@ -1688,7 +1662,6 @@ Public Function Methods() As Variant
, "ExportAsPDF" _
, "GetColumnName" _
, "GetFormula" _
- , "GetRangeAddress" _
, "GetValue" _
, "ImportFromCSVFile" _
, "ImportFromDatabase" _
@@ -1961,6 +1934,9 @@ Public Function Properties() As Variant
, "Description" _
, "DocumentProperties" _
, "DocumentType" _
+ , "FirstCell" _
+ , "FirstColumn" _
+ , "FirstRow" _
, "Height" _
, "IsBase" _
, "IsCalc" _
@@ -1976,6 +1952,7 @@ Public Function Properties() As Variant
, "Readonly" _
, "Region" _
, "Sheet" _
+ , "SheetName" _
, "Sheets" _
, "Subject" _
, "Title" _
@@ -2193,7 +2170,7 @@ Public Function SetFormula(Optional ByVal TargetRange As
Variant _
''' oDoc.SetFormula("A1:F1",
Array("=A2", "=B2", "=C2+10")) '
Horizontal vector, partially empty
''' oDoc.SetFormula("A1:D2",
"=E1") ' D2 contains the formula "=H2"
-Dim sSet As String ' Return value
+Dim sSet As String ' Return
value.XSpreadsheet.Name)
Dim oAddress As Object ' Alias of TargetRange
Dim vDataArray As Variant ' DataArray compatible
with .DataArray UNO property
Const cstThisSub = "SFDocuments.Calc.SetFormula"
@@ -2301,7 +2278,7 @@ Public Function SetValue(Optional ByVal TargetRange As
Variant _
''' Vectors are always expanded vertically, except if the
range has a height of exactly 1 row
''' Args:
''' TargetRange : the range as a string that should
receive a new value
-''' Value: a scalar, a vector or an array with the
new values for each cell of the range.
+''' Value: a scalar, a vector or an array with the
new values for each cell o.XSpreadsheet.Name)f the range.
''' The new values should be strings,
numeric values or dates. Other types empty the corresponding cell
''' Returns:
''' A string representing the updated range
@@ -3553,19 +3530,28 @@ Const cstSubArgs = ""
If Not ScriptForge.SF_Utils._Validate(pvArg,
"Range", V_STRING) Then GoTo Finally
_PropertyGet = _ParseAddress(pvArg).Height
End If
- Case UCase("LastCell"),
UCase("LastColumn"), UCase("LastRow")
+ Case UCase("FirstCell"), UCase("FirstRow"),
UCase("FirstColumn") _
+ , UCase("LastCell"),
UCase("LastColumn"), UCase("LastRow") _
+ , UCase("SheetName")
If IsMissing(pvArg) Or IsEmpty(pvArg) Then '
Avoid errors when instance is watched in Basic IDE
- _PropertyGet = -1
+ If InStr(UCase(psProperty), "CELL")
> 0 Then _PropertyGet = "" Else _PropertyGet = -1
Else
- If Not _ValidateSheet(pvArg,
"SheetName", , True) Then GoTo Finally
- vLastCell =
_LastCell(_Component.getSheets.getByName(pvArg))
- If psProperty = "LastRow" Then
- _PropertyGet = vLastCell(1)
- ElseIf psProperty = "LastColumn" Then
- _PropertyGet = vLastCell(0)
- Else
- _PropertyGet =
GetColumnName(vLastCell(0)) & CStr(vLastCell(1))
- End If
+ If Not ScriptForge.SF_Utils._Validate(pvArg,
"Range", V_STRING) Then GoTo Finally
+ Set oAddress = _ParseAddress(pvArg)
+ With oAddress.XCellRange
+ Select Case UCase(psProperty)
+ Case
UCase("FirstCell")
+ _PropertyGet =
A1Style(.RangeAddress.StartRow + 1, .RangeAddress.StartColumn + 1, , ,
oAddress.XSpreadsheet.Name)
+ Case
UCase("FirstRow") : _PropertyGet =
CLng(.RangeAddress.StartRow + 1)
+ Case
UCase("FirstColumn") : _PropertyGet =
CLng(.RangeAddress.StartColumn + 1)
+ Case
UCase("FirstRow") : _PropertyGet =
CLng(.RangeAddress.StartRow + 1)
+ Case UCase("LastCell")
+ _PropertyGet =
A1Style(.RangeAddress.EndRow + 1, .RangeAddress.EndColumn + 1, , ,
oAddress.XSpreadsheet.Name)
+ Case
UCase("LastColumn") : _PropertyGet =
CLng(.RangeAddress.EndColumn + 1)
+ Case UCase("LastRow")
: _PropertyGet = CLng(.RangeAddress.EndRow + 1)
+ Case
UCase("SheetName") : _PropertyGet =
oAddress.XSpreadsheet.Name
+ End Select
+ End With
End If
Case UCase("Range")
If IsMissing(pvArg) Or IsEmpty(pvArg) Then
