HSSFCell returns numeric value instead of Strings (for numeric strings)

["Srivastava, Deepak" <de...@citigroup.com>]

When I read the spreadsheet(cell format as text), it has numbers with  like:
100, 10000, 0.967, etc. When I read them using POI, I get 100.0, 10000.0, 0.97, etc.

1. In Excel, I formatted all cells as String for the required columns.  
2. In the code it looks like this:  
 
  private String convertToString(HSSFCell cell) {  
         String ret = "";  
 
         if (cell != null)  
         {  
             int type = cell.getCellType();  
             switch (type)  
             {  
             case HSSFCell.CELL_TYPE_BLANK:  
             case HSSFCell.CELL_TYPE_BOOLEAN:  
             case HSSFCell.CELL_TYPE_ERROR:  
             case HSSFCell.CELL_TYPE_FORMULA:  
                 break;  
             case HSSFCell.CELL_TYPE_NUMERIC:  
                 ret = Double.toString(cell.getNumericCellValue());  
                 System.out.println("number: " + ret);  
                 break;  
             case HSSFCell.CELL_TYPE_STRING:  
                 ret = cell.getStringCellValue();  
                 System.out.println("String: " + ret);  
                 break;  
             }  
         }  
 
         return ret;  
     }  
 
 
3. With Strings, erverything is ok. Values consisting of numbers  
appear as numbers (although I had formatted them as Strings in Excel).  
4.Furthermore,  A number like 736132469 is displayed as 7.36132469E8  
 
Please advise. I hope this is a well known problem and solutions already exists for these. 
(A possible work around, or a fix available in new version or so).

Thanks,
Deepak

---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/

Re: HSSFCell returns numeric value instead of Strings (for numeric strings)

[Daniel Noll <da...@nuix.com>]

Srivastava, Deepak wrote:
> When I read the spreadsheet(cell format as text), it has numbers with  like:
> 100, 10000, 0.967, etc. When I read them using POI, I get 100.0, 10000.0, 0.97, etc.
> 
> 1. In Excel, I formatted all cells as String for the required columns.  
> 2. In the code it looks like this:  
<cut>
> Please advise. I hope this is a well known problem and solutions already exists for these. 
> (A possible work around, or a fix available in new version or so).

It's a misunderstanding.  Merely formatting a number as a string does 
not make it a string.  To actually write a number as a string in Excel, 
you would prefix it with a single quote (') character.

Daniel


-- 
Daniel Noll

Nuix Pty Ltd
Suite 79, 89 Jones St, Ultimo NSW 2007, Australia    Ph: +61 2 9280 0699
Web: http://nuix.com/                               Fax: +61 2 9212 6902

This message is intended only for the named recipient. If you are not
the intended recipient you are notified that disclosing, copying,
distributing or taking any action in reliance on the contents of this
message or attachment is strictly prohibited.

---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/