[Expand]General Information
[Expand]WinForms Controls
[Collapse]ASP.NET Controls and MVC Extensions
 [Expand]Prerequisites
 [Expand]What's Installed
 [Expand]Common Concepts
 [Collapse]ASP.NET WebForms Controls
   Getting Started
  [Expand]Grid View
  [Expand]Tree List
  [Expand]Card View
  [Expand]Reporting
  [Expand]Chart Control
  [Expand]Pivot Grid
  [Collapse]Spreadsheet
   [Expand]Product Information
    Getting Started
   [Collapse]Concepts
    [Expand]Spreadsheet Document
     Supported Formats
     Import and Export
    [Expand]Cell Basics
    [Collapse]Spreadsheet Formulas
     [Collapse]Functions
       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)
      Operators
      Array Formulas
     Defined Names
     Data Validation
    [Expand]Data Presentation
     Pivot Table
    [Expand]Charting
     Printing
    [Expand]Mail Merge
     Protection
     Adaptivity
     Keyboard Shortcuts
   [Expand]Visual Elements
  [Expand]Rich Text Editor
  [Expand]Scheduler
  [Expand]Site Navigation and Layout
  [Expand]HTML Editor
  [Expand]Gauges
  [Expand]Vertical Grid
  [Expand]Data Editors
  [Expand]Docking and Popups
  [Expand]File Management
  [Expand]Data and Image Navigation
  [Expand]Multi-Use Site Controls
  [Expand]Spell Checker
  [Expand]Query Builder
 [Expand]ASP.NET MVC Extensions
 [Expand]Localization
 [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]Office File API
[Expand]Reporting
[Expand]Report and Dashboard Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]eXpress Persistent Objects
[Expand]CodeRush
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation
View this topic on docs.devexpress.com (Learn more)

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?​​​​​​​