Bug 56702

Summary: Get one exception "java.lang.IllegalStateException: Cannot get a numeric value from a text cell" when calling org.apache.poi.ss.usermodel.DataFormatter.formatCellValue
Product: POI Reporter: sangshenghong <278880105>
Component: XSSFAssignee: POI Developers List <dev>
Severity: major CC: herve.guerin, vitali_chasalau
Priority: P3    
Version: 3.13-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   
Bug Depends on:    
Bug Blocks: 57062    
Attachments: Error file
Test java class
hssf behaviour .java test file
XSSF java test class
XSSF Excel test file (.xlsx)
HSSF Excel test file (.xls)
HSSF java test class (ExcelBugPoiHSSF.java)

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
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

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)

Comment 20 Vitali 2017-06-23 13:00:56 UTC
Upvoted this bug.
Just faced it on Production server. That was fully unexpected that method isCellDateFormatted could throw an IllegalStateException!
Comment 21 Nick Burch 2017-06-23 14:10:32 UTC
Is someone able to produce a unit test against the very latest version of Apache POI, showing this problem still remaining? 

This bug was initially reported against quite an old version of POI, and some fixes have been done for it, so it's hard to know what (if anything) still remains unfixed on the latest versions, and what is just people tripping over a know bug on old versions of POI?
Comment 22 Dominik Stadler 2020-03-08 13:27:54 UTC
No response for a long time so I am closing this for now until more information becomes available.