Custom Function Support

Hi,

I’m currently evaluating spreadsheet calculation engines that can be deployed to a server and used to calculate pre-built spreadsheets which are then emailed out once populated with data. These spreadsheets may contain several Add-In formulae that we have developed that make calls to an API and return data. Some of these API calls return multiple results. When calculated in Excel, we set the first value as the result of the calling cell with the other values inserted into sequential cells. In the example below, MyCustomFunc is called from A3 and returns 6 values. The first result would be set as the value of A3, with the other results being inserted sequentially into A4:A8.

MyCustomFunc.png (2.1 KB)

Could you please provide answers to the following questions about the support File Formats offers for this scenario:

  1. Does your product allow the creation of custom functions so that we can calculate our Add-In formula?
  2. If yes, does it allow us to set the results from within our custom function to cells other than the calling cell, as in the example above, without entering the formula as an array formula?

@sam.hooper,

Thanks for your query.

Aspose.Cells supports to implement custom functions to extent the formula calculation engine. See the documents for your reference:

Hope, this helps a bit.

@sam.hooper,

For your second question, please notice further details with sample code segment for your reference:

As the document for AbstractCalculationEngine describes, users can only return the calculated result in their implementation of the custom engine. So, you should not change any other cell’s data (including the value, style, …etc.), otherwise unexpected results or exception will be thrown. So, for your requirements, you have to use array formula for the range of cells for your custom functions. For the calculated results of the array formula, the calculated value (CalculationData.CalculatedValue) should be array corresponding to the applied range of the array formula, such as:
e.g
Sample code:

class MyEngine : AbstractCalculationEngine
{
    public override void Calculate(CalculationData data)
    {
        if (data.FunctionName.ToLower().Equals("MYCUSTOMFUNC"))
        {
            data.CalculatedValue = new object[][]{
                new object[] {"Result1"}, new object[] {"Result2"}, new object[] {"Result3"},
                new object[] {"Result4"}, new object[] {"Result5"}, new object[] {"Result6"}
            };
        }
    }
}