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)
        {
        }
    }
}