Bug 48140 - Cannot get a numeric value from a text cell
Summary: Cannot get a numeric value from a text cell
Status: RESOLVED INVALID
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.5-FINAL
Hardware: PC Windows Vista
: P2 blocker (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2009-11-05 08:42 UTC by Michel Jung
Modified: 2009-11-24 08:38 UTC (History)
0 users



Attachments
The file I try to read (16.36 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2009-11-05 08:42 UTC, Michel Jung
Details

Note You need to log in before you can comment on or make changes to this bug.
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.