SpreadsheetGear.CustomFunctions Namespace > IArguments Interface : GetArrayDimensions Method |
'Declaration
Sub GetArrayDimensions( _ ByVal index As System.Integer, _ ByRef rows As System.Integer, _ ByRef columns As System.Integer _ )
'Usage
Dim instance As IArguments Dim index As System.Integer Dim rows As System.Integer Dim columns As System.Integer instance.GetArrayDimensions(index, rows, columns)
procedure GetArrayDimensions( index: System.Integer; Out rows: System.Integer; Out columns: System.Integer );
using System; using SpreadsheetGear; namespace ConsoleApplication { /// <summary> /// Demonstrate a custom function which sums the range of cells or values passed to it. /// </summary> class Program { static void Main(string[] args) { // Create a new empty workbook. IWorkbookSet workbookSet = Factory.GetWorkbookSet(); // Add the custom function to the workbook set. workbookSet.Add(MySum.MySumSingleton); // Add a workbook to the workbook set. IWorkbook workbook = workbookSet.Workbooks.Add(); // Get the first sheet and it's cells SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[0]; SpreadsheetGear.IRange cells = worksheet.Cells; // Assign values and formulas to cells. cells["A1"].Value = 1; // The OFFSET(...) function enables creation of a range equal to // the number of rows which have values in column A and one column. cells["B1"].Formula = "=MYSUM(OFFSET(A1,0,0,COUNT(A:A),1))"; Console.WriteLine("Result calculated from MYSUM = " + cells["B1"].Value + " (should be 1)."); // Add another value to column A. cells["A2"].Value = 2; Console.WriteLine("Result calculated from MYSUM = " + cells["B1"].Value + " (should be 3)."); } } // A simple SUM custom function. public class MySum : SpreadsheetGear.CustomFunctions.Function { // Set to the one and only instance of MySum. public static readonly MySum MySumSingleton = new MySum(); // Sum the numbers in a range or array. public override void Evaluate( SpreadsheetGear.CustomFunctions.IArguments arguments, SpreadsheetGear.CustomFunctions.IValue result) { // Verify that there are two arguments. if (arguments.Count == 1) { int rowCount; int colCount; arguments.GetArrayDimensions(0, out rowCount, out colCount); double[,] numbers = new double[rowCount, colCount]; for (int row = 0; row < rowCount; row++) { for (int col = 0; col < colCount; col++) { SpreadsheetGear.CustomFunctions.IValue value = arguments.GetArrayValue(0, row, col); if (value.Type == SpreadsheetGear.CustomFunctions.ValueType.Number) numbers[row, col] = value.Number; } } // Get the two arguments as numbers, and sum them. result.Number = SumThem(numbers); } else // Return ValueError.Value. result.Error = SpreadsheetGear.ValueError.Value; } private double SumThem(double[,] numbers) { double sum = 0.0; int rowCount = numbers.GetUpperBound(0) + 1; int colCount = numbers.GetUpperBound(1) + 1; for (int row = 0; row < rowCount; row++) for (int col = 0; col < colCount; col++) sum += numbers[row, col]; return sum; } // Singleton class - so make the constructor private. private MySum() : base( // The name of the custom function. "MYSUM", // For a given set of inputs, this function always returns the same value. SpreadsheetGear.CustomFunctions.Volatility.Invariant, // This function returns a number. SpreadsheetGear.CustomFunctions.ValueType.Number) { } } }
Imports System Imports SpreadsheetGear Namespace ConsoleApplication _ '/ <summary> '/ Demonstrate a custom function which sums the range of cells or values passed to it. '/ </summary> Class Program 'Entry point which delegates to C-style main Private Function Public Overloads Shared Sub Main() ' Create a new empty workbook. Dim workbookSet As IWorkbookSet = Factory.GetWorkbookSet() ' Add the custom function to the workbook set. workbookSet.Add(MySum.MySumSingleton) ' Add a workbook to the workbook set. Dim workbook As IWorkbook = workbookSet.Workbooks.Add() ' Get the first sheet and it's cells Dim worksheet As SpreadsheetGear.IWorksheet = workbook.Worksheets(0) Dim cells As SpreadsheetGear.IRange = worksheet.Cells ' Assign values and formulas to cells. cells("A1").Value = 1 ' The OFFSET(...) function enables creation of a range equal to ' the number of rows which have values in column A and one column. cells("B1").Formula = "=MYSUM(OFFSET(A1,0,0,COUNT(A:A),1))" Console.WriteLine(("Result calculated from MYSUM = " + cells("B1").Text + " (should be 1).")) ' Add another value to column A. cells("A2").Value = 2 Console.WriteLine(("Result calculated from MYSUM = " + cells("B1").Text + " (should be 3).")) End Sub 'Main End Class 'Program _ ' A simple SUM custom function. Public Class MySum Inherits SpreadsheetGear.CustomFunctions.Function ' Set to the one and only instance of MySum. Public Shared MySumSingleton As New MySum() ' Sum the numbers in a range or array. Public Overrides Sub Evaluate(ByVal arguments As SpreadsheetGear.CustomFunctions.IArguments, ByVal result As SpreadsheetGear.CustomFunctions.IValue) ' Verify that there are two arguments. If arguments.Count = 1 Then Dim rowCount As Integer Dim colCount As Integer arguments.GetArrayDimensions(0, rowCount, colCount) Dim numbers(rowCount, colCount) As Double Dim row As Integer For row = 0 To rowCount - 1 Dim col As Integer For col = 0 To colCount - 1 Dim value As SpreadsheetGear.CustomFunctions.IValue = arguments.GetArrayValue(0, row, col) If value.Type = SpreadsheetGear.CustomFunctions.ValueType.Number Then numbers(row, col) = value.Number End If Next col Next row ' Get the two arguments as numbers, and sum them. result.Number = SumThem(numbers) ' Return ValueError.Value. Else result.Error = SpreadsheetGear.ValueError.Value End If End Sub 'Evaluate Private Function SumThem(ByVal numbers(,) As Double) As Double Dim sum As Double = 0.0 Dim rowCount As Integer = numbers.GetUpperBound(0) + 1 Dim colCount As Integer = numbers.GetUpperBound(1) + 1 Dim row As Integer For row = 0 To rowCount - 1 Dim col As Integer For col = 0 To colCount - 1 sum += numbers(row, col) Next col Next row Return sum End Function 'SumThem ' Singleton class - so make the constructor private. Private Sub New() ' The name of the custom function. MyBase.New("MYSUM", SpreadsheetGear.CustomFunctions.Volatility.Invariant, SpreadsheetGear.CustomFunctions.ValueType.Number) End Sub 'New ' For a given set of inputs, this function always returns the same value. End Class 'MySum End Namespace 'ConsoleApplication ' This function returns a number.
Target Platforms: Windows 7, Windows Vista SP1 or later, Windows XP SP3, Windows Server 2008 (Server Core not supported), Windows Server 2008 R2 (Server Core supported with SP1 or later), Windows Server 2003 SP2