wizards/source/scriptforge/SF_Exception.xba | 2
wizards/source/sfdocuments/SF_Calc.xba | 73 +++++++++++++++++++---------
wizards/source/sfdocuments/SF_Document.xba | 3 -
3 files changed, 53 insertions(+), 25 deletions(-)
New commits:
commit e58a75251a4c877ff4ded94ede01310135b996db
Author: Jean-Pierre Ledure <j...@ledure.be>
AuthorDate: Sun Jun 26 16:30:11 2022 +0200
Commit: Jean-Pierre Ledure <j...@ledure.be>
CommitDate: Sun Jun 26 17:17:53 2022 +0200
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 <j...@ledure.be>
Reviewed-by: Jean-Pierre Ledure <j...@ledure.be>
Tested-by: Jenkins
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
''' Returns a file identification from the information that
is currently available
''' Useful e.g. for display in error messages
- _FileIdent = Iif(Len(_WindowFileName) > 0,
SF_FileSystem._ConvertFromUrl(_WindowFileName), _WindowTitle)
+ ' OS notation is used to avoid presence of "%nn" in
error messages and wrong parameter substitutions
+ _FileIdent = Iif(Len(_WindowFileName) > 0,
ConvertFromUrl(_WindowFileName), _WindowTitle)
End Function ' SFDocuments.SF_Document._FileIdent
