Custom Functions

DsExcel.Net 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 a company's proprietary functions, apply a nested formula with custom functions, or use a combination of standard built-in functions to handle complex spreadsheet calculations.

Create Custom Function Using Code

To implement custom functions in DsExcel.Net, you must 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 Example 5 of this topic. If one parameter of the overloaded Parameter method is set to FunctionValueType.Object and acceptCustomObjects is set to True, then custom objects can be used. Similarly, if the return type is FunctionValueType.Object, the formula can return custom objects.

To create a custom function, follow these steps:

Step 1: Define the custom function: Create a new class derived from CustomFunction and implement the required logic.
Step 2: Register the custom function: Register the custom function to the worksheet using the AddCustomFunction() method.
Step 3: Implement the custom function: Write the specific implementation of the custom function.

Note: When exporting custom functions to .xlsx files, Excel cannot recognize these functions, so cells containing custom functions will display a #NAME error.

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.

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.

// 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");
    }
}

// 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);

Accepting Errors in Custom Functions

When using custom functions, the input parameters passed to the function may contain error values such as #DIV/0!, #N/A, or #VALUE!, etc. Similar to MS Excel, by default, if any argument contains an error, the function will not be called and the error will be returned directly as the result of the formula cell.

To help developers handle error values in function arguments, DsExcel.Net provides the AcceptErrors property in the CustomFunction class. If AcceptErrors is set to True, the custom function will be called even if there are errors in the inputs. By default, AcceptErrors is false, so the function won’t be executed if any argument is an error, and the error will be returned directly.

For implementation details, please see the following example code.

public class MyFunctionWithAcceptErrors : CustomFunction
{
    public MyFunctionWithAcceptErrors(string funcName, bool acceptError)
        : base(funcName, FunctionValueType.Text)
    {
        this._acceptErrors = acceptError;
    }

    private bool _acceptErrors = false;

    override public bool AcceptErrors
    {
        get => _acceptErrors;
        set => _acceptErrors = value;
    }

    public override object Evaluate(object[] arguments, ICalcContext context)
    {
        if (arguments == null || arguments.Length == 0)
        {
            return null;
        }

        StringBuilder result = new StringBuilder();
        for (int i = 0; i < arguments.Length; i++)
        {
            if (arguments[i] is CalcError calcError)
            {
                result.Append(result.Length > 0 ? $", #{calcError}" : $"Exist errors: #{calcError}");
            }
        }

        return result.Length > 0 ? result.ToString() : "No error";
        
    }
}

// Create a new workbook.
var workbook = new GrapeCity.Documents.Excel.Workbook();

Excel.Workbook.AddCustomFunction(new MyFunctionWithAcceptErrors("MyFuncCanAcceptErrors", true));
Excel.Workbook.AddCustomFunction(new MyFunctionWithAcceptErrors("MyFuncNotAcceptErrors", false));
IWorksheet worksheet = workbook.ActiveSheet;

worksheet.Range["A1:B1"].Merge();
worksheet.Range["A7:B7"].Merge();
worksheet.Range["A1:B1"].Value = "Custom function that accepts errors";
worksheet.Range["A7:B7"].Value = "Custom function that does not accept errors";
worksheet.Range["A2, A8"].Value = "Formula";
worksheet.Range["B2, B8"].Value = "Result";
worksheet.Range["A1:B1, A7:B7, A2, A8, B2, B8"].Font.Bold = true;
worksheet.Range["A2, A8, B2, B8"].HorizontalAlignment = HorizontalAlignment.Center;

// Range["B3"]'s value is "Exist errors: #Div0".
worksheet.Range["A3"].Value = "=MyFuncCanAcceptErrors(1, 2, 1/0)";
worksheet.Range["B3"].Formula = "=MyFuncCanAcceptErrors(1, 2, 1/0)";
// Range["B4"]'s value is "Exist errors: #Value".
worksheet.Range["A4"].Value = "=MyFuncCanAcceptErrors(1, \"test\" + 1, 3)";
worksheet.Range["B4"].Formula = "=MyFuncCanAcceptErrors(1, \"test\" + 1, 3)";
// Range["B5"]'s value is "Exist errors: #Name, #Num".
worksheet.Range["A5"].Value = "=MyFuncCanAcceptErrors(SUME(1), 2, SQRT(-1), 4)";
worksheet.Range["B5"].Formula = "=MyFuncCanAcceptErrors(SUME(1), 2, SQRT(-1), 4)";

// Range["B9"]'s value is error of #DIV/0!.
worksheet.Range["A9"].Value = "=MyFuncNotAcceptErrors(1, 2, 1/0)";
worksheet.Range["B9"].Formula = "=MyFuncNotAcceptErrors(1, 2, 1/0)";
// Range["B10"]'s value is error of #VALUE!.
worksheet.Range["A10"].Value = "=MyFuncNotAcceptErrors(1, \"test\" + 1, 3)";
worksheet.Range["B10"].Formula = "=MyFuncNotAcceptErrors(1, \"test\" + 1, 3)";
// Range["B11"]'s value is error #NAME?.
worksheet.Range["A11"].Value = "=MyFuncNotAcceptErrors(SUME(1), 2, SQRT(-1), 4)";
worksheet.Range["B11"].Formula = "=MyFuncNotAcceptErrors(SUME(1), 2, SQRT(-1), 4)";

worksheet.Range["A:B"].AutoFit();
worksheet.PageSetup.PrintHeadings = true;
worksheet.PageSetup.PrintGridlines = true;
worksheet.PageSetup.Orientation = PageOrientation.Landscape;
worksheet.PageSetup.PrintArea = "$A$1:$G$20";

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

The output is shown in the figure below:

Example

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

// 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;
                    }
                }
            }
        }
    }
}

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

// 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();
    }
}

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

// 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;
    }
}

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

// 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;
        }
    }

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

// 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;
    }
  
}   
}

//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");