| Summary: | Error Value can only be 0,7,15,23,29,36 or 42. It cannot be -30 | ||
|---|---|---|---|
| Product: | POI | Reporter: | criverapr |
| Component: | HSSF | Assignee: | POI Developers List <dev> |
| Status: | RESOLVED LATER | ||
| Severity: | normal | ||
| Priority: | P2 | ||
| Version: | 3.2-FINAL | ||
| Target Milestone: | --- | ||
| Hardware: | PC | ||
| OS: | Windows XP | ||
| Attachments: | Excel and Java file to reproduce error | ||
|
Description
criverapr
2009-03-31 07:05:08 UTC
Created attachment 23431 [details]
Excel and Java file to reproduce error
(In reply to comment #0) > I get the following exception when trying to refresh all formulas on an excel > sheet. I've attached an excel sheet and a java file that produce the error. > > > java.lang.RuntimeException: Error Value can only be 0,7,15,23,29,36 or 42. It > cannot be -30 > at > org.apache.poi.hssf.record.BoolErrRecord.setValue(BoolErrRecord.java:101) > at > org.apache.poi.hssf.usermodel.HSSFCell.setCellErrorValue(HSSFCell.java:750) > at > org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.setCellValue(HSSFFormulaEvaluator.java:222) > at > org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateInCell(HSSFFormulaEvaluator.java:194) > at > com.tech_innovapr.poi.examples.UpdateFormulas.refreshWorkbookFormulas(UpdateFormulas.java:67) > at > com.tech_innovapr.poi.examples.UpdateFormulas.main(UpdateFormulas.java:25) Ok I found that this doesn't happen when I use the FormulaEvaluator.evaluateFormulaCell which should be the method used for refreshing the sheet formulas instead of the one I was using(FormulaEvaluator.evaluateInCell). But I think the above error should be checked out anyways. The -30 code is caused by POI attempting to evaluate a function which has not been implemented. Part of the problem is that the -30 error value can silently propagate around, masquerading as a genuine Excel error code. So using FormulaEvaluator.evaluateInCell() has probably not avoided the real problem. Since bug 46580 , POI has been improved to throw an exception explaining which function needs to be implemented. In the file you have uploaded, there are actually two functions that POI does not support yet: ADDRESS() and INDIRECT(). The implementation of ADDRESS() should be relatively straightforward. INDIRECT() might be more complex because it involves invoking a formula parser on an arbitrary string value, during evaluation (something that hasn't been done in POI yet). Since you are using a combination of INDIRECT(ADDRESS(...)) it might be possible to replace this with OFFSET() (which POI *can* evaluate). I am marking this bug as 'LATER' in the hope that OFFSET() might do what you need. Please change the status if you can't find an acceptable work around. As always, we are happy to receive patches from new contributors. |