From 1161077a6bcd94f3b39e7b0e7de1fd7338eeef93 Mon Sep 17 00:00:00 2001 From: Nick Burch Date: Thu, 24 Jul 2014 22:44:51 +0000 Subject: [PATCH] More progress towards #55906 - FormulaParser is able to identify and parse multi-sheet references, but not yet fully round-trip them. (No evaluation support yet either) git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1613317 13f79535-47bb-0310-9956-ffa450edef68 --- .../apache/poi/ss/formula/FormulaParser.java | 29 ++++++++- .../poi/ss/formula/SheetIdentifier.java | 2 +- .../poi/ss/formula/SheetRangeIdentifier.java | 42 ++++++++++++ .../xssf/usermodel/TestXSSFFormulaParser.java | 64 ++++++++++++++----- .../poi/hssf/model/TestFormulaParser.java | 28 ++++++++ 5 files changed, 146 insertions(+), 19 deletions(-) create mode 100644 src/java/org/apache/poi/ss/formula/SheetRangeIdentifier.java diff --git a/src/java/org/apache/poi/ss/formula/FormulaParser.java b/src/java/org/apache/poi/ss/formula/FormulaParser.java index 519e08ea71..1bae4fda31 100644 --- a/src/java/org/apache/poi/ss/formula/FormulaParser.java +++ b/src/java/org/apache/poi/ss/formula/FormulaParser.java @@ -366,6 +366,8 @@ public final class FormulaParser { * a..b!A1 * 'my sheet'!A1 * .my.sheet!A1 + * 'my sheet':'my alt sheet'!A1 + * .my.sheet1:.my.sheet2!$B$2 * my.named..range. * 'my sheet'!my.named.range * .my.sheet!my.named.range @@ -383,6 +385,7 @@ public final class FormulaParser { SkipWhite(); int savePointer = _pointer; SheetIdentifier sheetIden = parseSheetName(); + if (sheetIden == null) { resetPointer(savePointer); } else { @@ -807,6 +810,10 @@ public final class FormulaParser { GetChar(); return new SheetIdentifier(bookName, iden); } + // See if it's a multi-sheet range, eg Sheet1:Sheet3!A1 + if (look == ':') { + return parseSheetRange(bookName, iden); + } return null; } @@ -818,20 +825,38 @@ public final class FormulaParser { sb.append(look); GetChar(); } + NameIdentifier iden = new NameIdentifier(sb.toString(), false); SkipWhite(); if (look == '!') { GetChar(); - return new SheetIdentifier(bookName, new NameIdentifier(sb.toString(), false)); + return new SheetIdentifier(bookName, iden); } + // See if it's a multi-sheet range, eg Sheet1:Sheet3!A1 + if (look == ':') { + return parseSheetRange(bookName, iden); + } return null; } if (look == '!' && bookName != null) { - // Raw book reference, wihtout a sheet + // Raw book reference, without a sheet GetChar(); return new SheetIdentifier(bookName, null); } return null; } + + /** + * If we have something that looks like [book]Sheet1: or + * Sheet1, see if it's actually a range eg Sheet1:Sheet2! + */ + private SheetIdentifier parseSheetRange(String bookname, NameIdentifier sheet1Name) { + GetChar(); + SheetIdentifier sheet2 = parseSheetName(); + if (sheet2 != null) { + return new SheetRangeIdentifier(bookname, sheet1Name, sheet2.getSheetIdentifier()); + } + return null; + } /** * very similar to {@link SheetNameFormatter#isSpecialChar(char)} diff --git a/src/java/org/apache/poi/ss/formula/SheetIdentifier.java b/src/java/org/apache/poi/ss/formula/SheetIdentifier.java index be5639b37f..71c7cd0f0c 100644 --- a/src/java/org/apache/poi/ss/formula/SheetIdentifier.java +++ b/src/java/org/apache/poi/ss/formula/SheetIdentifier.java @@ -31,7 +31,7 @@ public class SheetIdentifier { public NameIdentifier getSheetIdentifier() { return _sheetIdentifier; } - private void asFormulaString(StringBuffer sb) { + protected void asFormulaString(StringBuffer sb) { if (_bookName != null) { sb.append(" [").append(_sheetIdentifier.getName()).append("]"); } diff --git a/src/java/org/apache/poi/ss/formula/SheetRangeIdentifier.java b/src/java/org/apache/poi/ss/formula/SheetRangeIdentifier.java new file mode 100644 index 0000000000..c0b851ed57 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/SheetRangeIdentifier.java @@ -0,0 +1,42 @@ +/* ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +==================================================================== */ + +package org.apache.poi.ss.formula; + +public class SheetRangeIdentifier extends SheetIdentifier { + public NameIdentifier _lastSheetIdentifier; + + public SheetRangeIdentifier(String bookName, NameIdentifier firstSheetIdentifier, NameIdentifier lastSheetIdentifier) { + super(bookName, firstSheetIdentifier); + _lastSheetIdentifier = lastSheetIdentifier; + } + public NameIdentifier getFirstSheetIdentifier() { + return super.getSheetIdentifier(); + } + public NameIdentifier getLastSheetIdentifier() { + return _lastSheetIdentifier; + } + protected void asFormulaString(StringBuffer sb) { + super.asFormulaString(sb); + sb.append(':'); + if (_lastSheetIdentifier.isQuoted()) { + sb.append("'").append(_lastSheetIdentifier.getName()).append("'"); + } else { + sb.append(_lastSheetIdentifier.getName()); + } + } +} \ No newline at end of file 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 c006227931..222da5cd62 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java @@ -28,7 +28,9 @@ import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.formula.FormulaParseException; import org.apache.poi.ss.formula.FormulaParser; import org.apache.poi.ss.formula.FormulaParsingWorkbook; +import org.apache.poi.ss.formula.FormulaRenderingWorkbook; import org.apache.poi.ss.formula.FormulaType; +import org.apache.poi.ss.formula.WorkbookDependentFormula; import org.apache.poi.ss.formula.ptg.Area3DPxg; import org.apache.poi.ss.formula.ptg.AreaPtg; import org.apache.poi.ss.formula.ptg.AttrPtg; @@ -38,13 +40,13 @@ import org.apache.poi.ss.formula.ptg.IntPtg; import org.apache.poi.ss.formula.ptg.NamePtg; import org.apache.poi.ss.formula.ptg.NameXPxg; import org.apache.poi.ss.formula.ptg.Ptg; +import org.apache.poi.ss.formula.ptg.Ref3DPtg; import org.apache.poi.ss.formula.ptg.Ref3DPxg; import org.apache.poi.ss.formula.ptg.RefPtg; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.XSSFTestDataSamples; -import org.junit.Ignore; import org.junit.Test; public final class TestXSSFFormulaParser { @@ -244,11 +246,8 @@ public final class TestXSSFFormulaParser { * This test, based on common test files for HSSF and XSSF, checks * that we can read and parse these kinds of references * (but not evaluate - that's elsewhere in the test suite) - * - * DISABLED pending support, see bug #55906 */ @Test - @Ignore public void multiSheetReferencesHSSFandXSSF() throws Exception { Workbook[] wbs = new Workbook[] { HSSFTestDataSamples.openSampleWorkbook("55906-MultiSheetRefs.xls"), @@ -282,25 +281,58 @@ public final class TestXSSFFormulaParser { else fpb = XSSFEvaluationWorkbook.create((XSSFWorkbook)wb); + // Check things parse as expected: + // Note - Ptgs will only show one sheet, the formula + // parser stuff looks up the second later + // SUM to one cell over 3 workbooks, relative reference - ptgs = parse(fpb, "SUM(Sheet1:Sheet3!A1"); - // TODO -// assertEquals(1, ptgs.length); -// assertEquals(Ref3DPxg.class, ptgs[0].getClass()); + ptgs = parse(fpb, "SUM(Sheet1:Sheet3!A1)"); + assertEquals(2, ptgs.length); + if (wb instanceof HSSFWorkbook) { + assertEquals(Ref3DPtg.class, ptgs[0].getClass()); + assertEquals("Sheet1!A1", toFormulaString(ptgs[0], fpb)); + } else { + assertEquals(Ref3DPxg.class, ptgs[0].getClass()); + assertEquals("Sheet1!A1", toFormulaString(ptgs[0], fpb)); + } + assertEquals(AttrPtg.class, ptgs[1].getClass()); + assertEquals("SUM", toFormulaString(ptgs[1], fpb)); + // MAX to one cell over 3 workbooks, absolute row reference - ptgs = parse(fpb, "MAX(Sheet1:Sheet3!A$1"); - // TODO -// assertEquals(1, ptgs.length); -// assertEquals(Ref3DPxg.class, ptgs[0].getClass()); + ptgs = parse(fpb, "MAX(Sheet1:Sheet3!A$1)"); + assertEquals(2, ptgs.length); + if (wb instanceof HSSFWorkbook) { + assertEquals(Ref3DPtg.class, ptgs[0].getClass()); + assertEquals("Sheet1!A$1", toFormulaString(ptgs[0], fpb)); + } else { + assertEquals(Ref3DPxg.class, ptgs[0].getClass()); + assertEquals("Sheet1!A$1", toFormulaString(ptgs[0], fpb)); + } + assertEquals(FuncVarPtg.class, ptgs[1].getClass()); + assertEquals("MAX", toFormulaString(ptgs[1], fpb)); + // MIN to one cell over 3 workbooks, absolute reference - ptgs = parse(fpb, "MIN(Sheet1:Sheet3!$A$1"); - // TODO -// assertEquals(1, ptgs.length); -// assertEquals(Ref3DPxg.class, ptgs[0].getClass()); + ptgs = parse(fpb, "MIN(Sheet1:Sheet3!$A$1)"); + assertEquals(2, ptgs.length); + if (wb instanceof HSSFWorkbook) { + assertEquals(Ref3DPtg.class, ptgs[0].getClass()); + assertEquals("Sheet1!$A$1", toFormulaString(ptgs[0], fpb)); + } else { + assertEquals(Ref3DPxg.class, ptgs[0].getClass()); + assertEquals("Sheet1!$A$1", toFormulaString(ptgs[0], fpb)); + } + assertEquals(FuncVarPtg.class, ptgs[1].getClass()); + assertEquals("MIN", toFormulaString(ptgs[1], fpb)); + } + } + private static String toFormulaString(Ptg ptg, FormulaParsingWorkbook wb) { + if (ptg instanceof WorkbookDependentFormula) { + return ((WorkbookDependentFormula)ptg).toFormulaString((FormulaRenderingWorkbook)wb); } + return ptg.toFormulaString(); } } diff --git a/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java b/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java index 59faa04303..f780bde0e6 100644 --- a/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java +++ b/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java @@ -454,6 +454,34 @@ public final class TestFormulaParser extends TestCase { assertEquals("A1:A2", formula); } + public void testMultiSheetReference() { + HSSFWorkbook wb = new HSSFWorkbook(); + + wb.createSheet("Cash_Flow"); + wb.createSheet("Test Sheet"); + + HSSFSheet sheet = wb.createSheet("Test"); + HSSFRow row = sheet.createRow(0); + HSSFCell cell = row.createCell(0); + String formula = null; + + // One sheet + cell.setCellFormula("Cash_Flow!A1"); + formula = cell.getCellFormula(); + assertEquals("Cash_Flow!A1", formula); + + // Then the other + cell.setCellFormula("\'Test Sheet\'!A1"); + formula = cell.getCellFormula(); + assertEquals("\'Test Sheet\'!A1", formula); + + // Now both + // TODO Implement remaining logic for #55906 + cell.setCellFormula("Cash_Flow:\'Test Sheet\'!A1"); + formula = cell.getCellFormula(); +// assertEquals("Cash_Flow:\'Test Sheet\'!A1", formula); + } + /** * Test for bug observable at svn revision 618865 (5-Feb-2008)
* a formula consisting of a single no-arg function got rendered without the function braces -- 2.39.5