You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ni...@apache.org on 2014/07/25 00:44:51 UTC
svn commit: r1613317 - in /poi/trunk/src: java/org/apache/poi/ss/formula/
ooxml/testcases/org/apache/poi/xssf/usermodel/
testcases/org/apache/poi/hssf/model/
Author: nick
Date: Thu Jul 24 22:44:51 2014
New Revision: 1613317
URL: http://svn.apache.org/r1613317
Log:
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)
Added:
poi/trunk/src/java/org/apache/poi/ss/formula/SheetRangeIdentifier.java
Modified:
poi/trunk/src/java/org/apache/poi/ss/formula/FormulaParser.java
poi/trunk/src/java/org/apache/poi/ss/formula/SheetIdentifier.java
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java
poi/trunk/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/FormulaParser.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/FormulaParser.java?rev=1613317&r1=1613316&r2=1613317&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/FormulaParser.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/FormulaParser.java Thu Jul 24 22:44:51 2014
@@ -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)}
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/SheetIdentifier.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/SheetIdentifier.java?rev=1613317&r1=1613316&r2=1613317&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/SheetIdentifier.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/SheetIdentifier.java Thu Jul 24 22:44:51 2014
@@ -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("]");
}
Added: poi/trunk/src/java/org/apache/poi/ss/formula/SheetRangeIdentifier.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/SheetRangeIdentifier.java?rev=1613317&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/SheetRangeIdentifier.java (added)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/SheetRangeIdentifier.java Thu Jul 24 22:44:51 2014
@@ -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
Modified: poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java?rev=1613317&r1=1613316&r2=1613317&view=diff
==============================================================================
--- poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java (original)
+++ poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java Thu Jul 24 22:44:51 2014
@@ -28,7 +28,9 @@ import org.apache.poi.hssf.usermodel.HSS
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.Int
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();
}
}
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java?rev=1613317&r1=1613316&r2=1613317&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java Thu Jul 24 22:44:51 2014
@@ -454,6 +454,34 @@ public final class TestFormulaParser ext
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)<br/>
* a formula consisting of a single no-arg function got rendered without the function braces
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org