FormulaEngine.Evaluate(String, ExpressionContext) Method

Evaluates the specified formula using the specified context.

Namespace: DevExpress.Spreadsheet.Formulas

Assembly: DevExpress.Spreadsheet.v23.2.Core.dll

NuGet Package: DevExpress.Spreadsheet.Core

Declaration

C#

ParameterValue Evaluate(
    string formula,
    ExpressionContext context
)

VB.NET

Function Evaluate(
    formula As String,
    context As ExpressionContext
) As ParameterValue

Parameters

Name	Type	Description
formula	String	A string that is the formula to evaluate. Note that the leading equal sign “=” in the formula is optional and can be omitted.
context	ExpressionContext	An ExpressionContext object providing information on the cell for which a formula will be evaluated.

Returns

Type	Description
ParameterValue	A ParameterValue object containing the result of the formula calculation.

Remarks

Use the Evaluate method to calculate a worksheet formula for any cell in any worksheet. To do this, create or obtain the expression context that specifies the worksheet environment for which the formula will be evaluated.

This code snippet evaluates the formula “SUM(R[-2]C:R[-1]C)” that sums up two cells above the context cell for which the formula is calculated. The context cell can be any worksheet cell. In this example, it is specified earlier in code.

The ExpressionContext that determines the environment in which the formula is calculated, is created by using the Cell.ColumnIndex and Cell.RowIndex properties of the context cell, the current worksheet, the default “en-US” culture, formula type that is not an Array Formula and the ReferenceStyle.R1C1 cell reference style.

The FormulaEngine.Evaluate method calculates formula for the specified context and returns the object of the ParameterValue type that can be easily transformed to the required standard type in this case, double.

View Example

Form1.vb

Imports DevExpress.Spreadsheet
Imports DevExpress.Spreadsheet.Formulas
Imports DevExpress.Spreadsheet.Functions
            Dim engine As FormulaEngine = spreadsheetControl1.Document.FormulaEngine
            ' Get coordinates of the context cell.
            Dim columnIndex As Integer = Me.contextCell.ColumnIndex
            Dim rowIndex As Integer = Me.contextCell.RowIndex
            ' Create the expression context.
            Dim context As New ExpressionContext(columnIndex, rowIndex, Me.contextSheet, CultureInfo.GetCultureInfo("en-US"), ReferenceStyle.R1C1, DevExpress.Spreadsheet.Formulas.ExpressionStyle.Normal)
            ' Evaluate the expression.
            Dim result As ParameterValue = engine.Evaluate("SUM(R[-2]C:R[-1]C)", context)
            ' Get the result.
            Dim res As Double = result.NumericValue

Form1.cs

using DevExpress.Spreadsheet;
using DevExpress.Spreadsheet.Formulas;
using DevExpress.Spreadsheet.Functions;
            FormulaEngine engine = spreadsheetControl1.Document.FormulaEngine;
            // Get coordinates of the context cell.
            int columnIndex = this.contextCell.ColumnIndex;
            int rowIndex = this.contextCell.RowIndex;
            // Create the expression context.
            ExpressionContext context = new ExpressionContext(columnIndex, rowIndex, this.contextSheet,
                CultureInfo.GetCultureInfo("en-US"), ReferenceStyle.R1C1, DevExpress.Spreadsheet.Formulas.ExpressionStyle.Normal);
            // Evaluate the expression.
            ParameterValue result = engine.Evaluate("SUM(R[-2]C:R[-1]C)", context);
            // Get the result.
            double res = result.NumericValue;

Related GitHub Examples

The following code snippet (auto-collected from DevExpress Examples) contains a reference to the Evaluate(String, ExpressionContext) 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#

winforms-spreadsheet-use-formula-engine/CS/FormulaEngineTest/Form1.cs#L70

// Evaluate the expression.
ParameterValue result = engine.Evaluate("SUM(R[-2]C:R[-1]C)", context);
// Get the result.

VB.NET

winforms-spreadsheet-use-formula-engine/VB/FormulaEngineTest/Form1.vb#L65

' Evaluate the expression.
Dim result As ParameterValue = engine.Evaluate("SUM(R[-2]C:R[-1]C)", context)
' Get the result.