| 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: | XSSF | Assignee: | POI Developers List <dev> |
| Status: | RESOLVED LATER | ||
| 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
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? (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. 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. Created attachment 31809 [details]
Error file
Created attachment 31810 [details]
Test java class
(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 Thanks for your fixing, which version I can use? Hi Nick: Could you tell me which version I can use? For now, a nightly snapshot or svn checkout from trunk It'll be in 3.11 beta 1, once that is released 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? 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 Created attachment 33484 [details]
hssf behaviour .java test file
Created attachment 33485 [details]
XSSF java test class
Created attachment 33486 [details]
XSSF Excel test file (.xlsx)
Created attachment 33487 [details]
HSSF Excel test file (.xls)
Created attachment 33488 [details]
HSSF java test class (ExcelBugPoiHSSF.java)
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) 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 Upvoted this bug. Just faced it on Production server. That was fully unexpected that method isCellDateFormatted could throw an IllegalStateException! 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? No response for a long time so I am closing this for now until more information becomes available. |