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 15:59:08 UTC
svn commit: r1613437 - in /poi/trunk: src/java/org/apache/poi/ss/formula/ptg/
src/ooxml/java/org/apache/poi/xssf/usermodel/
src/ooxml/testcases/org/apache/poi/xssf/usermodel/
src/testcases/org/apache/poi/hssf/model/ test-data/spreadsheet/
Author: nick
Date: Fri Jul 25 13:59:07 2014
New Revision: 1613437
URL: http://svn.apache.org/r1613437
Log:
Areas can have multi-sheet references too, so add FormulaParser support to these as well
Modified:
poi/trunk/src/java/org/apache/poi/ss/formula/ptg/Area3DPxg.java
poi/trunk/src/java/org/apache/poi/ss/formula/ptg/Ref3DPxg.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java
poi/trunk/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java
poi/trunk/test-data/spreadsheet/55906-MultiSheetRefs.xls
poi/trunk/test-data/spreadsheet/55906-MultiSheetRefs.xlsx
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/ptg/Area3DPxg.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/ptg/Area3DPxg.java?rev=1613437&r1=1613436&r2=1613437&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/ptg/Area3DPxg.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/ptg/Area3DPxg.java Fri Jul 25 13:59:07 2014
@@ -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.AreaReference;
import org.apache.poi.util.LittleEndianOutput;
@@ -31,21 +33,27 @@ import org.apache.poi.util.LittleEndianO
*/
public final class Area3DPxg extends AreaPtgBase implements Pxg {
private int externalWorkbookNumber = -1;
- private String sheetName;
+ private String firstSheetName;
+ private String lastSheetName;
- public Area3DPxg(int externalWorkbookNumber, String sheetName, String arearef) {
+ public Area3DPxg(int externalWorkbookNumber, SheetIdentifier sheetName, String arearef) {
this(externalWorkbookNumber, sheetName, new AreaReference(arearef));
}
- public Area3DPxg(int externalWorkbookNumber, String sheetName, AreaReference arearef) {
+ public Area3DPxg(int externalWorkbookNumber, SheetIdentifier sheetName, AreaReference arearef) {
super(arearef);
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 Area3DPxg(String sheetName, String arearef) {
+ public Area3DPxg(SheetIdentifier sheetName, String arearef) {
this(sheetName, new AreaReference(arearef));
}
- public Area3DPxg(String sheetName, AreaReference arearef) {
+ public Area3DPxg(SheetIdentifier sheetName, AreaReference arearef) {
this(-1, sheetName, arearef);
}
@@ -60,6 +68,10 @@ public final class Area3DPxg extends Are
sb.append("] ");
}
sb.append("sheet=").append(getSheetName());
+ if (lastSheetName != null) {
+ sb.append(" : ");
+ sb.append("sheet=").append(lastSheetName);
+ }
sb.append(" ! ");
sb.append(formatReferenceAsString());
sb.append("]");
@@ -70,11 +82,17 @@ public final class Area3DPxg extends Are
return externalWorkbookNumber;
}
public String getSheetName() {
- return sheetName;
+ return firstSheetName;
+ }
+ public String getLastSheetName() {
+ return lastSheetName;
}
public void setSheetName(String sheetName) {
- this.sheetName = sheetName;
+ this.firstSheetName = sheetName;
+ }
+ public void setLastSheetName(String sheetName) {
+ this.lastSheetName = sheetName;
}
public String format2DRefAsString() {
@@ -88,7 +106,11 @@ public final class Area3DPxg extends Are
sb.append(externalWorkbookNumber);
sb.append(']');
}
- SheetNameFormatter.appendFormat(sb, sheetName);
+ SheetNameFormatter.appendFormat(sb, firstSheetName);
+ if (lastSheetName != null) {
+ sb.append(':');
+ SheetNameFormatter.appendFormat(sb, lastSheetName);
+ }
sb.append('!');
sb.append(formatReferenceAsString());
return sb.toString();
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/ptg/Ref3DPxg.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/ptg/Ref3DPxg.java?rev=1613437&r1=1613436&r2=1613437&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/ptg/Ref3DPxg.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/ptg/Ref3DPxg.java Fri Jul 25 13:59:07 2014
@@ -81,14 +81,14 @@ public final class Ref3DPxg extends RefP
public int getExternalWorkbookNumber() {
return externalWorkbookNumber;
}
- public String getSheetName() { // TODO Rename to getFirstSheetName
+ public String getSheetName() {
return firstSheetName;
}
public String getLastSheetName() {
return lastSheetName;
}
- public void setSheetName(String sheetName) { // TODO Rename to setFirstSheetName
+ public void setSheetName(String sheetName) {
this.firstSheetName = sheetName;
}
public void setLastSheetName(String sheetName) {
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java?rev=1613437&r1=1613436&r2=1613437&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java Fri Jul 25 13:59:07 2014
@@ -200,13 +200,11 @@ public final class XSSFEvaluationWorkboo
}
}
public Ptg get3DReferencePtg(AreaReference area, SheetIdentifier sheet) {
- String sheetName = sheet._sheetIdentifier.getName();
-
if (sheet._bookName != null) {
int bookIndex = resolveBookIndex(sheet._bookName);
- return new Area3DPxg(bookIndex, sheetName, area);
+ return new Area3DPxg(bookIndex, sheet, area);
} else {
- return new Area3DPxg(sheetName, area);
+ return new Area3DPxg(sheet, area);
}
}
Modified: poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java?rev=1613437&r1=1613436&r2=1613437&view=diff
==============================================================================
--- poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java (original)
+++ poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java Fri Jul 25 13:59:07 2014
@@ -201,46 +201,46 @@ public final class TestXSSFFormulaEvalua
Cell sumF = s1.getRow(2).getCell(0);
assertNotNull(sumF);
assertEquals("SUM(Sheet1:Sheet3!A1)", sumF.getCellFormula());
- assertEquals("66", evaluator.evaluate(sumF).formatAsString());
+ assertEquals("66.0", evaluator.evaluate(sumF).formatAsString());
// Various Stats formulas on numbers
Cell avgF = s1.getRow(2).getCell(1);
assertNotNull(avgF);
assertEquals("AVERAGE(Sheet1:Sheet3!A1)", avgF.getCellFormula());
- assertEquals("22", evaluator.evaluate(avgF).formatAsString());
+ assertEquals("22.0", evaluator.evaluate(avgF).formatAsString());
Cell minF = s1.getRow(3).getCell(1);
assertNotNull(minF);
assertEquals("MIX(Sheet1:Sheet3!A$1)", minF.getCellFormula());
- assertEquals("11", evaluator.evaluate(minF).formatAsString());
+ assertEquals("11.0", evaluator.evaluate(minF).formatAsString());
Cell maxF = s1.getRow(4).getCell(1);
assertNotNull(maxF);
assertEquals("MAX(Sheet1:Sheet3!A$1)", maxF.getCellFormula());
- assertEquals("33", evaluator.evaluate(maxF).formatAsString());
+ assertEquals("33.0", evaluator.evaluate(maxF).formatAsString());
Cell countF = s1.getRow(5).getCell(1);
assertNotNull(countF);
assertEquals("COUNT(Sheet1:Sheet3!A$1)", countF.getCellFormula());
- assertEquals("3", evaluator.evaluate(countF).formatAsString());
+ assertEquals("3.0", evaluator.evaluate(countF).formatAsString());
// Various CountAs on Strings
Cell countA_1F = s1.getRow(2).getCell(2);
assertNotNull(countA_1F);
assertEquals("COUNTA(Sheet1:Sheet3!C1)", countA_1F.getCellFormula());
- assertEquals("3", evaluator.evaluate(countA_1F).formatAsString());
+ assertEquals("3.0", evaluator.evaluate(countA_1F).formatAsString());
Cell countA_2F = s1.getRow(2).getCell(3);
assertNotNull(countA_2F);
assertEquals("COUNTA(Sheet1:Sheet3!D1)", countA_2F.getCellFormula());
- assertEquals("0", evaluator.evaluate(countA_2F).formatAsString());
+ assertEquals("0.0", evaluator.evaluate(countA_2F).formatAsString());
Cell countA_3F = s1.getRow(2).getCell(4);
assertNotNull(countA_3F);
assertEquals("COUNTA(Sheet1:Sheet3!E1)", countA_3F.getCellFormula());
- assertEquals("3", evaluator.evaluate(countA_3F).formatAsString());
+ assertEquals("3.0", evaluator.evaluate(countA_3F).formatAsString());
}
}
}
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=1613437&r1=1613436&r2=1613437&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 Fri Jul 25 13:59:07 2014
@@ -31,6 +31,7 @@ import org.apache.poi.ss.formula.Formula
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.Area3DPtg;
import org.apache.poi.ss.formula.ptg.Area3DPxg;
import org.apache.poi.ss.formula.ptg.AreaPtg;
import org.apache.poi.ss.formula.ptg.AttrPtg;
@@ -242,7 +243,9 @@ public final class TestXSSFFormulaParser
/**
* A handful of functions (such as SUM, COUNTA, MIN) support
* multi-sheet references (eg Sheet1:Sheet3!A1 = Cell A1 from
- * Sheets 1 through Sheet 3).
+ * Sheets 1 through Sheet 3) and multi-sheet area references
+ * (eg Sheet1:Sheet3!A1:B2 = Cells A1 through B2 from Sheets
+ * 1 through Sheet 3).
* 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)
@@ -274,6 +277,24 @@ public final class TestXSSFFormulaParser
assertNotNull(maxF);
assertEquals("MAX(Sheet1:Sheet3!A$1)", maxF.getCellFormula());
+
+ Cell sumFA = s1.getRow(2).getCell(7);
+ assertNotNull(sumFA);
+ assertEquals("SUM(Sheet1:Sheet3!A1:B2)", sumFA.getCellFormula());
+
+ Cell avgFA = s1.getRow(2).getCell(8);
+ assertNotNull(avgFA);
+ assertEquals("AVERAGE(Sheet1:Sheet3!A1:B2)", avgFA.getCellFormula());
+
+ Cell maxFA = s1.getRow(4).getCell(8);
+ assertNotNull(maxFA);
+ assertEquals("MAX(Sheet1:Sheet3!A$1:B$2)", maxFA.getCellFormula());
+
+ Cell countFA = s1.getRow(5).getCell(8);
+ assertNotNull(countFA);
+ assertEquals("COUNT(Sheet1:Sheet3!$A$1:$B$2)", countFA.getCellFormula());
+
+
// Create a formula parser
FormulaParsingWorkbook fpb = null;
if (wb instanceof HSSFWorkbook)
@@ -324,10 +345,41 @@ public final class TestXSSFFormulaParser
assertEquals("MIN", toFormulaString(ptgs[1], fpb));
- // Check we can round-trip - try to set a new one to a new cell
+ // SUM to a range of cells over 3 workbooks
+ ptgs = parse(fpb, "SUM(Sheet1:Sheet3!A1:B2)");
+ assertEquals(2, ptgs.length);
+ if (wb instanceof HSSFWorkbook) {
+ assertEquals(Area3DPtg.class, ptgs[0].getClass());
+ } else {
+ assertEquals(Area3DPxg.class, ptgs[0].getClass());
+ }
+ assertEquals("Sheet1:Sheet3!A1:B2", toFormulaString(ptgs[0], fpb));
+ assertEquals(AttrPtg.class, ptgs[1].getClass());
+ assertEquals("SUM", toFormulaString(ptgs[1], fpb));
+
+
+ // MIN to a range of cells over 3 workbooks, absolute reference
+ ptgs = parse(fpb, "MIN(Sheet1:Sheet3!$A$1:$B$2)");
+ assertEquals(2, ptgs.length);
+ if (wb instanceof HSSFWorkbook) {
+ assertEquals(Area3DPtg.class, ptgs[0].getClass());
+ } else {
+ assertEquals(Area3DPxg.class, ptgs[0].getClass());
+ }
+ assertEquals("Sheet1:Sheet3!$A$1:$B$2", toFormulaString(ptgs[0], fpb));
+ assertEquals(FuncVarPtg.class, ptgs[1].getClass());
+ assertEquals("MIN", toFormulaString(ptgs[1], fpb));
+
+
+ // Check we can round-trip - try to set a new one to a new single cell
Cell newF = s1.getRow(0).createCell(10, Cell.CELL_TYPE_FORMULA);
newF.setCellFormula("SUM(Sheet2:Sheet3!A1)");
assertEquals("SUM(Sheet2:Sheet3!A1)", newF.getCellFormula());
+
+ // Check we can round-trip - try to set a new one to a cell range
+ newF = s1.getRow(0).createCell(11, Cell.CELL_TYPE_FORMULA);
+ newF.setCellFormula("MIN(Sheet1:Sheet2!A1:B2)");
+ assertEquals("MIN(Sheet1:Sheet2!A1:B2)", newF.getCellFormula());
}
}
private static String toFormulaString(Ptg ptg, FormulaParsingWorkbook wb) {
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=1613437&r1=1613436&r2=1613437&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 Fri Jul 25 13:59:07 2014
@@ -465,6 +465,9 @@ public final class TestFormulaParser ext
HSSFCell cell = row.createCell(0);
String formula = null;
+
+ // References to a single cell:
+
// One sheet
cell.setCellFormula("Cash_Flow!A1");
formula = cell.getCellFormula();
@@ -479,6 +482,24 @@ public final class TestFormulaParser ext
cell.setCellFormula("Cash_Flow:\'Test Sheet\'!A1");
formula = cell.getCellFormula();
assertEquals("Cash_Flow:\'Test Sheet\'!A1", formula);
+
+
+ // References to a range (area) of cells:
+
+ // One sheet
+ cell.setCellFormula("Cash_Flow!A1:B2");
+ formula = cell.getCellFormula();
+ assertEquals("Cash_Flow!A1:B2", formula);
+
+ // Then the other
+ cell.setCellFormula("\'Test Sheet\'!A1:B2");
+ formula = cell.getCellFormula();
+ assertEquals("\'Test Sheet\'!A1:B2", formula);
+
+ // Now both
+ cell.setCellFormula("Cash_Flow:\'Test Sheet\'!A1:B2");
+ formula = cell.getCellFormula();
+ assertEquals("Cash_Flow:\'Test Sheet\'!A1:B2", formula);
}
/**
Modified: poi/trunk/test-data/spreadsheet/55906-MultiSheetRefs.xls
URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/55906-MultiSheetRefs.xls?rev=1613437&r1=1613436&r2=1613437&view=diff
==============================================================================
Binary files - no diff available.
Modified: poi/trunk/test-data/spreadsheet/55906-MultiSheetRefs.xlsx
URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/55906-MultiSheetRefs.xlsx?rev=1613437&r1=1613436&r2=1613437&view=diff
==============================================================================
Binary files - no diff available.
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org