Spread ASP.NET 6.0 Product Documentation
Creating a Custom Function
Send Feedback
Spread ASP.NET 6.0 Product Documentation > Developer's Guide > Managing Formulas > Creating a Custom Function

Glossary Item Box

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.

Return to the overview of Managing Formulas.

Using Code

  1. Define the custom function(s).
  2. Register the function(s) in the sheet.
  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 
FpSpread1.ActiveSheetView.AddCustomFunction(new CubeFunctionInfo());
FpSpread1.ActiveSheetView.AddCustomFunction(new XorFunctionInfo());
FpSpread1.ActiveSheetView.AddCustomFunction(new NullFunctionInfo());

The third step is to use the customs 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)");
© 2002-2012 GrapeCity, Inc. All Rights Reserved.