Worksheet reference

[wojciechczerniak]

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

[wojciechczerniak]

@jansiegel mentioned at the meeting that he had to remove underscore _ from Handsontable id. We need to check this and allow more characters.

[jansiegel]

@jansiegel mentioned at the meeting that he had to remove underscore _ from Handsontable id. We need to check this and allow more characters.

Correct, also, when the name of the sheet contains a space, referencing it as ='Name with space'.A1 doesn't work, neither does =$'Name with space'.A1.

[swistak35]

I've changed format from $Sheet42.A1 to Sheet42!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:

• other tools treat sheet names with case insensitivity (when adding and when fetching sheet. But we would still need to keep the original case-sensitive name, because when unparsing, we want probably show him shEET42, if he entered it this way)
• there's no support yet for escaping characters, so there's no way to use Some'sheet sheet name. In other tools it's possible by escaping with '', so by writing Some''sheet.

This looks like topics which could be covered by standard. Does ODDF says something about that?

[swistak35]

Closing the issue for now, as the things from the task are done, and my above comments can be extracted into different tasks.

[wojciechczerniak]

I haven't seen spec about keeping the sheetName formatting. 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 on sheetRename action? 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:

SheetNames include single-quote“'” (APOSTROPHE, U+0027) characters by doubling them and having the entire name surrounded by single-quotes.

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:

"OOo 2.0 always generates absolute Sheetnames if it's not the current sheet (i.e., '$' always prefixes a Sheetname). However, the OpenDocument 1.0 specification 8.3.1 clearly makes the "$" optional in front of a sheetname. "

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]

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 ?

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?

Maybe we can keep them in regex as well?

Sounds doable