Set Table Formula

Table formula refers to a formula that is used as a structured reference in the worksheet instead of using it as an explicit cell reference. Structured reference in a table formula is the combination of table and column names in a spreadsheet with syntax rules that must be applied while creating a table formula.

For instance, let us consider an example of a table formula in a spreadsheet.

Table Formula

The structured reference components in the above table formula are described below.

Components	Description
Table Name	References the table data, without any header or total rows. You can use a default table name, such as Table1, or change it to use a custom name. Example: DeptSales is a custom table name in the table formula.
Column Specifier	Column specifiers use the names of the columns they represent. They reference column data without any column header or total row. Column specifiers must be enclosed in [] square brackets when they are written in the table formula. Example: [SalesAmount] and [ComAmt]
Item Specifier	Refers to a specific portions of the table such as total row. Example: [#Totals] and [#Data]
Table Specifier	Represents the outer portions of the structured reference. Outer references follow table names and are enclosed within the square brackets. Example: [[#Totals],[SalesAmount]],[[#Data],[ComAmt]]
Structures Reference	Represented by a string that begins with the table name and ends with the column specifier. Example: DeptSales[[#Totals],[SalesAmount]] and DeptSales[[#Data],[ComAmt]]

Reference operators

In Spread.Services, reference operators are used to combine column specifiers in a table formula.

Shared below is a table that describes the reference operators along with structured reference components and cell range corresponding to the table formula.

Operators	Description	Example
:(colon) range operator	All of the cells in two or more adjacent columns.	=DeptSales[[SalesPerson]:[Region]]
,(comma) union operator	A combination of two or more columns.	=DeptSales[SalesAmount],DeptSales[ComAmt]
(space) intersection operator	The intersection of two or more columns.	=DeptSales[[SalesPerson]:[SalesAmount]]DeptSales[[Region]:[ComPct]]

Special item specifier

Special item specifier refers to a particular area in a table formula which is identified either with a # prefix or with an @ prefix.

Spread.Services supports the following types of special item specifiers:

Special Item Specifier	Description
#All	To the entire table including column headers, data and totals (if any).
#Data	Only the data rows
#Headers	Only the header rows
#Totals	Only the total row. If there is none, it returns null.
#This Row	Cells in the same row as the formula
@	Cells in the same row as the formula

Refer to the following example code to set table formula in your spreadsheets.

C#	Copy Code
// Define Data worksheet.Range["A1:E3"].Value = new object[,] { {"SalesPerson", "Region", "SalesAmount", "ComPct", "ComAmt"}, {"Joe", "North", 260, 0.10, null}, {"Robert", "South", 660, 0.15, null}, }; worksheet.Tables.Add(worksheet.Range["A1:E3"], true); worksheet.Tables[0].Name = "DeptSales"; worksheet.Tables[0].Columns["ComPct"].DataBodyRange.NumberFormat = "0%"; //Use table formula in table range. worksheet.Tables[0].Columns["ComAmt"].DataBodyRange.Formula = "=[@ComPct]*[@SalesAmount]"; //Use table formula out of table range. worksheet.Range["F2"].Formula = "=SUM(DeptSales[@SalesAmount])"; worksheet.Range["G2"].Formula = "=SUM(DeptSales[[#Data],[SalesAmount]])"; worksheet.Range["H2"].Formula = "=SUM(DeptSales[SalesAmount])"; worksheet.Range["I2"].Formula = "=SUM(DeptSales[@ComPct], DeptSales[@ComAmt])";

Copy Code

// Define Data
worksheet.Range["A1:E3"].Value = new object[,]
    {
        {"SalesPerson", "Region",  "SalesAmount", "ComPct", "ComAmt"},
        {"Joe", "North", 260, 0.10, null},
        {"Robert", "South", 660, 0.15, null},
    };

worksheet.Tables.Add(worksheet.Range["A1:E3"], true);
worksheet.Tables[0].Name = "DeptSales";
worksheet.Tables[0].Columns["ComPct"].DataBodyRange.NumberFormat = "0%";

//Use table formula in table range.
worksheet.Tables[0].Columns["ComAmt"].DataBodyRange.Formula = "=[@ComPct]*[@SalesAmount]";

//Use table formula out of table range.
worksheet.Range["F2"].Formula = "=SUM(DeptSales[@SalesAmount])";
worksheet.Range["G2"].Formula = "=SUM(DeptSales[[#Data],[SalesAmount]])";
worksheet.Range["H2"].Formula = "=SUM(DeptSales[SalesAmount])";
worksheet.Range["I2"].Formula = "=SUM(DeptSales[@ComPct], DeptSales[@ComAmt])";