getStringValue() returns #VALUE!

Aspose.Cells Product Family
1

Hello,
We are using Aspose Java Cells 7.7.1 in our project and we have observed problem with proper retrieving values of the cells.
When the value in the particular cell A1 (with VLOOKUP formula) is recalculated from the value of the another one (with RANK formula), then the retrieved value by getStringValue() method from the A1 cell returns “#VALUE!” string.

We used threeways of recalculation formulas in the workbooks:
1. workbook.calculateFormula();
2. worksheet.calculateFormula();
3. worksheet.calculateFormula(true, true, null);

None of them worked. Could you please advice?

We have attached the sample workbook.
Thanks,
Marek

2

Hi Marek,

Thanks for your posting and using Aspose.Cells for Java.

Please download and try the latest version: Aspose.Cells
for Java v7.7.1.4 it works fine.

We have tested your issue with the latest version and it is working fine. We used the following code to test this issue.

Java


String filePath = “F:\Shak-Data-RW\Downloads\example.xlsx”;


Workbook workbook = new Workbook(filePath);

workbook.calculateFormula();


Worksheet worksheet = workbook.getWorksheets().get(0);


Cell cellA1 = worksheet.getCells().get(“A1”);


System.out.println(cellA1.getStringValue());

3

Hi,
Sorry for attaching wrong example.
Now, I am attaching the whole sample (java code + excel workbook) which causes the problem. When we are trying to obtain the A1 cell’s value we still get #VALUE! even with the Aspose 7.7.1.4

Regards

4

Hi,
From our observation the reason for that bug is the semicolon which is added after the Rank formula in the attached workbook:
=RANK($A$11;($A$11:$A$12):wink:

It is properly accepted by Excel and means that the optional parameter “order” is empty in this formula (does not exist).
Unfortunately, Aspose probably expects the argument after semicolon and does not recalculate this formula properly like Excel.

Could you please advice if this bug could be fixed?

Regards

5

Hi Marek,

Thanks for using Aspose.Cells for Java.

We were able to find the problem with your new sample code and source file. The cells with the formulas return #VALUE! instead of their values. Excel works fine though.

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSJAVA-40728.

6

Hi,


Please try our latest version/fix: Aspose.Cells for Java v7.7.1.5

We have fixed your issue now.

Let us know your feedback.

Thank you.

7

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

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

Thank you,
This fix has solved the problem.

Regards

9

Hi,

Thank you for the confirmation on this. Please feel free to write back in case you need our further assistance. We will be glad to help you out soon.