Reading Cell value as #Value! while value is correct in Excel

Aspose.Cells Product Family
1

Aspose cell library (Java) is reading a cell value as #Value!. Error comes with latest aspose cell library as well.

Please find the code snippet attached. The values in Column B (Sheet s2) are read as #Value! whereas the values are displaying correct in excel.

public static void main(String[] args) throws Exception{

    Workbook workbook = new Workbook("C:/sample.xlsb");
    workbook.calculateFormula();
    Worksheet worksheet = workbook.getWorksheets().get("s2");
    Cells cells = worksheet.getCells();
    System.out.println(cells.get(3,CellsHelper.columnNameToIndex("B")).getValue().toString());
}

Output
#VALUE!

value in Excel - 41.00

aspose-ticket.zip (45.1 KB)

2

@guptadeeptarun,
We have observed the issue where value is read as #Value instead of 41 from the cell. We have logged this issue in our database for further analysis and fix if possible.

This issue is logged as:
CELLSJAVA-43210 - Wrong value #Value read by Aspose.Cells

3

@ahsaniqbalsidiqui
Thanks for acknowledging the issue. Can you please tell when this can be available as this is a Production Issue. Appreciate your quick resolution.

4

@guptadeeptarun,
Although this issue is logged too recently and may take couple of days to analyze it. We have recorded your concerns with the ticket and will let you know the ETA as soon as it is avaialble.

5

@guptadeeptarun,
This is to inform you that we have fixed your issue (logged earlier as “CELLSJAVA-43210”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

6

@ahsaniqbalsidiqui
Thanks. we look forward to get the release soon.

7

@guptadeeptarun,

Sure, we are on it. Hopefully you will be getting the fix in the next few days (2-5 days or so).

Keep in touch.

8

@ahsaniqbalsidiqui @Amjad_Sahi
Can you please tell like what was the issue here exaclty in reading the values from Column B.
Because all the functions used in the formula for that column are compatible with Aspose. Also all the dependent columns are also read successfully.
Then what was going wrong there ?

9

@guptadeeptarun,
The #VALUE! was not read incorrectly from the template file, but caused by a bug of calculating formulas. The coming fix for this issue is just fixing the bug of formula calculation.

10

@guptadeeptarun,
Please try our latest version/fix: Aspose.Cells for Java v20.6.2:
aspose-cells-20.6.2-java.zip (7.1 MB)

Your issue should be fixed in it.

11

@ahsaniqbalsidiqui
We would like to test the fix. but cannot find the v20.6.2 below

Neither its available on maven
https://repository.aspose.com/repo/com/aspose/aspose-cells/

12

@guptadeeptarun,
We only publish official releases on Nuget\maven repos. However, if you still want the version to be uploaded onto Maven, then you have to wait for our next official release of the product. We will publish our next official release, i.e., Aspose.Cells for Java v20.7 (which will also include your fix) in the third week of July, 2020. For now, you can use the attached file.

13

@ahsaniqbalsidiqui
We find that if we simply delete the cell formula and paste it again it works perfectly even with the old version of aspose library.This doesn’t seem to be formula issue as the same formula is computed properly after setting again.

Can you please help us understand where the problem is and what is actually fixed. ?

14

@guptadeeptarun,
We have tested your scenario and observed the same that after deleting and pasting the same formula again, it works with old version also. This observation is being discussed here and we will share our feedback soon.

15

@ahsaniqbalsidiqui
Sure. will be waiting for your response.
Please let us know if this is something that can be fixed in excel.

16

@guptadeeptarun,

Well, formula calculation depends on information in the formula data, such as the reference mode of every token. Commonly MS Excel will create the correct reference mode for every token when parsing formulas. For such situation both MS Excel and Aspose.Cells can calculate those formulas correctly.

If the formula data was created by other software other than MS Excel, the reference mode may be not be the correct one. For some formulas, such as the one in your template file, MS Excel still can calculate it correctly. But our component of old versions cannot. When you reset the formula, the formula data will be rebuilt to the correct one, so we can give the correct result after such kind of operation even with old versions.

In the new fix what we improved is that we can calculate formulas correctly even if the formula data contains some incorrect information, it just behaves in the same way with MS Excel.

17

@Amjad_Sahi
Thanks Amjad for detailed explanation.
When you say “In the new fix what we improved is that we can calculate formulas correctly even if the formula data contains some incorrect information” -> can you point us to that specific incorrect information in the formula you are referring to that’s causing issue with Aspose.
This can probably help our developers to fix the excel as we never experienced such issue before.

18

@guptadeeptarun,

We will provide you more details on it. We will get back to you soon.

19

@Amjad_Sahi
Dear Amjad. do we have any updates on this…?

20

@guptadeeptarun,
Commonly the incorrect information which may cause such kind of issue is those tokens with incorrect reference mode. Such as 0x41 which should be 0x21, 0x5A which should be 0x3A, …etc. However, for your sample.xlsb, we cannot find old versions can work after resetting s2!B4 in ms excel as you described. After the operation we still got #VALUE! with old versions.