Documents for Excel .NET Edition | GCDocuments
Features / Custom Functions
In This Topic
    Custom Functions
    In This Topic

    GcExcel provides support for adding custom functions, thus enabling users to implement custom arithmetic logic to spreadsheets. These functions run extremely fast, can make web service calls, look similar to the native Excel functions, and can be used across all Excel platforms including major operating systems (Windows, Mac, Mobile OS and Office: both online and offline).

    For instance, you can use company’s proprietary functions, apply a nested formula with custom functions, or use a combination of standard built-in functions in order to handle complex spreadsheet calculations.

    To implement custom functions in GcExcel .NET, you need to create a derived class from the CustomFunction class and declare the custom function in the new class along with the function name, return type, and parameters.

    Using Code

    Shared below are some examples of custom functions that can be created and used to perform complex calculation tasks:  

    Example 1: Conditional Sum Function

    Refer to the following example code to create and use custom conditional sum function in your spreadsheet. This function can sum cell values based on specific display format or style (like cells with interior color as red).

    C#
    Copy Code
    // Step 1- Defining custom function: MyConditionalSum
    // Creating a new class MyConditionalSumFunctionX by inheriting the CustomFunction class
    public class MyConditionalSumFunctionX : CustomFunction
    {
        public MyConditionalSumFunctionX() : base("MyConditionalSum", FunctionValueType.Number, CreateParameters())
        {
        }
        private static Parameter[] CreateParameters()
        {
            Parameter[] parameters = new Parameter[254];
            for (int i = 0; i < 254; i++)
            {
                parameters[i] = new Parameter(FunctionValueType.Object, true);
            }
            return parameters;
        }
        public override object Evaluate(object[] arguments, ICalcContext context)
        {
            double sum = 0d;
            foreach (var argument in arguments)
            {
                foreach (var item in Enumerate(argument))
                {
                    if (item is CalcError)
                    {
                        return item;
                    }
                    if (item is double)
                    {
                        sum += (double)item;
                    }
                }
            }
            return sum;
        }
        private static IEnumerable<object> Enumerate(object obj)
        {
            if (obj is IEnumerable<object>)
            {
                foreach (var item in obj as IEnumerable<object>)
                {
                    foreach (var item2 in Enumerate(item))
                    {
                        yield return item2;
                    }
                }
            }
            else if (obj is object[,])
            {
                var array = obj as object[,];
                int rowCount = array.GetLength(0);
                int colCount = array.GetLength(1);
                for (int i = 0; i < rowCount; i++)
                {
                    for (int j = 0; j < colCount; j++)
                    {
                        yield return array[i, j];
                    }
                }
            }
            else if (obj is CalcReference)
            {
                foreach (var item in Enumerate(obj as CalcReference))
                {
                    yield return item;
                }
            }
            yield return obj;
        }
        private static IEnumerable<object> Enumerate(CalcReference reference)
        {
            foreach (var range in reference.GetRanges())
            {
                int rowCount = range.Rows.Count;
                int colCount = range.Columns.Count;
                for (int i = 0; i < rowCount; i++)
                {
                    for (int j = 0; j < colCount; j++)
                    {
                        if (range.Cells[i, j].DisplayFormat.Interior.Color == System.Drawing.Color.Red)
                        {
                            yield return range.Cells[i, j].Value;
                        }
                    }
                }
            }
        }
    }
    C#
    Copy Code
    // Step2: Register the custom function using AddCustomFunction() method
    var workbook = new GrapeCity.Documents.Excel.Workbook();
    GrapeCity.Documents.Excel.Workbook.AddCustomFunction(new MyConditionalSumFunctionX());
    IWorksheet worksheet = workbook.Worksheets[0];
    
    // Step3- Implement the Custom Function
    worksheet.Range["A1:A10"].Value = new object[,] { { 1 }, { 2 }, { 3 }, { 4 }, { 5 }, { 6 }, { 7 }, { 8 }, { 9 }, { 10 } };
    IFormatCondition cellValueRule = worksheet.Range["A1:A10"].FormatConditions.Add(FormatConditionType.CellValue, FormatConditionOperator.Greater, 5) as IFormatCondition;
    cellValueRule.Interior.Color = System.Drawing.Color.Red;
    // Sum cells value which display format interior color are red.
    worksheet.Range["C1"].Formula = "=MyConditionalSum(A1:A10)";
    // Range["C1"]'s value is 40.
    var result = worksheet.Range["C1"].Value;
    // Display result in cell E2
    worksheet.Range["E2"].Value = result;

    Example 2: Custom Concatenation Function

    Refer to the following example code to create and use custom concatenation function in your spreadsheet.

    C#
    Copy Code
    // Step 1- Defining custom function: MyConcatenate 
    // Creating a new class MyConcatenateFunctionX by inheriting the CustomFunction class
    public class MyConcatenateFunctionX : CustomFunction
    {
        public MyConcatenateFunctionX() : base("MyConcatenate", FunctionValueType.Text, CreateParameters())
        {
        }
        private static Parameter[] CreateParameters()
        {
            Parameter[] parameters = new Parameter[254];
            for (int i = 0; i < 254; i++)
            {
                parameters[i] = new Parameter(FunctionValueType.Variant);
            }
            return parameters;
        }
        public override object Evaluate(object[] arguments, ICalcContext context)
        {
            StringBuilder sb = new StringBuilder();
            string result = string.Empty;
            foreach (var argument in arguments)
            {
                if (argument is CalcError)
                {
                    return argument;
                }
                if (argument is string || argument is double)
                {
                    sb.Append(argument);
                }
            }
            return sb.ToString();
        }
    }
    C#
    Copy Code
    // Step2: Register the custom function using AddCustomFunction() method
    var workbook = new GrapeCity.Documents.Excel.Workbook();
    GrapeCity.Documents.Excel.Workbook.AddCustomFunction(new MyConcatenateFunctionX());
    IWorksheet worksheet = workbook.Worksheets[0];
            
    // Step3- Implement the Custom Function
    worksheet.Range["A1"].Formula = "=MyConcatenate(\"I\", \" \", \"work\", \" \", \"with\", \" \", \"GcExcel\", \".\")";
    worksheet.Range["A2"].Formula = "=MyConcatenate(A1, \"Documents.\")";        
    // Value of cell A1 is "I work with GcExcel."
    var resultA1 = worksheet.Range["A1"].Value;
    // Display result in cell C1
    worksheet.Range["C1"].Value = resultA1;
    // Value of cell A2 is "I work with GcExcel Documents."
    var resultA2 = worksheet.Range["A2"].Value;
    // Display result in cell C2
    worksheet.Range["C2"].Value = resultA2;

    Example 3: Merged Range Function

    Refer to the following example code to create and use custom merged range function in your spreadsheet.

    C#
    Copy Code
    // Step 1- Defining custom function: MyIsMergedRange 
    // Creating a new class MyIsMergedRangeFunctionX by inheriting the CustomFunction class
    public class MyIsMergedRangeFunctionX : CustomFunction
    {
        public MyIsMergedRangeFunctionX()
            : base("MyIsMergedRange", FunctionValueType.Boolean, new Parameter[] { new Parameter(FunctionValueType.Object, true) })
        {
        }
        public override object Evaluate(object[] arguments, ICalcContext context)
        {
            if (arguments[0] is CalcReference)
            {
                IEnumerable<IRange> ranges = (arguments[0] as CalcReference).GetRanges();
    
                foreach (var range in ranges)
                {
                    return range.MergeCells;
                }
            }
            return false;
        }
    }
    C#
    Copy Code
    // Step2: Register the custom function using AddCustomFunction() method
    var workbook = new GrapeCity.Documents.Excel.Workbook();
    GrapeCity.Documents.Excel.Workbook.AddCustomFunction(new MyIsMergedRangeFunctionX());
    IWorksheet worksheet = workbook.Worksheets[0];
    
    // Step3- Implement the Custom Function
    worksheet.Range["A1:B2"].Merge();
    worksheet.Range["C1"].Formula = "=MyIsMergedRange(A1)";
    worksheet.Range["C2"].Formula = "=MyIsMergedRange(H2)";
    //A1 is a merged cell, Range["C1"]'s value is true.
    var resultC1 = worksheet.Range["C1"].Value;
    // Display result in cell D1
    worksheet.Range["D1"].Value = resultC1;
    //H2 is not a merged cell, Range["C2"]'s value is false.
    var resultC2 = worksheet.Range["C2"].Value;
    // Display result in cell D2
    worksheet.Range["D2"].Value = resultC2;

    Example 4: Error Detection Function

    Refer to the following example code to create and use custom error detection function in your spreadsheet.

    C#
    Copy Code
    // Step 1- Defining custom function: MyIsError 
    // Creating a new class MyIsErrorFunctionX by inheriting the CustomFunction class
    public class MyIsErrorFunctionX : CustomFunction
        {
            public MyIsErrorFunctionX()
                : base("MyIsError", FunctionValueType.Boolean, new Parameter[] { new Parameter(FunctionValueType.Variant) })
            {
            }
            public override object Evaluate(object[] arguments, ICalcContext context)
            {
                if (arguments[0] is CalcError)
                {
                    if ((CalcError)arguments[0] != CalcError.None && (CalcError)arguments[0] != CalcError.GettingData)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                return false;
            }
        }
    C#
    Copy Code
    // Step2: Register the custom function using AddCustomFunction() method
    var workbook = new Workbook();
    Workbook.AddCustomFunction(new MyIsErrorFunctionX());
    IWorksheet worksheet = workbook.Worksheets[0];
            
    // Step3: Implement the custom function
    worksheet.Range["A1"].Value = CalcError.Num;
    worksheet.Range["A2"].Value = 100;
    worksheet.Range["B1"].Formula = "=MyIsError(A1)";
    worksheet.Range["B2"].Formula = "=MyIsError(A2)";
    // Range["B1"]'s value is true.
    var resultB1 = worksheet.Range["B1"].Value;
    // Display Result in cell C1
    worksheet.Range["C1"].Value = resultB1;
    // Range["B2"]'s value is false.
    var resultB2 = worksheet.Range["B2"].Value;
    // Display Result in cell C2
    worksheet.Range["C2"].Value = resultB2;