Bug 56254 - [PATCH] Reference to named range that does not exist causes exception
Summary: [PATCH] Reference to named range that does not exist causes exception
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 3.11-dev
Hardware: PC Linux
: P2 critical (vote)
Target Milestone: ---
Assignee: POI Developers List
Keywords: PatchAvailable
Depends on: 47312
  Show dependency tree
Reported: 2014-03-13 00:14 UTC by Cliff Binstock
Modified: 2016-06-20 05:27 UTC (History)
1 user (show)

Patch (and test) (664.18 KB, application/x-gzip)
2014-03-13 00:14 UTC, Cliff Binstock

Note You need to log in before you can comment on or make changes to this bug.
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.