Cells for Java: Double rounding problems

Aspose.Cells Product Family
1

Hi,

Please find attached an excel file.
Using the following java code (with Aspose.Cells 7.1.2 and correct libs):

Workbook wb = new Workbook(“trouble_double.xls”);
Cells cells = wb.getWorksheets().get(“Sheet1”).getCells();
System.out.println(cells.get(“A1”).getDoubleValue());
System.out.println(cells.get(“A2”).getDoubleValue());
System.out.println(cells.get(“A3”).getDoubleValue());
System.out.println(cells.get(“A4”).getDoubleValue());
System.out.println(cells.get(“A5”).getDoubleValue());
Expecting the following result:
4038244.86
5542755.09
123.0
377263.61
-587839.42
Obtaining the following result:
4038244.8600000003
5542755.09
123.0
377263.61000000004
-587839.4200000002
Any idea why I would get these rounding issues on some values but not all?
Any chance we could avoid it?

Thanks and regards
VR
2

Hi,


I think you may use Cell.getStringValue() method instead for your requirements.

Thank you.
3

Hello,

The cells contain numeric data, so I assume there is a way to retrieve this numeric data so that I can make calculations.

Calling getSttingValue() will render this data with a formatter that I don’t know (decimal separator, thousands separator, number of decimals etc…). Then I will have to convert this into a Number, not knowing the format ! This format may depend on how the workbook creator is setup (Excel on any worlwide user’s computer), locales on the server, default formatters in Aspose, or whatever…

Our dev team has the instruction to not use getStringValue() for numbers or dates.

Is this an Aspose bug or limitation, or just a double precision issue?
I feel like these double precision numbers can be handled correctly in Java. I have no precision issues with the following tests:
System.out.println(4038244.86);
System.out.println(new BigDecimal(“4038244.86”).doubleValue());
System.out.println(BigDecimal.valueOf(4038244.86).doubleValue());
System.out.println(Double.valueOf(“4038244.86”));
System.out.println(((Double)4038244.86).toString());

Thanks
VR

4

Hi,


OK, I can notice the issue as you pointed out regarding the Cell.getDoubleValue() method for some values as you mentioned. I have logged a ticket with an id: CELLSJAVA-40172 for our investigation. We will figure it out soon.

Thank you.
5

Hi,

It is not a bug of our component. The double values are read from the template files. Such as A1, its double value was saved as 0x414ecf326e147ae2 in long format in your given xls file. When convert this long to double back, the double value is just 4038244.8600000003. In Ms excel the precision is always 15 so the value you get in ms excel will be 4038244.86. To get the same value as in ms excel, we think you may try following code for double values:

System.out.println(new BigDecimal(cells.get(i, 0).getDoubleValue(), new MathContext(15)).doubleValue());

For performance consideration, we cannot do such kind of operation for every cell in common APIs such as Cell.getValue()/getDoubleValue().


6

Hello,

Thanks for the detailed answer.

Regards
VR

7

Hi,

For your benefit, please also try our Offline Aspose.Cells for Java Demos.
Aspose.Cells for Java Examples