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 16:15:56 UTC
svn commit: r1613438 - in /poi/trunk: src/java/org/apache/poi/ss/formula/
src/ooxml/testcases/org/apache/poi/xssf/usermodel/ test-data/spreadsheet/
Author: nick
Date: Fri Jul 25 14:15:56 2014
New Revision: 1613438
URL: http://svn.apache.org/r1613438
Log:
More tests for #55906, and provide a new eval that lets you get at evals for many sheets
Added:
poi/trunk/src/java/org/apache/poi/ss/formula/SheetRangeEvaluator.java
Modified:
poi/trunk/src/java/org/apache/poi/ss/formula/SheetRefEvaluator.java
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java
poi/trunk/test-data/spreadsheet/55906-MultiSheetRefs.xls
poi/trunk/test-data/spreadsheet/55906-MultiSheetRefs.xlsx
Added: poi/trunk/src/java/org/apache/poi/ss/formula/SheetRangeEvaluator.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/SheetRangeEvaluator.java?rev=1613438&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/SheetRangeEvaluator.java (added)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/SheetRangeEvaluator.java Fri Jul 25 14:15:56 2014
@@ -0,0 +1,57 @@
+/* ====================================================================
+ 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;
+
+import org.apache.poi.ss.formula.eval.ValueEval;
+
+/**
+ * Evaluator for returning cells or sheets for a range of sheets
+ */
+final class SheetRangeEvaluator {
+ private final int _firstSheetIndex;
+ private final int _lastSheetIndex;
+ private SheetRefEvaluator[] _sheetEvaluators;
+
+ public SheetRangeEvaluator(int firstSheetIndex, int lastSheetIndex, SheetRefEvaluator[] sheetEvaluators) {
+ if (firstSheetIndex < 0) {
+ throw new IllegalArgumentException("Invalid firstSheetIndex: " + firstSheetIndex + ".");
+ }
+ if (lastSheetIndex < firstSheetIndex) {
+ throw new IllegalArgumentException("Invalid lastSheetIndex: " + lastSheetIndex + " for firstSheetIndex: " + firstSheetIndex + ".");
+ }
+ _firstSheetIndex = firstSheetIndex;
+ _lastSheetIndex = lastSheetIndex;
+ _sheetEvaluators = sheetEvaluators;
+ }
+
+ public SheetRefEvaluator getSheetEvaluator(int sheetIndex) {
+ if (sheetIndex < _firstSheetIndex || sheetIndex > _lastSheetIndex) {
+ throw new IllegalArgumentException("Invalid SheetIndex: " + sheetIndex +
+ " - Outside range " + _firstSheetIndex + " : " + _lastSheetIndex);
+ }
+ return _sheetEvaluators[sheetIndex-_firstSheetIndex];
+ }
+
+ public String getSheetName(int sheetIndex) {
+ return getSheetEvaluator(sheetIndex).getSheetName();
+ }
+
+ public ValueEval getEvalForCell(int sheetIndex, int rowIndex, int columnIndex) {
+ return getSheetEvaluator(sheetIndex).getEvalForCell(rowIndex, columnIndex);
+ }
+}
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/SheetRefEvaluator.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/SheetRefEvaluator.java?rev=1613438&r1=1613437&r2=1613438&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/SheetRefEvaluator.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/SheetRefEvaluator.java Fri Jul 25 14:15:56 2014
@@ -23,9 +23,7 @@ import org.apache.poi.ss.formula.ptg.Ptg
import org.apache.poi.ss.usermodel.Cell;
/**
- *
- *
- * @author Josh Micich
+ * Evaluator for cells within a specific Sheet
*/
final class SheetRefEvaluator {
private final WorkbookEvaluator _bookEvaluator;
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=1613438&r1=1613437&r2=1613438&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 14:15:56 2014
@@ -212,7 +212,7 @@ public final class TestXSSFFormulaEvalua
Cell minF = s1.getRow(3).getCell(1);
assertNotNull(minF);
- assertEquals("MIX(Sheet1:Sheet3!A$1)", minF.getCellFormula());
+ assertEquals("MIN(Sheet1:Sheet3!A$1)", minF.getCellFormula());
assertEquals("11.0", evaluator.evaluate(minF).formatAsString());
Cell maxF = s1.getRow(4).getCell(1);
@@ -241,6 +241,35 @@ public final class TestXSSFFormulaEvalua
assertNotNull(countA_3F);
assertEquals("COUNTA(Sheet1:Sheet3!E1)", countA_3F.getCellFormula());
assertEquals("3.0", evaluator.evaluate(countA_3F).formatAsString());
+
+
+ // SUM over a range
+ Cell sumFA = s1.getRow(2).getCell(7);
+ assertNotNull(sumFA);
+ assertEquals("SUM(Sheet1:Sheet3!A1:B2)", sumFA.getCellFormula());
+ assertEquals("110.0", evaluator.evaluate(sumFA).formatAsString());
+
+
+ // Various Stats formulas on ranges of numbers
+ Cell avgFA = s1.getRow(2).getCell(7);
+ assertNotNull(avgFA);
+ assertEquals("AVERAGE(Sheet1:Sheet3!A1:B2)", avgFA.getCellFormula());
+ assertEquals("27.5", evaluator.evaluate(avgFA).formatAsString());
+
+ Cell minFA = s1.getRow(3).getCell(8);
+ assertNotNull(minFA);
+ assertEquals("MIN(Sheet1:Sheet3!A$1:B$2)", minFA.getCellFormula());
+ assertEquals("11.0", evaluator.evaluate(minFA).formatAsString());
+
+ Cell maxFA = s1.getRow(4).getCell(8);
+ assertNotNull(maxFA);
+ assertEquals("MAX(Sheet1:Sheet3!A$1:B$2)", maxFA.getCellFormula());
+ assertEquals("44.0", evaluator.evaluate(maxFA).formatAsString());
+
+ Cell countFA = s1.getRow(5).getCell(8);
+ assertNotNull(countFA);
+ assertEquals("COUNT(Sheet1:Sheet3!$A$1:$B$2)", countFA.getCellFormula());
+ assertEquals("4.0", evaluator.evaluate(countFA).formatAsString());
}
}
}
Modified: poi/trunk/test-data/spreadsheet/55906-MultiSheetRefs.xls
URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/55906-MultiSheetRefs.xls?rev=1613438&r1=1613437&r2=1613438&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=1613438&r1=1613437&r2=1613438&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