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

2 files changed, 83 insertions, 1 deletions

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
@@ -265,6 +265,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
 '''	Args:
@@ -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