References (ODFF 4.8 & 5.8) are not fully supported

[wojciechczerniak]

Description

I've tried A:A range selector and it's not supported. Therefore, I've checked ODFF for references and looks like we're lacking a little bit in terms of references syntax.

4.8 Reference

• A cell position is the location of a single cell at the intersection of a column and a row.
• A cell strip consists of cell positions in the same row and in one or more contiguous columns.
• A cell rectangle consists of cell positions in the same cell strips of one or more contiguous rows.
• A cell cuboid consists of cell positions in the same cell rectangles of one or more contiguous sheets.
• A reference is the smallest cuboid that (1) contains specifically-identified cell positions and/or specifically-identified complete columns/rows such that (2) removal of any cell positions either violates condition (1) or does not leave a cuboid.
• Cell positions in a cell cuboid/rectangle/strip can resolve to empty cells (section 4.7).
• The definitions of specific operations and functions that allow references as operands and parameters stipulate any particular limitations there are on forms of references and how empty cells, when permitted, are interpreted.

5.8 References

References refer to a specific cell or set of cells. The syntax for a constant reference:

Reference ::= '[' (Source? RangeAddress) | ReferenceError ']'
RangeAddress ::=
 SheetLocatorOrEmpty '.' Column Row (':' '.' Column Row )? |
 SheetLocatorOrEmpty '.' Column ':' '.' Column |
 SheetLocatorOrEmpty '.' Row ':' '.' Row |
 SheetLocator '.' Column Row ':' SheetLocator '.' Column Row |
 SheetLocator '.' Column ':' SheetLocator '.' Column |
 SheetLocator '.' Row ':' SheetLocator '.' Row
SheetLocatorOrEmpty ::= SheetLocator | /* empty */
SheetLocator ::= SheetName ('.' SubtableCell)*
SheetName ::= QuotedSheetName | '$'? [^\]\. #$']+
QuotedSheetName ::= '$'? SingleQuoted
SubtableCell ::= ( Column Row ) | QuotedSheetName
ReferenceError ::= "#REF!"
Column ::= '$'? [A-Z]+
Row ::= '$'? [1-9] [0-9]*
Source ::= "'" IRI "'" "#"
CellAddress ::= SheetLocatorOrEmpty '.' Column Row /* Not used directly */

• References always begin with '[' (LEFT SQUARE BRACKET, U+005B); this disambiguates cell addresses from function names and named expressions. Not our syntax
• SheetNames include single-quote“'” (APOSTROPHE, U+0027) characters by doubling them and having the entire name surrounded by single-quotes. Escaping apostrophe in sheet name #64
• Column labels shall be in uppercase.
• The syntax supports whole-row and whole-column references.
• A reference is of type Reference.
• A ReferenceError provides information that a formula evaluates to an Error because of a particular reference having been invalidated by actions that occurred after the formula was validly created.
• Columns are named by a sequence of one or more uppercase letters A-Z (U+0041 through U+005A). Columns are named A, B, C, ... X, Y, Z, AA, AB, AC, ... AY, AZ, BA, BB, BC, ... ZX, ZY, ZZ, AAA, AAB, AAC, AAZ, ABA, ABB, and so on.
• If a RangeAddress does not contain a Column element or does not contain a Row element, it specifies a cell rectangle (4.8 Reference).
• If it contains Row elements, the cell rectangle starts on the first column and ends on the last column the evaluator supports.
• If it contains Column elements, the cell rectangle starts on the first row and ends on the last row the evaluator supports.
• If in a RangeAddress the first part (left of ':' colon) contains a SheetLocator and the second part (right of ':' colon) does not contain a SheetLocator, the second part inherits the SheetLocator from the first part.
• If a RangeAddress contains two different SheetLocators, it specifies a cell cuboid (4.8 Reference).
• If a RangeAddress contains no SheetLocator, the current sheet local to the position where the expression is evaluated is referred.
• A reference with an explicit row or column value beyond the capabilities of an evaluator shall be computed as an Error, and not as a reference.
• Note that references can include a single embedded “:” separator. Evaluators should use references with embedded “:” separators inside the [..] markers, instead of the general-purpose “:” operator, when saving files, and, where there is a choice of cells to join, evaluators should choose the leftmost pair.
• The optional Source expresses that the reference is to sheets and/or cells in a different location (possibly in a same-document fragment) from that for the formula in which the reference occurs. The optional Source is also used for locating Named Expressions (section 5.11).

Examples

• A1
• A:B
• 1:2
• Sheet1!A1
• Sheet1!A1:B2
• Sheet1!A:B
• Sheet1!1:2
• Sheet1!A1:Sheet2!B2
• Sheet1!A:Sheet2!B
• Sheet1!1:Sheet2!2
• ReferenceError "#REF!"

Links

4.8 Reference: https://docs.oasis-open.org/office/OpenDocument/v1.3/csprd02/part4-formula/OpenDocument-v1.3-csprd02-part4-formula.html#Reference
5.8 References: https://docs.oasis-open.org/office/OpenDocument/v1.3/csprd02/part4-formula/OpenDocument-v1.3-csprd02-part4-formula.html#__RefHeading__1017946_715980110

[voodoo11]

I believe #283 resolves:

• If a RangeAddress does not contain a Column element or does not contain a Row element, it specifies a cell rectangle (4.8 Reference).
• If it contains Row elements, the cell rectangle starts on the first column and ends on the last column the evaluator supports.
• If it contains Column elements, the cell rectangle starts on the first row and ends on the last row the evaluator supports.

And covers all examples. 3D reference will parse, but return #REF!.

---

• A reference with an explicit row or column value beyond the capabilities of an evaluator shall be computed as an Error, and not as a reference.

For this we would need to agree on some limits.

---

• Columns are named by a sequence of one or more uppercase letters A-Z (U+0041 through U+005A). Columns are named A, B, C, ... X, Y, Z, AA, AB, AC, ... AY, AZ, BA, BB, BC, ... ZX, ZY, ZZ, AAA, AAB, AAC, AAZ, ABA, ABB, and so on.

This is covered, but again need to think what is the last supported column address and what error return. I can see some inconsistency in other products.

---

Don't know what to do with below:

• Note that references can include a single embedded “:” separator. Evaluators should use references with embedded “:” separators inside the [..] markers, instead of the general-purpose “:” operator, when saving files, and, where there is a choice of cells to join, evaluators should choose the leftmost pair.
• The optional Source expresses that the reference is to sheets and/or cells in a different location (possibly in a same-document fragment) from that for the formula in which the reference occurs. The optional Source is also used for locating Named Expressions (section 5.11).

[wojciechczerniak]

Don't know what to do with below:

• Note that references can include a single embedded “:” separator. Evaluators should use references with embedded “:” separators inside the [..] markers, instead of the general-purpose “:” operator, when saving files, and, where there is a choice of cells to join, evaluators should choose the leftmost pair.

"The [..] markers" are their syntax for a reference i.e: [A1:A3]. They explain how range separator is handled within the reference and that this is different from the separator inside file://C:/Documents... which can be a part of the source mentioned below. Also, ODFF is a file format, so this indicates how they are stored as an XML fragments.

The "leftmost pair" sounds like it's related to invalid syntax: =SUM( [A1:A4:B8] ). Libre will not follow the spec here, the final output is: [A1:B8]. XL will do some weird 💩

• The optional Source expresses that the reference is to sheets and/or cells in a different location (possibly in a same-document fragment) from that for the formula in which the reference occurs. The optional Source is also used for locating Named Expressions (section 5.11).

IMO source is a file link or URL for external source. Not for us ATM

[wojciechczerniak]

For this we would need to agree on some limits.

I think this will be based on our max number of cells that we can support?

Did a short research:

	Max Rows	Max Columns
GS	40,000	18,278
XL	1,048,576	16,384
LC	1048576	1024 (Col A to AMJ)

Sources:
https://gsuitetips.com/tips/sheets/google-spreadsheet-limitations/
https://support.office.com/en-us/article/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3
https://libreofficehelp.com/maximum-number-rows-columns-cells-libreoffice-calc/

[wojciechczerniak]

Notes from the discussion on Slack:

• We're not going to support multiple ":" references. They're not valid according to the first sentence of the quoted paragraph: "Note that references can include a single embedded “:” separator. " and the current HF behavior is the correct one.
• We're ignoring source. It's for linking files or HF instances. Not on our roadmap.
• We'll add the default column and rows limits same as GS, for the best performance and compatibility with web browsers and mobile
• But will add an option in the Config Global settings #58 so it can be customized is someone is going to use the library server-side on a powerful hardware

[wojciechczerniak]

Done in #294