wpf-16376-controls-and-libraries-spreadsheet-examples-formulas-how-to-create-a-simple-custom-function.md
This example demonstrates how to create a custom worksheet function. A custom function is an object that implements the ICustomFunction interface. The IFunction.Evaluate method performs all required calculations. To use a custom function, add it to the IWorkbook.CustomFunctions collection.
Note
Custom functions are not saved in workbook files.
The following code sample demonstrates how to implement 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;
public class SphereMassFunction : Object, ICustomFunction
{
const string functionName = "SPHEREMASS";
readonly ParameterInfo[] functionParameters;
public SphereMassFunction()
{
// Missing optional parameters do not result in 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; } }
// Reevaluate cells on every recalculation.
bool IFunction.Volatile { get { return true; } }
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;
}
}
//...
// Create a custom function and add it to the global scope.
SphereMassFunction customFunction = new SphereMassFunction();
if (!workbook.CustomFunctions.Contains(customFunction.Name))
workbook.CustomFunctions.Add(customFunction);
// Add data to a workbook
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)";
}
finally
{
workbook.EndUpdate();
}
Imports DevExpress.Spreadsheet
Imports DevExpress.Spreadsheet.Functions
Public Class SphereMassFunction
Inherits Object
Implements ICustomFunction
Private Const functionName As String = "SPHEREMASS"
Private ReadOnly functionParameters() As ParameterInfo
Public Sub New()
' Missing optional parameters do not result in 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
' Reevaluate cells on every recalculation.
Private ReadOnly Property IFunction_Volatile() As Boolean Implements IFunction.Volatile
Get
Return True
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
'...
' Create a custom function and add it to the global scope.
Private customFunction As New SphereMassFunction()
If Not workbook.CustomFunctions.Contains(customFunction.Name) Then
workbook.CustomFunctions.Add(customFunction)
End If
' Add data to a workbook
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)"
Finally
workbook.EndUpdate()
End Try
Tip
Refer to the following article for information on how to create a custom function that uses a cell range as a parameter:
Read Tutorial: How to: Create a Custom Function that Returns an Array