 |
 |
SpreadsheetGear | The Performance Spreadsheet Component Company |
| SpreadsheetGear.CustomFunctions Namespace > IArguments Interface : GetArrayValue Method |
|
|
SpreadsheetGear | The Performance Spreadsheet Component Company |
| SpreadsheetGear.CustomFunctions Namespace > IArguments Interface : GetArrayValue Method |
Returns the specified value.
[Visual Basic]
Overridable Function GetArrayValue( _
ByVal index As Integer , _
ByVal row As Integer , _
ByVal column As Integer _
) As IValueThe specified range or array value.
For single values, arguments.GetArrayValue(index, 0, 0) returns the same value as arguments[index].
[C#]
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 Basic]
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.
Platforms: Windows 98, Windows NT 4.0, Windows Millennium Edition, Windows 2000, Windows XP Home Edition, Windows XP Professional, Windows Server 2003 family
IArguments Interface | IArguments Members
See our live Excel Reporting, Excel Charting, Excel to DataGrid and Excel Calculation Samples.
Copyright © 2003-2007 SpreadsheetGear LLC. All Rights Reserved.
SpreadsheetGear and Spreadsheet Gear are trademarks of SpreadsheetGear LLC.