If you have functions that you use on a regular basis that are not in the built-in functions or if you wish to combine some of the built-in functions into a single function, you can do so by defining your own custom functions. They can be called as you would call any of the built-in functions.
A custom function can have the same name as a built-in function. The custom function takes priority over the built-in function. Custom functions are dynamically linked at evaluation time. Thus, the application can redefine an existing custom function.
For more information about formulas, refer to the Formula Reference.
Using Code
- Define the custom function(s).
- Register the function(s) in the sheet using the AddCustomFunction method.
- Use the custom function(s).
Example: Creating, Registering, and Using Three Custom Functions
The first step is to create the custom functions. In this example, we create three functions: a cube mathematical function, an XOR logical function, and a null string function. The following code implements the custom functions.
The CUBE custom function raises a number to the third power. That is, CUBE(x) is equivalent to POWER(x,3).
| C# |  Copy Code |
|---|---|
public class CubeFunctionInfo : FunctionInfo { public override string Name { get { return "CUBE"; } } public override int MinArgs { get { return 1; } } public override int MaxArgs { get { return 1; } } public override object Evaluate (object[] args) { double num = CalcConvert.ToDouble(args[0]); return num * num * num; } } |
|
The XOR custom function performs an exclusive OR operation on two boolean values. This is similar to the "^"operator in C or the XOR operator in VB.
| C# | Copy Code |
|---|---|
public class XorFunctionInfo : FunctionInfo { public override string Name { get { return "XOR"; } } public override int MinArgs { get { return 2; } } public override int MaxArgs { get { return 2; } } public override object Evaluate (object[] args) { bool arg0 = CalcConvert.ToBool(args[0]); bool arg1 = CalcConvert.ToBool(args[1]); return (arg0 || arg1) && (arg0 != arg1); } } |
|
The NULL function returns the constant value null [i.e. similar to how FALSE() function returns the constant value false].
| C# | Copy Code |
|---|---|
public class NullFunctionInfo : FunctionInfo { public override string Name { get { return "NULL"; } } public override int MinArgs { get { return 0; } } public override int MaxArgs { get { return 0; } } public override object Evaluate (object[] args) { return null; } } |
|
The second step is to register the custom functions as this code does.
| C# | Copy Code |
|---|---|
DefaultSheetDataModel dataModel = FpSpread1.ActiveSheetView.DataModel as FarPoint.Web.Spread.Model.DefaultSheetDataModel; if( dataModel != null ) { dataModel.AddCustomFunction(new CubeFunctionInfo()); dataModel.AddCustomFunction(new XorFunctionInfo()); dataModel.AddCustomFunction(new NullFunctionInfo()); } |
|
The third step is to use the custom functions in formulas, as shown in this code.
| C# | Copy Code |
|---|---|
FpSpread1.ActiveSheetView.SetFormula(0, 0, "CUBE(5)"); FpSpread1.ActiveSheetView.SetFormula(1, 0, "XOR(FALSE,FALSE)"); FpSpread1.ActiveSheetView.SetFormula(1, 1, "XOR(TRUE,FALSE)"); FpSpread1.ActiveSheetView.SetFormula(1, 2, "XOR(FALSE,TRUE)"); FpSpread1.ActiveSheetView.SetFormula(1, 3, "XOR(TRUE,TRUE)"); FpSpread1.ActiveSheetView.SetFormula(2, 0, "CHOOSE(1,100,NULL(),300)"); FpSpread1.ActiveSheetView.SetFormula(2, 1, "CHOOSE(2,100,NULL(),300)"); FpSpread1.ActiveSheetView.SetFormula(2, 2, "CHOOSE(3,100,NULL(),300)"); |
|