Excel Exponential problem

[Bi...@cognizant.com]

Hello,

 

I have an excel file in which there is a column with no format
specified. The column contains numeric data 

 

e.g. 3333333333333333333333333333333333333

 

This numeric data gets converted in excel as 3.33333333333333E+36 

 

How can I retrieve the original value using POI? 

Thanks and Regards

Bipin Jadhav | Hinjewadi H1, Pune |

www.cognizant.com <blocked::blocked::http://www.cognizant.com>  | E:
bipin.jadhav2@cognizant.com
<bl...@t-systems.com> | VNet: 202166 |
M: +91-9922068837

 



This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information.
If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. 
Any unauthorised review, use, disclosure, dissemination, forwarding, printing or copying of this email or any action taken in reliance on this e-mail is strictly 
prohibited and may be unlawful.

Re: Excel Exponential problem

[Josh Micich <jo...@gmail.com>]

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

Re: Excel Exponential problem

[MSB <ma...@tiscali.co.uk>]

Perhaps a silly question but are you certain there is no format applied to
the cell? Have you tried passing the cell to the static formatCellValue()
method of the DataFormatter class yet? If that works, it will only return a
String for you but that should suffice for presentation purposes.

The second tool you could look at is the java.text.NumberFormat class. That
may provide a way for you to convert from the 'scientific' notation; the
only problem could be, as Josh indicated, that the value entered into the
cell originally cannot be represented exactly.

Yours

Mark B


Bipin.Jadhav2 wrote:
> 
> 
> Hello,
> 
>  
> 
> I have an excel file in which there is a column with no format
> specified. The column contains numeric data 
> 
>  
> 
> e.g. 3333333333333333333333333333333333333
> 
>  
> 
> This numeric data gets converted in excel as 3.33333333333333E+36 
> 
>  
> 
> How can I retrieve the original value using POI? 
> 
> Thanks and Regards
> 
> Bipin Jadhav | Hinjewadi H1, Pune |
> 
> www.cognizant.com <blocked::blocked::http://www.cognizant.com>  | E:
> bipin.jadhav2@cognizant.com
> <bl...@t-systems.com> | VNet: 202166 |
> M: +91-9922068837
> 
>  
> 
> 
> 
> This e-mail and any files transmitted with it are for the sole use of the
> intended recipient(s) and may contain confidential and privileged
> information.
> If you are not the intended recipient, please contact the sender by reply
> e-mail and destroy all copies of the original message. 
> Any unauthorised review, use, disclosure, dissemination, forwarding,
> printing or copying of this email or any action taken in reliance on this
> e-mail is strictly 
> prohibited and may be unlawful.
> 

-- 
View this message in context: http://www.nabble.com/Excel-Exponential-problem-tp25592589p25607098.html
Sent from the POI - User mailing list archive at Nabble.com.


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