 |
 |
 |
SpreadsheetGear | The Performance Spreadsheet Component Company |
| SpreadsheetGear.CustomFunctions Namespace : Function Class |
|
|
|
SpreadsheetGear | The Performance Spreadsheet Component Company |
| SpreadsheetGear.CustomFunctions Namespace : Function Class |
Override this class to implement a SpreadsheetGear for .NET custom function.
For a list of all members of this type, see Function members.
SpreadsheetGear.CustomFunctions.Function
[Visual Basic]
Public MustInherit Class Function [C#]
public abstract class Function [C++]
public __gc abstract class Function [C++/CLI]
public ref class Function abstract Evaluate must not use any API involving the workbook set containing the instance of the custom function being evaluated. However, custom functions may use one or more separate workbook sets without limitation.
Create a default custom function by specifying "*" as the name parameter to _ctor. Use CurrentFunctionName to retrieve the name of the function being evaluated.
[C#]
class Program
{
// Demonstrate the use of custom functions in SpreadsheetGear for .NET.
static void Main()
{
// Create a new empty workbook.
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
// Add the custom functions to the workbook set.
workbook.WorkbookSet.Add(MyAdd.MyAddSingleton);
workbook.WorkbookSet.Add(MySum.MySumSingleton);
// Get Sheet1 and its cells.
SpreadsheetGear.IWorksheet sheet1 = workbook.Worksheets[0];
SpreadsheetGear.IRange cells = sheet1.Cells;
// Assign values and formulas to cells.
cells["A1"].Value = 1.1;
cells["B1"].Value = 1.2;
cells["C1"].Formula = "=MYADD(A1,B1)";
cells["A2"].Value = 2.1;
cells["B2"].Value = 2.2;
cells["C2"].Formula = "=MYADD(A2,B2)";
cells["C3"].Formula = "=MYSUM(C1:C2)";
// Output the result of the custom functions.
System.Console.WriteLine("1.1+1.2+2.1+2.2={0}", cells["C3"].Text);
}
// A simple addition custom function.
public class MyAdd : SpreadsheetGear.CustomFunctions.Function
{
// Set to the one and only instance of MyAdd.
public static readonly MyAdd MyAddSingleton = new MyAdd();
// Add two numbers.
public override void Evaluate(
SpreadsheetGear.CustomFunctions.IArguments arguments,
SpreadsheetGear.CustomFunctions.IValue result)
{
// Verify that there are two arguments.
if (arguments.Count == 2)
// Get the two arguments as numbers, and add them.
result.Number = arguments.GetNumber(0) + arguments.GetNumber(1);
else
// Return ValueError.Value.
result.Error = SpreadsheetGear.ValueError.Value;
}
// Singleton class - so make the constructor private.
private MyAdd()
: base(
// The name of the custom function.
"MYADD",
// 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)
{
}
}
// A custom function which sums any number of simple or complex values.
public class MySum : SpreadsheetGear.CustomFunctions.Function
{
// Set to the one and only instance of MySum.
public static readonly MySum MySumSingleton = new MySum();
// Sums any number of values or ranges.
public override void Evaluate(
SpreadsheetGear.CustomFunctions.IArguments arguments,
SpreadsheetGear.CustomFunctions.IValue result)
{
// Initialize the sum to 0.
double sum = 0.0;
// for each argument.
for (int i = 0; i < arguments.Count; i++)
{
int rows;
int cols;
// Get the dimensions (returns 1x1 for simple values).
arguments.GetArrayDimensions(i, out rows, out cols);
// for each row.
for (int row = 0; row < rows; row++)
{
// for each column.
for (int col = 0; col < cols; col++)
{
// Get the value.
SpreadsheetGear.CustomFunctions.IValue val = arguments.GetArrayValue(i, row, col);
// Is it a number?
if (val.IsNumber)
// Add it to the sum.
sum += val.Number;
// Is it an error?
else if (val.IsError)
{
// Return an error immediately.
result.Error = val.Error;
return;
}
}
}
}
// Return the sum.
result.Number = sum;
}
// Singleton class - so make the constructor private.
private MySum()
// The name of the custom function.
: base("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]
Class Program
' Demonstrate the use of custom functions in SpreadsheetGear for .NET.
Shared Sub Main()
' Create a new empty workbook.
Dim workbook As SpreadsheetGear.IWorkbook = SpreadsheetGear.Factory.GetWorkbook()
' Add the custom functions to the workbook set.
workbook.WorkbookSet.Add(MyAdd.MyAddSingleton)
workbook.WorkbookSet.Add(MySum.MySumSingleton)
' Get Sheet1 and its cells.
Dim sheet1 As SpreadsheetGear.IWorksheet = workbook.Worksheets(0)
Dim cells As SpreadsheetGear.IRange = sheet1.Cells
' Assign values and formulas to cells.
cells("A1").Value = 1.1
cells("B1").Value = 1.2
cells("C1").Formula = "=MYADD(A1,B1)"
cells("A2").Value = 2.1
cells("B2").Value = 2.2
cells("C2").Formula = "=MYADD(A2,B2)"
cells("C3").Formula = "=MYSUM(C1:C2)"
' Output the result of the custom functions.
System.Console.WriteLine("1.1+1.2+2.1+2.2={0}", cells("C3").Text)
End Sub 'Main
' A simple addition custom function.
Public Class MyAdd
Inherits SpreadsheetGear.CustomFunctions.Function
' Set to the one and only instance of MyAdd.
Public Shared MyAddSingleton As New MyAdd
' Add two numbers.
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 = 2 Then
' Get the two arguments as numbers, and add them.
result.Number = arguments.GetNumber(0) + arguments.GetNumber(1)
Else
' Return ValueError.Value.
result.Error = SpreadsheetGear.ValueError.Value
End If
End Sub 'Evaluate
' Singleton class - so make the constructor private.
Private Sub New()
' The name of the custom function.
MyBase.New("MYADD", SpreadsheetGear.CustomFunctions.Volatility.Invariant, SpreadsheetGear.CustomFunctions.ValueType.Number)
End Sub 'New
End Class 'MyAdd
' A custom function which sums any number of simple or complex values.
Public Class MySum
Inherits SpreadsheetGear.CustomFunctions.Function
' Set to the one and only instance of MySum.
Public Shared MySumSingleton As New MySum
' Sums any number of values or ranges.
Public Overrides Sub Evaluate(ByVal arguments As SpreadsheetGear.CustomFunctions.IArguments, ByVal result As SpreadsheetGear.CustomFunctions.IValue)
' Initialize the sum to 0.
Dim sum As Double = 0.0
' for each argument.
Dim i As Integer
For i = 0 To arguments.Count - 1
Dim rows As Integer
Dim cols As Integer
' Get the dimensions (returns 1x1 for simple values).
arguments.GetArrayDimensions(i, rows, cols)
' for each row.
Dim row As Integer
For row = 0 To rows - 1
' for each column.
Dim col As Integer
For col = 0 To cols - 1
' Get the value.
Dim val As SpreadsheetGear.CustomFunctions.IValue = arguments.GetArrayValue(i, row, col)
' Is it a number?
If val.IsNumber Then
' Add it to the sum.
sum += val.Number
' Is it an error?
Else
If val.IsError Then
' Return an error immediately.
result.Error = val.Error
Return
End If
End If
Next col
Next row
Next i
' Return the sum.
result.Number = sum
End Sub 'Evaluate
' 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
End Class 'MySum
End Class 'Program
Namespace: SpreadsheetGear.CustomFunctions
Platforms: Windows 98, Windows NT 4.0, Windows Millennium Edition, Windows 2000, Windows XP Home Edition, Windows XP Professional, Windows Server 2003 family
Assembly: SpreadsheetGear (in SpreadsheetGear.dll)
Function Members | SpreadsheetGear.CustomFunctions Namespace
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.