ASF Bugzilla – Full Text Bug Listing

- Home
- | New
- | Browse
- | Search
- | [?]
- | Reports
- | Help
- | New Account
- | Log In
- | Forgot Password

Summary: | The Precision is lossed when i prasing excel used poi | ||
---|---|---|---|

Product: | POI | Reporter: | xunwei <xunwei> |

Component: | HSSF | Assignee: | 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
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 *** Bug 48081 has been marked as a duplicate of this bug. *** 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. |