IXlTableColumn.SetFormula(XlExpression) Method

Specifies the formula to apply to all cells in the table column.

Namespace: DevExpress.Export.Xl

Assembly: DevExpress.Printing.v23.2.Core.dll

NuGet Package: DevExpress.Printing.Core

Declaration

C#

void SetFormula(
    XlExpression formula
)

VB.NET

Sub SetFormula(
    formula As XlExpression
)

Parameters

Name	Type	Description
formula	XlExpression	An XlExpression object that is a collection of formula tokens (aka PTGs, “parsed things”) arranged in Reverse-Polish Notation order.

Example

The following example demonstrates how to use the IXlTableColumn.SetFormula method to assign a formula composed of tokens to a table column to calculate yearly sales for each product in the table (the sample table is shown in the image below). This formula will be automatically filled into all cells of the column.

To create an expression, you should add the required tokens in the proper order to the XlExpression instance. First, create an instance of the XlPtgTableRef class that specifies a reference to table data. Use the IXlTable.GetRowReference method to return a reference to the portion of columns “Q1” through “Q4” in the same row as the formula. Then, create an XlPtgFuncVar class instance to specify a function to use in the formula. In this example, the SUM function with the ptg code 0x0004 is used. Finally, pass the specified expression to the IXlTableColumn.SetFormula method.

The textual representation of the created formula is “=SUM(Table1[@[Q1]:[Q4]])”.

C#

IXlTable table;
// Specify an array containing column headings for a table
string[] columnNames = new string[] { "Product", "Q1", "Q2", "Q3", "Q4", "Yearly Total" };

// Create the first row in the worksheet from which the table starts
using (IXlRow row = sheet.CreateRow())
{
    // Start generating the table with a header row displayed
    table = row.BeginTable(columnNames, true);
    // Specify the total row label
    table.Columns[0].TotalRowLabel = "Total";
    // Specify the function to calculate the total
    table.Columns[5].TotalRowFunction = XlTotalRowFunction.Sum;
    // Specify the number format for numeric values in the table and the total cell of the "Yearly Total" column
    XlNumberFormat accounting = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)";
    table.DataFormatting = accounting;
    table.Columns[5].TotalRowFormatting = accounting;
    // Set the formula to calculate annual sales of each product
    // and display results in the "Yearly Total" column
    XlExpression expression = new XlExpression();
    expression.Add(new XlPtgTableRef(table.GetRowReference("Q1", "Q4"), XlPtgDataType.Reference));
    expression.Add(new XlPtgFuncVar(0x0004, 1, XlPtgDataType.Value));
    table.Columns[5].SetFormula(expression);
}

// Generate table rows and populate them with data
using (IXlRow row = sheet.CreateRow())
    row.BulkCells(new object[] { "Camembert Pierrot", 17000, 18500, 17500, 18000 }, null);
using (IXlRow row = sheet.CreateRow())
    row.BulkCells(new object[] { "Gnocchi di nonna Alice", 15500, 14500, 15000, 14000 }, null);
using (IXlRow row = sheet.CreateRow())
    row.BulkCells(new object[] { "Mascarpone Fabioli", 15000, 15750, 16000, 15500 }, null);
using (IXlRow row = sheet.CreateRow())
    row.BulkCells(new object[] { "Ravioli Angelo", 12500, 11000, 13500, 12000 }, null);

// Create the total row and finish the table
using (IXlRow row = sheet.CreateRow())
   row.EndTable(table, true);

VB.NET

Dim table As IXlTable
' Specify an array containing column headings for a table
Dim columnNames() As String = { "Product", "Q1", "Q2", "Q3", "Q4", "Yearly Total" }

' Create the first row in the worksheet from which the table starts
Using row As IXlRow = sheet.CreateRow()
    ' Start generating the table with a header row displayed
    table = row.BeginTable(columnNames, True)
    ' Specify the total row label
    table.Columns(0).TotalRowLabel = "Total"
    ' Specify the function to calculate the total
    table.Columns(5).TotalRowFunction = XlTotalRowFunction.Sum
    ' Specify the number format for numeric values in the table and the total cell of the "Yearly Total" column
    Dim accounting As XlNumberFormat = "_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)"
    table.DataFormatting = accounting
    table.Columns(5).TotalRowFormatting = accounting
    ' Set the formula to calculate annual sales of each product
    ' and display results in the "Yearly Total" column
    Dim expression As New XlExpression()
    expression.Add(New XlPtgTableRef(table.GetRowReference("Q1", "Q4"), XlPtgDataType.Reference))
    expression.Add(New XlPtgFuncVar(&H4, 1, XlPtgDataType.Value))
    table.Columns(5).SetFormula(expression)
End Using

' Generate table rows and populate them with data
Using row As IXlRow = sheet.CreateRow()
    row.BulkCells(New Object() { "Camembert Pierrot", 17000, 18500, 17500, 18000 }, Nothing)
End Using
Using row As IXlRow = sheet.CreateRow()
    row.BulkCells(New Object() { "Gnocchi di nonna Alice", 15500, 14500, 15000, 14000 }, Nothing)
End Using
Using row As IXlRow = sheet.CreateRow()
    row.BulkCells(New Object() { "Mascarpone Fabioli", 15000, 15750, 16000, 15500 }, Nothing)
End Using
Using row As IXlRow = sheet.CreateRow()
    row.BulkCells(New Object() { "Ravioli Angelo", 12500, 11000, 13500, 12000 }, Nothing)
End Using

' Create the total row and finish the table
Using row As IXlRow = sheet.CreateRow()
   row.EndTable(table, True)
End Using