Document Solutions for Excel, .NET Edition | Document Solutions
Features / Custom Functions
In This Topic
    Custom Functions
    In This Topic

    DsExcel 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 DsExcel .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.

    You can also use custom objects in custom functions as demonstrated by the Example 5 of this topic. If one parameter of overloaded Parameter method is set to FunctionValueType.Object and acceptCustomObjects is set to True, custom objects can be used. Similarly, if the return type is FunctionValueType.Object, the formula can return custom objects.

    Caching in Custom Functions

    Custom functions in the same column store resultant value as cache. Hence, when a custom function in a column is called subsequently with previous parameter, custom function uses the cached value instead of calculating it again. This feature helps in optimizing performance especially in case of repetitive use of the custom function in a single column.

    However, to control this caching behavior of custom functions, DsExcel.Net provides IsVolatile property in the class inherited from CustomFunction class. The property lets you choose whether to recalculate a custom function for a column having same parameters every time or use the cached result. The default value of this property is false, which means custom function applied on a single column maintains its own cache and reuses it on a repeated call. For implementation, see Example 6: Create Volatile Cache.

    Create Custom Function Using Code

    Creating custom function in DsExcel.Net involves following three steps.

    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\", \" \", \"Excel\", \".\")";
worksheet.Range["A2"].Formula = "=MyConcatenate(A1, \"Documents.\")";        
// Value of cell A1 is "I work with Excel."
var resultA1 = worksheet.Range["A1"].Value;
// Display result in cell C1
worksheet.Range["C1"].Value = resultA1;
// Value of cell A2 is "I work with Excel 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;

     

    Example 5: Greatest Common Division Function using Custom Objects

    Refer to the following example code to create and use BigInteger function to calculate greatest common division.

    C#
    Copy Code 
    // Step 1.1- Defining custom function: BigIntegerMultiplyFunction
internal class BigIntegerMultiplyFunction : CustomFunction
{
public BigIntegerMultiplyFunction() : base("BIG.INTEGER.MULT", FunctionValueType.Object, new[]
    {
              new Parameter(FunctionValueType.Text),
              new Parameter(FunctionValueType.Text)
          })
    {
    }

public override object Evaluate(object[] arguments, ICalcContext context)
{
    if (!(arguments[0] is string) || !(arguments[1] is string))
    {
        return CalcError.Value;
    }
    var leftNumber = (string)arguments[0];
    var rightNumber = (string)arguments[1];
    try
    {
        return BigInteger.Parse(leftNumber) * BigInteger.Parse(rightNumber);
    }
    catch (FormatException)
    {
        return CalcError.Value;
    }
    catch (ArgumentException)
    {
        return CalcError.Value;
    }
}
        
}
// Step 1.2- Defining custom function: BigIntegerPowFunction
internal class BigIntegerPowFunction : CustomFunction
{
public BigIntegerPowFunction() : base("BIG.INTEGER.POW", FunctionValueType.Object, new[]
{
              new Parameter(FunctionValueType.Text),
              new Parameter(FunctionValueType.Number)
          })
    {
    }

public override object Evaluate(object[] arguments, ICalcContext context)
{
    if (!(arguments[0] is string) || !(arguments[1] is double))
    {
        return CalcError.Value;
    }
    var number = (string)arguments[0];
    var exp = (double)arguments[1];
    if (exp > int.MaxValue || exp < int.MinValue)
    {
        return CalcError.Value;
    }
    var iExp = Convert.ToInt32(exp);
    try
    {
        return BigInteger.Pow(BigInteger.Parse(number), iExp);
    }
    catch (FormatException)
    {
        return CalcError.Value;
    }
    catch (ArgumentException)
    {
        return CalcError.Value;
    }
}
}
// Step 1.3- Defining custom function: GreatestCommonDivisionFunction
internal class GreatestCommonDivisionFunction : CustomFunction
{
public GreatestCommonDivisionFunction() : base("BIG.INTEGER.GCD", FunctionValueType.Object, new[] {
              new Parameter(FunctionValueType.Object, false, true),
              new Parameter(FunctionValueType.Object, false, true)
          })
    {
    }

public override object Evaluate(object[] arguments, ICalcContext context)
{
    if (!(arguments[0] is BigInteger) || !(arguments[1] is BigInteger))
    {
        return CalcError.Value;
    }
    var leftNumber = (BigInteger)arguments[0];
    var rightNumber = (BigInteger)arguments[1];
    try
    {
        return BigInteger.GreatestCommonDivisor(leftNumber, rightNumber);
    }
    catch (ArgumentException)
    {
        return CalcError.Value;
    }
  
}   
}
    C#
    Copy Code 
    //create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();

try
{
    // Step2.1: Register the custom function using AddCustomFunction() method
    GrapeCity.Documents.Excel.Workbook.AddCustomFunction(new BigIntegerPowFunction());
}
catch (Exception)
{
    // Function was added
} // End Try
try
{
    // Step2.2: Register the custom function using AddCustomFunction() method           
   GrapeCity.Documents.Excel.Workbook.AddCustomFunction(new BigIntegerMultiplyFunction());
}
catch (Exception)
{
    // Function was added
} // End Try
try
{
    // Step2.3: Register the custom function using AddCustomFunction() method
    GrapeCity.Documents.Excel.Workbook.AddCustomFunction(new GreatestCommonDivisionFunction());
}
catch (Exception)
{
    // Function was added
} // End Try
        
// Use BigInteger to calculate results
IWorksheet worksheet = workbook.ActiveSheet;
// Step3- Implement the Custom Function
worksheet.Range["A1"].Value = "154382190 ^ 3 = ";
worksheet.Range["A2"].Value = "1643590 * 166935 = ";
worksheet.Range["A3"].Value = "Greatest common division = ";
worksheet.Range["B1"].Formula = "=BIG.INTEGER.POW(\"154382190\", 3)";
worksheet.Range["B2"].Formula = "=BIG.INTEGER.MULT(\"1643590\", \"166935\")";
worksheet.Range["B3"].Formula = "=BIG.INTEGER.GCD(B1,B2)";

// Arrange
worksheet.Columns[0].AutoFit();
worksheet.Columns[1].ColumnWidth = worksheet.Range["B1"].Text.Length + 1;

//save to a pdf file
workbook.Save("customobjectincustomfunction.pdf");

    Example 6: Create Volatile Custom Function

    Following example demonstrates how to create a custom function for generating GUID. To generate a unique GUID every time, custom function should not be using cache. Hence, example code sets the IsVolatile property to true, so that a new GUID is generated on every call.

    C#
    Copy Code 
    // Creating a new class GeneralID by inheriting the CustomFunction class 
internal class GeneralID : CustomFunction
{
    public GeneralID() : base("GeneralID", FunctionValueType.Object)
    {
        // set IsVolatile to true to avoid using cached value
        this.IsVolatile = true;
    }

    public override object Evaluate(object[] arguments, ICalcContext context)
    {
        return Guid.NewGuid().ToString("N");
    }
}
    C#
    Copy Code 
    // Step2: Register the custom function using AddCustomFunction() method
//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();

GrapeCity.Documents.Excel.Workbook.AddCustomFunction(new GeneralID());

IWorksheet worksheet = workbook.Worksheets[0];

// Step3- Implement the Custom Function 
worksheet.Range["A1"].Formula = "GeneralID()";
var valueA1Before = worksheet.Range["A1"].Value;

Console.WriteLine(valueA1Before);
worksheet.Range["A2"].Formula = "GeneralID()";

// Observe A1's value has changed because it's not using cached value.
var valueA1After = worksheet.Range["A1"].Value;
Console.WriteLine(valueA1After);

    Example 7: Create Asynchronous Custom Functions

    Asynchronous function is any function that delivers its result asynchronously or concurrently. Asynchronous functions have a non-blocking architecture, so the execution of one task isn't dependent on another. Tasks can run simultaneously. Running asynchronous functions can improve performance by allowing several calculations to run at the same time. DsExcel enables functions to perform asynchronous calculations by deriving them from AsyncCustomFunction class. EvaluateAsync method calculates the function asynchronously. DsExcel also provides an enumeration value "Busy" in CalcError enumeration that indicates that a cell is calculating an async formula.

    Refer to the following example code to add and use a custom Asynchronous function:

    C#
    Copy Code 
    internal class Program
{
    static void Main(string[] args)
    {
        // Register Async custom function.
        Workbook.AddCustomFunction(new MyAddFunction());

        // Implement the Async custom Function.
        Workbook workbook = new Workbook();
        var worksheet = workbook.Worksheets[0];
        worksheet.Range["A1"].Value = 1;
        worksheet.Range["B1"].Value = 2;

        // Add the cell values.
        worksheet.Range["C1"].Formula = "=MyAdd(A1,B1)";
        var value = worksheet.Range["C1"].Value;

        // Display result. The result will be "Busy".
        Console.WriteLine($"get value first time:{value}");
        Thread.Sleep(2000);
        value = worksheet.Range["C1"].Value;

        // Display result. The result will be "3".
        Console.WriteLine($"get value second time:{value}");
    }
}
// Define Async custom function: MyAddFunction.
public sealed class MyAddFunction : AsyncCustomFunction
{
    public MyAddFunction()
        : base("MyAdd", FunctionValueType.Number, new Parameter[] { new Parameter(FunctionValueType.Number), new Parameter(FunctionValueType.Number) })
    {

    }

    async protected override Task<object> EvaluateAsync(object[] arguments, ICalcContext context)
    {
        await Task.Delay(1000);
        return (double)arguments[0] + (double)arguments[1];
    }
}

    Limitations

    The AsyncCustomFunction's parameters do not accept any reference because the asynchronous function may run in another thread, and it will cause multi-thread conflicts if you use a reference. Similarly, using objects such as IWorksheet and IWorkbook is not allowed within asynchronous functions.