Skip to content

Custom Functions For Calculations

Jump to bottom
swmal edited this page Oct 18, 2023 · 12 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.

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

Here is how to return an InMemoryRange from a function:

return CreateDynamicArrayResult(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.
/// </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 size1 = 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
        var result = double.NaN;
        if(convertType == 1)
        {
            // EU to US, the only conversion supported...
            return size1 - 33;
        }
        // If we couldn't convert, return a #VALUE! error.
        if(result == double.NaN)
        {
            return CompileResult.GetErrorResult(eErrorType.Value);
        }
        return CreateResult(result, DataType.Decimal);
    }
}

Then you can add the module to EPPlus in runtime:

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

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