Bug 56254

Summary: [PATCH] Reference to named range that does not exist causes exception
Product: POI Reporter: Cliff Binstock <cliff.binstock>
Component: SS CommonAssignee: POI Developers List <dev>
Status: NEW ---    
Severity: critical CC: cliff.binstock
Priority: P2 Keywords: PatchAvailable
Version: 3.11-dev   
Target Milestone: ---   
Hardware: PC   
OS: Linux   
Bug Depends on: 47312    
Bug Blocks:    
Attachments: Patch (and test)

Description Cliff Binstock 2014-03-13 00:14:20 UTC
Created attachment 31385 [details]
Patch (and test)

Referencing a syntactically correct named range that does not exist should result in a cell with a #REF! error.  However, it creates an exception.

I have submitted a patch that works for INDIRECT() reference which solves my immediate need, but the exception for a direct reference is pretty "deep" in the stack:  I suspect that there should be a unique exception created that is caught by the formula evaluator, which would then set the error code properly.

Note that the patch includes:  1) code fix; 2) working test for INDIRECT reference; 3) commented out test for direct reference.
Comment 1 Dominik Stadler 2015-03-14 22:37:18 UTC
FYI, the direct case can be done by returning "new ParseNode(ErrPtg.REF_INVALID);" instead of throwing the Exception.

Note however that this will change what was fixed in bug 47312 as with this the parser is less strict and will allow invalid formulas that it does reject now. 

While the change makes POI a bit more do whatever Excel does it actually makes working with the lib less fun in some cases as the error is much more hidden in a simple #REF! instead of a detailed error message in an Exception.
Comment 2 Javen O'Neal 2016-06-20 05:27:29 UTC
Perhaps a compromise would be to log to the POI Logger when an invalid reference is found. Otherwise, we would need to write a second Formula Parser or modify the existing to let the user chooser what kinds of errors it will propagate and what kinds of errors will raise an exception.

Alternatively, we could write a formula evaluator that steps through one layer at a time.