Named Range issue

Aspose.Cells Product Family
1

Hi. I have a custom function, that calls with named range as one of param. But by unknown reason API provide null for this parameter. There is some issue with this named range, because i unable to reproduce this issue on another book.

in2.zip (9.0 KB)

@Test
public void namedRange() throws Exception {
    Workbook workbook = new Workbook("D://in2.xlsx");

    ICustomFunction resolver = (functionName, params, arrayList1) -> {
        if ("HCOLLECTION".equals(functionName)) {
            if (params.size() == 7 && params.get(5) != null) {
                return 1;
            } else {
                return -1;
            }
        }
        return null;
    };

    CalculationOptions options = new CalculationOptions();
    options.setCustomFunction(resolver);
    workbook.calculateFormula(options);

    assertEquals("1", workbook.getWorksheets().get(0).getCells().get("B8").getDisplayStringValue());
}

Best regards. Alexey

2

@makarovalv,

Could you mention if this named range has been created with Aspose.Cells for Java API or with MS Excel. Please share the code if it has been created with the API, and the template file which does not reproduce this issue. We will analyze this file specific problem and will provide our feedback.

3

Named range was created by MS Excel

There is working example (“B2”)
in.zip (5.8 KB)

4

@makarovalv,

We were able to observe the issue with named range and we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSJAVA-42753 - Named Range Issue
5

@makarovalv,

This is to inform you that we have fixed your issue now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

In your case, the value represented by the parameter is a list of ranges which cannot be given as simple object by the model of ICustomFunction. In past versions, we provided the APIs (i.e., AbstractCalculationEngine, CalculationData) for the custom engine of formula calculations to replace ICustomFunction and the APIs provided users more capability and flexibility. In the (coming) new fix, we will support such kind of data, the parameter value will be returned as object[] array and the items are ReferredArea objects. So, you should use the new engine to implement the custom calculations (with the new fix that we will provide soon).
e.g
Sample code:

...
        options.setCustomEngine(new CustomFunctionEngineImpl()); 
        ...

        class CustomFunctionEngineImpl extends AbstractCalculationEngine
        {
            public void calculate(CalculationData data)
            {
                if ("HCOLLECTION".equals(data.getFunctionName()))
                {
                    data.setCalculatedValue(data.getParamCount() == 7 && data.getParamValue(5) != null ? 1 : -1);
                }
            }
        }
6

@makarovalv,

Please try our latest version/fix: Aspose.Cells for Java v18.11.1 (attached)

Please see the description and sample code in our previous reply for your reference.

Let us know your feedback.
Aspose.Cells for Java_v18.11.1.zip (6.3 MB)

7

The issues you have found earlier (filed as CELLSJAVA-42753) have been fixed in Aspose.Cells for Java 18.12. You can also get the latest Aspose.Cells for Java version from Maven repos. with simple configurations. Please see the document for your reference: Installation|Documentation

This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi