Bug 49258

Summary: Not existing external links cause crash
Product: POI Reporter: Vasili <gavr145>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED INVALID    
Severity: normal    
Priority: P2    
Version: 3.7-dev   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Attachments: patch screens the exceptions thrown due to errors during evaluation of forumas

Description Vasili 2010-05-06 10:44:33 UTC 
I think, the code should rather check such references, being silent - in case there are such unresolved references, ignoring them.
Anyway, no sense in such references in Excel 2003- old files and the workbook may be processed without them.
The benefit would be - compatibility with the old code (jxl, for example) which ignores them. Now we have backward incompatibility - after porting of the old code to POI, getting exception in files which were processed without problems with the old code.


------8<------
java.lang.Class - java.lang.RuntimeException: Could not resolve external workbook name 'Documents and SettingsuserLocal SettingsTemporary Internet FilesOLK91097209YE01.xls'. Workbook environment has not been set up.
	at org.apache.poi.ss.formula.OperationEvaluationContext.createExternSheetRefEvaluator(OperationEvaluationContext.java:84)
	at org.apache.poi.ss.formula.OperationEvaluationContext.getRef3DEval(OperationEvaluationContext.java:244)
	at org.apache.poi.ss.formula.WorkbookEvaluator.getEvalForPtg(WorkbookEvaluator.java:554)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:440)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:261)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:207)
	at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCellValue(HSSFFormulaEvaluator.java:317)
	at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluate(HSSFFormulaEvaluator.java:179)
	at org.is.jxlpoi.JXLPOIWorkbook.getCellContentAsString(JXLPOIWorkbook.java:172)

------8<------
Comment 1 Nick Burch 2010-05-06 10:47:52 UTC 
If you don't have the external files, you can't calculate the formulas

So, either don't explicitly trigger the calculation of the formulas (use the cached values instead), or get the files to hand before you trigger the evaluation
Comment 2 Vasili 2010-05-06 11:14:54 UTC 
(In reply to comment #1)
> If you don't have the external files, you can't calculate the formulas
> 
> So, either don't explicitly trigger the calculation of the formulas (use the
> cached values instead), or get the files to hand before you trigger the
> evaluation

I understand the 1st. 
But we have no control on what users may leave (they may leave references).
JXL code processes such 2003 spreadsheets, POI crashes, with the same approach.
Isn't it easier - to put try/catch in POI code or to test File.exists(), treating the links as not existing?
The problem that the references are resolved inside POI code and our code just evaluates formulas - when it is a formula, calling formulaEvaluator.evaluate()-  when formula is found  (so has no knowledge that it is a file/reference at all. And the path to the file is resolved inside POI). 

As I believe - the check File.exists or try/catch and compatibility with the old code (which is still in production) will be only beneficial.
Comment 3 Vasili 2010-05-06 11:20:22 UTC 
so, please consider this as a request for a very useful (at least for us) feature, which as I suspect - not a big problem to insert (I understand that this is not a bug).

Thanks for replying!

Regards
vasili
Comment 4 David Fisher 2010-05-06 11:31:08 UTC 
If you would like to offer a patch, contributions are welcome. A patch would include a unit test that shows throwing the error and catching the error. The feature - "Allow unresolved external references" -  would need to be a setting. I'm not sure of the best place, but likely in the FormulaEvaluator interface. Perhaps Josh has an opinion.
Comment 5 Vasili 2010-05-06 15:22:44 UTC 
Created attachment 25410 [details]
patch screens the exceptions thrown due to errors during evaluation of forumas

This patch works for me, making POI behaving like jxl, i.e. ignoring broken links in formulas.
Comment 6 Vasili 2010-05-06 15:29:52 UTC 
Obviously, you, guys will do it cleaner. I just made a dirty fix for our case, mimicking JXL's behavior, catching all exceptions been caused by incorrect formula evaluation. So, formulas will be evaluated in any case - if  
formulaEvaluator.setIgnoreFormulaException() was previously invoked. 

So, the patch does not break backward compatibility.

Oh, sorry, I'm not sending unit test (our spreadsheets are too complex, and I do not know Excel good enough - to reproduce the same manually).
Comment 7 Vasili 2010-05-06 16:36:30 UTC 
Auch,

I just got failures on my unit tests, so the patch breaks formulas initializer.
Not such simple, as always...

Please ignore the patch.