Named Expressions: structured references (aka Stage 3)

Description

This is a part of the #126 Named Expressions Epic.

Goals

• Add support for #131 array type named expressions =A1:C22
• Add support for table references A1:C22, they have table header, total row, data rows
• Add support for structured references syntax TableName[Row][Column]
• Support CRUD operations for structured references within formulas
• Support Copy, Cut & Paste for structured references within formulas
• Structured references should be supported in range, union, and intersection operators.
• Support top-level headers for adding named columns at the worksheet level

Types

• Defined names: Done in #239
  • Reference. If the expression does start with equality sign: range =A1:C30 or cell =A1
  • Formula. If the reference contains the formula: =SUM(A1:A5)
  • Constant. If reference is just a value: 3, "some string", 10/12/2020
• Defined table. If the expression does not start with equality sign: E1:G26 it is a table where the first row is a header and the last row is a total.

Syntax

There are three parts: =TableName[[RowName][ColumnName]] with some small differences. This is an absolute referenced table cell that is relative to the current worksheet.

To have totally absolute cell reference the table name can be prefixed with the worksheet name: =Sheet1!TableName[[RowName][ColumnName]]. To reference worksheet top header just omit the TableName ie: =Sheet1![[RowName][ColumnName]]

If table name is omitted: =[[RowName][ColumnName]] engine will check if we're already in defined table. This is an absolute cell in a relative table. If we're not in a table range, this will reference the worksheet headers.

If row name is omitted: =TableName[ColumnName] or =[ColumnName] the row is relative to the current row.

If the row is not provided the query should return the whole column. Same as: A:A.

If the column is not provided the query should return the whole row. Same as 1:1.

The external widget would need a worksheet reference: =Sheet1![[RowName][ColumnName]] to reference the cell of Handsontable instance. And since we're outside of any defined table this should look for a global column and row names.

If the name contains special characters it can be enclosed by additional square brackets: [[Column # Name]]. Probably we should keep this syntax for compatibility with other apps.

Top level headers

When we're not in any named table, or Handsontable ID is used as a table name then the RowName, ColumnName, [#Headers] should refer to Handsontable column headers and row headers. For this to work, we have to provide the mapping between name and index.

How do we feed headers to HyperFormula from an external source?

1. We can set them as arrays: rowHeaders, columnHeaders and mapping is done by HF internally both ways.
2. We have to define callbacks to get rowHeader name by index but also to get index by name:
   • getRowHeaderByIndex(sheet, index) { return rowHeaders[sheet][index]; }
   • getRowHeaderByName(sheet, name) { return rowHeaders[sheet].indexOf(name); }

Non Goals

• We're not going to support special keywords at this stage. [#All], [#This Row], [#Data], [#Headers], [#Totals] are not supported.

No files, tests, or entry points are named. Start by reviewing the Named Expressions epic (#126), the completed defined-names work (#239), and the array-type work (#131) to establish the existing scope. Done would require implementing and validating the listed structured-reference, table, header, and formula operations, while preserving the stated non-goals.