Using 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.

A table name is a meaningful name that you provide to reference the actual table data (excluding the headers and totals row, if any).
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).
A special item specifier can be used to refer to specific portions of the table, such as the Totals row.
The table specifier is the outer portion of the structured reference that is enclosed in square brackets following the table name.
A structured reference is the 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:

Special Item	Example	References	Cell Range
#All	=DeptSales[#All]	Entire table including column headers, data, and totals	A1:E8
#Data	=DeptSales[#Data]	Data	A2:E7
#Headers	=DeptSales[#Headers]	Header row	A1:E1
#Totals	=DeptSales[#Totals]	Total row. Returns null if none exists	A8:E8
#This Row	=DeptSales[#This Row]	Portion of the columns in the current row	A5:E5 (current row is 5)

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:

Special Item	Example	References	Cell Range
#Headers and #Data	=DeptSales[[#Headers], [#Data]]	Column headers and data	A1:E7
#Data and #Totals	=DeptSales[[#Data], [#Totals]]	Data and the total row	A2:E8

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

Special Item	Example	References	Cell Range
None	=DeptSales[] or =DeptSales	Data	A2:E7

Column specifiers represent references to the entire data column with the exception of the column header and total. The following table lists the column specifiers:

Column Specifier	Example	References	Cell Range
Single column	=DeptSales[SaleAmt]	"SaleAmt" column in data	C2:C7
Column range	=DeptSales[[SaleAmt]:[TaxAmt]]	Range of column "SaleAmt" to column "TaxAmt" in data	C2:E7
None	=DeptSales[[#Data]]	All columns in data	A2:E8

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:

If there are no specifiers, the table specifier brackets can be left out. For example, "=DeptSales[]" equals "=DeptSales".
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]".
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.
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 (]), pound sign (#), single quotation mark ('), double quotation mark ("), left brace ({), right brace (}), dollar sign ($), caret (^), ampersand (&), 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]".
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]]".
The '@' special item brackets can be left out. For example, "=DeptSales[@, TaxAmt]" equals "=DeptSales[[@], TaxAmt]".

The following general rules also apply:

All table, column, and special item specifiers are case insensitive.
The following characters have special meaning and require the use of a single quotation mark (') as an escape character: left bracket ([), right bracket (]), pound sign(#), and single quotation mark ('). For example, "=Table1[colu#mn1]" is not allowed, it must be "=Table1[colu'#mn1]".
Use the comma (,) to separate the combined special items and column specifiers. For example, "=DeptSales[[#Data], [#Totals], TaxAmt]".
The order between special items and column specifiers is independent. For example, "=DeptSales[[#Data], [#Totals], TaxAmt]" equals "=DeptSales[[#Totals], TaxAmt, [#Data]]".
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.

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

Using Code

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

JavaScript	Copy Code
activeSheet.addTable("Table1", 0, 0, 4, 3, GcSpread.Sheets.TableStyles.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.findTableByName("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])");

JavaScript

Copy Code

activeSheet.addTable("Table1", 0, 0, 4, 3, GcSpread.Sheets.TableStyles.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.findTableByName("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])");