IXlCell.SetFormula(XlExpression) Method

Assigns the specified formula expression to a cell.

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	A XlExpression object that is a collection of syntactic tokens (PTGs, an acronym of “parsed thing”).

Remarks

Use this SetFormula method override instead of the method which uses a formula string as a parameter if the formula parser cannot be specified when calling the XlExport.CreateExporter method to start export.

The XlFormulaParser class used as a formula parser requires a reference to the DevExpress.Spreadsheet.v23.2.Core.dll assembly, so if this assembly is missing, a string formula cannot be parsed. Missing parser does not allow export to the .xls (Excel 97-2003) format and leaves unvalidated formulas in a worksheet exported to the .xlsx (OpenXml) format.

Example

This code snippet show how to create an expression “from scratch” by adding formula tokens (aka PTGs, “parsed things”) to the XlExpression instance which is a List<DevExpress.Export.Xl.XlPtgBase> list of tokens arranged in Reverse-Polish Notation order. Subsequently, the expression is passed to the IXlCell.SetFormula method to specify a cell formula.

Note

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

FormulaActions.cs

// Create a formula using XlExpression.
using (IXlRow row = sheet.CreateRow()) {
    row.SkipCells(2);
    using (IXlCell cell = row.CreateCell()) {
        cell.Value = "Mean value:";
        cell.ApplyFormatting(totalRowFormatting);
    }
    using (IXlCell cell = row.CreateCell()) {
        // Set the formula to calculate the mean value.
        // =$D$6/4
        XlExpression expression = new XlExpression();
        expression.Add(new XlPtgRef(new XlCellPosition(cell.ColumnIndex, row.RowIndex - 1, XlPositionType.Absolute, XlPositionType.Absolute)));
        expression.Add(new XlPtgInt(row.RowIndex - 2));
        expression.Add(new XlPtgBinaryOperator(XlPtgTypeCode.Div));
        cell.SetFormula(expression);
        cell.ApplyFormatting(totalRowFormatting);
    }
}

FormulaActions.vb

' Create a formula using XlExpression.
Using row As IXlRow = sheet.CreateRow()
    row.SkipCells(2)
    Using cell As IXlCell = row.CreateCell()
        cell.Value = "Mean value:"
        cell.ApplyFormatting(totalRowFormatting)
    End Using
    Using cell As IXlCell = row.CreateCell()
        ' Set the formula to calculate the mean value.
        ' =$D$6/4
        Dim expression As New XlExpression()
        expression.Add(New XlPtgRef(New XlCellPosition(cell.ColumnIndex, row.RowIndex - 1, XlPositionType.Absolute, XlPositionType.Absolute)))
        expression.Add(New XlPtgInt(row.RowIndex - 2))
        expression.Add(New XlPtgBinaryOperator(XlPtgTypeCode.Div))
        cell.SetFormula(expression)
        cell.ApplyFormatting(totalRowFormatting)
    End Using
End Using