Implementing missing or new Excel functions

Epplus has a number of builtin Excel functions, these should cover the most commonly used functions in Excel. For various reasons you might want to add new ones - for example, you could implement missing Excel functions or you could implement functions that corresponds to VBA functions in a workbook. Here is how you do it:

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 CompileResult Execute(IEnumerable<FunctionArgument> arguments, ParsingContext context)
        {
            ValidateArguments(arguments, 1);
            var arg = ArgToDecimal(arguments, 0);
            return CreateResult(System.Math.Cos(arg), DataType.Decimal);
        }
    }

Epplus will execute this function and send in the arguments to the function via the parameter arguments. The parameter context contains some useful information, one example is context.Scopes.Current.Address which gives you the address of the current cell.

ValidateArguments, ArgToDecimal and CreateResult are examples of helper methods that you will find in the ExcelFunction class.

Add a new function to EPPlus code base

New functions should be added to the namespace hierarchy under OfficeOpenXml.FormulaParsing.Excel.Functions. When this is done you need to register the new function in the constructor of the OfficeOpenXml.FormulaParsing.Excel.Functions.BuildInFunctions class.

Functions["cos"] = new Cos();

If you want to commit new functions to the EPPlus project you must also write unit tests for the added function/functions. These tests are located in the EPPlusTest project, see namespace EPPlusTest.Excel.Functions. Contributions/Pull requests without test coverage will not be accepted.

Add a new function in runtime

If you don't want to add your function to EPPlus code, you can add it 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("seanconneryfy", new SeanConneryfy());
    }
}

/// <summary>
/// A really unnecessary function. Adds two to all numbers in the supplied range and calculates the sum.
/// </summary>
class SumAddTwo : ExcelFunction
{
    public override CompileResult Execute(IEnumerable<FunctionArgument> arguments, ParsingContext context)
    {
        // Sanity check, will set excel VALUE error if min length is not met
        ValidateArguments(arguments, 1);
            
        // Helper method that converts function arguments to an enumerable of doubles
        var numbers = ArgsToDoubleEnumerable(arguments, context);
            
        // Do the work
        var result = 0d;
        numbers.ToList().ForEach(x => result += (x + 2));

        // return the result
        return CreateResult(result, DataType.Decimal);
    }
}
/// <summary>
/// This function handles Swedish formatting strings.
/// </summary>
class TextSwedish : ExcelFunction
{
    public override CompileResult Execute(IEnumerable<FunctionArgument> arguments, ParsingContext context)
    {
        // Sanity check, will set excel VALUE error if min length is not met
        ValidateArguments(arguments, 2);

        //Replace swedish year format with invariant for parameter 2.
        var format = arguments.ElementAt(1).Value.ToString().Replace("åååå", "yyyy");   
        var newArgs = new List<FunctionArgument> { arguments.ElementAt(0) };
        newArgs.Add(new FunctionArgument(format));

        //Use the build-in Text function.
        var func = new Text();
        return func.Execute(newArgs, context);
    }
}

/// <summary>
/// An even more unnecessary function, inspired by the Sean Connery keyboard;) Will add 'sh' at the end of the supplied string.
/// </summary>
class SeanConneryfy : ExcelFunction
{
    public override CompileResult Execute(IEnumerable<FunctionArgument> arguments, ParsingContext context)
    {
        // Sanity check, will set excel VALUE error if min length is not met
        ValidateArguments(arguments, 1);
        // Get the first arg
        var input = ArgToString(arguments, 0);

        // return the result
        return CreateResult(input + "sh", DataType.String);
    }
}

Then you can add the module to EPPlus in runtime:

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