User-Defined Functions (UDF)
- 5 minutes to read
Overview
The Spreadsheet API allows you to create custom functions. Custom functions are available for spreadsheet calculations and can be used in formulas in the same manner as built-in functions. The main difference is that custom functions are not saved within a workbook. They should be added to the Workbook.CustomFunctions or Workbook.GlobalCustomFunctions collection.
Note
Add a custom function to the CustomFunctionCollection collection before you load a document.
If a worksheet contains a custom function that is not recognized by the Spreadsheet (or Microsoft® Excel®), the “#NAME!” error is displayed after the function is calculated. To replace a custom function definition with a calculated value when you save the document, set the WorkbookExportOptions.CustomFunctionExportMode option to CustomFunctionExportMode.CalculatedValue.
Implementation
A custom function is an object that exposes the ICustomFunction interface. To create a custom function, derive it from this interface and implement necessary properties and methods. You should specify the name of the function (IFunction.Name), its arguments (IFunction.Parameters) and return type (IFunction.ReturnType). The IFunction.Volatile property specifies whether the custom function is volatile and should be recalculated each time the Spreadsheet calculates a workbook. The IFunction.Evaluate method performs required calculations.
If you implement ICustomCalculationService, the Spreadsheet uses only one thread to calculate built-in and custom formulas.
The Spreadsheet validates the number and type of arguments for a custom function. If function parameters are missing, an exception occurs.
Custom functions are stored in the Workbook.CustomFunctions collection. To use a custom function in calculations, add an instance of your function to this collection.
Example: Create a User-Defined Function
The following code sample implements a custom function called SPHEREMASS, which calculates the mass of a sphere made of a material with a specified density. If the density is not provided, the density of water is used.
using DevExpress.Spreadsheet;
using DevExpress.Spreadsheet.Functions;
using DevExpress.XtraSpreadsheet;
//...
public class SphereMassFunction : ICustomFunction {
const string functionName = "SPHEREMASS";
readonly ParameterInfo[] functionParameters;
public SphereMassFunction()
{
// Missing optional parameters do not result in an error message.
this.functionParameters = new ParameterInfo[] { new ParameterInfo(ParameterType.Value, ParameterAttributes.Required),
new ParameterInfo(ParameterType.Value, ParameterAttributes.Optional)};
}
public string Name { get { return functionName; } }
ParameterInfo[] IFunction.Parameters { get { return functionParameters; } }
ParameterType IFunction.ReturnType { get { return ParameterType.Value; } }
bool IFunction.Volatile { get { return false; } }
ParameterValue IFunction.Evaluate(IList<ParameterValue> parameters, EvaluationContext context)
{
double radius;
double density = 1000;
ParameterValue radiusParameter;
ParameterValue densityParameter;
if (parameters.Count == 2)
{
densityParameter = parameters[1];
if (densityParameter.IsError)
return densityParameter;
else
density = densityParameter.NumericValue;
}
radiusParameter = parameters[0];
if (radiusParameter.IsError)
return radiusParameter;
else
radius = radiusParameter.NumericValue;
return (4 * Math.PI) / 3 * Math.Pow(radius,3) * density;
}
string IFunction.GetName(CultureInfo culture)
{
return functionName;
}
}
//...
Workbook workbook = new Workbook();
// Create a custom function and add it to the global scope.
SphereMassFunction customFunction = new SphereMassFunction();
var globalFunctions = workbook.Functions.GlobalCustomFunctions;
if (!globalFunctions.Contains(customFunction.Name))
globalFunctions.Add(customFunction);
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1:H1"].ColumnWidthInCharacters = 12;
worksheet.Range["A1:H1"].Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
worksheet.DefinedNames.Add("seawater", "1025");
worksheet["A1"].Value = "Radius, m";
worksheet["B1"].Value = "Material";
worksheet["C1"].Value = "Mass, kg";
worksheet["A2"].Value = 0.1;
worksheet["B2"].Value = "";
worksheet["C2"].FormulaInvariant = "=SPHEREMASS(A2)";
worksheet["C2"].NumberFormat = "#.##";
worksheet["A3"].Value = 0.1;
worksheet["B3"].Value = "Seawater";
worksheet["C3"].FormulaInvariant = "=SPHEREMASS(A3, seawater)";
worksheet["C3"].NumberFormat = "#.##";
}
finally
{
workbook.EndUpdate();
}
Limitations
A custom function should not change properties and characteristics of a worksheet. The function’s IFunction.Evaluate method has access to the EvaluationContext object that contains information about the current workbook and worksheet. However, do not call methods or specify properties that can execute the following actions:
- Insert, delete, or format cells;
- Move, rename, delete, or add sheets to a workbook;
- Add names to a workbook;
- Change values of other cells.