ScriptForge - (SF_Calc) accurate sheet name check

The parsing of sheet names and range addresses
has been reviewed to include the exact rules for
sheet naming:
must not be empty
must not contain []*?:/\
must not use ' (apostrophe) as 1st and last character
Additionally (ScriptForge only) it must not contain "~"
which, by convention, indicates the active sheet.

The code accepts exotic sheet names like:
"$"
"$Sheet99"
"$Sheet'99"
"$.Sheet'99"
"!'@#$%^&()-_=+{}|;,<.>"""
and exotic addresses like:
"'$'.A1"
"'$Sheet99'.A1"
"'$Sheet''99'.A1"
"'$.Sheet''99'.A1"
"'!''@#$%^&()-_=+{}|;,<.>""'.A1"

Target is to match completely the Calc constraints for
writing formulas and sheet names..

The same rules are applicable evenly for user scripts
written either in Basic or in Python.

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

3 files changed, 53 insertions, 25 deletions

diff --git a/wizards/source/scriptforge/SF_Exception.xba b/wizards/source/scriptforge/SF_Exception.xba
index 11e97b02b753..572b0fb950a6 100644
--- a/wizards/source/scriptforge/SF_Exception.xba
+++ b/wizards/source/scriptforge/SF_Exception.xba
@@ -962,7 +962,7 @@ Try:
 				pvArgs(0) = _RightCase(pvArgs(0))	:	pvArgs(2) = _RightCase(pvArgs(2))
 				sMessage = sLocation _
 					& "\n" & "\n" & "\n" & .GetText("VALIDATEERROR", pvArgs(0)) _
-					& "\n" & "\n" & .GetText("CALCADDRESS" & Iif(pvArgs(0) = "Sheet", "1", "2"), pvArgs(0), pvArgs(1), pvArgs(2), pvArgs(3))
+					& "\n" & "\n" & .GetText("CALCADDRESS" & Iif(Left(pvArgs(0), 5) = "Sheet", "1", "2"), pvArgs(0), pvArgs(1), pvArgs(2), pvArgs(3))
 			Case DUPLICATESHEETERROR	'	SF_Calc.InsertSheet(arg, SheetName, Document)
 				pvArgs(0) = _RightCase(pvArgs(0))
 				sMessage = sLocation _
diff --git a/wizards/source/sfdocuments/SF_Calc.xba b/wizards/source/sfdocuments/SF_Calc.xba
index 0b7b88ae8f76..57d3d0fb63cf 100644
--- a/wizards/source/sfdocuments/SF_Calc.xba
+++ b/wizards/source/sfdocuments/SF_Calc.xba
@@ -127,7 +127,7 @@ Private Const SERVICEREFERENCE	= "SFDocuments.CalcReference"
 
 Private Const ISCALCFORM		= 2						'	Form is stored in a Calc document
 
-Private Const cstSPECIALCHARS	= " `~!@#$%^&()-_=+{}|;:,<.>"""
+Private Const cstSPECIALCHARS	= " `~!@#$%^&()-_=+{}|;,<.>"""
 			'	Presence of a special character forces surrounding the sheet name with single quotes in absolute addresses
 
 
@@ -4080,6 +4080,7 @@ Private Function _ParseAddress(ByVal psAddress As String) As Object
 
 Dim oAddress As Object				'	Return value
 Dim sAddress As String				'	Alias of psAddress
+Dim vRangeName As Variant			'	Array Sheet/Range
 Dim lStart As Long					'	Position of found regex
 Dim sSheet As String				'	Sheet component
 Dim sRange As String				'	Range component
@@ -4112,23 +4113,17 @@ Dim oSelect As Object				'	Current selection
 		.RawAddress = psAddress
 		Set .XSpreadSheet = Nothing	:	Set .XCellRange = Nothing
 
-		'	Remove leading '$'
-		If Left(psAddress, 1) = "$" Then sAddress = Mid(psAddress, 2) Else sAddress = psAddress
-		'	Split in sheet and range components - Check presence of surrounding single quotes or dot
-		If Left(sAddress, 1) = "'" Then
-			lStart = 1
-			sSheet = ScriptForge.SF_String.FindRegex(sAddress, "^'[^\[\]*?:\/\\]+'")
-			If lStart = 0 Then GoTo CatchAddress	'	Invalid sheet name
-			If Len(sAddress) > Len(sSheet) + 1 Then
-				If Mid(sAddress, Len(sSheet) + 1, 1) = "." then sRange = Mid(sAddress, Len(sSheet) + 2)
-			End If
-			sSheet = Replace(Replace(sSheet, "$", ""), "'", "")
-		ElseIf InStr(sAddress, ".") > 0 Then
-			sSheet = Replace(Split(sAddress, ".")(0), "$", "")
-			sRange = Replace(Split(sAddress, ".")(1), "$", "")
-		Else
-			sSheet = sAddress
-		End If
+		'	Remove leading "$' when followed with an apostrophe
+		If Left(psAddress, 2) = "$'" Then sAddress = Mid(psAddress, 2) Else sAddress = psAddress
+		'	Split in sheet and range components on dot not enclosed in single quotes
+		vRangeName = ScriptForge.SF_String.SplitNotQuoted(sAddress, Delimiter := ".", QuoteChar := "'")
+		sSheet = ScriptForge.SF_String.Unquote(Replace(vRangeName(0), "''", "\'"), QuoteChar := "'")
+		'	Keep a leading "$" in the sheet name only if name enclosed in single quotes
+		'	Notes:
+		'		sheet names may contain "$" (even "$" is a valid sheet name), named ranges must not
+		'		sheet names may contain apostrophes (except in 1st and last positions), range names must not
+		If Left(vRangeName(0), 2) <> "'$" And Left(sSheet, 1) = "$" And Len(sSheet) > 1 Then sSheet = Mid(sSheet, 2)
+		If UBound(vRangeName) > 0 Then sRange = vRangeName(1)
 
 		'	Resolve sheet part: either a document named range, or the active sheet or a real sheet
 		Set oSheets = _Component.getSheets()
@@ -4444,7 +4439,6 @@ Private Function _ValidateSheet(Optional ByRef pvSheetName As Variant _
 '''		DUPLICATESHEETERROR		A sheet with the given name exists already
 
 Dim vSheets As Variant				'	List of sheets
-Dim sSheet As String				'	Sheet name without single quotes
 Dim lSheet As Long					'	Index in list of sheets
 Dim vTypes As Variant				'	Array of accepted variable types
 Dim bValid As Boolean				'	Return value
@@ -4475,13 +4469,13 @@ Try:
 			pvSheetName = _Component.CurrentController.ActiveSheet.Name
 		Else
 			vSheets = _Component.getSheets.getElementNames()
-			sSheet = Replace(pvSheetName, "'", "")
 			If pvNew Then
-				If ScriptForge.SF_Array.Contains(vSheets, sSheet) Then GoTo CatchDuplicate
+				'	ScriptForge.SF_String.FindRegex(sAddress, "^'[^\[\]*?:\/\\]+'")
+				If ScriptForge.SF_Array.Contains(vSheets, pvSheetName) Then GoTo CatchDuplicate
 			Else
-				If Not ScriptForge.SF_Utils._Validate(sSheet, psArgName, V_STRING, vSheets) Then GoTo Finally
+				If Not ScriptForge.SF_Utils._Validate(pvSheetName, psArgName, V_STRING, vSheets) Then GoTo Finally
 				If pvResetSheet Then
-					lSheet = ScriptForge.SF_Array.IndexOf(vSheets, sSheet, CaseSensitive := False)
+					lSheet = ScriptForge.SF_Array.IndexOf(vSheets, pvSheetName, CaseSensitive := False)
 					pvSheetName = vSheets(lSheet)
 				End If
 			End If
@@ -4497,5 +4491,38 @@ CatchDuplicate:
 	GoTo Finally
 End Function	'	SFDocuments.SF_Calc._ValidateSheet
 
+REM -----------------------------------------------------------------------------
+Private Function _ValidateSheetName(ByRef psSheetName As String _
+										, ByVal psArgName As String _
+										) As Boolean
+'''	Check the validity of the sheet name:
+'''	A sheet name	- must not be ampty
+'''					- must not contain next characters: []*?:/\
+'''					- must not use ' (the apostrophe) as first or last character
+'''	Args:
+'''		psSheetName: the name to check
+'''		psArgName: the name of the argument to appear in error messages
+'''	Returns:
+'''		True when the sheet name is valid
+'''	Exceptions:
+'''		CALCADDRESSERROR		'	Sheet name could not be parsed to a valid name
+
+Dim bValid As Boolean				'	Return value
+
+Try:
+	bValid = ( Len(psSheetName) > 0 )
+	If bValid Then bValid = ( Left(psSheetName, 1) <> "'" And Right(psSheetName, 1) <> "'" )
+	If bValid Then bValid = ( Len(ScriptForge.SF_String.FindRegex(psSheetName, "^[^\[\]*?:\/\\]+$", 1, CaseSensitive := False)) > 0 )
+	If Not bValid Then GoTo CatchSheet
+
+Finally:
+	_ValidateSheetName = bValid
+	Exit Function
+CatchSheet:
+	ScriptForge.SF_Exception.RaiseFatal(CALCADDRESSERROR, psArgName, psSheetName _
+				, "Document", [_Super]._FileIdent())
+	GoTo Finally
+End Function	'	SFDocuments.SF_Calc._ValidateSheetName
+
 REM ============================================ END OF SFDOCUMENTS.SF_CALC
 </script:module>
\ No newline at end of file
diff --git a/wizards/source/sfdocuments/SF_Document.xba b/wizards/source/sfdocuments/SF_Document.xba
index c5440944584d..7c9991124d63 100644
--- a/wizards/source/sfdocuments/SF_Document.xba
+++ b/wizards/source/sfdocuments/SF_Document.xba
@@ -1279,7 +1279,8 @@ Private Function _FileIdent() As String
 &apos;&apos;&apos;	Returns a file identification from the information that is currently available
 &apos;&apos;&apos;	Useful e.g. for display in error messages
 
-	_FileIdent = Iif(Len(_WindowFileName) &gt; 0, SF_FileSystem._ConvertFromUrl(_WindowFileName), _WindowTitle)
+	&apos;	OS notation is used to avoid presence of &quot;%nn&quot; in error messages and wrong parameter substitutions
+	_FileIdent = Iif(Len(_WindowFileName) &gt; 0, ConvertFromUrl(_WindowFileName), _WindowTitle)
 
 End Function	&apos;	SFDocuments.SF_Document._FileIdent