Bug 46944 - Error Value can only be 0,7,15,23,29,36 or 42. It cannot be -30
Summary: Error Value can only be 0,7,15,23,29,36 or 42. It cannot be -30
Status: RESOLVED LATER
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.2-FINAL
Hardware: PC Windows XP
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2009-03-31 07:05 UTC by criverapr
Modified: 2009-04-01 13:37 UTC (History)
0 users


Attachments
Excel and Java file to reproduce error (3.13 KB, application/zip)
2009-03-31 07:06 UTC, criverapr 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description criverapr 2009-03-31 07:05:08 UTC 
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)
Comment 1 criverapr 2009-03-31 07:06:02 UTC 
Created attachment 23431 [details]
Excel and Java file to reproduce error
Comment 2 criverapr 2009-03-31 13:23:04 UTC 
(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.
Comment 3 Josh Micich 2009-04-01 13:37:41 UTC 
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.