Using ICustomFunction Feature

Creating and Evaluating a User-defined Function

This article demonstrates the implementation of ICustomFunction interface to write a custom function and use it in the spreadsheet to get the results. We will define a custom function by name MyFunc which will accepts 2 parameters with 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 2nd parameter and divide the result with 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 reference. If you need the reference itself then you can use the ReferredArea directly. If you need to get value of a single cell from the reference corresponding with the formula’s position, you can use ReferredArea.GetValue(rowOffset, int colOffset) method. If you need cell values array for the whole area then you can use 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 one-to-one map to the parameters of the custom function always) therefore it has been removed from the “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...

    ...

}