Structured Reference Formulas

SpreadJS supports structured reference formulas in tables.

The structured reference formula uses keywords and the column name of the table to refer to cell ranges in the table.

The components of a structured reference are illustrated in the following image. This formula adds total sales and tax amounts.

1. A table name is a meaningful name that you provide to reference the actual table data (excluding the headers and totals row, if any).

2. A column specifier is derived from the column header and is enclosed in brackets. The column specifier references the column data (excluding the column header and total, if any).

3. A special item specifier can be used to refer to specific portions of the table, such as the Totals row.

4. The table specifier is the outer portion of the structured reference that is enclosed in square brackets following the table name.

5. A structured reference is an entire string beginning with the table name and ending with the table specifier.

The user can use special items to refer to various areas of a table for easier use in formulas. The following table lists the special item specifiers that the user can use in a structured reference:

The "#This Row" item can be abbreviated as "@". For example =DeptSales[#This Row] is the same as =DeptSales[@].

The "#Data" item can be combined with "#Headers" or "#Totals". The following table lists examples:

If the special item is not specified, it is treated as data as shown in the following table:

Column specifiers represent references to the entire data column except for the column header and total. The following table lists the column specifiers:

All table, column, and special item specifiers must be enclosed in matching brackets ([ ]). A specifier that contains other specifiers requires outer matching brackets to enclose the inner matching brackets of the other specifiers. Priority is from highest to lowest if there is a conflict between the policies.

The following rules apply to the brackets:

1. If there are no specifiers, the table specifier brackets can be left out. For example, "=DeptSales[]" equals "=DeptSales".

2. If there is only a single special item or a single column specifier, the table specifier brackets can be left out. For example, "=DeptSales[[#Data]]" equals "=DeptSales[#Data]", "=DeptSales[[TaxAmt]]" equals "=DeptSales[TaxAmt]".

3. If there is a column specifier or a combination of special items, the special item must be enclosed in matching brackets. For example, "=DeptSales[#Data, [TaxAm]]" and "=DeptSales[#Data, #Totals]" is not allowed.

4. If a table column header contains one of the following special characters, the entire column header must be enclosed in brackets: space, tab, line break, carriage return, comma (,), colon (:), period (.), left bracket ([), right bracket (]), the pound sign (#), single quotation mark ('), double quotation mark ("), left brace ({), right brace (}), dollar sign ($), caret (^), ampersand (&), an asterisk (*), plus sign (+), equal sign (=), minus sign (-), greater than symbol (>), less than symbol (<), and division sign (//). For example, if one column name is "#column1", the formula "=Table1[#column1]" is not allowed, it must be "=Table1['#column1]".

5. If there is a column range specifier, one of the column specifiers must be enclosed in brackets. For example, "=Table1[column1:column2]" is not allowed, it must be "=Table1[[column1]:column2]" or "=Table1[column1:[column2]]" or "=Table1[[column1]:[column2]]".

6. The '@' special item brackets can be left out. For example, "=DeptSales[@, TaxAmt]" equals "=DeptSales[[@], TaxAmt]".

The following general rules also apply:

1. All table, column, and special item specifiers are case insensitive.

2. The following characters have special meaning and require the use of a single quotation mark (') as an escape character: left bracket ([), right bracket (]), the pound sign(#), and single quotation mark ('). For example, "=Table1[colu#mn1]" is not allowed, it must be "=Table1[colu'#mn1]".

3. Use the comma (,) to separate the combined special items and column specifiers. For example, "=DeptSales[[#Data], [#Totals], TaxAmt]".

4. The order between special items and column specifiers is independent. For example, "=DeptSales[[#Data], [#Totals], TaxAmt]" equals "=DeptSales[[#Totals], TaxAmt, [#Data]]".

5. The comma (,) behind the '@' special item can be left out. For example, "=DeptSales[@, TaxAmt]" equals "=DeptSales[@TaxAmt]".

You can add structured references to formulas in tables using the setColumnDataFormula and setColumnFormula methods. You can also reference table data in formulas outside of the table area. For example, activeSheet.getCell(7,1).formula("SUM(Table1[SubTotal])");. The last formula that is set has priority between column and cell formulas.

Note: The column specifier in the formula does not apply to the cells in headers and footers.

The following code sample creates a table and uses structured reference formulas to create totals.

activeSheet.tables.add("Table1", 0, 0, 4, 3, GC.Spread.Sheets.Tables.TableThemes.dark1);
activeSheet.getCell(0,0).text("Value1");
activeSheet.getCell(0,1).text("Value2");
activeSheet.getCell(0,2).text("SubTotal");
activeSheet.getCell(1,0).text("1");
activeSheet.getCell(2,0).text("2");
activeSheet.getCell(3,0).text("3");
activeSheet.getCell(1,1).text("5");
activeSheet.getCell(2,1).text("5");
activeSheet.getCell(3,1).text("5");
var sTable = activeSheet.tables.findByName("Table1").setColumnDataFormula(2, "=[Value1]\*[Value2]");
sTable.showFooter(true);
//set footer value
sTable.setColumnValue(0, "Total");
sTable.setColumnFormula(2, "SUM(Table1[SubTotal])");
activeSheet.getColumn(0).width(80);
activeSheet.getColumn(1).width(80);
activeSheet.getColumn(2).width(80);
//activeSheet.getCell(7,1).formula("SUM(Table1[SubTotal])");