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