Override this class to implement a SpreadsheetGear custom function.
Syntax
Visual Basic (Declaration) | |
---|
Public MustInherit Class Function |
C# | |
---|
public abstract class Function |
Example
C# | Copy Code |
---|
class Program
{
// Demonstrate the use of custom functions in SpreadsheetGear.
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 | Copy Code |
---|
Class Program
Shared Sub Main()
Dim workbook As SpreadsheetGear.IWorkbook = SpreadsheetGear.Factory.GetWorkbook()
workbook.WorkbookSet.Add(MyAdd.MyAddSingleton)
workbook.WorkbookSet.Add(MySum.MySumSingleton)
Dim sheet1 As SpreadsheetGear.IWorksheet = workbook.Worksheets(0)
Dim cells As SpreadsheetGear.IRange = sheet1.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)"
System.Console.WriteLine("1.1+1.2+2.1+2.2={0}", cells("C3").Text)
End Sub
Public Class MyAdd
Inherits SpreadsheetGear.CustomFunctions.Function
Public Shared MyAddSingleton As New MyAdd
Public Overrides Sub Evaluate(ByVal arguments As SpreadsheetGear.CustomFunctions.IArguments, ByVal result As SpreadsheetGear.CustomFunctions.IValue)
If arguments.Count = 2 Then
result.Number = arguments.GetNumber(0) + arguments.GetNumber(1)
Else
result.Error = SpreadsheetGear.ValueError.Value
End If
End Sub
Private Sub New()
MyBase.New("MYADD", SpreadsheetGear.CustomFunctions.Volatility.Invariant, SpreadsheetGear.CustomFunctions.ValueType.Number)
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)
Dim sum As Double = 0.0
Dim i As Integer
For i = 0 To arguments.Count - 1
Dim rows As Integer
Dim cols As Integer
arguments.GetArrayDimensions(i, rows, cols)
Dim row As Integer
For row = 0 To rows - 1
Dim col As Integer
For col = 0 To cols - 1
Dim val As SpreadsheetGear.CustomFunctions.IValue = arguments.GetArrayValue(i, row, col)
If val.IsNumber Then
sum += val.Number
Else
If val.IsError Then
result.Error = val.Error
Return
End If
End If
Next col
Next row
Next i
result.Number = sum
End Sub
Private Sub New()
MyBase.New("MYSUM", SpreadsheetGear.CustomFunctions.Volatility.Invariant, SpreadsheetGear.CustomFunctions.ValueType.Number)
End Sub
End Class
End Class
|
Remarks
Inheritance Hierarchy
System.Object
SpreadsheetGear.CustomFunctions.Function
Requirements
Namespace: SpreadsheetGear.CustomFunctions
Platforms: Windows 2000, Windows XP, Windows Vista, Windows Server 2003 and Windows Server 2008. SpreadsheetGear 2008 requires the Microsoft .NET Framework 2.0 and supports .NET 3.0 and .NET 3.5.
Assembly: SpreadsheetGear (in SpreadsheetGear.dll)
See Also