Returns the specified value.
Syntax
Parameters
- index
- Specifies the zero based index of the argument.
- row
- Specifies the zero based row index of the value to get.
- column
- Specifies the zero based column index of the value to get.
Return Value
The specified range or array value.
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 Basic | Copy Code |
---|
Imports System
Imports SpreadsheetGear
Namespace ConsoleApplication
_
Class Program
Public Overloads Shared Sub Main()
Dim workbookSet As IWorkbookSet = Factory.GetWorkbookSet()
workbookSet.Add(MySum.MySumSingleton)
Dim workbook As IWorkbook = workbookSet.Workbooks.Add()
Dim worksheet As SpreadsheetGear.IWorksheet = workbook.Worksheets(0)
Dim cells As SpreadsheetGear.IRange = worksheet.Cells
cells("A1").Value = 1
cells("B1").Formula = "=MYSUM(OFFSET(A1,0,0,COUNT(A:A),1))"
Console.WriteLine(("Result calculated from MYSUM = " + cells("B1").Text + " (should be 1)."))
cells("A2").Value = 2
Console.WriteLine(("Result calculated from MYSUM = " + cells("B1").Text + " (should be 3)."))
End Sub
End Class
_
Public Class MySum
Inherits SpreadsheetGear.CustomFunctions.Function
Public Shared MySumSingleton As New MySum()
Public Overrides Sub Evaluate(ByVal arguments As SpreadsheetGear.CustomFunctions.IArguments, ByVal result As SpreadsheetGear.CustomFunctions.IValue)
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
result.Number = SumThem(numbers)
Else
result.Error = SpreadsheetGear.ValueError.Value
End If
End Sub
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
Private Sub New()
MyBase.New("MYSUM", SpreadsheetGear.CustomFunctions.Volatility.Invariant, SpreadsheetGear.CustomFunctions.ValueType.Number)
End Sub
End Class
End Namespace
|
Remarks
Requirements
Platforms: Windows Vista, Windows XP, Windows Server 2008, Windows Server 2003, Windows 2000, Windows Me and Windows 98, including 32 bit and 64 bit editions where applicable. SpreadsheetGear for .NET 1.x requires the Microsoft .NET Framework 1.1 or .NET 2.0 (works with .NET 3.x). SpreadsheetGear for .NET 2007 requires the Microsoft .NET Framework 2.0 (works with .NET 3.x).
See Also