From a62dd1bf8fd82d7e4461b56a05efd17a89ed9fe5 Mon Sep 17 00:00:00 2001 From: Nick Burch Date: Sat, 19 Jul 2014 20:51:23 +0000 Subject: [PATCH] Move towards supporting same workbook named ranges in external style for xssf git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1611961 13f79535-47bb-0310-9956-ffa450edef68 --- .../apache/poi/ss/formula/FormulaParser.java | 9 +++- .../apache/poi/ss/formula/ptg/NameXPxg.java | 4 +- .../usermodel/XSSFEvaluationWorkbook.java | 43 +++++++++++-------- .../xssf/usermodel/TestXSSFFormulaParser.java | 42 ++++++++++-------- 4 files changed, 61 insertions(+), 37 deletions(-) diff --git a/src/java/org/apache/poi/ss/formula/FormulaParser.java b/src/java/org/apache/poi/ss/formula/FormulaParser.java index 51f1a50b1d..519e08ea71 100644 --- a/src/java/org/apache/poi/ss/formula/FormulaParser.java +++ b/src/java/org/apache/poi/ss/formula/FormulaParser.java @@ -373,6 +373,9 @@ public final class FormulaParser { * 123.456 * "abc" * true + * [Foo.xls]!$A$1 + * [Foo.xls]'my sheet'!$A$1 + * [Foo.xls]!my.named.range * * */ @@ -768,7 +771,6 @@ public final class FormulaParser { * @return The sheet name as an identifier null if '!' is not found in the right place */ private SheetIdentifier parseSheetName() { - String bookName; if (look == '[') { StringBuilder sb = new StringBuilder(); @@ -823,6 +825,11 @@ public final class FormulaParser { } return null; } + if (look == '!' && bookName != null) { + // Raw book reference, wihtout a sheet + GetChar(); + return new SheetIdentifier(bookName, null); + } return null; } 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 62e56c28f7..0a49b175f5 100644 --- a/src/java/org/apache/poi/ss/formula/ptg/NameXPxg.java +++ b/src/java/org/apache/poi/ss/formula/ptg/NameXPxg.java @@ -74,7 +74,9 @@ public final class NameXPxg extends OperandPtg { sb.append(externalWorkbookNumber); sb.append(']'); } - sb.append(sheetName); + if (sheetName != null) { + sb.append(sheetName); + } sb.append('!'); sb.append(nameName); return sb.toString(); 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 0014693f27..17d92f902c 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java @@ -78,23 +78,25 @@ public final class XSSFEvaluationWorkbook implements FormulaRenderingWorkbook, E return sheetIndex; } - public int getExternalSheetIndex(String sheetName) { - int sheetIndex = _uBook.getSheetIndex(sheetName); - return convertToExternalSheetIndex(sheetIndex); - } - - private int resolveBookIndex(String bookName) { - // Is it already in numeric form? - if (bookName.startsWith("[") && bookName.endsWith("]")) { - bookName = bookName.substring(1, bookName.length()-2); - try { - return Integer.parseInt(bookName); - } catch (NumberFormatException e) {} - } - - // Look up an External Link Table for this name - throw new RuntimeException("Not implemented yet"); // TODO - } + public int getExternalSheetIndex(String sheetName) { + int sheetIndex = _uBook.getSheetIndex(sheetName); + return convertToExternalSheetIndex(sheetIndex); + } + + private int resolveBookIndex(String bookName) { + // Strip the [] wrapper, if still present + if (bookName.startsWith("[") && bookName.endsWith("]")) { + bookName = bookName.substring(1, bookName.length()-2); + } + + // Is it already in numeric form? + try { + return Integer.parseInt(bookName); + } catch (NumberFormatException e) {} + + // Look up an External Link Table for this name + throw new RuntimeException("Not implemented yet for book " + bookName); // TODO + } public EvaluationName getName(String name, int sheetIndex) { for (int i = 0; i < _uBook.getNumberOfNames(); i++) { @@ -129,6 +131,13 @@ public final class XSSFEvaluationWorkbook implements FormulaRenderingWorkbook, E } // Otherwise, try it as a named range + if (sheet._sheetIdentifier == null) { + // Workbook + Named Range only + int bookIndex = resolveBookIndex(sheet._bookName); + return new NameXPxg(bookIndex, null, name); + } + + // Use the sheetname and process String sheetName = sheet._sheetIdentifier.getName(); if (sheet._bookName != null) { 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 f0cc98a31f..21634b598d 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java @@ -26,6 +26,7 @@ import org.apache.poi.ss.formula.FormulaParser; import org.apache.poi.ss.formula.FormulaType; import org.apache.poi.ss.formula.ptg.FuncPtg; import org.apache.poi.ss.formula.ptg.IntPtg; +import org.apache.poi.ss.formula.ptg.NameXPxg; import org.apache.poi.ss.formula.ptg.Ptg; import org.apache.poi.ss.formula.ptg.Ref3DPxg; import org.apache.poi.ss.formula.ptg.RefPtg; @@ -95,8 +96,26 @@ public final class TestXSSFFormulaParser { assertTrue("", ptgs[1] instanceof FuncPtg); } + @Test + public void formaulReferncesSameWorkbook() { + // Use a test file with "other workbook" style references + // to itself + XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("56737.xlsx"); + XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb); + Ptg[] ptgs; + + // Reference to a named range in our own workbook, as if it + // were defined in a different workbook + ptgs = parse(fpb, "[0]!NR_Global_B2"); + assertEquals(1, ptgs.length); + assertEquals(NameXPxg.class, ptgs[0].getClass()); + assertEquals(null, ((NameXPxg)ptgs[0]).getSheetName()); + assertEquals("NR_Global_B2",((NameXPxg)ptgs[0]).getNameName()); + assertEquals("[0]!NR_Global_B2",((NameXPxg)ptgs[0]).toFormulaString()); + } + @Test - @Ignore("Work in progress, see bug #56737") + @Ignore("Work in progress, see bug #56737") public void formulaReferencesOtherSheets() { // Use a test file with the named ranges in place XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("ref-56737.xlsx"); @@ -113,7 +132,10 @@ public final class TestXSSFFormulaParser { // Reference to a sheet scoped named range from another sheet ptgs = parse(fpb, "Defines!NR_To_A1"); assertEquals(1, ptgs.length); - // TODO assert + assertEquals(NameXPxg.class, ptgs[0].getClass()); + assertEquals("Defines", ((NameXPxg)ptgs[0]).getSheetName()); + assertEquals("NR_To_A1",((NameXPxg)ptgs[0]).getNameName()); + assertEquals("Defines!NR_To_A1",((NameXPxg)ptgs[0]).toFormulaString()); // Reference to a workbook scoped named range ptgs = parse(fpb, "NR_Global_B2"); @@ -121,22 +143,6 @@ public final class TestXSSFFormulaParser { // TODO assert } - @Test - @Ignore("Work in progress, see bug #56737") - public void fFormaulReferncesSameWorkbook() { - // Use a test file with "other workbook" style references - // to itself - XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("56737.xlsx"); - XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb); - Ptg[] ptgs; - - // Reference to a named range in our own workbook, as if it - // were defined in a different workbook - ptgs = parse(fpb, "[0]!NR_Global_B2"); - assertEquals(1, ptgs.length); - // TODO assert - } - @Test @Ignore("Work in progress, see bug #56737") public void formulaReferencesOtherWorkbook() { -- 2.39.5