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.
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:
The following general rules also apply:
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 following example creates a table and uses structured reference formulas to create totals.
JavaScript 
Copy Code


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])"); 