Spread ASP.NET 17
Spread for ASP.NET 17 Product Documentation / Developer's Guide / Managing Formulas / Creating a Custom Function
In This Topic
    Creating a Custom Function
    In This Topic

    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

    1. Define the custom function(s).
    2. Register the function(s) in the sheet using the AddCustomFunction method.
    3. 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)");
    See Also
    SetFormula Method
    AddCustomFunction Method