Use the Excel Export API to Create a Calculated Column

Sep 19, 2023
5 minutes to read

This example demonstrates how to create a calculated column. The latter uses a single formula that adjusts for each table row. To assign a formula to a table column, use the IXlTableColumn.SetFormula method. The formula you specify will be automatically filled into all cells of the table column.

Important

Due to the infrastructure of the Excel Export Library , which writes data directly into a stream in consecutive order, you should assign the required formula to a table column before you start generating any table data. Otherwise, a System.InvalidOperationException will be thrown.

The IXlTableColumn.SetFormula method allows you to specify a formula for a calculated column in different ways.

Use textual representation of a formula
Compose a formula from tokens
Construct a formula from the IXlFormulaParameter objects

View Example

TableActions.cs
TableActions.vb

csharp

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

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

The image below illustrates the result (the workbook is opened in Microsoft® Excel®).