Repository navigation
Worksheet reference #12
Description
Current notation is =$Sheet1.A1, while the more popular is using exclamation mark =Sheet1!A1 and does support spaces ='Sheet number two'!B4
We need:
- test for spaces in the sheet name
- test for special characters in the sheet name
- configuration option for the separation dot/exclamation mark
Activity
wojciechczerniak commentedon Nov 28, 2019
@jansiegel mentioned at the meeting that he had to remove underscore
_from Handsontable id. We need to check this and allow more characters.jansiegel commentedon Dec 2, 2019
Correct, also, when the name of the sheet contains a
space, referencing it as='Name with space'.A1doesn't work, neither does=$'Name with space'.A1.Use exclamation mark instead of dot as sheet separator
swistak35 commentedon Dec 5, 2019
I've changed format from
$Sheet42.A1toSheet42!A1; added support for_character and added support for quoted sheet names ('Sheet with spaces'!A1).Also, now I am not so sure, whether it's worth to pursue the support for other characters separating sheet name from the reference, because the tools supporting
.notation, also have$sign in the beginning. So it's not only that one character which changes...There are few more tasks which may be worth adding:
shEET42, if he entered it this way)Some'sheetsheet name. In other tools it's possible by escaping with'', so by writingSome''sheet.This looks like topics which could be covered by standard. Does ODDF says something about that?
swistak35 commentedon Dec 5, 2019
Closing the issue for now, as the things from the task are done, and my above comments can be extracted into different tasks.
wojciechczerniak commentedon Dec 5, 2019
I haven't seen spec about keeping the
sheetNameformatting. It looks like it's part of the formula general reference and should be normalized on save. This is what all tested by me spreadsheets do (XL, GS, LC). And after a while, I came to the conclusion that it makes a lot of sense, because what should we do onsheetRenameaction? Keep part of the formatting and change the rest? The name can be changed externally and is always overwritten fully.TLDR; I think we should compare it as case-insensitive and display always as normalized to the name stored by the engine. Are helpers from #32 case-insensitive @swistak35 ?
There is a fragment about escaping characters. Like you said, by a double single quote:
I will create a new task for this one 👍
The older draft for ODFF [1] explains why there is
$at the beginning of SheetName in some apps:And indeed the regex marks is as optional
QuotedSheetName ::= '$'? SingleQuoted.@swistak35 Maybe we can keep them in regex as well? Just mark them as optional 🤔
[1] https://www.oasis-open.org/committees/download.php/16826/openformula-spec-20060221.html
swistak35 commentedon Dec 6, 2019
No, they are not -- we should have either case insensitivity in all places or not :) I mean, I agree with you, but the change is not only scoped to these two helpers -- we also need to change unparser to return correct name, etc. -- can you create a task for that as well? For case insensitivity?
Sounds doable
wojciechczerniak commentedon Dec 6, 2019
All done.
Optional
$in sheet name: #65Escaping apostrophe in sheet name: #64
Case sensitivity of sheet names: #66