Bug 56702 - Get one exception "java.lang.IllegalStateException: Cannot get a numeric value from a text cell" when calling org.apache.poi.ss.usermodel.DataFormatter.formatCellValue
Summary: Get one exception "java.lang.IllegalStateException: Cannot get a numeric valu...
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.13-FINAL
Hardware: PC All
: P2 normal with 4 votes (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks: 57062
  Show dependency tree
 
Reported: 2014-07-08 07:31 UTC by sangshenghong
Modified: 2016-04-05 15:24 UTC (History)
1 user (show)



Attachments
Error file (24.52 KB, application/x-msdownload)
2014-07-11 02:04 UTC, sangshenghong
Details
Test java class (939 bytes, text/plain)
2014-07-11 02:04 UTC, sangshenghong
Details
hssf behaviour .java test file (2.87 KB, application/java)
2016-01-24 15:07 UTC, Herve
Details
XSSF java test class (2.87 KB, application/java)
2016-01-24 15:08 UTC, Herve
Details
XSSF Excel test file (.xlsx) (8.61 KB, application/excel)
2016-01-24 15:10 UTC, Herve
Details
HSSF Excel test file (.xls) (24.00 KB, application/excel)
2016-01-24 15:11 UTC, Herve
Details
HSSF java test class (ExcelBugPoiHSSF.java) (2.87 KB, application/java)
2016-01-24 15:13 UTC, Herve
Details

Note You need to log in before you can comment on or make changes to this bug.
Description sangshenghong 2014-07-08 07:31:16 UTC
There in one cell which value is empty string instead of null cell.
I got the exception 
Caused by: java.lang.IllegalStateException: Cannot get a numeric value from a text cell
	at org.apache.poi.xssf.usermodel.XSSFCell.typeMismatch(XSSFCell.java:845)
	at org.apache.poi.xssf.usermodel.XSSFCell.getNumericCellValue(XSSFCell.java:202)
	at org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(DateUtil.java:325)
	at org.apache.poi.ss.usermodel.DataFormatter.formatCellValue(DataFormatter.java:756)


Also, I debugged source code and found that the cell type is CELL_TYPE_NUMERIC, so when it convert to double, the exception will be thrown.
Comment 1 Nick Burch 2014-07-08 16:29:00 UTC
3.8 is a little old now, can you please try with a recent svn checkout / nightly build, and see if the problem still remains?
Comment 2 sangshenghong 2014-07-10 08:08:07 UTC
(In reply to Nick Burch from comment #1)
> 3.8 is a little old now, can you please try with a recent svn checkout /
> nightly build, and see if the problem still remains?

Thanks for caring this issue.  I tried the 3.10 version, but this issue still exist.
Comment 3 Nick Burch 2014-07-10 12:42:44 UTC
Any chance you could provide a small junit test case that shows the problem, so we can try to reproduce it? Please include a small sample file if one's needed to show the issue.
Comment 4 sangshenghong 2014-07-11 02:04:04 UTC
Created attachment 31809 [details]
Error file
Comment 5 sangshenghong 2014-07-11 02:04:25 UTC
Created attachment 31810 [details]
Test java class
Comment 6 sangshenghong 2014-07-11 02:09:07 UTC
(In reply to Nick Burch from comment #3)
> Any chance you could provide a small junit test case that shows the problem,
> so we can try to reproduce it? Please include a small sample file if one's
> needed to show the issue.

I have attached the test java class and error excel file.please notes that the Row 8 and Column 7 is empty string cell not null cell
Comment 7 Nick Burch 2014-07-14 18:54:19 UTC
Thanks for the test file, fixed in r1610482.
Comment 8 sangshenghong 2014-07-15 02:27:39 UTC
Thanks for your fixing, which version I can use?
Comment 9 sangshenghong 2014-07-25 01:39:45 UTC
Hi Nick:
Could you tell me which version I can use?
Comment 10 Nick Burch 2014-07-25 08:36:22 UTC
For now, a nightly snapshot or svn checkout from trunk

It'll be in 3.11 beta 1, once that is released
Comment 11 Dominik Stadler 2015-07-16 19:55:35 UTC
Can you provide some information why you think this is not fixed? Did you try with the latest version? Can you provide samples or sample code to show that it still does not work for you?
Comment 12 Herve 2015-09-03 13:28:57 UTC
I got this bug with version 3.12final

Here is my context : I ask for the values of a range of cells (tab!A1:A4 for instance).  
In the Excel file, tab!A1 contains a formula with hlookup (or vlookup, i don't remember) and I copy tab!A1 to tab!A1:A3  
The result of A1 is a string, the result of A2 is an empty cell (it displays 0).

POI computed tab!A1 value ok,  
and then raises the error on cell tab!A2.

I found that the exception was risen by HSSFCell.getNumericCellValue which was called by DateUtil.isCellDateFormatted.  
The issue I found is that HSSFCell.getNumericCellValue calls checkFormulaCachedValueType(CELL_TYPE_NUMERIC, (FormulaRecord)fr) and checkFormulaCachedValueType() returns stringtype for A2, despite the fact that A2 contains a number.  
I guess that the FormulaRecord of tab!A1 is the same as FormulaRecord of tab!A2 and as tab!A1 contains a string and caches this type, checkFormulaCachedValueType returns also string for tab!A2.

I have not enough knowledge of POI code to propose a fix,
but I put the workaround I used there : http://stackoverflow.com/questions/6508203/when-getting-cell-content-using-apache-poi-library-i-get-both-cannot-get-a-num/32376505#32376505
Comment 13 Herve 2016-01-24 15:07:55 UTC
Created attachment 33484 [details]
hssf behaviour .java test file
Comment 14 Herve 2016-01-24 15:08:54 UTC
Created attachment 33485 [details]
XSSF java test class
Comment 15 Herve 2016-01-24 15:10:58 UTC
Created attachment 33486 [details]
XSSF Excel test file (.xlsx)
Comment 16 Herve 2016-01-24 15:11:42 UTC
Created attachment 33487 [details]
HSSF Excel test file (.xls)
Comment 17 Herve 2016-01-24 15:13:29 UTC
Created attachment 33488 [details]
HSSF java test class (ExcelBugPoiHSSF.java)
Comment 18 Herve 2016-01-24 15:13:49 UTC
Hi,
I finished to get some time to go more deeply into my bug.
I've not found a fix but the mecanism is very clear now :
1) in HSSFCell and also in XSSFCell, the type of a cell which contains a formula is retreived without computing the formula from an attribut of the formulaRecord named specialCachedValue
2) this attribute specialCachedValue seems to be computed when the excel file is load and not refreshed after that
3) if the type of the result of the formula changes by a computation, the type returned for the cell may be wrong.

To reproduce this behaviour, look at files :
- ExcelBugPoiHSSF.java and ExcelBugPoi.xls for HSSF behaviour
- ExcelBugPoiXSSF.java and ExcelBugPoi.xlsx for XSSF behaviour
(both .java files are junit test files)
Comment 19 Herve 2016-02-21 00:01:36 UTC
Hi,

I have tested a new diagnostic for this bug
done on the last version (3.13) and with an complete example to reproduce the issue (in both xssf and hssf format)
(First time, my bug interested several people, but this time : none !, that is why I add this comment)

Herve