poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bugzi...@apache.org
Subject DO NOT REPLY [Bug 48124] The Precision is lossed when i prasing excel used poi
Date Thu, 05 Nov 2009 02:08:45 GMT
https://issues.apache.org/bugzilla/show_bug.cgi?id=48124

Josh Micich <josh@gildedtree.com> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEEDINFO                    |RESOLVED
         Resolution|                            |INVALID

--- Comment #3 from Josh Micich <josh@gildedtree.com> 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.

-- 
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org


Mime
View raw message