IXlTableColumn.SetFormula(IXlFormulaParameter) Method

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

Namespace: DevExpress.Export.Xl

Assembly: DevExpress.Printing.v24.1.Core.dll

NuGet Package: DevExpress.Printing.Core

Declaration

C#

void SetFormula(
    IXlFormulaParameter formula
)

VB.NET

Sub SetFormula(
    formula As IXlFormulaParameter
)

Parameters

Name	Type	Description
formula	IXlFormulaParameter	An object exposing the IXlFormulaParameter interface that specifies a formula contained in the cells of the table column.

Remarks

Use this method to construct a formula for a calculated column from a combination of the most commonly used functions (XlFunc), arithmetic and relational operators (XlOper) and constants (transformed into the IXlFormulaParameter object by using the XlFunc.Param method).

The example below demonstrates how to use the SetFormula method to assign a formula to a table column to calculate yearly sales for each product in the table. In the formula, the IXlTable.GetRowReference method is used to refer to the corresponding values in each table row that should be summed.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/excel-export-api-examples

TableActions.cs

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.
    table.Columns[5].SetFormula(XlFunc.Sum(table.GetRowReference("Q1", "Q4")));
}

// 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);

TableActions.vb

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.
    table.Columns(5).SetFormula(XlFunc.Sum(table.GetRowReference("Q1", "Q4")))
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

For more details on how to assign a formula to a calculated column, refer to the How to: Create a Calculated Column article.