This example implements a custom function (UDF) that returns an array. The TESTARRAY function gets a range of cells, multiplies relative indexes of a cell in the range by the cell value and returns the resulting array.
To display the result in cells, each cell should contain an array formula with that function. To insert an array formula, the example uses the cell’s Range.ArrayFormulaInvariant property.
You can insert array formulas using the keyboard shortcut: select a range for the output array, enter a formula, and press Ctrl+Shift+Enter.
TestArrayCustomFunction customFunction = new TestArrayCustomFunction();
if (!spreadsheet.Document.Functions.CustomFunctions.Contains(customFunction.Name))
spreadsheet.Document.Functions.CustomFunctions.Add(customFunction);
spreadsheet.ActiveWorksheet.Range["$A$4:$E$5"].ArrayFormulaInvariant = "TESTARRAY(A1:E2)";
spreadsheet.ActiveWorksheet.Range["E7"].Formula = "SUM(TESTARRAY(A1:E2))";
public class TestArrayCustomFunction : ICustomFunction {
private string name = "TESTARRAY";
private ParameterInfo[] parameters = new ParameterInfo[] { new ParameterInfo(ParameterType.Array, ParameterAttributes.Required) };
private ParameterType return_type = ParameterType.Array;
public string Name { get { return this.name; } }
public ParameterInfo[] Parameters { get { return this.parameters; } }
public ParameterType ReturnType { get { return this.return_type; } }
public bool Volatile { get { return false; } }
public string GetName(CultureInfo culture) {
return Name;
}
public ParameterValue Evaluate(IList<ParameterValue> parameters, EvaluationContext context) {
CellValue[,] args = parameters[0].ArrayValue;
int xDim = args.GetLength(0);
int yDim = args.GetLength(1);
CellValue[,] result = new CellValue[xDim, yDim];
for (int n = 0; n < args.GetLength(0); n++) {
for (int m = 0; m < result.GetLength(1); m++) {
double coeff = (args[n, m].NumericValue == 0) ? 1 : args[n, m].NumericValue;
result[n, m] = (n + 1) * (m + 1) * coeff;
}
}
return result;
}
}
Public Class TestArrayCustomFunction
Implements ICustomFunction
Private name_Renamed As String = "TESTARRAY"
Private parameters_Renamed() As ParameterInfo = { New ParameterInfo(ParameterType.Array, ParameterAttributes.Required) }
Private return_type As ParameterType = ParameterType.Array
Public ReadOnly Property Name() As String Implements ICustomFunction.Name
Get
Return Me.name_Renamed
End Get
End Property
Public ReadOnly Property Parameters() As ParameterInfo() Implements ICustomFunction.Parameters
Get
Return Me.parameters_Renamed
End Get
End Property
Public ReadOnly Property ReturnType() As ParameterType Implements ICustomFunction.ReturnType
Get
Return Me.return_type
End Get
End Property
Public ReadOnly Property Volatile() As Boolean Implements ICustomFunction.Volatile
Get
Return False
End Get
End Property
Public Function GetName(ByVal culture As CultureInfo) As String Implements ICustomFunction.GetName
Return Name
End Function
Public Function Evaluate(ByVal parameters As IList(Of ParameterValue), ByVal context As EvaluationContext) As ParameterValue Implements ICustomFunction.Evaluate
Dim args(,) As CellValue = parameters(0).ArrayValue
Dim xDim As Integer = args.GetLength(0)
Dim yDim As Integer = args.GetLength(1)
Dim result(xDim - 1, yDim - 1) As CellValue
For n As Integer = 0 To args.GetLength(0) - 1
For m As Integer = 0 To result.GetLength(1) - 1
Dim coeff As Double = If(args(n, m).NumericValue = 0, 1, args(n, m).NumericValue)
result(n, m) = (n + 1) * (m + 1) * coeff
Next m
Next n
Return result
End Function
End Class
Dim customFunction As New TestArrayCustomFunction()
If Not spreadsheet.Document.Functions.CustomFunctions.Contains(customFunction.Name) Then
spreadsheet.Document.Functions.CustomFunctions.Add(customFunction)
End If
spreadsheet.ActiveWorksheet.Range("$A$4:$E$5").ArrayFormulaInvariant = "TESTARRAY(A1:E2)"
spreadsheet.ActiveWorksheet.Range("E7").Formula = "SUM(TESTARRAY(A1:E2))"