Custom function array parameter issue when not a ReferredArea type

Aspose.Cells Product Family
1

When using custom function via AbstractCalculationEngine
I am having an issue when array parameters are expressions rather than a ReferredArea.

Is there support for handling parameters that do not translate directly to a ReferredArea?

I’ll try to provide an example scenario.

Example code:

private void TestFunction(CalculationData data)
{
    if (data.FunctionName == "TEST_FUNC")
    {
        if (data.ParamCount < 1)
        {
            return;
        }

        var paramValue = data.GetParamValue(0);
        if (paramValue is double numValue)
        {
            data.CalculatedValue = "Double";
            return;
        }

        if (paramValue is ReferredArea refArea)
        {
            data.CalculatedValue = "ReferredArea";
            return;
        }
    }
}

and for example if in Excel I have values ‘1,2,3’ in cells ‘A1:C1’
A1 = 1
B1 = 2
C1 = 3

then I have a formula in another cell as:
=TEST_FUNC(A1:C1)
everything works fine, I can see that CalculationData.GetParamValue(0) is a ReferredArea, and I can further process its properties to get all values: 1,2 and 3.

the issue is when there is a sum in the Excel function parameter:
=TEST_FUNC(A1:C1 + 10)
In this case the CalculationData.GetParamValue(0) will always be of type double. I can only ever get the first parameter value, which in this example will be 11.
Is it at all possible to get the values 11,12,13 from CalculationData?

This is a simple case, but it is the same if it it is named ranges e.g.
=TEST_FUNC(Array.1 * Array.2)

Unfortunately source models are not so easy to control and this will otherwise work outside Aspose Cells.

Thanks

2

@rscottqdev,

We will look into it. In the mean time, could you please also share complete sample (runnable) code or console demo application with resource file(s) to demonstrate on how you are implementing AbstractCalculationEngine and how do you use, set and calculate custom engine. This will help us really to evaluate your issue/requirements precisely to consequently figure it out soon.

3

@rscottqdev,

For operators in formula, currently we just calculate it as the final value and provide it to user’s custom calculation engine. For your requirement, we think you need one flag to denote whether the operator should be calculated as array mode or value mode. We will investigate this requirement and give feedback for the possibility of supporting it later.

1 Like
4

@rscottqdev
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-53805

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

1 Like
5

AsposeCustomFunction.zip (13.3 KB)

I have included a sample project as requested.

There is a 'TODO: ’ line in AsposeFunction class GetList method where I’d hope to be able to get all the values.

Thanks

6

@rscottqdev,

Thanks for the sample project.

We have logged it with your existing ticket “CELLSNET-53805” into our database. As we already logged a ticket for your issue/requirements, so this project may help to evaluate and support the feature (“Support calculating parameters as Array for custom calculation engine”) for your needs.

Once we have an update on it, we will let you know.

7

@rscottqdev

We have added some new APIs to support such kind of requirement. Please try our latest version 23.8. For your implementation of AbstractCalculationEngine, please specify the new property IsParamArrayModeRequired as true and use the new method CalculationData.GetParamValueInArrayMode() to get the expected array result.

This solution requires a bit deeper stack when calculating all formulas so we will provide another better solution in next version. In our next version we will provide some other APIs(Workbook.UpdateCustomFunctionDefinition(CustomFunctionDefinition definition)) to update existing formulas for those custom function so you can get the expected result by CalculationData.GetParamValue() directly with better performance. You will get updated here when the new version is released.

1 Like
8

The issues you have found earlier (filed as CELLSNET-53805) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi