Bug 39998

Summary: Conversion of numbers while reading through HSSF
Product: POI Reporter: bhushan tari <bhushan.tari>
Component: HSSFAssignee: POI Developers List <dev>
Severity: major    
Priority: P2    
Version: 2.5-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   

Description bhushan tari 2006-07-10 06:56:56 UTC

I have an excel file. I have entered the value 1234 in a cell (without the 
single quote). But when i read the excel from my java program the value is 
being read as 1234.0. I am reading the value in a string variable. Also, if the 
number is big ex. 410012985 without the single quote, then it gets converted to 
4.10012985E8. I want the value and format of the number read to be retained as 
it is when i open the excel file through microsoft excel.
Comment 1 Yegor Kozlov 2007-04-19 10:40:34 UTC
It's not a POI bug. What you see in Excel is not exactly the same what is stored
in xls file. To get the formated values you need to do the following:

 (1) get raw value as double
 (2) get the cell formatter as follows:

  HSSFDataFormat dataformat = workbook.createDataFormat();
  short idx = style.getDataFormat();
  String format = dataformat.getFormat(idx);

  (3) create appropriate Java formatter and convert double to string


Comment 2 Andy Oliver 2007-04-19 10:43:57 UTC
Possibly this is slightly wrong.  A while back someone pointed out that we're
not going to the same precision that we should (possibly some functions should
be decoded as big decimals)
Comment 3 Yegor Kozlov 2007-04-20 03:17:23 UTC

I got the point.

The thread you are talking about is "Numeric Cell Type not consistent" from 28 
Feb 2007. 

Comment 4 Himanshu 2009-11-24 08:55:02 UTC
Could you please paste a sample code to get the value displayed in Excel not what and how it is stored internally in Excel.

I believe everyone is more interested in getting the same data as they see while opening the Excel document, not to go in the details of how Excel stores it (Text as numeric etc..)

If we have to do all of this again on each data type, It would be unnecessary piece of additional code that needs to be written when using apache poi library.

for example: If I as a user created an excel file and placed "222" as a value, what I wish from the library is to give me "222" and not "222.0" or something else based on how excel stores it internally.

The place where I had to use this to simply read the excel file and display them as a grid on the UI for user. Which when using Jexcel-API was displayed correctly (I am not sure what and how they display data, but this was what most of us are interested and expected) However when to support Excel 2007 document, I started playing with Apache POI I am stuck at the very first moment in reading the correct data.
(Don't you think checking each column here for cell data type and converting to appropriate value for each column would be a pain and additional unnecessary code.)

I do not understand why you being so ignorant in realizing the problems and seems more interested in discarding them.

I hope you will realize the issue and will provide some sort of easy solution.
Comment 5 Nick Burch 2009-11-24 09:01:05 UTC
There is a very easy solution. However, this is not the place to ask for help with using POI / finding bits of the library to do what you want. It's for reporting bugs (hence the cunning name bugzilla).

If you'd taken 2 minutes to google, or asked on the mailing list, you'd have come across a class to do exactly what you want -