poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Josh Micich <josh.mic...@gmail.com>
Subject Re: Excel Exponential problem
Date Thu, 24 Sep 2009 19:48:46 GMT
The original value is not available for two reasons. Firstly, Excel
uses IEEE doubles to represent all numbers, which reduces the
precision to barely below 16 decimal digits.  Secondly, Excel
initially performs its own extra rounding, which reduces precision to
ideally 15 decimal digits.  The quantity is available to POI as a
standard IEEE double, but with approximately one decimal digit less
precision than normally representable.

In the example you gave, Excel encodes the text number to the IEEE
double value with hex bits 0x47840FD02CE4F0B7.  This value chosen by
Excel is the closest double value to
333333333333333000000000000000000000 (15 significant digits).  If you
were to attempt the same conversion directly in java you will observe
16 digits of decimal precision:
	System.out.println(Double.parseDouble("3333333333333333333333333333333333333"));
// prints 3.3333333333333334E36
The best double value has hex encoding 0x47840FD02CE4F0BD:
	System.out.println(Long.toHexString(Double.doubleToLongBits(Double.parseDouble("3333333333333333333333333333333333333"))));

For interest, here are the double encodings and their exact decimal
equivalents nearby these two values (47840FD02CE4F0B7,
47840FD02CE4F0BD):

0x47840FD02CE4F0B6 333333333333332924428215034266936934.4
0x47840FD02CE4F0B7 333333333333332983457796070137502105.6
0x47840FD02CE4F0B8 333333333333333042487377106008067276.8

0x47840FD02CE4F0BC 333333333333333278605701249490327961.6
0x47840FD02CE4F0BD 333333333333333337635282285360893132.8
0x47840FD02CE4F0BE 333333333333333396664863321231458304.0

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


Mime
View raw message