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
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
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.
Created attachment 25193 [details] Trintech_POI examples.zip
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
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...
Apache POI is a volunteer project. If this new feature is important to you, please do work on it and send in patches!
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
Is there ANY workaround?
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!
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.
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
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);