officefileapi-117876-spreadsheet-document-api-formulas-functions-user-defined-functions-udf.md
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.
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.
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();
}
Imports DevExpress.Spreadsheet
Imports DevExpress.Spreadsheet.Functions
Imports DevExpress.XtraSpreadsheet
'...
Public Class SphereMassFunction
Implements ICustomFunction
Private Const functionName As String = "SPHEREMASS"
Private ReadOnly functionParameters() As ParameterInfo
Public Sub New()
' Missing optional parameters do not result in an error message.
Me.functionParameters = New ParameterInfo() {
New ParameterInfo(ParameterType.Value, ParameterAttributes.Required),
New ParameterInfo(ParameterType.Value, ParameterAttributes.Optional)
}
End Sub
Public ReadOnly Property Name() As String
Get
Return functionName
End Get
End Property
Private ReadOnly Property IFunction_Parameters() As ParameterInfo() Implements IFunction.Parameters
Get
Return functionParameters
End Get
End Property
Private ReadOnly Property IFunction_ReturnType() As ParameterType Implements IFunction.ReturnType
Get
Return ParameterType.Value
End Get
End Property
Private ReadOnly Property IFunction_Volatile() As Boolean Implements IFunction.Volatile
Get
Return False
End Get
End Property
Private Function IFunction_Evaluate(ByVal parameters As IList(Of ParameterValue), ByVal context As EvaluationContext) As ParameterValue Implements IFunction.Evaluate
Dim radius As Double
Dim density As Double = 1000
Dim radiusParameter As ParameterValue
Dim densityParameter As ParameterValue
If parameters.Count = 2 Then
densityParameter = parameters(1)
If densityParameter.IsError Then
Return densityParameter
Else
density = densityParameter.NumericValue
End If
End If
radiusParameter = parameters(0)
If radiusParameter.IsError Then
Return radiusParameter
Else
radius = radiusParameter.NumericValue
End If
Return (4 * Math.PI) / 3 * Math.Pow(radius,3) * density
End Function
Private Function IFunction_GetName(ByVal culture As CultureInfo) As String Implements IFunction.GetName
Return functionName
End Function
End Class
'...
Dim workbook As Workbook = New Workbook()
' Create a custom function and add it to the global scope.
Private customFunction As New SphereMassFunction()
Private globalFunctions = workbook.Functions.GlobalCustomFunctions
If Not globalFunctions.Contains(customFunction.Name) Then
globalFunctions.Add(customFunction)
End If
workbook.BeginUpdate()
Try
Dim worksheet As 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()
End Try
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: