Class CustomFunction

java.lang.Object

com.grapecity.documents.excel.CustomFunction

Direct Known Subclasses:: AsyncCustomFunction

public abstract class CustomFunction extends Object

Represents the base type for a custom function.

Derive from this abstract class to define spreadsheet functions that behave like built-in formula functions in GcExcel. A custom function supplies its function name, return type, and optional parameter metadata through one of the CustomFunction constructors, and must implement evaluate(Object[],ICalcContext) to calculate the result.

After a derived function is created, register it by using Workbook.AddCustomFunction(CustomFunction) or Workbook.AddCustomFunction(CustomFunction,boolean) before using it in formulas.

Custom functions can be configured to participate in calculation caching and error propagation. Use setIsVolatile(boolean) to control whether repeated calls with the same arguments can reuse cached results, and use setAcceptErrors(boolean) to control whether error values such as #N/A or #VALUE! are passed to the function for handling. By default, custom functions are not volatile and do not accept error arguments.

This type is the synchronous custom-function base class. For asynchronous evaluation scenarios, use AsyncCustomFunction.


 CustomFunction function = new CustomFunction(
     "ADD2",
     FunctionValueType.Number,
     new Parameter[] {
         new Parameter(FunctionValueType.Number),
         new Parameter(FunctionValueType.Number)
     }) {
     public Object evaluate(Object[] arguments, ICalcContext context) {
         double left = ((Number) arguments[0]).doubleValue();
         double right = ((Number) arguments[1]).doubleValue();
         return left + right;
     }
 };
 Workbook.AddCustomFunction(function);
 worksheet.getRange("A1").setFormula("=ADD2(10,20)");
 Object sum = worksheet.getRange("A1").getValue();

Field Summary

Fields

Modifier and Type

Field

Description

String

name
Constructor Summary

Constructors

Constructor

Description

CustomFunction(String name, FunctionValueType result)

Initializes an instance of a custom function.

CustomFunction(String name, FunctionValueType result, Parameter[] parameters)

Initializes an instance of a custom function.

CustomFunction(String name, String description, FunctionValueType result, Parameter[] parameters)

Initializes an instance of a custom function.
Method Summary

Modifier and Type

Method

Description

abstract Object

evaluate(Object[] arguments, ICalcContext context)

Calculate the function.

boolean

getAcceptErrors()

Gets whether the function accepts error values as arguments.

boolean

getIsVolatile()

Gets whether this custom function is volatile.

void

setAcceptErrors(boolean acceptErrors)

Sets whether the function accepts error values as arguments.

void

setIsVolatile(boolean aVolatile)

Sets whether this custom function is volatile.

Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Details
- name
  
  public String name
Constructor Details
- CustomFunction
  
  public CustomFunction(String name, FunctionValueType result)
  
  Initializes an instance of a custom function.
  
  Parameters:
  
  name - The name of the custom function.
  
  result - Specifies the return type for the custom function.
- CustomFunction
  
  public CustomFunction(String name, FunctionValueType result, Parameter[] parameters)
  
  Initializes an instance of a custom function.
  This constructor passes null as the description to CustomFunction(String,String,FunctionValueType,Parameter[]).
  
  Parameters:
  
  name - The name of the custom function. Must not be null or empty.
  
  result - Specifies the return type for the custom function.
  
  parameters - Specifies the parameters for the custom function. Can be null.
  
  Throws:
  
  IllegalArgumentException - if name is null or empty.
- CustomFunction
  
  public CustomFunction(String name, String description, FunctionValueType result, Parameter[] parameters)
  
  Initializes an instance of a custom function.
  
  Parameters:
  
  name - The name of the custom function.
  
  description - The description of the custom function.
  
  result - Specifies the return type for the custom function.
  
  parameters - Specifies the parmeters for the custom function.

Method Details

setAcceptErrors

public void setAcceptErrors(boolean acceptErrors)
Sets whether the function accepts error values as arguments.
If this property is set to true, error values such as #N/A and #VALUE! can be passed to evaluate(Object[],ICalcContext) for custom handling. If it is set to false, the calculation engine returns the error immediately instead of passing the error argument to the custom function.
```
 CustomFunction function = new CustomFunction(
     "HANDLEERROR",
     FunctionValueType.Text,
     new Parameter[] { new Parameter(FunctionValueType.Object) }) {
     {
         setAcceptErrors(true);
     }

     public Object evaluate(Object[] arguments, ICalcContext context) {
         return arguments[0] instanceof CalcError ? "Handled error" : arguments[0];
     }
 };
 Workbook.AddCustomFunction(function, true);
 worksheet.getRange("A1").setFormula("=HANDLEERROR(1/0)");
 Object result = worksheet.getRange("A1").getValue();
 
```
Parameters:

acceptErrors - true if error values are passed to the custom function as arguments; false if an error argument causes calculation to return the error immediately.

getAcceptErrors

public boolean getAcceptErrors()

Gets whether the function accepts error values as arguments.

If this property returns true, error values such as #N/A and #VALUE! can be passed to evaluate(Object[],ICalcContext) for custom handling. If it returns false, the calculation engine returns the error immediately instead of passing the error argument to the custom function.


 CustomFunction function = new CustomFunction(
     "HANDLEERROR",
     FunctionValueType.Text,
     new Parameter[] { new Parameter(FunctionValueType.Object) }) {
     {
         setAcceptErrors(true);
     }

     public Object evaluate(Object[] arguments, ICalcContext context) {
         return arguments[0] instanceof CalcError ? "Handled error" : arguments[0];
     }
 };
 boolean acceptErrors = function.getAcceptErrors();
 Workbook.AddCustomFunction(function, true);
 worksheet.getRange("A1").setFormula("=HANDLEERROR(1/0)");
 Object result = worksheet.getRange("A1").getValue();

Returns:: true if error values are passed to the custom function as arguments; false if an error argument causes calculation to return the error immediately.

getIsVolatile

public boolean getIsVolatile()

Gets whether this custom function is volatile.

If this property returns true, formulas that call this custom function are recalculated whenever calculation is triggered, even when the arguments have not changed. If it returns false, calculation can reuse cached results for repeated calls with the same arguments.


 CustomFunction function = new CustomFunction("NEXTVALUE", FunctionValueType.Number) {
     private int calculationCount;
     {
         setIsVolatile(true);
     }

     public Object evaluate(Object[] arguments, ICalcContext context) {
         return (double) ++calculationCount;
     }
 };
 boolean isVolatile = function.getIsVolatile();
 Workbook.AddCustomFunction(function, true);
 worksheet.getRange("A1").setFormula("=NEXTVALUE()");
 Object firstValue = worksheet.getRange("A1").getValue();
 worksheet.getRange("A2").setValue("trigger recalculation");
 workbook.calculate();
 Object recalculatedValue = worksheet.getRange("A1").getValue();

Returns:: true if this custom function is volatile, false otherwise.

setIsVolatile

public void setIsVolatile(boolean aVolatile)
Sets whether this custom function is volatile.
By default, custom functions can reuse cached calculation results when they are evaluated repeatedly with the same arguments. Set this property to true to force the function to recalculate whenever the formula is evaluated instead of reusing a cached result. Set it to false to allow cached results to be reused.
Use getIsVolatile() to get the current setting.
```
 CustomFunction function = new CustomFunction("NEXTVALUE", FunctionValueType.Number) {
     private int calculationCount;
     {
         setIsVolatile(true);
     }

     public Object evaluate(Object[] arguments, ICalcContext context) {
         return (double) ++calculationCount;
     }
 };
 Workbook.AddCustomFunction(function, true);
 worksheet.getRange("A1").setFormula("=NEXTVALUE()");
 Object firstValue = worksheet.getRange("A1").getValue();
 worksheet.getRange("A2").setValue("trigger recalculation");
 workbook.calculate();
 Object recalculatedValue = worksheet.getRange("A1").getValue();
 
```
Parameters:

aVolatile - true to mark the custom function as volatile so it is recalculated every time it is evaluated; false to allow cached results to be reused.

evaluate

public abstract Object evaluate(Object[] arguments, ICalcContext context)

Calculate the function.


 CustomFunction function = new CustomFunction("DOUBLE", FunctionValueType.Number) {
     public Object evaluate(Object[] arguments, ICalcContext context) {
         return ((Number) arguments[0]).doubleValue() * 2;
     }
 };
 ICalcContext calcContext = new ICalcContext() {
     public IWorksheet getWorksheet() { return worksheet; }
     public int getRow() { return 0; }
     public int getColumn() { return 0; }
 };
 Object result = function.evaluate(new Object[] { 21d }, calcContext);

Parameters:: arguments - the value collection of the arguments; context - the context of the calculation.
Returns:: the result of the function.

Class CustomFunction

Field Summary

Constructor Summary

Method Summary

Methods inherited from class java.lang.Object

Field Details

name

Constructor Details

CustomFunction

CustomFunction

CustomFunction

Method Details

setAcceptErrors

getAcceptErrors

getIsVolatile

setIsVolatile

evaluate