More tests for #55906, and provide a new eval that lets you get at evals for many sheets

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1613438 13f79535-47bb-0310-9956-ffa450edef68

5 files changed, 88 insertions, 4 deletions

diff --git a/src/java/org/apache/poi/ss/formula/SheetRangeEvaluator.java b/src/java/org/apache/poi/ss/formula/SheetRangeEvaluator.java
new file mode 100644
index 0000000000..9157a1d0da
--- /dev/null
+++ b/src/java/org/apache/poi/ss/formula/SheetRangeEvaluator.java
@@ -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);
+	}
+}
diff --git a/src/java/org/apache/poi/ss/formula/SheetRefEvaluator.java b/src/java/org/apache/poi/ss/formula/SheetRefEvaluator.java
index 7452d5ab58..bc4c862620 100644
--- a/src/java/org/apache/poi/ss/formula/SheetRefEvaluator.java
+++ b/src/java/org/apache/poi/ss/formula/SheetRefEvaluator.java
@@ -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;
diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java
index 4e92fda697..9199058ffb 100644
--- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java
+++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java
@@ -212,7 +212,7 @@ public final class TestXSSFFormulaEvaluation extends BaseTestFormulaEvaluator {
             
             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 TestXSSFFormulaEvaluation extends BaseTestFormulaEvaluator {
             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());
         }
     }
 }
diff --git a/test-data/spreadsheet/55906-MultiSheetRefs.xls b/test-data/spreadsheet/55906-MultiSheetRefs.xls
index 565330810a..c31728d820 100644
--- a/test-data/spreadsheet/55906-MultiSheetRefs.xls
+++ b/test-data/spreadsheet/55906-MultiSheetRefs.xls
diff --git a/test-data/spreadsheet/55906-MultiSheetRefs.xlsx b/test-data/spreadsheet/55906-MultiSheetRefs.xlsx
index 10b18b768a..4099796fc6 100644
--- a/test-data/spreadsheet/55906-MultiSheetRefs.xlsx
+++ b/test-data/spreadsheet/55906-MultiSheetRefs.xlsx