From 846a1f84a2ee08a731f35f66086e04aebfaf56bf Mon Sep 17 00:00:00 2001 From: Nick Burch Date: Fri, 25 Jul 2014 10:22:21 +0000 Subject: [PATCH] Update Ref3DPxg to support multi-sheet ranges too, for #55906 git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1613376 13f79535-47bb-0310-9956-ffa450edef68 --- .../apache/poi/ss/formula/ptg/Ref3DPxg.java | 49 ++++++++++++++----- .../usermodel/XSSFEvaluationWorkbook.java | 6 +-- .../xssf/usermodel/TestXSSFFormulaParser.java | 16 +++--- 3 files changed, 44 insertions(+), 27 deletions(-) diff --git a/src/java/org/apache/poi/ss/formula/ptg/Ref3DPxg.java b/src/java/org/apache/poi/ss/formula/ptg/Ref3DPxg.java index d851b4474b..ce007d0415 100644 --- a/src/java/org/apache/poi/ss/formula/ptg/Ref3DPxg.java +++ b/src/java/org/apache/poi/ss/formula/ptg/Ref3DPxg.java @@ -17,7 +17,9 @@ package org.apache.poi.ss.formula.ptg; +import org.apache.poi.ss.formula.SheetIdentifier; import org.apache.poi.ss.formula.SheetNameFormatter; +import org.apache.poi.ss.formula.SheetRangeIdentifier; import org.apache.poi.ss.util.CellReference; import org.apache.poi.util.LittleEndianOutput; @@ -31,21 +33,28 @@ import org.apache.poi.util.LittleEndianOutput; */ public final class Ref3DPxg extends RefPtgBase implements Pxg { private int externalWorkbookNumber = -1; - private String sheetName; + private String firstSheetName; + private String lastSheetName; - public Ref3DPxg(int externalWorkbookNumber, String sheetName, String cellref) { + public Ref3DPxg(int externalWorkbookNumber, SheetIdentifier sheetName, String cellref) { this(externalWorkbookNumber, sheetName, new CellReference(cellref)); } - public Ref3DPxg(int externalWorkbookNumber, String sheetName, CellReference c) { + public Ref3DPxg(int externalWorkbookNumber, SheetIdentifier sheetName, CellReference c) { super(c); this.externalWorkbookNumber = externalWorkbookNumber; - this.sheetName = sheetName; + + this.firstSheetName = sheetName.getSheetIdentifier().getName(); + if (sheetName instanceof SheetRangeIdentifier) { + this.lastSheetName = ((SheetRangeIdentifier)sheetName).getLastSheetIdentifier().getName(); + } else { + this.lastSheetName = null; + } } - public Ref3DPxg(String sheetName, String cellref) { + public Ref3DPxg(SheetIdentifier sheetName, String cellref) { this(sheetName, new CellReference(cellref)); } - public Ref3DPxg(String sheetName, CellReference c) { + public Ref3DPxg(SheetIdentifier sheetName, CellReference c) { this(-1, sheetName, c); } @@ -58,7 +67,11 @@ public final class Ref3DPxg extends RefPtgBase implements Pxg { sb.append("workbook=").append(getExternalWorkbookNumber()); sb.append("] "); } - sb.append("sheet=").append(getSheetName()); + sb.append("sheet=").append(firstSheetName); + if (lastSheetName != null) { + sb.append(" : "); + sb.append("sheet=").append(lastSheetName); + } sb.append(" ! "); sb.append(formatReferenceAsString()); sb.append("]"); @@ -68,12 +81,18 @@ public final class Ref3DPxg extends RefPtgBase implements Pxg { public int getExternalWorkbookNumber() { return externalWorkbookNumber; } - public String getSheetName() { - return sheetName; + public String getSheetName() { // TODO Rename to getFirstSheetName + return firstSheetName; + } + public String getLastSheetName() { + return lastSheetName; } - public void setSheetName(String sheetName) { - this.sheetName = sheetName; + public void setSheetName(String sheetName) { // TODO Rename to setFirstSheetName + this.firstSheetName = sheetName; + } + public void setLastSheetName(String sheetName) { + this.lastSheetName = sheetName; } public String format2DRefAsString() { @@ -87,8 +106,12 @@ public final class Ref3DPxg extends RefPtgBase implements Pxg { sb.append(externalWorkbookNumber); sb.append(']'); } - if (sheetName != null) { - SheetNameFormatter.appendFormat(sb, sheetName); + if (firstSheetName != null) { + SheetNameFormatter.appendFormat(sb, firstSheetName); + } + if (lastSheetName != null) { + sb.append(':'); + SheetNameFormatter.appendFormat(sb, lastSheetName); } sb.append('!'); sb.append(formatReferenceAsString()); diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java index 84f6366fe0..5c3773d1cb 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java @@ -192,13 +192,11 @@ public final class XSSFEvaluationWorkbook implements FormulaRenderingWorkbook, E } } public Ptg get3DReferencePtg(CellReference cell, SheetIdentifier sheet) { - String sheetName = sheet._sheetIdentifier.getName(); - if (sheet._bookName != null) { int bookIndex = resolveBookIndex(sheet._bookName); - return new Ref3DPxg(bookIndex, sheetName, cell); + return new Ref3DPxg(bookIndex, sheet, cell); } else { - return new Ref3DPxg(sheetName, cell); + return new Ref3DPxg(sheet, cell); } } public Ptg get3DReferencePtg(AreaReference area, SheetIdentifier sheet) { 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 bf86a004cb..65e269d546 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java @@ -283,19 +283,17 @@ public final class TestXSSFFormulaParser { // Check things parse as expected: - // TODO Fix it so that the Pxgs get the second sheet too! // SUM to one cell over 3 workbooks, relative reference ptgs = parse(fpb, "SUM(Sheet1:Sheet3!A1)"); assertEquals(2, ptgs.length); if (wb instanceof HSSFWorkbook) { - assertEquals(Ref3DPtg.class, ptgs[0].getClass()); - assertEquals("Sheet1:Sheet3!A1", toFormulaString(ptgs[0], fpb)); + assertEquals(Ref3DPtg.class, ptgs[0].getClass()); } else { assertEquals(Ref3DPxg.class, ptgs[0].getClass()); - assertEquals("Sheet1!A1", toFormulaString(ptgs[0], fpb)); } + assertEquals("Sheet1:Sheet3!A1", toFormulaString(ptgs[0], fpb)); assertEquals(AttrPtg.class, ptgs[1].getClass()); assertEquals("SUM", toFormulaString(ptgs[1], fpb)); @@ -304,12 +302,11 @@ public final class TestXSSFFormulaParser { ptgs = parse(fpb, "MAX(Sheet1:Sheet3!A$1)"); assertEquals(2, ptgs.length); if (wb instanceof HSSFWorkbook) { - assertEquals(Ref3DPtg.class, ptgs[0].getClass()); - assertEquals("Sheet1:Sheet3!A$1", toFormulaString(ptgs[0], fpb)); + assertEquals(Ref3DPtg.class, ptgs[0].getClass()); } else { assertEquals(Ref3DPxg.class, ptgs[0].getClass()); - assertEquals("Sheet1!A$1", toFormulaString(ptgs[0], fpb)); } + assertEquals("Sheet1:Sheet3!A$1", toFormulaString(ptgs[0], fpb)); assertEquals(FuncVarPtg.class, ptgs[1].getClass()); assertEquals("MAX", toFormulaString(ptgs[1], fpb)); @@ -318,12 +315,11 @@ public final class TestXSSFFormulaParser { ptgs = parse(fpb, "MIN(Sheet1:Sheet3!$A$1)"); assertEquals(2, ptgs.length); if (wb instanceof HSSFWorkbook) { - assertEquals(Ref3DPtg.class, ptgs[0].getClass()); - assertEquals("Sheet1:Sheet3!$A$1", toFormulaString(ptgs[0], fpb)); + assertEquals(Ref3DPtg.class, ptgs[0].getClass()); } else { assertEquals(Ref3DPxg.class, ptgs[0].getClass()); - assertEquals("Sheet1!$A$1", toFormulaString(ptgs[0], fpb)); } + assertEquals("Sheet1:Sheet3!$A$1", toFormulaString(ptgs[0], fpb)); assertEquals(FuncVarPtg.class, ptgs[1].getClass()); assertEquals("MIN", toFormulaString(ptgs[1], fpb)); -- 2.39.5