IXlCell.SetFormula(IXlFormulaParameter) Method

Assigns the specified formula to a cell.

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 with the IXlFormulaParameter interface which can be transformed into an expression.

Remarks

Use the IXlFormulaParameter object instead of a string if a formula parser is not available when calling the XlExport.CreateExporter method, such as in a situation when the DevExpress.Spreadsheet.v24.1.Core.dll assembly is not referenced or unavailable.

Example

This code snippet creates an IXlFormulaParameter expression from a combination of constants, operators and functions. Constants are transformed into the IXlFormulaParameter objects with the XlFunc.Param method. Operators are static methods of the XlOper object and functions are static methods of the XlFunc object.

When an expression is created, the IXlCell.SetFormula method is used to enter expression into a worksheet cell as the cell formula.

Note

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

FormulaActions.cs

// Create the total row using IXlFormulaParameter.
using (IXlRow row = sheet.CreateRow()) {
    row.SkipCells(2);
    using (IXlCell cell = row.CreateCell()) {
        cell.Value = "Total:";
        cell.ApplyFormatting(totalRowFormatting);
    }
    using (IXlCell cell = row.CreateCell()) {
        // Set the formula to calculate the total amount plus 10 handling fee.
        // =SUM($D$2:$D$5)+10
        IXlFormulaParameter const10 = XlFunc.Param(10);
        IXlFormulaParameter sumAmountFunction = XlFunc.Sum(XlCellRange.FromLTRB(cell.ColumnIndex, 1, cell.ColumnIndex, row.RowIndex - 1).AsAbsolute());
        cell.SetFormula(XlOper.Add(sumAmountFunction, const10));
        cell.ApplyFormatting(totalRowFormatting);
    }
}

FormulaActions.vb

' Create the total row using IXlFormulaParameter.
Using row As IXlRow = sheet.CreateRow()
    row.SkipCells(2)
    Using cell As IXlCell = row.CreateCell()
        cell.Value = "Total:"
        cell.ApplyFormatting(totalRowFormatting)
    End Using
    Using cell As IXlCell = row.CreateCell()
        ' Set the formula to calculate the total amount plus 10 handling fee.
        ' =SUM($D$2:$D$5)+10
        Dim const10 As IXlFormulaParameter = XlFunc.Param(10)
        Dim sumAmountFunction As IXlFormulaParameter = XlFunc.Sum(XlCellRange.FromLTRB(cell.ColumnIndex, 1, cell.ColumnIndex, row.RowIndex - 1).AsAbsolute())
        cell.SetFormula(XlOper.Add(sumAmountFunction, const10))
        cell.ApplyFormatting(totalRowFormatting)
    End Using
End Using

Related GitHub Examples

The following code snippets (auto-collected from DevExpress Examples) contain references to the SetFormula(IXlFormulaParameter) method.

Note

The algorithm used to collect these code examples remains a work in progress. Accordingly, the links and snippets below may produce inaccurate results. If you encounter an issue with code examples below, please use the feedback form on this page to report the issue.

C#

excel-export-library-generate-sales-report/CS/XLExportExampleSalesReport/Form1.cs#L327

using (IXlCell cell = row.CreateCell()) {
    cell.SetFormula(XlFunc.Sum(XlCellRange.FromLTRB(2, row.RowIndex, 5, row.RowIndex)));
    cell.ApplyFormatting(dataRowFormatting);

excel-export-library-generate-sales-analysis-report/CS/XLExportExample/Form1.cs#L299

using (IXlCell cell = row.CreateCell()) {
    cell.SetFormula(XlFunc.Subtotal(XlCellRange.FromLTRB(1, firstDataRowIndex, 1, row.RowIndex - 1), XlSummary.Average, false));
    cell.ApplyFormatting(totalRowFormatting);

VB.NET

excel-export-library-generate-sales-report/VB/XLExportExampleSalesReport/Form1.vb#L340

Using cell As IXlCell = row.CreateCell()
    cell.SetFormula(XlFunc.Sum(XlCellRange.FromLTRB(2, row.RowIndex, 5, row.RowIndex)))
    cell.ApplyFormatting(dataRowFormatting)

excel-export-library-generate-sales-analysis-report/VB/XLExportExample/Form1.vb#L283

Using cell As IXlCell = row.CreateCell()
    cell.SetFormula(XlFunc.Subtotal(XlCellRange.FromLTRB(1, firstDataRowIndex, 1, row.RowIndex - 1), XlSummary.Average, False))
    cell.ApplyFormatting(totalRowFormatting)