Skip to content

Custom functions for calculations

The calculation engine in EPPlus supports over 470 functions. For various reasons you might want to add your own custom functions - for example, you could implement functions that corresponds to VBA functions in a workbook. From version 7 that introduces support for dynamic arrayformulas, the interface for creating custom function has changed significantly.

See this wiki page for how to add custom functions in version 6 and below.

The ExcelFunction class

Every function in Epplus must inherit the abstract OfficeOpenXml.FormulaParsing.Excel.Functions.ExcelFunction. Here is an example of how to implement a function, using the existing Cos function in EPPlus:

public class Cos : ExcelFunction
{
    public override ExcelFunctionArrayBehaviour ArrayBehaviour => ExcelFunctionArrayBehaviour.FirstArgCouldBeARange;
    public override int ArgumentMinLength => 1;

    public override CompileResult Execute(IList<FunctionArgument> arguments, ParsingContext context)
    {
        var arg = ArgToDecimal(arguments, 0, out ExcelErrorValue e1);
        // If an error occurred when parsing the first argument to a double
        // an error will be provided via the out parameter e1. If so, return the error.
        if (e1 != null) return CompileResult.GetErrorResult(e1.Type);
        return CreateResult(Math.Cos(arg), DataType.Decimal);
    }
}

Public Class Cos
    Inherits ExcelFunction

    Public Overrides ReadOnly Property ArrayBehaviour As ExcelFunctionArrayBehaviour
        Get
            Return ExcelFunctionArrayBehaviour.FirstArgCouldBeARange
        End Get
    End Property

    Public Overrides ReadOnly Property ArgumentMinLength As Integer
        Get
            Return 1
        End Get
    End Property

    Public Overrides Function Execute(arguments As IList(Of FunctionArgument), context As ParsingContext) As CompileResult
        Dim e1 As ExcelErrorValue = Nothing
        Dim arg = ArgToDecimal(arguments, 0, e1)
        ' If an error occurred when parsing the first argument to a double
        ' an error will be provided via the out parameter e1. If so, return the error.
        If e1 IsNot Nothing Then Return CompileResult.GetErrorResult(e1.Type)
        Return CreateResult(Math.Cos(arg), DataType.Decimal)
    End Function
End Class

The Execute function

Must be overridden from the ExcelFunction base class. This is where you write the actual implementation of your custom function. This function has two arguments:

Argument Type Description
arguments List<FunctionArgument> The arguments of the function. The Value property of the FunctionArgument argument class contains the argument.
context ParsingContext Contains information about the ongoing calculation, such as the address of the cell in which your function is executing.

ArrayBehaviour

Can be (virtual) overridden from the ExcelFunction base class. Use this property to indicate if your function can return an array. This property can have the following values:

Value Description
None (default) The function does not support arrays (returns single values only)
Custom The function supports arrays, but not according to any of the options in this enum. If a function returns this value should also implement the ExcelFunction.ConfigureArrayBehaviour(ArrayBehaviourConfig) function.
FirstArgCouldBeARange The function supports arrays and the first argument could be a range. No further configuration will be needed (as for the Custom option) and if the first argument is a range (ex. A1:C5) of cell values the function will be called once for each cell and the result will be an array of values.

ConfigureArrayBehaviour

Here is an example on how to configure a function where the first three arguments can be ranges/arrays:

public override ExcelFunctionArrayBehaviour ArrayBehaviour => ExcelFunctionArrayBehaviour.Custom;

public override void ConfigureArrayBehaviour(ArrayBehaviourConfig config)
{
    config.SetArrayParameterIndexes(0, 1, 2);
}

Public Overrides ReadOnly Property ArrayBehaviour As ExcelFunctionArrayBehaviour
    Get
        Return ExcelFunctionArrayBehaviour.Custom
    End Get
End Property

Public Overrides Sub ConfigureArrayBehaviour(config As ArrayBehaviourConfig)
    config.SetArrayParameterIndexes(0, 1, 2)
End Sub

ArgumentMinLength

Must be overridden from the ExcelFunction base class. Specifies the minimum number of required arguments. If the function is called with a smaller number of arguments than the specified value a #VALUE! error will be returned.

ParametersInfo

You can configure how errors, addresses, conditions, etc, is handled for your function by overriding the ParametersInfo property. See this example:

// This example shows how to instruct the calculation engine to pass errors (#VALUE!, #REF, etc) on to the function.
public override ExcelFunctionParametersInfo ParametersInfo => new ExcelFunctionParametersInfo(new Func<int, FunctionParameterInformation>((argumentIndex) =>
{
    // since we don't test against the argument index, errors in any argument will be ignored and passed to the function.
    return FunctionParameterInformation.IgnoreErrorInPreExecute;
}));

// This example shows how to instruct the calculation engine to ignore an address 
// in the second function argument (to avoid circular reference checks, etc).
public override ExcelFunctionParametersInfo ParametersInfo => new ExcelFunctionParametersInfo(new Func<int, FunctionParameterInformation>((argumentIndex) =>
{
    if(argumentIndex == 1)
    {
        return FunctionParameterInformation.IgnoreAddress;
    }
    return FunctionParameterInformation.Normal;
}));

' This example shows how to instruct the calculation engine to pass errors (#VALUE!, #REF, etc) on to the function.
Public Overrides ReadOnly Property ParametersInfo As ExcelFunctionParametersInfo
    Get
        Return New ExcelFunctionParametersInfo(New Func(Of Integer, FunctionParameterInformation)(
            Function(argumentIndex)
                ' since we don't test against the argument index, errors in any argument will be ignored and passed to the function.
                Return FunctionParameterInformation.IgnoreErrorInPreExecute
            End Function))
    End Get
End Property

' This example shows how to instruct the calculation engine to ignore an address 
' in the second function argument (to avoid circular reference checks, etc).
Public Overrides ReadOnly Property ParametersInfo As ExcelFunctionParametersInfo
    Get
        Return New ExcelFunctionParametersInfo(New Func(Of Integer, FunctionParameterInformation)(
            Function(argumentIndex)
                If argumentIndex = 1 Then
                    Return FunctionParameterInformation.IgnoreAddress
                End If
                Return FunctionParameterInformation.Normal
            End Function))
    End Get
End Property

NamespacePrefix

Should only be used if Excel (or whatever spreadsheet application you are using) requires a namespace prefix.

public override string NamespacePrefix => "_xlfn.";

Public Overrides ReadOnly Property NamespacePrefix As String
    Get
        Return "_xlfn."
    End Get
End Property

Returning arrays/ranges from functions

As shown in the previous chapter you can configure your function to return arrays based on input parameters. In this case EPPlus will calculate the size of the result range and fill it by calling the function once for each cell in the range. However, you can also let the function return an array of any height/with by using the new OfficeOpenXml.FormulaParsing.Ranges.InMemoryRange class.

var range = new InMemoryRange(nRows, nCols);
// set a value
var v = 1;
var row = 0;
var col = 0;
range.SetValue(row, col, v);

Dim range = New InMemoryRange(nRows, nCols)
' set a value
Dim v = 1
Dim row = 0
Dim col = 0
range.SetValue(row, col, v)

Returning an InMemoryRange from a function

Use the CreateDynamicArrayResult for dynamic array formulas. This will make sure the result will be a dynamic array formula even if the return array is empty or only covers one cell.

return CreateDynamicArrayResult(range, DataType.ExcelRange);

Return CreateDynamicArrayResult(range, DataType.ExcelRange)

You can also use the CreateResult method, but then empty/single cell results will not be stored as a dynamic array.

return CreateResult(range, DataType.ExcelRange);

Return CreateResult(range, DataType.ExcelRange)

Add a new function in runtime

Here is how you can add a custom function to your ExcelPackage instance in runtime.

using (var package = new ExcelPackage())
{
    package.Workbook.FormulaParserManager.AddOrReplaceFunction("cos", new Cos());
}

Using package As New ExcelPackage()
    package.Workbook.FormulaParserManager.AddOrReplaceFunction("cos", New Cos())
End Using

If you have created many functions you can bundle them together into a module by inheriting the OfficeOpenXml.FormulaParsing.Excel.Functions.FunctionsModule class:

class MyFunctionModule : FunctionsModule
{
    public MyFunctionModule()
    {
        base.Functions.Add("sum.addtwo", new SumAddTwo());
        base.Functions.Add("shoesizeconvert", new ShoeSizeConvert());
    }
}

/// <summary>
/// Adds two to all numbers in the supplied range and calculates the sum.
/// </summary>
class SumAddTwo : ExcelFunction
{
    public override int ArgumentMinLength => 1;

    public override CompileResult Execute(IEnumerable<FunctionArgument> arguments, ParsingContext context)
    {            
        // Helper method that converts function arguments to an enumerable of doubles
        var numbers = ArgsToDoubleEnumerable(arguments, context, out ExcelErrorValue e1);
        if(e1 != null) return CompileResult.GetErrorResult(e1.Type); 

        // Do the work
        var result = 0d;
        numbers.ToList().ForEach(x => result += (x + 2));

        // return the result
        return CreateResult(result, DataType.Decimal);
    }
}
/// <summary>
/// This function converts EU shoe size to US shoe size and vice versa.
/// </summary>
class ShoeSizeConvert : ExcelFunction
{
    public override int ArgumentMinLength => 2;

    public override CompileResult Execute(IEnumerable<FunctionArgument> arguments, ParsingContext context)
    {
        // The first argument is the shoe size to convert
        var size = ArgToDecimal(arguments, 0, out ExcelErrorValue e1);
        if(e1 != null) return CompileResult.GetErrorResult(e1.Type); 

        // The second argument is convert type, an optional parameter.
        // 1 is the default value.
        var convertType = 1;
        if(arguments.Count() > 1)
        {
            convertType = ArgToInt(arguments, 1, out ExcelErrorValue e3);
            if(e3 != null) return CompileResult.GetErrorResult(e3.Type);
        }

        // Calculate the result
        double result;
        switch(convertType)
        {
            case 1:
                // EU to US
                result = size - 33;
                break;
            case 2:
                // US to EU
                result = size + 33;
                break;
            default:
                // not supported conversion type, return a #VALUE! error.
                return CreateResult(eErrorType.Value);
        }
        return CreateResult(result, DataType.Decimal);
    }
}

Friend Class MyFunctionModule
    Inherits FunctionsModule

    Public Sub New()
        MyBase.Functions.Add("sum.addtwo", New SumAddTwo())
        MyBase.Functions.Add("shoesizeconvert", New ShoeSizeConvert())
    End Sub
End Class

''' <summary>
''' Adds two to all numbers in the supplied range and calculates the sum.
''' </summary>
Friend Class SumAddTwo
    Inherits ExcelFunction

    Public Overrides ReadOnly Property ArgumentMinLength As Integer
        Get
            Return 1
        End Get
    End Property

    Public Overrides Function Execute(arguments As IEnumerable(Of FunctionArgument), context As ParsingContext) As CompileResult
        ' Helper method that converts function arguments to an enumerable of doubles
        Dim e1 As ExcelErrorValue = Nothing
        Dim numbers = ArgsToDoubleEnumerable(arguments, context, e1)
        If e1 IsNot Nothing Then Return CompileResult.GetErrorResult(e1.Type)

        ' Do the work
        Dim result = 0.0R
        numbers.ToList().ForEach(Sub(x) result += (x + 2))

        ' return the result
        Return CreateResult(result, DataType.Decimal)
    End Function
End Class

''' <summary>
''' This function converts EU shoe size to US shoe size and vice versa.
''' </summary>
Friend Class ShoeSizeConvert
    Inherits ExcelFunction

    Public Overrides ReadOnly Property ArgumentMinLength As Integer
        Get
            Return 2
        End Get
    End Property

    Public Overrides Function Execute(arguments As IEnumerable(Of FunctionArgument), context As ParsingContext) As CompileResult
        ' The first argument is the shoe size to convert
        Dim e1 As ExcelErrorValue = Nothing
        Dim size = ArgToDecimal(arguments, 0, e1)
        If e1 IsNot Nothing Then Return CompileResult.GetErrorResult(e1.Type)

        ' The second argument is convert type, an optional parameter.
        ' 1 is the default value.
        Dim convertType = 1
        If arguments.Count() > 1 Then
            Dim e3 As ExcelErrorValue = Nothing
            convertType = ArgToInt(arguments, 2, e3)
            If e3 IsNot Nothing Then Return CompileResult.GetErrorResult(e3.Type)
        End If

        ' Calculate the result
        Dim result As Double
        Select Case convertType
            Case 1
                ' EU to US
                result = size - 33
            Case 2
                ' US to EU
                result = size + 33
            Case Else
                ' not supported conversion type, return a #VALUE! error.
                Return CreateResult(eErrorType.Value)
        End Select
        Return CreateResult(result, DataType.Decimal)
    End Function
End Class

Then you can add the module to EPPlus in runtime:

package.Workbook.FormulaParserManager.LoadFunctionModule(new MyFunctionModule());

package.Workbook.FormulaParserManager.LoadFunctionModule(New MyFunctionModule())

See also

Formula calculation