//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); GrapeCity.Documents.Excel.Workbook.AddCustomFunction(new MySumFunctionX()); IWorksheet worksheet = workbook.Worksheets[0]; worksheet.Range["A1"].Value = 1; worksheet.Range["B1"].Value = 2; worksheet.Range["C1"].Formula = "=MySum(A1:B1, 2, {3,4})"; //Range["C1"]'s value is 12. var result = worksheet.Range["C1"].Value; /* Implementation of MySumFunctionX public class MySumFunctionX : CustomFunction { public MySumFunctionX() : base("MYSUM", FunctionValueType.Number, CreateParameters()) { } private static Parameter[] CreateParameters() { Parameter[] parameters = new Parameter[254]; for (int i = 0; i < 254; i++) { parameters[i] = new Parameter(FunctionValueType.Object); } return parameters; } public override object Evaluate(object[] arguments, ICalcContext context) { double sum = 0d; foreach (var argument in arguments) { foreach (var item in Enumerate(argument)) { if (item is CalcError) { return item; } if (item is double) { sum += (double)item; } } } return sum; } private static IEnumerable<object> Enumerate(object obj) { if (obj is IEnumerable<object>) { foreach (var item in obj as IEnumerable<object>) { foreach (var item2 in Enumerate(item)) { yield return item2; } } } else if (obj is object[,]) { var array = obj as object[,]; int rowCount = array.GetLength(0); int colCount = array.GetLength(1); for (int i = 0; i < rowCount; i++) { for (int j = 0; j < colCount; j++) { yield return array[i, j]; } } } if (obj != null) { yield return obj; } } } */
' Create a new Workbook Dim workbook As New Workbook Excel.Workbook.AddCustomFunction(New MySumFunctionX) Dim worksheet As IWorksheet = workbook.Worksheets(0) worksheet.Range!A1.Value = 1 worksheet.Range!B1.Value = 2 worksheet.Range!C1.Formula = "=MySum(A1:B1, 2, {3,4})" ' Range!C1.Value = 12. Dim result = worksheet.Range!C1.Value ' Implementation of MySumFunctionX ' ' Public Class MySumFunctionX ' Inherits CustomFunction ' Public Sub New() ' MyBase.New("MYSUM", FunctionValueType.Number, CreateParameters()) ' End Sub ' Private Shared Function CreateParameters() As Parameter() ' Dim parameters(253) As Parameter ' For i As Integer = 0 To 253 ' parameters(i) = New Parameter(FunctionValueType.Object) ' Next ' Return parameters ' End Function ' Public Overrides Function Evaluate(arguments As Object(), context As ICalcContext) As Object ' Dim sum As Double = 0R ' For Each argument In arguments ' For Each item In Enumerate(argument) ' If TypeOf item Is CalcError Then ' Return item ' End If ' If TypeOf item Is Double Then ' sum += CDbl(item) ' End If ' Next item ' Next argument ' Return sum ' End Function ' Private Shared Iterator Function Enumerate(obj As Object) As IEnumerable(Of Object) ' Dim objects = TryCast(obj, IEnumerable(Of Object)) ' If objects IsNot Nothing Then ' For Each item In objects ' For Each item2 In Enumerate(item) ' Yield item2 ' Next item2 ' Next item ' Else ' Dim array = TryCast(obj, Object(,)) ' If array IsNot Nothing Then ' Dim rowCount As Integer = array.GetLength(0) ' Dim colCount As Integer = array.GetLength(1) ' For i As Integer = 0 To rowCount - 1 ' For j As Integer = 0 To colCount - 1 ' Yield array(i, j) ' Next j ' Next i ' End If ' End If ' If obj IsNot Nothing Then ' Yield obj ' End If ' End Function ' End Class