User-Defined Functions (UDF)
- 2 minutes to read
Overview
The Spreadsheet API provides the capability to create your own custom functions. Custom functions are available for spreadsheet calculations, and can be used in formulas in the same manner as built-in functions. A custom function executes a server custom code that performs the required calculation and returns the result.
Custom functions supported by the Spreadsheet are global - they can be accessed from all spreadsheet documents opened on the server. So, are urged to register custom functions only once at the application level.
If a worksheet contains a custom function that is not recognized by the workbook (or MS Excel), the “#NAME!” error is displayed after the cell containing the function is recalculated. To replace custom function definitions with the corresponding calculated values when saving a workbook, set the workbook’s WorkbookExportOptions.CustomFunctionExportMode option to CustomFunctionExportMode.CalculatedValue.
Implementation
A custom function is an object that exposes the ICustomFunction interface. To create a custom function, inherit from this interface and implement the required properties and methods. You should specify the IFunction.Name, and input IFunction.Parameters and IFunction.ReturnType. All calculations in a custom function are performed in the IFunction.Evaluate method. The IFunction.Volatile property specifies whether a cell that contains a custom function should be reevaluated every time a spreadsheet is recalculated.
By specifying the number and type of input parameters, you enable the workbook to validate the formula entered. If the formula is missing required parameters, an error message is displayed.
To use a custom function in Spreadsheets within your web application, register your function using the static ASPxSpreadsheet.RegisterGlobalCustomFunction method. The code below demonstrates how this can be done in the Global.asax file’s Application_Start method.
void Application_Start(object sender, EventArgs e) {
...
ASPxSpreadsheet.RegisterGlobalCustomFunction(new DiscountFunction());
...
}
Limitations
A custom function called in a worksheet cell should not change the properties and characteristics of the worksheet. The IFunction.Evaluate method of the function has access to the EvaluationContext object, which provides information about the current worksheet and workbook. However, do not call methods or set properties that might perform the following actions.
- Insert, delete, or format cells
- Move, rename, delete, or add sheets to a workbook
- Add names to a workbook
- Change the values of other cells