FunctionWrapper Class

A base class that allows you to override a built-in spreadsheet function.

Namespace: DevExpress.Spreadsheet.Functions

Assembly: DevExpress.Spreadsheet.v19.1.Core.dll

Declaration

C#

public abstract class FunctionWrapper :
    ICustomFunction,
    IFunction

VB.NET

Public MustInherit Class FunctionWrapper
    Implements ICustomFunction,
               IFunction

Remarks

This example creates a function that calculates the sine of an angle measured in degrees and uses that function to replace the built-in SIN worksheet function.

The collection of worksheet functions is available by using the IWorkbook.Functions property. The WorkbookFunctions.OverrideFunction method is used to override a worksheet built-in function. The method requires the function to replace and a custom function with which the built-in function will be replaced.

To create a custom function, implement the FunctionWrapper descendant and override the FunctionWrapper.Evaluate method.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/udf-custom-function-how-to-replace-the-built-in-worksheet-function-t124453

Form1.cs

using DevExpress.Spreadsheet.Functions;
using System;
using System.Collections.Generic;
using System.Windows.Forms;
        private void ReplaceSinFunction()
        {
            WorkbookFunctions functions = spreadsheetControl1.Document.Functions;
            // Obtain the built-in function to override.
            IFunction sinFunction = functions.Math.Sin;
            // Create a new function descending from the FunctionWrapper
            // to replace the built-in function.
            OverridenFunction function = new OverridenFunction(sinFunction);
            // Substitute the built-in function with the custom function.
            functions.OverrideFunction(function.Name, function);
        }

        // A custom function that calculates the sine function for the angle measured in degerees.
        public class OverridenFunction : FunctionWrapper
        {

            public OverridenFunction(IFunction function)
                : base(function)
            {
            }

            public override ParameterValue Evaluate(IList<ParameterValue> parameters, EvaluationContext context)
            {
                ParameterValue value = parameters[0];
                if (value.IsError)
                    return value;

                double angle = parameters[0].NumericValue * Math.PI / 180;
                double sin = Math.Sin(angle);
                return Math.Round(sin, 5);
            }
        }
    }

Form1.vb

Imports DevExpress.Spreadsheet.Functions
Imports System
Imports System.Collections.Generic
Imports System.Windows.Forms
        Private Sub ReplaceSinFunction()
            Dim functions As WorkbookFunctions = spreadsheetControl1.Document.Functions
            ' Obtain the built-in function to override.
            Dim sinFunction As IFunction = functions.Math.Sin
            ' Create a new function descending from the FunctionWrapper
            ' to replace the built-in function.
            Dim [function] As New OverridenFunction(sinFunction)
            ' Substitute the built-in function with the custom function.
            functions.OverrideFunction([function].Name, [function])
        End Sub

        ' A custom function that calculates the sine function for the angle measured in degerees.
        Public Class OverridenFunction
            Inherits FunctionWrapper

            Public Sub New(ByVal [function] As IFunction)
                MyBase.New([function])
            End Sub

            Public Overrides Function Evaluate(ByVal parameters As IList(Of ParameterValue), ByVal context As EvaluationContext) As ParameterValue
                Dim value As ParameterValue = parameters(0)
                If value.IsError Then
                    Return value
                End If

                Dim angle As Double = parameters(0).NumericValue * Math.PI / 180
                Dim sin As Double = Math.Sin(angle)
                Return Math.Round(sin, 5)
            End Function
        End Class

Implements

ICustomFunction

IFunction

Inheritance

Object

FunctionWrapper