| /* ==================================================================== |
| 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 static org.junit.jupiter.api.Assertions.assertEquals; |
| import static org.junit.jupiter.api.Assertions.assertNotNull; |
| |
| import java.io.IOException; |
| |
| 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.AreaEval; |
| import org.apache.poi.ss.formula.eval.ErrorEval; |
| import org.apache.poi.ss.formula.eval.NotImplementedException; |
| import org.apache.poi.ss.formula.eval.NumberEval; |
| import org.apache.poi.ss.formula.eval.ValueEval; |
| import org.apache.poi.ss.usermodel.Cell; |
| import org.apache.poi.ss.usermodel.CellValue; |
| import org.apache.poi.ss.usermodel.FormulaError; |
| import org.apache.poi.ss.usermodel.FormulaEvaluator; |
| import org.apache.poi.ss.usermodel.Sheet; |
| import org.apache.poi.ss.usermodel.Workbook; |
| import org.junit.jupiter.api.Test; |
| |
| /** |
| * Tests for {@link Subtotal} |
| */ |
| final class TestSubtotal { |
| 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); |
| } |
| |
| @Test |
| 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); |
| } |
| |
| @Test |
| void testAvg() throws IOException { |
| 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(), 0); |
| assertEquals(8.0, a6.getNumericCellValue(), 0); |
| assertEquals(3.0, a7.getNumericCellValue(), 0); |
| assertEquals(3.0, a8.getNumericCellValue(), 0); |
| wb.close(); |
| |
| } |
| |
| @Test |
| void testSum() throws IOException { |
| 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(), 0); |
| assertEquals(26.0, a6.getNumericCellValue(), 0); |
| assertEquals(12.0, a7.getNumericCellValue(), 0); |
| assertEquals(12.0, a8.getNumericCellValue(), 0); |
| wb.close(); |
| } |
| |
| @Test |
| void testCount() throws IOException { |
| |
| 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(), 0); |
| assertEquals(6.0, a6.getNumericCellValue(), 0); |
| assertEquals(2.0, a7.getNumericCellValue(), 0); |
| assertEquals(2.0, a8.getNumericCellValue(), 0); |
| wb.close(); |
| } |
| |
| @Test |
| void testCounta() throws IOException { |
| |
| 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(), 0); |
| assertEquals(8.0, a6.getNumericCellValue(), 0); |
| assertEquals(3.0, a7.getNumericCellValue(), 0); |
| assertEquals(3.0, a8.getNumericCellValue(), 0); |
| wb.close(); |
| } |
| |
| @Test |
| void testMax() throws IOException { |
| |
| 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(), 0); |
| assertEquals(16.0, a6.getNumericCellValue(), 0); |
| assertEquals(7.0, a7.getNumericCellValue(), 0); |
| assertEquals(7.0, a8.getNumericCellValue(), 0); |
| wb.close(); |
| } |
| |
| @Test |
| void testMin() throws IOException { |
| |
| 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(), 0); |
| assertEquals(4.0, a6.getNumericCellValue(), 0); |
| assertEquals(1.0, a7.getNumericCellValue(), 0); |
| assertEquals(1.0, a8.getNumericCellValue(), 0); |
| wb.close(); |
| } |
| |
| @Test |
| void testStdev() throws IOException { |
| |
| 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); |
| wb.close(); |
| } |
| |
| @Test |
| void test50209() throws IOException { |
| 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(), 0); |
| assertEquals(1.0, a3.getNumericCellValue(), 0); |
| wb.close(); |
| } |
| |
| 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(expected, value.getNumberValue(), 0, msg); |
| } |
| |
| @Test |
| void testFunctionsFromTestSpreadsheet() throws IOException { |
| HSSFWorkbook workbook = HSSFTestDataSamples.openSampleWorkbook("SubtotalsNested.xls"); |
| HSSFSheet sheet = workbook.getSheetAt(0); |
| FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); |
| |
| assertEquals(10.0, sheet.getRow(1).getCell(1).getNumericCellValue(), 0, "B2"); |
| assertEquals(20.0, sheet.getRow(2).getCell(1).getNumericCellValue(), 0, "B3"); |
| |
| //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(sheet.getRow(1).getCell(2), "C2 must not be null"); |
| assertEquals(7.0, sheet.getRow(1).getCell(2).getNumericCellValue(), 0, "C2"); |
| |
| 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); |
| |
| // test same functions ignoring hidden rows over a copy of the same data |
| cellC1 = sheet.getRow(11).getCell(3); |
| cellC2 = sheet.getRow(12).getCell(3); |
| cellC3 = sheet.getRow(13).getCell(3); |
| confirmExpectedResult(evaluator, "SUBTOTAL(SUM NO HIDDEN;B22:B28;C22:C28)", cellC1, 17.0); |
| confirmExpectedResult(evaluator, "SUBTOTAL(COUNT NO HIDDEN;B22:B28,C22:C28)", cellC2, 2.0); |
| confirmExpectedResult(evaluator, "SUBTOTAL(COUNTA NO HIDDEN;B22:B28,C22:C28)", cellC3, 4.0); |
| |
| |
| workbook.close(); |
| } |
| |
| @Test |
| void testUnimplemented() throws IOException { |
| Workbook wb = new HSSFWorkbook(); |
| |
| FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator(); |
| |
| Sheet sh = wb.createSheet(); |
| Cell a3 = sh.createRow(3).createCell(1); |
| |
| // formula, throws NotImplemnted? |
| String[][] formulas = { |
| { "SUBTOTAL(8,B2:B3)", NotImplementedException.class.getName() }, |
| { "SUBTOTAL(10,B2:B3)", NotImplementedException.class.getName() }, |
| { "SUBTOTAL(11,B2:B3)", NotImplementedException.class.getName() }, |
| { "SUBTOTAL(0,B2:B3)", null }, |
| { "SUBTOTAL(9)", FormulaParseException.class.getName() }, |
| { "SUBTOTAL()", FormulaParseException.class.getName() }, |
| }; |
| |
| for (String[] f : formulas) { |
| Exception actualEx = null; |
| try { |
| a3.setCellFormula(f[0]); |
| fe.evaluateAll(); |
| assertEquals(FormulaError.VALUE.getCode(), a3.getErrorCellValue(), f[0]); |
| } catch (Exception e) { |
| actualEx = e; |
| } |
| String msg = |
| "Check "+(f[1] == null ? "unexpected exception" : f[1])+" here, "+ |
| "adjust these tests if it was actually implemented - "+f[0]; |
| assertEquals(f[1], (actualEx == null ? null : actualEx.getClass().getName()), msg); |
| } |
| |
| Subtotal subtotal = new Subtotal(); |
| assertEquals(ErrorEval.VALUE_INVALID, subtotal.evaluate(new ValueEval[] {}, 0, 0)); |
| |
| wb.close(); |
| } |
| } |