Set Table Formula

Spread.Services Documentation
Set Table Formula
Collapse All Expand All

Spread.Services Documentation > Developer's Guide > Manage Formulas > 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.

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

Copyright © GrapeCity, inc. All rights reserved.