SpreadsheetGear
GetArrayDimensions Method
See Also  Example Send Feedback
SpreadsheetGear.CustomFunctions Namespace > IArguments Interface : GetArrayDimensions Method

index
Specifies the zero based index of the argument.
rows
Set to the number of rows in the specified argument, or one for a simple argument.
columns
Set to the number columns in the specified argument, or one for a simple argument.
Returns the dimensions, consisting of the number of rows and columns, of the specified argument.

Syntax

Visual Basic (Declaration) 
Sub GetArrayDimensions( _
   ByVal index As Integer, _
   ByRef rows As Integer, _
   ByRef columns As Integer _
)
C# 
void GetArrayDimensions( 
   int index,
   out int rows,
   out int columns
)

Parameters

index
Specifies the zero based index of the argument.
rows
Set to the number of rows in the specified argument, or one for a simple argument.
columns
Set to the number columns in the specified argument, or one for a simple argument.

Example

C#Copy Code
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)
       {
       }
   }
}
    
Visual BasicCopy Code
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.

Remarks

This method returns the dimensions of ranges and arrays. For simple values, one is returned for rows and columns.

Requirements

Platforms: Windows 2000, Windows XP, Windows Vista, Windows Server 2003 and Windows Server 2008. SpreadsheetGear 2009 requires the Microsoft .NET Framework 2.0 and supports .NET 3.0 and .NET 3.5.

See Also

Copyright © 2003-2009 SpreadsheetGear LLC. All Rights Reserved.Help Powered by Innovasys   
SpreadsheetGear is a registered trademark of SpreadsheetGear LLC.
Microsoft, Microsoft Excel and Visual Studio are trademarks or registered trademarks of Microsoft Corporation.