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 va:757) 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 teWorkbook.java:86) in order to find out more i modified the example program and found the following: 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.
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.
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....)
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 ce ll at org.apache.poi.hssf.usermodel.HSSFCell.getStringCellValue (HSSFCell.ja va:775) at org.apache.poi.hssf.usermodel.HSSFCell.setCellType (HSSFCell.java:473) at org.apache.poi.hssf.usermodel.HSSFCell.setCellType (HSSFCell.java:388) thanks. Nirupa Ravilla
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.