Custom Functions
This article provides information about the possible approaches for creating a custom function. It contains the following sections:
Inheriting FunctionBase abstract class
The document model provides powerful API for creating custom functions. All functions must inherit from the abstract FunctionBase class, providing basic methods and properties for each function instance.
These are the basic FunctionBase members:
Name: Property of type String, defining the name of the function. The property is used for registering the function, so the name of the function must be unique (case insensitive). If a function with repeating name is registered, it overrides the previous function registered with this name.
FunctionInfo: Property of type FunctionInfo providing description of the function and its arguments. For more detailed description of this class you may follow this link.
ArgumentConversionRules: Property describing the way different argument types are interpreted. The functions API works with 5 argument types (Logical, Number, Text, Reference and Array) and each function may interpret each of this argument types differently. For more information you may follow this link.
Evaluate and EvaluateOverride methods: The methods where the function calculations take place. In order to define custom function you need to override the EvaluateOverride method so that later you may obtain function calculations value through the Evaluate method.
Additionally each custom function needs to be registered through the FunctionManager class. This is easily done by passing an instance of the function class to the static Register() method.
Example 1 shows how to register a function class ArgumentsFunction, inheritor of FunctionBase.
Example 1: Register custom function
FunctionManager.RegisterFunction(new ArgumentsFunction());
Functions Inheritance Tree
The document model provides an inheritance tree of classes providing ready to use functionalities for different function types depending on the function arguments and the desired result.
Figure 1 the base abstract function classes.
Figure 1: Functions Inheritance
FunctionBase: Provides the base functions properties (Name, FunctionInfo, ArgumentConvertionRules). Also provides the logic of the IsArgumentNumberValid() method which handles the logic when invalid arguments count is inputted by the user. By inheriting FunctionBase you must override the EvaluateOverride(RadExpression[] arguments) method, so you need to handle the whole logic of converting RadExpression arguments to function arguments.
FunctionWithArguments: Handles the basic logic of converting RadExpression's value to some other value type corresponding to the ArgumentType defined in FunctionInfo property. By inheriting from this class you need to override the EvaluateOverride(object[] arguments) method and handle and array of already converted function argument values.
FunctionWithSameTypeArguments
: By inheriting this class you need to override EvaluateOverride(T[] arguments) method and handle an array of arguments with same type T. StringInFunctions, NumbersInFunction, BooleansInFunction: These classes inherit directly from FunctionWithSameTypeArguments
, FunctionWithSameTypeArguments . Using them is appropriate in cases when the function the respective argument type - String, double or Boolean.and FunctionWithSameTypeArguments
ArgumentConversionRules
The ArgumentConversionRules class provides properties describing the way different function argument types are interpreted. The functions API works with 5 argument types (Logical, Number, Text, Reference and Array) and each function may interpret each of these argument types differently. Additionally, RadSpreadProcessing allows to be made difference between direct arguments (value passed directly into the formula) and indirect arguments (values that depending on some other cells referencing).
ArgumentConversionRules has the following properties:
EmptyDirectArgument: The ArgumentInterpretation of an Empty cell value, passed as direct argument.
NumberDirectArgument: The ArgumentInterpretation of a Number cell value, passed as direct argument.
BoolDirectArgument: The ArgumentInterpretation of a Boolean cell value, passed as direct argument.
TextNumberDirectArgument: The ArgumentInterpretation of a String cell value that may successfully be parsed to a number, passed as direct argument.
NonTextNumberDirectArgument: The ArgumentInterpretation of a String cell value that cannot be parsed to a number, passed as direct argument.
EmptyIndirectArgument: The ArgumentInterpretation of an Empty cell value, passed as indirect argument.
NumberIndirectArgument: The ArgumentInterpretation of a Number cell value, passed as direct argument.
BoolIndirectArgument: The ArgumentInterpretation of a Boolean cell value, passed as indirect argument.
TextNumberIndirectArgument: The ArgumentInterpretation of a String cell value that may successfully be parsed to a number, passed as indirect argument.
NonTextNumberIndirectArgument: The ArgumentInterpretation of a String cell value that cannot be parsed to a number, passed as indirect argument.
ArrayArgument: The ArrayArgumentInterpretaion.
The value of these properties are from the enumerations ArgumentInterpretation and ArrayArgumentInterpretation and they are set through the constructor of ArgumentConversionRules. The default values of these interpretations in the constructor are accordingly ArgumentInterpretation.UseAsIs and ArrayArgumentInterpretation.UseFirstElement.
Example 2 creates an instance of ArgumentConversionRules:
Example 2: Create ArgumentConversionRules
public static readonly ArgumentConversionRules BoolFunctionConversion = new ArgumentConversionRules(
emptyIndirectArgument: ArgumentInterpretation.Ignore,
textNumberDirectArgument: ArgumentInterpretation.TreatAsError,
textNumberIndirectArgument: ArgumentInterpretation.Ignore,
nonTextNumberDirectArgument: ArgumentInterpretation.TreatAsError,
nonTextNumberIndirectArgument: ArgumentInterpretation.Ignore,
arrayArgument: ArrayArgumentInterpretation.UseAllElements);
FunctionInfo
The FunctionInfo class provides properties describing the purpose of the function and each of its arguments.
FunctionInfo has the following properties:
Category: The FunctionCategory to which the function belongs.
Description: Description of the function as string value.
RequiredArgumentsCount: Returns the number of required arguments of the function. If the user inputs less arguments than the RequiredArgumentsCount an error is raised.
OptionalArgumentsCount: Returns the count of the optional arguments group.
OptionalArgumentsRepetitionCount: Returns the number of repetitions of the optional group. The valid count of all arguments depends on this value by satisfying the following conditions:
When OptionalArgumentsRepetionCount <= 1:
ValidArgumentsCount >= RequiredArgumentsCount
ValidArgumentsCount <= RequiredArgumentsCount + OptionalArgumentsCount
When OptionalArgumentsRepetitionsCount > 1:
ValidArgumentsCount = RequiredArgumentsCount + i * OptionalArgumentsCount
i >= 0
i <= OptionalArgumentsRepetitionsCount
i is integer number
IsDefaultValueFunction: Returns Boolean indicating whether the function is default value function.
When true – the function returns some default value when all inputted values have ArgumentInterpretation.Ignore in ArgumentConversionRules of the function.
When false – the function returns ErrorExpressions.ValueError when all inputted values are invalid even if they have ArgumentInterpretation.Ignore in ArgumentConversionRules of the function.
Format: Returns the CellValueFormat of the function result, if the result needs specific formatting (for example DateTime or Currency).
Example 3 shows how to create an instance of FunctionInfo class.
Example 3: Create FunctionInfo
string functionName = "ADD";
string description = "Adds all the numbers in range of cells.";
IEnumerable<ArgumentInfo> requiredArguments = new ArgumentInfo[]
{
new ArgumentInfo("Number", "number1, number2,... are the numbers to sum. Logical values and text are ignored in cells, included if typed as arguments.", ArgumentType.Number),
};
IEnumerable<ArgumentInfo> optionalArguments = new ArgumentInfo[]
{
new ArgumentInfo("Number", "number1, number2,... are the numbers to sum. Logical values and text are ignored in cells, included if typed as arguments.", ArgumentType.Number),
};
FunctionInfo sumFunctionInfo = new FunctionInfo(functionName, FunctionCategory.MathTrig, description, requiredArguments, optionalArguments, 254, true);
Custom Function Examples
The next example is of a custom function named "ARGUMENTS" inheriting from the FunctionBase class. In the FunctionInfo definition you can see that the function has three required arguments and three optional arguments with optionalArgumentsRepeatsCount equal to 3.
The result of the function's calculations is the number of arguments passed to the function, as you can see in the EvaluateOverride() method.
Example 4 shows how to create the 'ARGUMENTS' function.
Example 4: Create ARGUMENTS function
public class Arguments : FunctionBase
{
public static readonly string FunctionName = "ARGUMENTS";
private static readonly FunctionInfo Info;
public override string Name
{
get
{
return FunctionName;
}
}
public override FunctionInfo FunctionInfo
{
get
{
return Info;
}
}
static Arguments()
{
string description = "Returns number of used arguments.";
IEnumerable<ArgumentInfo> requiredArguments = new ArgumentInfo[]
{
new ArgumentInfo("First", "First argument.", ArgumentType.Any),
new ArgumentInfo("Second", "Second argument.", ArgumentType.Any),
new ArgumentInfo("Third", "Third argument.", ArgumentType.Any),
};
IEnumerable<ArgumentInfo> optionalArguments = new ArgumentInfo[]
{
new ArgumentInfo("First", "First argument.", ArgumentType.Any),
new ArgumentInfo("Second", "Second argument.", ArgumentType.Any),
new ArgumentInfo("Third", "Third argument.", ArgumentType.Any),
};
Info = new FunctionInfo(FunctionName, FunctionCategory.MathTrig, description, requiredArguments, optionalArguments, optionalArgumentsRepeatCount: 3);
}
protected override RadExpression EvaluateOverride(FunctionEvaluationContext<RadExpression> context)
{
return new NumberExpression(context.Arguments.Length);
}
}
The next example is of a custom function named "E" that inherits from the FunctionBase class. The function takes no arguments and it always returns the Napier's constant.
Example 5 shows how to create the 'E' function.
Example 5: Create E function
public class E : FunctionBase
{
public static readonly string FunctionName = "E";
private static readonly FunctionInfo Info;
public override string Name
{
get
{
return FunctionName;
}
}
public override FunctionInfo FunctionInfo
{
get
{
return Info;
}
}
static E()
{
string description = "Returns the Napier's constant.";
Info = new FunctionInfo(FunctionName, FunctionCategory.MathTrig, description);
}
protected override RadExpression EvaluateOverride(FunctionEvaluationContext<RadExpression> context)
{
return NumberExpression.E;
}
}
You can download a runnable project of the previous and several other examples of custom functions from our online SDK repository here.