Skip to main content

IXlCell.SetFormula(XlExpression) Method

Assigns the specified formula expression to a cell.

Namespace: DevExpress.Export.Xl

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

NuGet Package: DevExpress.Printing.Core

#Declaration

void SetFormula(
    XlExpression formula
)

#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.v24.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

// 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);
    }
}
See Also