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 <jp@ledure.be>
Tested-by: Jenkins
Reviewed-by: Jean-Pierre Ledure <jp@ledure.be>

3 files changed, 95 insertions, 101 deletions

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 -----------------------------------------------------------------------------
@@ -206,27 +210,48 @@ Catch:
 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
 	Height = _PropertyGet("Height", RangeName)
 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 -----------------------------------------------------------------------------
@@ -249,6 +274,12 @@ Property Get Sheet(Optional ByVal SheetName As Variant) As Variant
 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
 	Sheets = _PropertyGet("Sheets")
@@ -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), "")
 
@@ -1318,74 +1360,6 @@ Catch:
 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
 '''	Args:
@@ -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