Using ICustomFunction Feature

This article provides a detailed understanding of how to use the ICustomFunction feature to implement custom functions with Aspose.Cells APIs.

The ICustomFunction interface allows you to add custom formula calculation functions to extend the Aspose.Cells core calculation engine in order to meet certain requirements. This feature is useful to define custom (user‑defined) functions in a template file or in code where the custom function can be implemented and evaluated using Aspose.Cells APIs like any other default Microsoft Excel function.

Please note, this interface has been replaced by AbstractCalculationEngine and will be removed in future. Some technical articles/examples about the new API: here and here

Creating and Evaluating a User-defined Function

This article demonstrates the implementation of the ICustomFunction interface to write a custom function and use it in the spreadsheet to get the results. We will define a custom function named MyFunc which will accept 2 parameters with the following details.

• 1st parameter refers to a single cell
• 2nd parameter refers to a range of cells

The custom function will add all the values from the cell range specified as the 2nd parameter and divide the result by the value in the 1st parameter.

Here is how we have implemented the CalculateCustomFunction method.

Here is how to use the newly defined function in a spreadsheet

Overview

The Aspose.Cells APIs just put the ReferredArea object into the paramsList when the corresponding parameter is a reference or its calculated result is a reference. If you need the reference itself, then you can use the ReferredArea directly. If you need to get the value of a single cell from the reference corresponding with the formula’s position, you can use the ReferredArea.GetValue(rowOffset, colOffset) method. If you need a cell‑values array for the whole area, then you can use the ReferredArea.GetValues method.

As the Aspose.Cells APIs give the ReferredArea in paramsList, the ReferredAreaCollection in contextObjects will not be needed anymore (in old versions it was not able to give a one‑to‑one map to the parameters of the custom function consistently); therefore it has been removed from contextObjects.

 public object CalculateCustomFunction(string functionName, ArrayList paramsList, ArrayList contextObjects)
{
    ...
    object o = paramsList[i];
    if(o is ReferredArea) // fetch data from reference
    {
        ReferredArea ra = (ReferredArea)o;
        if(ra.IsArea)
        {
            o = ra.GetValues();
        }
        else
        {
            o = ra.GetValue(0, 0);
        }
    }
    if (o is Array)
    {
        ...
    }
    else if...
    ...
}