Creating a Custom Function

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

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

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

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

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

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