Correct non-sheet-based NameXPxg formula rendering, and add tests

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

2 files changed, 44 insertions, 1 deletions

diff --git a/src/java/org/apache/poi/ss/formula/ptg/NameXPxg.java b/src/java/org/apache/poi/ss/formula/ptg/NameXPxg.java
index 1655790327..b1ca488c78 100644
--- a/src/java/org/apache/poi/ss/formula/ptg/NameXPxg.java
+++ b/src/java/org/apache/poi/ss/formula/ptg/NameXPxg.java
@@ -74,15 +74,20 @@ public final class NameXPxg extends OperandPtg implements Pxg {
 
     public String toFormulaString() {
         StringBuffer sb = new StringBuffer();
+        boolean needsExclamation = false;
         if (externalWorkbookNumber >= 0) {
             sb.append('[');
             sb.append(externalWorkbookNumber);
             sb.append(']');
+            needsExclamation = true;
         }
         if (sheetName != null) {
             SheetNameFormatter.appendFormat(sb, sheetName);
+            needsExclamation = true;
+        }
+        if (needsExclamation) {
+            sb.append('!');
         }
-        sb.append('!');
         sb.append(nameName);
         return sb.toString();
     }
diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java
index d18705eb28..13bde7910c 100644
--- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java
+++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java
@@ -24,7 +24,11 @@ import static org.junit.Assert.fail;
 import org.apache.poi.ss.formula.FormulaParseException;
 import org.apache.poi.ss.formula.FormulaParser;
 import org.apache.poi.ss.formula.FormulaType;
+import org.apache.poi.ss.formula.ptg.Area3DPxg;
+import org.apache.poi.ss.formula.ptg.AreaPtg;
+import org.apache.poi.ss.formula.ptg.AttrPtg;
 import org.apache.poi.ss.formula.ptg.FuncPtg;
+import org.apache.poi.ss.formula.ptg.FuncVarPtg;
 import org.apache.poi.ss.formula.ptg.IntPtg;
 import org.apache.poi.ss.formula.ptg.NamePtg;
 import org.apache.poi.ss.formula.ptg.NameXPxg;
@@ -78,6 +82,40 @@ public final class TestXSSFFormulaParser {
         } catch (FormulaParseException e){
             assertEquals("Specified named range 'XFD1048577' does not exist in the current workbook.", e.getMessage());
         }
+        
+        // Formula referencing one cell
+        ptgs = parse(fpb, "ISEVEN(A1)");
+        assertEquals(3, ptgs.length);
+        assertEquals(NameXPxg.class,   ptgs[0].getClass());
+        assertEquals(RefPtg.class,     ptgs[1].getClass());
+        assertEquals(FuncVarPtg.class, ptgs[2].getClass());
+        assertEquals("ISEVEN", ptgs[0].toFormulaString());
+        assertEquals("A1",     ptgs[1].toFormulaString());
+        assertEquals("#external#", ptgs[2].toFormulaString());
+        
+        // Formula referencing an area
+        ptgs = parse(fpb, "SUM(A1:B3)");
+        assertEquals(2, ptgs.length);
+        assertEquals(AreaPtg.class, ptgs[0].getClass());
+        assertEquals(AttrPtg.class, ptgs[1].getClass());
+        assertEquals("A1:B3", ptgs[0].toFormulaString());
+        assertEquals("SUM",   ptgs[1].toFormulaString());
+        
+        // Formula referencing one cell in a different sheet
+        ptgs = parse(fpb, "SUM(Sheet1!A1)");
+        assertEquals(2, ptgs.length);
+        assertEquals(Ref3DPxg.class, ptgs[0].getClass());
+        assertEquals(AttrPtg.class,  ptgs[1].getClass());
+        assertEquals("Sheet1!A1", ptgs[0].toFormulaString());
+        assertEquals("SUM",       ptgs[1].toFormulaString());
+        
+        // Formula referencing an area in a different sheet
+        ptgs = parse(fpb, "SUM(Sheet1!A1:B3)");
+        assertEquals(2, ptgs.length);
+        assertEquals(Area3DPxg.class,ptgs[0].getClass());
+        assertEquals(AttrPtg.class,  ptgs[1].getClass());
+        assertEquals("Sheet1!A1:B3", ptgs[0].toFormulaString());
+        assertEquals("SUM",          ptgs[1].toFormulaString());
     }
 
 	@Test