Skip to content

Custom Functions For Calculations

Jump to bottom
Mats Alm edited this page Nov 14, 2023 · 10 revisions

The calculation engine in EPPlus supports over 400 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);
    }
}

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);
}

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;
}));

NamespacePrefix

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

public override string NamespacePrefix => "_xlfn.";

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);

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);

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);

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());
}

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, 2, 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;
            case 2:
                // US to EU
                result = size + 33;
            default:
                // not supported conversion type, return a #VALUE! error.
                return CreateResult(eErrorType.Value);
        }
        return CreateResult(result, DataType.Decimal);
    }
}

Then you can add the module to EPPlus in runtime:

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

See also

Formula calculation

EPPlus Software AB - https://epplussoftware.com

Features - Licence overview - Developers

Home

EPPlus wiki

Versions

Worksheet & Ranges

Styling

Import/Export data

Formulas and filters

Charts & Drawing objects

Tables & Pivot Tables

VBA & Protection

Branching strategy and CI

Roadmap

License FAQ

Using our develop nuget feed

Clone this wiki locally