SpreadsheetGear
Function Class
Members  Example  See Also  Send Feedback
SpreadsheetGear.CustomFunctions Namespace : Function Class

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 BasicCopy Code
Class Program
    ' Demonstrate the use of custom functions in SpreadsheetGear.
    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

Remarks

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 IArguments.CurrentFunctionName to retrieve the name of the function being evaluated.

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

Copyright © 2003-2009 SpreadsheetGear LLC. All Rights Reserved.Help Powered by Innovasys   
SpreadsheetGear is a registered trademark of SpreadsheetGear LLC.
Microsoft, Microsoft Excel and Visual Studio are trademarks or registered trademarks of Microsoft Corporation.