Document Solutions for Excel, Java Edition | Document Solutions
Features / Formulas / Set Table Formula
In This Topic
    Set Table Formula
    In This Topic

    Table formula refers to a formula that is used as a structured reference in a worksheet instead of an explicit cell reference.

    While creating a table formula, users must apply structured reference (the combination of table and column names in a spreadsheet) along with the syntax rules.

    For example, let's refer to the table formula in a worksheet as shown below.

    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.

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

    Reference operators

    In DsExcel Java, you can use reference operators in order to combine column specifiers in a table formula.

    Refer to the following 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.

    DsExcel Java 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.

    Java
    Copy Code 
    Object[][] data = new Object[][] 
{ 
        { "SalesPerson", "Region", "SalesAmount", "ComPct", "ComAmt" },
        { "Joe", "North", 260, 0.10, null }, 
        { "Robert", "South", 660, 0.15, null }, 
};
worksheet.getRange("A1:E3").setValue(data);
worksheet.getTables().add(worksheet.getRange("A1:E3"), true);
worksheet.getTables().get(0).setName("DeptSales");
worksheet.getTables().get(0).getColumns().get("ComPct").getDataBodyRange().setNumberFormat("0%");

// Use table formula in table range.
worksheet.getTables().get(0).getColumns().get("ComAmt").getDataBodyRange().setFormula("=[@ComPct]*[@SalesAmount]");

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