Override this class to implement a SpreadsheetGear for .NET custom function.
Syntax
C# | |
---|
public abstract class Function |
Example
C# | Copy Code |
---|
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 | 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 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).
Assembly: SpreadsheetGear (in SpreadsheetGear.dll)
See Also