Bug 46670

Summary: Reference to cell in different workbook not possible
Product: POI Reporter: Wim Goeman <wimgoeman>
Component: SS CommonAssignee: POI Developers List <dev>
Status: NEEDINFO ---    
Severity: enhancement CC: dave.sprague, valeriy.panfilov
Priority: P2    
Version: 3.2-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   
Bug Depends on: 45970    
Bug Blocks:    
Attachments: Trintech_POI examples.zip
Failed test case for 3.11-beta2

Description Wim Goeman 2009-02-07 02:58:17 UTC
When I try to refer to a cell in a different excel file, I do not get an error, but when I open the file in Excel, the contents of the cell is #REF!A1 for example. I used this code:

cell.setCellFormula("folder\\[workbook.xls]sheet!A1");

Since I cannot find anything about this problem, I thought it could be a bug.

Greetings,

Wim
Comment 1 dave.sprague 2010-03-18 22:42:54 UTC
I've discovered an issue (that appears to be similar to this issue) within an application that we have build (using POI 3.6). I can provide examples as needed, but is this specific issue slated for inclusion within POI 3.7?

Thanks,

Dave Sprague
Director, Product Management
Trintech
Comment 2 dave.sprague 2010-03-26 21:07:52 UTC
Trintech is supplying a zip file with examples. Please review file entitled 2160_1900_801_+_810_XXX_FY10_P05 (2).xls to determine if this issue is related.
Comment 3 dave.sprague 2010-03-26 21:10:32 UTC
Created attachment 25193 [details]
Trintech_POI examples.zip
Comment 4 Nick Burch 2010-06-04 10:52:10 UTC
This looks to be very similar to bug #45970, except that was for URLs and this is for local files. I suspect a fix for one will fix the other
Comment 5 Nico 2011-06-07 17:34:17 UTC
I got the same problem on POI 3.6, and i see this issue has been opened 2 years ago. 
This is a very important feature for developers working with excel files. 
I do not understand why is still unsolved...
Comment 6 Nick Burch 2011-06-07 21:09:16 UTC
Apache POI is a volunteer project. If this new feature is important to you, please do work on it and send in patches!
Comment 7 Nico 2011-06-08 07:43:24 UTC
I'm very disappointed with your banal reply. 
I know that this is a volunteer project, in my post I was just pointing out that in 2 years none has noticed this important issue, and I do not understand the roughness of your reply.
Greetings
Comment 8 panfil 2014-10-16 07:55:02 UTC
Is there ANY workaround?
Comment 9 Nick Burch 2014-10-16 08:34:27 UTC
There was a large amount of work done on 3D references (workbook+sheet+cell) in 3.11 beta 1, see http://poi.apache.org/changes.html#3.11-beta1 . A lot of things that used to fail with these now work. 

It'd therefore be great if someone could re-test this specific issue with 3.11 beta 2, and see if the work in there has fixed this case as well. If not, a small junit unit test showing how this issue remains would be most helpful!
Comment 10 panfil 2014-10-16 11:38:44 UTC
Created attachment 32116 [details]
Failed test case for 3.11-beta2

I wrote a simple test case for poi 3.11-beta2. I tried to modify some references to another .xls files, can be accessed locally or over http. The test failed, when I try to set cell formula with external reference it gives me #REF all the time.
Comment 11 Nick Burch 2014-10-20 23:24:17 UTC
Thanks for the tests

Based on this, I've added in r1633257 something based on your tests for HSSF only, along with an expanded version for HSSF and XSSF

Bad news - it fails on both, for different reasons... I suspect someone will need to fix the XSSF case first, as that should be simpler to understand, then look into why the HSSF one is failing earlier
Comment 12 Dominik Stadler 2015-05-17 21:53:32 UTC
I tried to work on this for the XSSF side, the following will parse the bookname when the formula is parsed, although the unit test does not build the "ExternalLinkTable" and thus the unit tests still fails and I could not see how that can be fixed correctly in the test or in the product code.

diff --git a/src/java/org/apache/poi/ss/formula/FormulaParser.java b/src/java/org/apache/poi/ss/formula/FormulaParser.java
index 72ed008..6940a71 100644
--- a/src/java/org/apache/poi/ss/formula/FormulaParser.java
+++ b/src/java/org/apache/poi/ss/formula/FormulaParser.java
@@ -792,6 +792,18 @@ public final class FormulaParser {
                        StringBuffer sb = new StringBuffer();
 
                        Match('\'');
+                       // try again to read external workbook-name as it could have been enclosed in the single quotes
+                       if (look == '[') {
+                               StringBuilder sbBook = new StringBuilder();
+                               GetChar();
+                               while (look != ']') {
+                                       sbBook.append(look);
+                                       GetChar();
+                               }
+                               GetChar();
+                               bookName = sbBook.toString();
+                       }
+
                        boolean done = look == '\'';
                        while(!done) {
                                sb.append(look);