Aspose custom calculation issue

Aspose.Cells Product Family
1

Hi.


Have you changed custom calculation API?

The following test returns different values in params array. Earlier you have returned calculated values of parameters (String or Array of String)

In previous versions of library (e.f. 8.4.2.1), my test returns

MYFUNC(2,7.0,[[Ljava.lang.Object;@6321d94c,)

In current version (8.4.2.5) my test returns

MYFUNC(2,com.aspose.cells.ReferredArea@71c76cf5,com.aspose.cells.ReferredArea@5bc2832e,)

There is test:

@Test
public void calculationOnReferredArea() throws Exception {
    Workbook wb = new Workbook(“D://in.xlsx”);

    ICustomFunction resolver = new ICustomFunction() {
        @Override
        public Object calculateCustomFunction(String s, ArrayList arrayList, ArrayList arrayList1) {
            StringBuilder sb = new StringBuilder(s);
            sb.append("(");
            for(Object p : arrayList) {
                sb.append§;
                sb.append(",");
            }
            sb.append(")");
            return sb.toString();
        }
    };

    wb.calculateFormula(true, resolver);

    System.out.println(wb.getWorksheets().get(0).getCells().get(“B2”).getDisplayStringValue());
}

Best regards. Alexey


2

Hi,


Thanks for providing us template file and sample code.

After an initial test, I observed the issue as you mentioned. I found indifferent returned values of parameters in calculating custom functions. I used the sample code provided by you with your template file. I noticed in previous versions of the library (e.g. 8.4.2.1) the test code returns MYFUNC(2,7.0,[[Ljava.lang.Object;@6321d94c,), whereas in the latest version/fix (v8.4.2.5) the test code returns: MYFUNC(2,com.aspose.cells.ReferredArea@71c76cf5,com.aspose.cells.ReferredArea@5bc2832e,)

We will look into it if the newly returned value is fine or we should follow previous approach. I have logged a ticket with an id “CELLSJAVA-41363” for your issue. We will look into it soon.

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

Thank you.


3

Also keep in mind following my thread:

Custom functions and ReferredAreaCollection

a array of ContextObjects should contains ReferredArea for second and third parameters.

Best regards. Alexey

4

Hi,


Thanks for providing further details.

We have logged your concerns against your issue into our database. We will try to keep your desired needs in mind while fixing the issue “CELLSJAVA-41363” regarding custom functions.

Thank you.
5

Hi,


For many functions and users, cell values array for all cells in a range reference parameter is not required, they just need one single cell corresponding to the position of the formula, or just need the reference itself instead of the cell value. For old version of our component, such kind of situation cannot be supported. And for such kind of situation, gathering the value array for all cells is useless, and even increase the risk of circular reference error. So in the latest fix and the official versions in future, we just return the reference itself. If you need the values array or specific value, we will add methods for ReferredArea(such as GetValues() and GetValue(int rowOffset, int colOffset)) to fetch the data accordingly.

For the ReferredAreaCollection in ContextObjects list, it may cause a user puzzled because it cannot give one-to-one map to the parameters of the custom function. Now that we give the reference in the param list of the custom function, we think you do not need to get it from the ContextObjects list anymore and we may remove the ReferredAreaCollection from ContextObjects later.

This modification for our component will require you to change your code of the implementation for ICustomFunction, but we think it is deserved because it give more flexibility and efficiency for the users.
e.g

When users need the values in the reference, their old code may be like:
object o = paramList[i];
if (o is Array)
{
…
}
Now they should add a line of code like following:
object o = paramList[i];
if(o is ReferredArea) { o = ((ReferredArea)o).GetValues(); } //fetch data from reference
if (o is Array)
{
…
}

Hope this solution can fit your requirement and we will add those new methods for ReferredArea in the next fix soon.


Thank you.
6
Hi.

Ok. So will you not returns an old behavior (when you have returns values of cells in params array)? In this case we will change our code for new implementation behavior of library.

Also, will you implement getValue (or getValues) methods for RefferedArea?

Best regards.
7

Hi,


Sure, you will use newer approach with the newer versions/ fix, in which we provide those two mentioned methods for ReferredArea.

Thank you.
8

Will you notify me, when you add new method for RefferedArea?


Best regards.
9

Hi,


Sure thing! we will notify you in this thread with either attaching the fixed version or sharing download link for the fix when we have fixed the issue.

Thank you.
10

Hi,

Thanks for using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells
for Java v8.4.2.8 and let us know your feedback.

Now we have added GetValues()/GetValue() methods for ReferredArea class.

11

Hi. Can you please provide example, when parameter will be instance of RefferedAreaCollection



Best regards. Alexey
12

Hi Alexey,

Thanks for your posting and using Aspose.Cells.

We have logged your comment in our database against this issue. We will look into it and provide you example. Once, there is some update for you, we will let you know asap.

13

Hi,

Thanks for using Aspose.Cells.

We do not use ReferredAreaCollection any more. The ReferredArea will be
set as the parameter’s value in “paramsList” when corresponding
parameter is a reference or its calculated result is a reference.

For example, for custom function MYFUNC(A1, 2.0, OFFSET(B1,1,1)), the
objects in paramsList is [ReferredArea(A1), Double(2.0),
ReferredArea(C2)]. User can just get the cell value from the reference
by code:


object p1 = ((ReferredArea)paramsList.get(0)).getValue(0, 0);

object p3 = ((ReferredArea)paramsList.get(2)).getValue(0, 0);

14

The issues you have found earlier (filed as CELLSJAVA-41363) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.