Bug 48124

Summary: The Precision is lossed when i prasing excel used poi
Product: POI Reporter: xunwei <xunwei>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED INVALID    
Severity: critical    
Priority: P5    
Version: 3.5-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: Linux   

Description xunwei 2009-11-04 02:43:22 UTC
hi
  I am so sorry ,my english is not very well.But i have some trouble in using POI to parse excel,the precision is lossed.
  For exampe,the right amount is:1860.68,but the result is 1860.6800000000001 when i using poi in Linux 64-bit and the poi version is poi-3.5-FINAL.
  If I run the project in Linux 32-bit,the result is right.But I can't change the os because it's a production.
  Pls give me some suggestions and i must solve it quickly because many users can't use  our product.

  This is the testing code in Linux RatHat5.0 64-bit :

   /**
    *This function return 1860.6800000000001 which i see in the log,but the 
    * amount is 1860.68 in the excel
    **/
   public double getNumericCellValue(HSSFSheet hssfsheet, int i, int j)
   {
       HSSFCell hssfcell = getCell(hssfsheet, i , j );
       double value=hssfcell.getNumericCellValue();
       return vaule;

   }
Comment 1 David Fisher 2009-11-04 10:26:39 UTC
I am going to answer you in two parts.

(1) How floating point numbers work.

This is not a POI issue. It is how doubles work. Double precision numbers are atored in a certain binary format and on 64 bit architectures you get more bits in your mantissa. This is the mathematics of numbers represented by the form of mantissa ** exponential, like for instance 1 / 3 = 0.3333333333333333. 64 bits gives you more. Some hardware even uses extra bits beyond the 64.

I suggest that you try these google searches and learn about how floating point works.

(a) 32 bit jvm on 64 bit linux

(b) ieee-754

(c) java double precision

If you want further explanation then you will need to tell us about your hardware and jvm versions, and we might have an insight.

You might be better off on a linux or redhat list for this issue.

(2) A question.

In your program how are you using the result of getNumericCellValue? How are you writing the value to your log?

Regards,
Dave
Comment 2 Josh Micich 2009-11-04 17:57:36 UTC
*** Bug 48081 has been marked as a duplicate of this bug. ***
Comment 3 Josh Micich 2009-11-04 18:08:41 UTC
This problem is almost certainly independent of POI.  The underlying floating point values are being stored/retrieved properly and any numerical calculations you perform on those will be fine (within normal IEEE double constraints).  Your problem seems to be related to text conversion, in particular the method "public static String toString(double d)" on class java.lang.Double in your JRE.

If you are concerned about the apparent error in the converted text value, you can attempt to get around these problems by using the POI class NumberToTextConverter (which uses Excel rules instead of JRE rules for text conversion).  Alternatively, if you have specified an number format in Excel (e.g. "2 decimal places") you can try HSSFDataFormatter.convertCellValue(Cell) to reproduce the same formatting.

--  --  --  --
Below I have added some details to help explain the source of your error.

Your value "1860.68" is represented by the double with raw bit value 0x409D12B851EB851FL. 

The following code shows this double value and its two neighbours:

System.out.println(Double.longBitsToDouble(0x409D12B851EB851EL));
System.out.println(Double.longBitsToDouble(0x409D12B851EB851FL));
System.out.println(Double.longBitsToDouble(0x409D12B851EB8520L));

On my JREs (Sun 1.5/WinXP and Sun 1.6/Ubuntu) I get this:
1860.6799999999998
1860.68
1860.6800000000003

I suspect you'll get 1860.6800000000001 for the second line

Hopefully it's clear that POI *is* reading the correct double value from your Excel file (since 1860.68+1e-13 is still closer to 1860.68 than 1860.68+3e-13).


To dig a little deeper, you can see the exact (over precise) decimal representation of these same three IEEE double values with the following code:

BigDecimal x = new BigDecimal(new BigInteger("2").pow(42));
System.out.println(new BigDecimal(Long.toString(0x1D12B851EB851EL)).divide(x));
System.out.println(new BigDecimal(Long.toString(0x1D12B851EB851FL)).divide(x));
System.out.println(new BigDecimal(Long.toString(0x1D12B851EB8520L)).divide(x));

Which gives:
1860.67999999999983629095368087291717529296875
1860.680000000000063664629124104976654052734375
1860.6800000000002910383045673370361328125

Now from this you might conclude that your JRE is actually doing the correct thing, since the true value (~1860.68+6.366e-14) is closer to 1860.68+1e-13 than 1860.68.  However, if you read the javadoc of Double.toString(double), you will see the following:

"How many digits must be printed for the fractional part of m or a? There must be at least one digit to represent the fractional part, and beyond that as many, but only as many, more digits as are needed to uniquely distinguish the argument value from adjacent values of type double"
http://java.sun.com/j2se/1.5.0/docs/api/java/lang/Double.html#toString(double)

So, since "1860.68" still uniquely identifies double value 0x409D12B851EB851FL (i.e. there is no other double value closer to 1860.68), only 2 decimal places are required.  Perhaps you should log a bug with your JRE provider.


BTW if you look for 0x409D12B851EB851FL in the hex dump of your XLS file you won't find it.  This is because 1860.68 has been encoded as an RKNum with value 1090959009 (A1 B6 06 41) which Excel/POI automatically translate into a double with value 0x409D12B851EB851FL.