Bug 16410 - Exception when using HSSFCell.setCellType to change cell type of existing cell
Summary: Exception when using HSSFCell.setCellType to change cell type of existing cell
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 1.5.1
Hardware: PC Windows XP
: P3 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2003-01-24 22:15 UTC by Jason Monberg
Modified: 2005-05-20 03:41 UTC (History)
0 users


Note You need to log in before you can comment on or make changes to this bug.
Description Jason Monberg 2003-01-24 22:15:02 UTC
POI version 1.5.1
excel 2002 10.2614.2625

not sure what the behavior of setCellType should be, but this strikes me as a 
bug. my assumption is that a cell, regardless of it's content, can have its 
type changed without directly modifying the content first.

using the example program, ReadWriteWorkbook, with a blank excel spreadsheet 
called workbook.xls. when the example program tries to set the cell type to 
CELL_TYPE_STRING the following exception is thrown:

Exception in thread "main" java.lang.NumberFormatException: You cannot get a str
ing value from a numeric cell
        at org.apache.poi.hssf.usermodel.HSSFCell.getStringCellValue(HSSFCell.ja
        at org.apache.poi.hssf.usermodel.HSSFCell.setCellType(HSSFCell.java:495)

        at org.apache.poi.hssf.usermodel.HSSFCell.setCellType(HSSFCell.java:410)

        at org.apache.poi.hssf.usermodel.examples.ReadWriteWorkbook.main(ReadWri

in order to find out more i modified the example program and found the 
the cell defaults to CELL_TYPE_NUMERIC.
if the cell VALUE is set as a numeric with a value of 0, then the value is set 
to a null string, then the cell type is changed to a string, it works fine.

guess at what is happening: cell, upon change of type, accesses the data in the 
cell as the type it will become. the cell type has not changed yet, so there is 
an exception where the data, still as old type cell, is trying to be accessed 
as new type cell.

the same behavior occurs when trying to convert to numeric cell to string cell.
Comment 1 Avik Sengupta 2003-01-26 20:29:17 UTC
Well, the way it probably works is that when you set the content, the cell type
gets set automatically (ie, if you call cell.setStringValue .. it'll change its
type. I dont think its a good idea to change type irrespective of content. Given
that this bug hasnt recieved any vote apart from the reporter, i am inclined to
mark this as WONTFIX and clarify the docs a bit. 
Comment 2 Andy Oliver 2003-07-24 14:14:53 UTC
I agree.  Its not a bug..  You're not supposed to do that and thats what the
exception means :-)  (I think I wrote that exception....)
Comment 3 Nirupa Ravilla 2005-03-05 00:49:36 UTC
I am trying to read an excel file. One of the columns in the file is a numeric 
type, it contains dates. I want to change the cell type to a String, so that i 
can get the data as it is seen on the sheet...for example, cell 1 as an value 
of '06/01/2003'. since cell 1 is numeric, i get a numeric value - which is 
37773.0, i am trying to set the cell type to String so that i can get the 
String as 06/01/2003

this is how i set the cell type of cell 1
row.getCell((short) colIndex).setCellType(1);

and this is how i try to get the string value
row.getCell((short) colIndex).getStringCellValue());

i get this error
java.lang.NumberFormatException: You cannot get a string value from a numeric 
        at org.apache.poi.hssf.usermodel.HSSFCell.getStringCellValue
        at org.apache.poi.hssf.usermodel.HSSFCell.setCellType

        at org.apache.poi.hssf.usermodel.HSSFCell.setCellType

Nirupa Ravilla
Comment 4 Avik Sengupta 2005-05-20 11:41:13 UTC
Not a bug. In respect of the original issue, i think setCellValue should be
marked as deprecated, since its use from outside is fraught with strange semantics. 

In respect of Comment #3, that is an incorrect way of using the API. Please see
the documentation, or ask on the user mailing list.