In This Topic

Set Table Formula

In This Topic

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. For more information on how to add custom names, see Defined Names. |

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]] |

In DsExcel .NET, 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 refers to a particular area in a table formula which is identified either with a # prefix or with an @ prefix.

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