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 46689] Numeric cell value problem
Date Wed, 11 Feb 2009 00:33:33 GMT
https://issues.apache.org/bugzilla/show_bug.cgi?id=46689


Josh Micich <josh@gildedtree.com> changed:

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




--- Comment #1 from Josh Micich <josh@gildedtree.com>  2009-02-10 16:33:30 PST ---
The IEEE 'double precision' data type provides around 15.95 decimal digits of
precision.  Any arbitrary number (assuming reasonable exponent/scale) can be
represented accurately to the first 15 digits, with the 16th digit being nearly
right.

If you use numeric cells there is no way to avoid using IEEE doubles when
accessing the values.  Given the above limitations you should not rely on more
than 15 digits of accuracy when using numeric cells in Excel(/POI).

Looking at your example from the inside out, the decimal number
95842303093988300 requires 57 bits to express in binary form:
101010100100000000001000100011110010111110101101111001100
Doubles have a 53 bit fraction (1 implicit + 52 explicit), so this number is 4
bits too large. In converting to double, the bottom 4 bits are rounded (up)
with carry into the next bit resulting in an error of (10000b-01100b) = +4. 
This error appears when converting back to a long: 95842303093988304. 

Since Excel performs this rounding silently, it may not be immediately apparent
where the discrepancy has originated.


Have you considered using text cells to store your contract IDs?  Text values
are still comparable, but don't support more complex mathematical operations. 
This is usually not a concern for PK/ID values.  I'd also suggest adding a
standard non-digit prefix (e.g. "#" or "ID-") to prevent accidental conversion
to numeric cell type.


-- 
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