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

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
public class CustomFunction : ICustomFunction
{
    public object CalculateCustomFunction(string functionName, System.Collections.ArrayList paramsList, System.Collections.ArrayList contextObjects)
    {
        decimal total = 0M;
        try
        {
            // Get value of first parameter
            decimal firstParamB1 = System.Convert.ToDecimal(paramsList[0]);

            // Get value of second parameter
            Array secondParamC1C5 = (Array)(paramsList[1]);

            // get every item value of second parameter
            foreach (object[] value in secondParamC1C5)
            {
                total += System.Convert.ToDecimal(value[0]);
            }

            total = total / firstParamB1;
        }
        catch
        {

        }

        // Return result of the function
        return total;
    }
}

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

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Open the workbook
Workbook workbook = new Workbook();

// Obtaining the reference of the first worksheet
Worksheet worksheet = workbook.Worksheets[0];

// Adding sample values to cells
worksheet.Cells["B1"].PutValue(5);
worksheet.Cells["C1"].PutValue(100);
worksheet.Cells["C2"].PutValue(150);
worksheet.Cells["C3"].PutValue(60);
worksheet.Cells["C4"].PutValue(32);
worksheet.Cells["C5"].PutValue(62);

// Adding custom formula to Cell A1
workbook.Worksheets[0].Cells["A1"].Formula = "=MyFunc(B1,C1:C5)";

// Calcualting Formulas
workbook.CalculateFormula(false, new CustomFunction());

// Assign resultant value to Cell A1
workbook.Worksheets[0].Cells["A1"].PutValue(workbook.Worksheets[0].Cells["A1"].Value);

// Save the file
workbook.Save(dataDir + "UsingICustomFunction_out.xls");

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

    ...

}