ScriptForge - (SF_Calc) new Printf() method

Returns the input string after substitution of its tokens by
their values in the given range

This method is usually used in combination with SetFormula()

The accepted tokens are:
- %S   The sheet name containing the range,
including single quotes when necessary
- %R1  The row number of the top-left part of the range
- %C1  The column letter of the top-left part of the range
- %R2  The row number of the bottom-right part of the range
- %C2  The column letter of the bottom-right part of the range

The default token character (%) may be modified.

The method is available both for Basic and Python user scripts

Change-Id: If9c3a042abf22ba760d1410b105b04716d092418
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/125044
Tested-by: Jean-Pierre Ledure <jp@ledure.be>
Tested-by: Jenkins
Reviewed-by: Jean-Pierre Ledure <jp@ledure.be>

2 files changed, 135 insertions, 9 deletions

diff --git a/wizards/source/scriptforge/python/scriptforge.py b/wizards/source/scriptforge/python/scriptforge.py
index 31a3f29ee799..8b6af8ecdc99 100644
--- a/wizards/source/scriptforge/python/scriptforge.py
+++ b/wizards/source/scriptforge/python/scriptforge.py
@@ -2010,6 +2010,9 @@ class SFDocuments:
                    width = ScriptForge.cstSymEmpty):
             return self.ExecMethod(self.vbMethod, 'Offset', range, rows, columns, height, width)
 
+        def Printf(self, inputstr, range, tokencharacter = '%'):
+            return self.ExecMethod(self.vbMethod, 'Printf', inputstr, range, tokencharacter)
+
         def PrintOut(self, sheetname = '~', pages = '', copies = 1):
             return self.ExecMethod(self.vbMethod, 'PrintOut', sheetname, pages, copies)
 
diff --git a/wizards/source/sfdocuments/SF_Calc.xba b/wizards/source/sfdocuments/SF_Calc.xba
index 87c111a64c96..56fc83c18cb4 100644
--- a/wizards/source/sfdocuments/SF_Calc.xba
+++ b/wizards/source/sfdocuments/SF_Calc.xba
@@ -337,7 +337,6 @@ Public Function A1Style(Optional ByVal Row1 As Variant _
 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"
@@ -375,13 +374,7 @@ Check:
 
 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
+	vSheetName = _QuoteSheetName(vSheetName)
 	'	Define the new range string
 	sA1Style = "$" & vSheetName & "." _
 					& "$" & _GetColumnName(Column1) & "$" & CLng(Row1) _
@@ -1669,6 +1662,7 @@ Public Function Methods() As Variant
 					, "MoveRange" _
 					, "MoveSheet" _
 					, "Offset" _
+					, "Printf" _
 					, "PrintOut" _
 					, "RemoveSheet" _
 					, "RenameSheet" _
@@ -1868,6 +1862,103 @@ Catch:
 End Function	'	SF_Documents.SF_Calc.Offset
 
 REM -----------------------------------------------------------------------------
+Public Function Printf(Optional ByVal InputStr As Variant _
+								, Optional ByVal Range As Variant _
+								, Optional ByVal TokenCharacter As Variant _
+								) As String
+'''	Returns the input string after substitution of its tokens by
+'''	their values in the given range
+'''	This method is usually used in combination with SetFormula()
+'''	The accepted tokens are:
+'''		- %S	The sheet name containing the range, including single quotes when necessary
+'''		- %R1	The row number of the topleft part of the range
+'''		- %C1	The column letter of the topleft part of the range
+'''		- %R2	The row number of the bottomright part of the range
+'''		- %C2	The column letter of the bottomright part of the range
+'''	Args:
+'''		InputStr: usually a Calc formula or a part of a formula, but may be any string
+'''		Range: the range, as a string from which the values of the tokens are derived
+'''		TokenCharacter: the character identifying tokens. Default = "%".
+'''			Double the TokenCharacter to not consider it as a token.
+'''	Returns:
+'''		The input string after substitution of the contained tokens
+'''	Exceptions:
+'''	Examples:
+'''		Assume we have in A1:E10 a matrix of numbers. To obtain the sum by row in F1:F10 ...
+'''			Dim range As String, formula As String
+'''				range = "$A$1:$E$10")
+'''				formula = "=SUM($%C1%R1:$%C2%R1)"	'	"=SUM($A1:$E1)", note the relative references
+'''				oDoc.SetFormula("$F$1:$F$10", formula)
+'''					'F1 will contain	=Sum($A1:$E1)
+'''					'F2					=Sum($A2:$E2)
+'''					'	...
+
+Dim sPrintf As String				'	Return value
+Dim vSubstitute As Variants			'	Array of strings representing the token values
+Dim oAddress As Object				'	A range as an _Address object
+Dim sSheetName As String			'	The %S token value
+Dim sC1 As String					'	The %C1 token value
+Dim sR1 As String					'	The %R1 token value
+Dim sC2 As String					'	The %C2 token value
+Dim sR2 As String					'	The %R2 token value
+Dim i As Long
+Const cstPseudoToken = "@#@"
+
+Const cstThisSub = "SFDocuments.Calc.Printf"
+Const cstSubArgs = "InputStr, Range, TokenCharacter=""%"""
+
+	If ScriptForge.SF_Utils._ErrorHandling() Then On Local Error GoTo Catch
+	sPrintf = ""
+
+Check:
+	If IsMissing(TokenCharacter) Or IsEmpty(TokenCharacter) Then TokenCharacter = "%"
+	If ScriptForge.SF_Utils._EnterFunction(cstThisSub, cstSubArgs) Then
+		If Not _IsStillAlive() Then GoTo Finally
+		If Not ScriptForge.SF_Utils._Validate(InputStr, "InputStr", V_STRING) Then GoTo Finally
+		If Not ScriptForge.SF_Utils._Validate(Range, "Range", V_STRING) Then GoTo Finally
+		If Not ScriptForge.SF_Utils._Validate(TokenCharacter, "TokenCharacter", V_STRING) Then GoTo Finally
+	End If
+
+Try:
+	'	Define the token values
+	Set oAddress = _ParseAddress(Range)
+	With oAddress.XCellRange
+		sC1 = _GetColumnName(.RangeAddress.StartColumn + 1)
+		sR1 = CStr(.RangeAddress.StartRow + 1)
+		sC2 = _GetColumnName(.RangeAddress.EndColumn + 1)
+		sR2 = CStr(.RangeAddress.EndRow + 1)
+		sSheetName = _QuoteSheetName(oAddress.XSpreadsheet.Name)
+	End With
+
+	'	Substitute tokens by their values
+	sPrintf = ScriptForge.SF_String.ReplaceStr(InputStr _
+					, Array(TokenCharacter & TokenCharacter _
+							, TokenCharacter & "R1" _
+							, TokenCharacter & "C1" _
+							, TokenCharacter & "R2" _
+							, TokenCharacter & "C2" _
+							, TokenCharacter & "S" _
+							, cstPseudoToken _
+							) _
+					, Array(cstPseudoToken _
+							, sR1 _
+							, sC1 _
+							, sR2 _
+							, sC2 _
+							, sSheetName _
+							, TokenCharacter _
+							) _
+					)
+
+Finally:
+	Printf = sPrintf
+	ScriptForge.SF_Utils._ExitFunction(cstThisSub)
+	Exit Function
+Catch:
+	GoTo Finally
+End Function	'	SF_Documents.SF_Calc.Printf
+
+REM -----------------------------------------------------------------------------
 Public Function PrintOut(Optional ByVal SheetName As Variant _
 							, Optional ByVal Pages As Variant _
 							, Optional ByVal Copies As Variant _
@@ -3542,7 +3633,6 @@ Const cstSubArgs = ""
 					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")
@@ -3620,6 +3710,39 @@ Finally:
 End Function	'	SFDocuments.SF_Calc._PropertyGet
 
 REM -----------------------------------------------------------------------------
+Private Function _QuoteSheetName(ByVal psSheetName As String) As String
+'''	Return the given sheet name surrounded with single quotes
+'''	when required to insert the sheet name into a Calc formula
+'''	Enclosed single quotes are doubled
+'''	Args:
+'''		psSheetName: the name to quote
+'''	Returns:
+'''		The quoted or unchanged sheet name
+
+Dim sSheetName As String			'	Return value
+Dim i As Long
+
+Try:
+	'	Surround the sheet name with single quotes when required by the presence of single quotes
+	If InStr(psSheetName, "'") > 0 Then
+		sSheetName = "'" & Replace(psSheetName, "'", "''") & "'"
+	Else
+		'	Surround the sheet name with single quotes when required by the presence of at least one of the special characters
+		sSheetName = psSheetName
+		For i = 1 To Len(cstSPECIALCHARS)
+			If InStr(sSheetName, Mid(cstSPECIALCHARS, i, 1)) > 0 Then
+				sSheetName = "'" & sSheetName & "'"
+				Exit For
+			End If
+		Next i
+	End If
+
+Finally:
+	_QuoteSheetName = sSheetName
+	Exit Function
+End Function	'	SFDocuments.SF_Calc._QuoteSheetName
+
+REM -----------------------------------------------------------------------------
 Private Function _Repr() As String
 '''	Convert the SF_Calc instance to a readable string, typically for debugging purposes (DebugPrint ...)
 '''	Args: