|Summary:||Cannot get a numeric value from a text cell|
|Product:||POI||Reporter:||Michel Jung <michel.jung89>|
|Component:||XSSF||Assignee:||POI Developers List <dev>|
|Attachments:||The file I try to read|
Description Michel Jung 2009-11-05 08:42:56 UTC
Created attachment 24490 [details] The file I try to read I have a column with a lot of numbers (for instance 1253323, 275527, ... ) and try to get them using getNumericCellValue(). It throws an exception for each row: java.lang.IllegalStateException: Cannot get a numeric value from a text cell Of course I already checked for the correct value: Could not convert "2844922" from row 575: java.lang.IllegalStateException: Cannot get a numeric value from a text cell There is no problem with getNumericCellValue() in other two rows where I have values like 0.50625, 0.75, 50 . I've already tried to set the cell's format to "numeric" (or however it's called in the english excel). No success. I hope it's not my fault...
Comment 1 Michel Jung 2009-11-05 08:46:02 UTC
Here comes the stack trace: java.lang.IllegalStateException: Cannot get a numeric value from a text cell at org.apache.poi.xssf.usermodel.XSSFCell.typeMismatch(XSSFCell.java:768) at org.apache.poi.xssf.usermodel.XSSFCell.getNumericCellValue(XSSFCell.java:191) at xxx.readInstrumentLines(Converter.java:347) at xxx.run(Converter.java:412)
Comment 2 Nick Burch 2009-11-05 11:01:53 UTC
You need to check the type of the cell. If it's of type numeric, then call getNumericCellValue(). If it's of type string, call getRichStringCellValue(). The type tells you which one you can call. Sometimes, excel decides to store a number in a cell of type string. For those cases, fetch the string value and parse it to a number in java. See the documentation on the site and/or javadocs for more details
Comment 3 Himanshu 2009-11-24 08:25:34 UTC
I am having difficulties in reading an excel file which contains data of mix type. For ex: If a cell contains value as "444444" with cell type as "General" then why Apache POI is returning the value as "444444.0" and no way to get the original value which is displayed when we open the document in MS Excel. We cannot expect the customer to provide the data with correct cell data type, Apache POI should return the same data value which is displayed when opening the document and should additionally provide the string equivalent for numeric data even. There is no place in your library classes where I see the returned data consider the settings (2 decimal digits of precision) in Excel document being taken care while returning the cell value. I previously used jexcel-Api and recently planned to shift to use Apache POI to support Excel 2007 files. But even in the initial bit it is a bit disappointing looking at the data returned and available from your library. Could you suggest me If there is a way possible to get the data in the required format present and displayed by MS-Excel. Hoping for an early response. Thanks, Himanshu
Comment 4 Nick Burch 2009-11-24 08:38:05 UTC
Please do not ask basic usage questions by re-opening old bugs relating to something else! Check the list archives, yours is a common misconception about excel, and all the answers you seek have come up on the list many times.