Log In
[Expand]Welcome to DevExpress .NET Documentation
[Expand]WinForms Controls
[Collapse]ASP.NET Controls and MVC Extensions
 [Expand]What's Installed
 [Expand]Common Concepts
 [Collapse]ASP.NET WebForms Controls
   Getting Started
  [Expand]Chart Control
  [Expand]Grid View
  [Expand]Card View
  [Expand]Vertical Grid
   [Expand]Product Information
    Getting Started
    [Expand]Spreadsheet Document
     Supported Formats
     Import and Export
    [Expand]Cell Basics
    [Collapse]Spreadsheet Formulas
       Mathematical Functions
       Statistical Functions
       Date and Time Functions
       Text Functions
       Financial Functions
       Logical Functions
       Lookup and Reference Functions
       Engineering Functions
       Information Functions
       Compatibility Functions
       Database Functions
       Web Functions
       User-Defined Functions (UDF)
      Array Formulas
     Defined Names
     Data Validation
    [Expand]Data Presentation
    [Expand]Mail Merge
     Keyboard Shortcuts
   [Expand]Visual Elements
  [Expand]Rich Text Editor
  [Expand]Image and Data Browsing
  [Expand]Docking and Popups
  [Expand]Site Navigation and Layout
  [Expand]File Management
  [Expand]Multi-Use Site Controls
  [Expand]HTML Editor
  [Expand]Pivot Grid
  [Expand]Tree List
  [Expand]Data Editors
  [Expand]Spell Checker
  [Expand]Query Builder
 [Expand]ASP.NET MVC Extensions
 [Expand]Redistribution and Deployment
  Get More Help
 [Expand]API Reference
[Expand]ASP.NET Bootstrap Controls
[Expand]ASP.NET Core Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Document Server
[Expand]Report Server
[Expand]eXpressApp Framework
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

User-Defined Functions (UDF)

Expanded 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.

Expanded 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 ASPxSpreadsheet.RegisterGlobalCustomFunction static method. The code below demonstrates how this can be done in the Global.asax file's Application_Start method.

Expanded 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

Expanded See Also

Is this topic helpful?​​​​​​​