/* ====================================================================
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.functions;
import org.apache.poi.hssf.HSSFTestDataSamples;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.FormulaParseException;
import org.apache.poi.ss.formula.eval.*;
import junit.framework.TestCase;
import org.apache.poi.ss.usermodel.*;
/**
* Tests for {@link Subtotal}
*
* @author Paul Tomlin
*/
public final class TestSubtotal extends TestCase {
private static final int FUNCTION_AVERAGE = 1;
private static final int FUNCTION_COUNT = 2;
private static final int FUNCTION_MAX = 4;
private static final int FUNCTION_MIN = 5;
private static final int FUNCTION_PRODUCT = 6;
private static final int FUNCTION_STDEV = 7;
private static final int FUNCTION_SUM = 9;
private static final double[] TEST_VALUES0 = {
1, 2,
3, 4,
5, 6,
7, 8,
9, 10
};
private static void confirmSubtotal(int function, double expected) {
ValueEval[] values = new ValueEval[TEST_VALUES0.length];
for (int i = 0; i < TEST_VALUES0.length; i++) {
values[i] = new NumberEval(TEST_VALUES0[i]);
}
AreaEval arg1 = EvalFactory.createAreaEval("C1:D5", values);
ValueEval args[] = { new NumberEval(function), arg1 };
ValueEval result = new Subtotal().evaluate(args, 0, 0);
assertEquals(NumberEval.class, result.getClass());
assertEquals(expected, ((NumberEval) result).getNumberValue(), 0.0);
}
public void testBasics() {
confirmSubtotal(FUNCTION_SUM, 55.0);
confirmSubtotal(FUNCTION_AVERAGE, 5.5);
confirmSubtotal(FUNCTION_COUNT, 10.0);
confirmSubtotal(FUNCTION_MAX, 10.0);
confirmSubtotal(FUNCTION_MIN, 1.0);
confirmSubtotal(FUNCTION_PRODUCT, 3628800.0);
confirmSubtotal(FUNCTION_STDEV, 3.0276503540974917);
}
public void testAvg(){
Workbook wb = new HSSFWorkbook();
FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
Sheet sh = wb.createSheet();
Cell a1 = sh.createRow(1).createCell(1);
a1.setCellValue(1);
Cell a2 = sh.createRow(2).createCell(1);
a2.setCellValue(3);
Cell a3 = sh.createRow(3).createCell(1);
a3.setCellFormula("SUBTOTAL(1,B2:B3)");
Cell a4 = sh.createRow(4).createCell(1);
a4.setCellValue(1);
Cell a5 = sh.createRow(5).createCell(1);
a5.setCellValue(7);
Cell a6 = sh.createRow(6).createCell(1);
a6.setCellFormula("SUBTOTAL(1,B2:B6)*2 + 2");
Cell a7 = sh.createRow(7).createCell(1);
a7.setCellFormula("SUBTOTAL(1,B2:B7)");
Cell a8 = sh.createRow(8).createCell(1);
a8.setCellFormula("SUBTOTAL(1,B2,B3,B4,B5,B6,B7,B8)");
fe.evaluateAll();
assertEquals(2.0, a3.getNumericCellValue());
assertEquals(8.0, a6.getNumericCellValue());
assertEquals(3.0, a7.getNumericCellValue());
assertEquals(3.0, a8.getNumericCellValue());
}
public void testSum(){
Workbook wb = new HSSFWorkbook();
FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
Sheet sh = wb.createSheet();
Cell a1 = sh.createRow(1).createCell(1);
a1.setCellValue(1);
Cell a2 = sh.createRow(2).createCell(1);
a2.setCellValue(3);
Cell a3 = sh.createRow(3).createCell(1);
a3.setCellFormula("SUBTOTAL(9,B2:B3)");
Cell a4 = sh.createRow(4).createCell(1);
a4.setCellValue(1);
Cell a5 = sh.createRow(5).createCell(1);
a5.setCellValue(7);
Cell a6 = sh.createRow(6).createCell(1);
a6.setCellFormula("SUBTOTAL(9,B2:B6)*2 + 2");
Cell a7 = sh.createRow(7).createCell(1);
a7.setCellFormula("SUBTOTAL(9,B2:B7)");
Cell a8 = sh.createRow(8).createCell(1);
a8.setCellFormula("SUBTOTAL(9,B2,B3,B4,B5,B6,B7,B8)");
fe.evaluateAll();
assertEquals(4.0, a3.getNumericCellValue());
assertEquals(26.0, a6.getNumericCellValue());
assertEquals(12.0, a7.getNumericCellValue());
assertEquals(12.0, a8.getNumericCellValue());
}
public void testCount(){
Workbook wb = new HSSFWorkbook();
FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
Sheet sh = wb.createSheet();
Cell a1 = sh.createRow(1).createCell(1);
a1.setCellValue(1);
Cell a2 = sh.createRow(2).createCell(1);
a2.setCellValue(3);
Cell a3 = sh.createRow(3).createCell(1);
a3.setCellFormula("SUBTOTAL(2,B2:B3)");
Cell a4 = sh.createRow(4).createCell(1);
a4.setCellValue("POI"); // A4 is string and not counted
/*Cell a5 =*/ sh.createRow(5).createCell(1); // A5 is blank and not counted
Cell a6 = sh.createRow(6).createCell(1);
a6.setCellFormula("SUBTOTAL(2,B2:B6)*2 + 2");
Cell a7 = sh.createRow(7).createCell(1);
a7.setCellFormula("SUBTOTAL(2,B2:B7)");
Cell a8 = sh.createRow(8).createCell(1);
a8.setCellFormula("SUBTOTAL(2,B2,B3,B4,B5,B6,B7,B8)");
fe.evaluateAll();
assertEquals(2.0, a3.getNumericCellValue());
assertEquals(6.0, a6.getNumericCellValue());
assertEquals(2.0, a7.getNumericCellValue());
assertEquals(2.0, a8.getNumericCellValue());
}
public void testCounta(){
Workbook wb = new HSSFWorkbook();
FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
Sheet sh = wb.createSheet();
Cell a1 = sh.createRow(1).createCell(1);
a1.setCellValue(1);
Cell a2 = sh.createRow(2).createCell(1);
a2.setCellValue(3);
Cell a3 = sh.createRow(3).createCell(1);
a3.setCellFormula("SUBTOTAL(3,B2:B3)");
Cell a4 = sh.createRow(4).createCell(1);
a4.setCellValue("POI"); // A4 is string and not counted
/*Cell a5 =*/ sh.createRow(5).createCell(1); // A5 is blank and not counted
Cell a6 = sh.createRow(6).createCell(1);
a6.setCellFormula("SUBTOTAL(3,B2:B6)*2 + 2");
Cell a7 = sh.createRow(7).createCell(1);
a7.setCellFormula("SUBTOTAL(3,B2:B7)");
Cell a8 = sh.createRow(8).createCell(1);
a8.setCellFormula("SUBTOTAL(3,B2,B3,B4,B5,B6,B7,B8)");
fe.evaluateAll();
assertEquals(2.0, a3.getNumericCellValue());
assertEquals(8.0, a6.getNumericCellValue());
assertEquals(3.0, a7.getNumericCellValue());
assertEquals(3.0, a8.getNumericCellValue());
}
public void testMax(){
Workbook wb = new HSSFWorkbook();
FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
Sheet sh = wb.createSheet();
Cell a1 = sh.createRow(1).createCell(1);
a1.setCellValue(1);
Cell a2 = sh.createRow(2).createCell(1);
a2.setCellValue(3);
Cell a3 = sh.createRow(3).createCell(1);
a3.setCellFormula("SUBTOTAL(4,B2:B3)");
Cell a4 = sh.createRow(4).createCell(1);
a4.setCellValue(1);
Cell a5 = sh.createRow(5).createCell(1);
a5.setCellValue(7);
Cell a6 = sh.createRow(6).createCell(1);
a6.setCellFormula("SUBTOTAL(4,B2:B6)*2 + 2");
Cell a7 = sh.createRow(7).createCell(1);
a7.setCellFormula("SUBTOTAL(4,B2:B7)");
Cell a8 = sh.createRow(8).createCell(1);
a8.setCellFormula("SUBTOTAL(4,B2,B3,B4,B5,B6,B7,B8)");
fe.evaluateAll();
assertEquals(3.0, a3.getNumericCellValue());
assertEquals(16.0, a6.getNumericCellValue());
assertEquals(7.0, a7.getNumericCellValue());
assertEquals(7.0, a8.getNumericCellValue());
}
public void testMin(){
Workbook wb = new HSSFWorkbook();
FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
Sheet sh = wb.createSheet();
Cell a1 = sh.createRow(1).createCell(1);
a1.setCellValue(1);
Cell a2 = sh.createRow(2).createCell(1);
a2.setCellValue(3);
Cell a3 = sh.createRow(3).createCell(1);
a3.setCellFormula("SUBTOTAL(5,B2:B3)");
Cell a4 = sh.createRow(4).createCell(1);
a4.setCellValue(1);
Cell a5 = sh.createRow(5).createCell(1);
a5.setCellValue(7);
Cell a6 = sh.createRow(6).createCell(1);
a6.setCellFormula("SUBTOTAL(5,B2:B6)*2 + 2");
Cell a7 = sh.createRow(7).createCell(1);
a7.setCellFormula("SUBTOTAL(5,B2:B7)");
Cell a8 = sh.createRow(8).createCell(1);
a8.setCellFormula("SUBTOTAL(5,B2,B3,B4,B5,B6,B7,B8)");
fe.evaluateAll();
assertEquals(1.0, a3.getNumericCellValue());
assertEquals(4.0, a6.getNumericCellValue());
assertEquals(1.0, a7.getNumericCellValue());
assertEquals(1.0, a8.getNumericCellValue());
}
public void testStdev(){
Workbook wb = new HSSFWorkbook();
FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
Sheet sh = wb.createSheet();
Cell a1 = sh.createRow(1).createCell(1);
a1.setCellValue(1);
Cell a2 = sh.createRow(2).createCell(1);
a2.setCellValue(3);
Cell a3 = sh.createRow(3).createCell(1);
a3.setCellFormula("SUBTOTAL(7,B2:B3)");
Cell a4 = sh.createRow(4).createCell(1);
a4.setCellValue(1);
Cell a5 = sh.createRow(5).createCell(1);
a5.setCellValue(7);
Cell a6 = sh.createRow(6).createCell(1);
a6.setCellFormula("SUBTOTAL(7,B2:B6)*2 + 2");
Cell a7 = sh.createRow(7).createCell(1);
a7.setCellFormula("SUBTOTAL(7,B2:B7)");
Cell a8 = sh.createRow(8).createCell(1);
a8.setCellFormula("SUBTOTAL(7,B2,B3,B4,B5,B6,B7,B8)");
fe.evaluateAll();
assertEquals(1.41421, a3.getNumericCellValue(), 0.0001);
assertEquals(7.65685, a6.getNumericCellValue(), 0.0001);
assertEquals(2.82842, a7.getNumericCellValue(), 0.0001);
assertEquals(2.82842, a8.getNumericCellValue(), 0.0001);
}
public void test50209(){
Workbook wb = new HSSFWorkbook();
Sheet sh = wb.createSheet();
Cell a1 = sh.createRow(1).createCell(1);
a1.setCellValue(1);
Cell a2 = sh.createRow(2).createCell(1);
a2.setCellFormula("SUBTOTAL(9,B2)");
Cell a3 = sh.createRow(3).createCell(1);
a3.setCellFormula("SUBTOTAL(9,B2:B3)");
FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
fe.evaluateAll();
assertEquals(1.0, a2.getNumericCellValue());
assertEquals(1.0, a3.getNumericCellValue());
}
private static void confirmExpectedResult(FormulaEvaluator evaluator, String msg, Cell cell, double expected) {
CellValue value = evaluator.evaluate(cell);
if (value.getErrorValue() != 0)
throw new RuntimeException(msg + ": " + value.formatAsString());
assertEquals(msg, expected, value.getNumberValue());
}
public void testFunctionsFromTestSpreadsheet() {
HSSFWorkbook workbook = HSSFTestDataSamples.openSampleWorkbook("SubtotalsNested.xls");
HSSFSheet sheet = workbook.getSheetAt(0);
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
assertEquals("B2", 10.0, sheet.getRow(1).getCell(1).getNumericCellValue());
assertEquals("B3", 20.0, sheet.getRow(2).getCell(1).getNumericCellValue());
//Test simple subtotal over one area
Cell cellA3 = sheet.getRow(3).getCell(1);
confirmExpectedResult(evaluator, "B4", cellA3, 30.0);
//Test existence of the second area
assertNotNull("C2 must not be null", sheet.getRow(1).getCell(2));
assertEquals("C2", 7.0, sheet.getRow(1).getCell(2).getNumericCellValue());
Cell cellC1 = sheet.getRow(1).getCell(3);
Cell cellC2 = sheet.getRow(2).getCell(3);
Cell cellC3 = sheet.getRow(3).getCell(3);
//Test Functions SUM, COUNT and COUNTA calculation of SUBTOTAL
//a) areas A and B are used
//b) first 2 subtotals don't consider the value of nested subtotal in A3
confirmExpectedResult(evaluator, "SUBTOTAL(SUM;B2:B8;C2:C8)", cellC1, 37.0);
confirmExpectedResult(evaluator, "SUBTOTAL(COUNT;B2:B8,C2:C8)", cellC2, 3.0);
confirmExpectedResult(evaluator, "SUBTOTAL(COUNTA;B2:B8,C2:C8)", cellC3, 5.0);
}
public void testUnimplemented(){
Workbook wb = new HSSFWorkbook();
FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
Sheet sh = wb.createSheet();
Cell a3 = sh.createRow(3).createCell(1);
a3.setCellFormula("SUBTOTAL(8,B2:B3)");
try {
fe.evaluateAll();
fail("Should catch an NotImplementedFunctionException here, adjust these tests if it was actually implemented");
} catch (NotImplementedException e) {
// expected here
}
a3.setCellFormula("SUBTOTAL(10,B2:B3)");
try {
fe.evaluateAll();
fail("Should catch an NotImplementedFunctionException here, adjust these tests if it was actually implemented");
} catch (NotImplementedException e) {
// expected here
}
a3.setCellFormula("SUBTOTAL(11,B2:B3)");
try {
fe.evaluateAll();
fail("Should catch an NotImplementedFunctionException here, adjust these tests if it was actually implemented");
} catch (NotImplementedException e) {
// expected here
}
a3.setCellFormula("SUBTOTAL(107,B2:B3)");
try {
fe.evaluateAll();
fail("Should catch an NotImplementedFunctionException here, adjust these tests if it was actually implemented");
} catch (NotImplementedException e) {
// expected here
}
a3.setCellFormula("SUBTOTAL(0,B2:B3)");
fe.evaluateAll();
assertEquals(FormulaError.VALUE.getCode(), a3.getErrorCellValue());
try {
a3.setCellFormula("SUBTOTAL(9)");
fail("Should catch an exception here");
} catch (FormulaParseException e) {
// expected here
}
try {
a3.setCellFormula("SUBTOTAL()");
fail("Should catch an exception here");
} catch (FormulaParseException e) {
// expected here
}
Subtotal subtotal = new Subtotal();
assertEquals(ErrorEval.VALUE_INVALID, subtotal.evaluate(new ValueEval[] {}, 0, 0));
}
}