-
Notifications
You must be signed in to change notification settings - Fork 275
Custom Functions For Calculations
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.
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);
}
}
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. |
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. |
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);
}
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.
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;
}));
Should only be used if Excel (or whatever spreadsheet application you are using) requires a namespace prefix.
public override string NamespacePrefix => "_xlfn.";
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);
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);
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());
EPPlus Software AB - https://epplussoftware.com
- Getting Started
- The Sample Project
- EPPlus and Docker
- EPPlus and Blazor
- EPPlus and Linux
- Security
- Configuration
- Releases/versioning
- What is new in EPPlus 5+
- Breaking Changes in EPPlus 5
- Breaking Changes in EPPlus 6
- Breaking Changes in EPPlus 7
- Addressing a worksheet
- Dimension/Used range
- Copying ranges/sheets
- Insert/Delete
- Filling ranges
- Sorting ranges
- Taking and skipping columns/rows
- Data validation
- Comments
- Freeze and Split Panes
- Header and Footer
- Autofit columns
- Grouping and Ungrouping Rows and Columns
- Formatting and styling
- Conditional formatting
- Using Themes
- Working with custom named table- or slicer- styles