[github-269] more work on external workbook links (formulas). Thanks to @aspojo. This closes #269

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1894695 13f79535-47bb-0310-9956-ffa450edef68

4 files changed, 33 insertions, 9 deletions

diff --git a/poi/src/main/java/org/apache/poi/ss/formula/FormulaParser.java b/poi/src/main/java/org/apache/poi/ss/formula/FormulaParser.java
index 94c88219e9..31649a5d85 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/FormulaParser.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/FormulaParser.java
@@ -1171,10 +1171,18 @@ public final class FormulaParser {
             while(!done) {
                 sb.appendCodePoint(look);
                 GetChar();
-                if(look == '\'')
-                {
-                    Match('\'');
-                    done = look != '\'';
+                switch (look){
+                    case '\'' : {
+                        GetChar();
+                        if (look == '\''){
+                            // Any single quotes which were already present in the sheet name will be converted to double single quotes ('')
+                            // so switch back to single quote
+                            GetChar();
+                            break;
+                        }
+                    }
+                    case ':':
+                        done = true;
                 }
             }
 
@@ -1200,6 +1208,9 @@ public final class FormulaParser {
                 sb.appendCodePoint(look);
                 GetChar();
             }
+            if (look == '\'') {
+                GetChar();
+            }
             NameIdentifier iden = new NameIdentifier(sb.toString(), false);
             SkipWhite();
             if (look == '!') {
@@ -1249,6 +1260,7 @@ public final class FormulaParser {
         switch(ch) {
             case '.': // dot is OK
             case '_': // underscore is OK
+            case ' ': // space is OK
                 return true;
         }
         return false;
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/ptg/AreaPtgBase.java b/poi/src/main/java/org/apache/poi/ss/formula/ptg/AreaPtgBase.java
index 496300720b..84cc42c81a 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/ptg/AreaPtgBase.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/ptg/AreaPtgBase.java
@@ -290,6 +290,9 @@ public abstract class AreaPtgBase extends OperandPtg implements AreaI {
         if(AreaReference.isWholeColumnReference(SpreadsheetVersion.EXCEL97, topLeft, botRight)) {
             return (new AreaReference(topLeft, botRight, SpreadsheetVersion.EXCEL97)).formatAsString();
         }
+        if (AreaReference.isWholeColumnReference(SpreadsheetVersion.EXCEL2007, topLeft, botRight)){
+            return new AreaReference(topLeft, botRight,SpreadsheetVersion.EXCEL2007).formatAsString();
+        }
         return topLeft.formatAsString() + ":" + botRight.formatAsString();
     }
 
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/ptg/ExternSheetNameResolver.java b/poi/src/main/java/org/apache/poi/ss/formula/ptg/ExternSheetNameResolver.java
index 3a35909cc2..dd2e2ddf37 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/ptg/ExternSheetNameResolver.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/ptg/ExternSheetNameResolver.java
@@ -44,7 +44,16 @@ final class ExternSheetNameResolver {
                 ExternalSheetRange r = (ExternalSheetRange)externalSheet;
                 if (! r.getFirstSheetName().equals(r.getLastSheetName())) {
                     sb.append(':');
-                    SheetNameFormatter.appendFormat(sb, r.getLastSheetName());
+                    // quote should appear at the beginning and end.
+                    StringBuilder temp = new StringBuilder();
+                    SheetNameFormatter.appendFormat(temp, r.getLastSheetName());
+                    char quote = '\'';
+                    if (temp.charAt(0) == quote){
+                        sb.insert(0 , quote);
+                        sb.append(temp.substring(1));
+                    }else {
+                        sb.append(temp);
+                    }
                 }
             }
         } else {
diff --git a/poi/src/test/java/org/apache/poi/hssf/model/TestFormulaParser.java b/poi/src/test/java/org/apache/poi/hssf/model/TestFormulaParser.java
index 6c55d18224..0768cd713e 100644
--- a/poi/src/test/java/org/apache/poi/hssf/model/TestFormulaParser.java
+++ b/poi/src/test/java/org/apache/poi/hssf/model/TestFormulaParser.java
@@ -535,9 +535,9 @@ final class TestFormulaParser {
         assertEquals("'Test Sheet'!A1", formula);
 
         // Now both
-        cell.setCellFormula("Cash_Flow:'Test Sheet'!A1");
+        cell.setCellFormula("'Cash_Flow:Test Sheet'!A1");
         formula = cell.getCellFormula();
-        assertEquals("Cash_Flow:'Test Sheet'!A1", formula);
+        assertEquals("'Cash_Flow:Test Sheet'!A1", formula);
 
 
         // References to a range (area) of cells:
@@ -553,9 +553,9 @@ final class TestFormulaParser {
         assertEquals("'Test Sheet'!A1:B2", formula);
 
         // Now both
-        cell.setCellFormula("Cash_Flow:'Test Sheet'!A1:B2");
+        cell.setCellFormula("'Cash_Flow:Test Sheet'!A1:B2");
         formula = cell.getCellFormula();
-        assertEquals("Cash_Flow:'Test Sheet'!A1:B2", formula);
+        assertEquals("'Cash_Flow:Test Sheet'!A1:B2", formula);
 
         wb.close();
     }