Examples with FormulaEvaluator org.apache.poi.ss.usermodel.FormulaEvaluator used on opensource projects

Example 36 with FormulaEvaluator

use of org.apache.poi.ss.usermodel.FormulaEvaluator in project poi by apache.

the class TestSubtotal method testUnimplemented.

@Test
public 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(107,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());
        } 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(msg, f[1], (actualEx == null ? null : actualEx.getClass().getName()));
    }
    Subtotal subtotal = new Subtotal();
    assertEquals(ErrorEval.VALUE_INVALID, subtotal.evaluate(new ValueEval[] {}, 0, 0));
    wb.close();
}

Example 37 with FormulaEvaluator

use of org.apache.poi.ss.usermodel.FormulaEvaluator in project poi by apache.

the class TestSubtotal method testSum.

@Test
public 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();
}

Example 38 with FormulaEvaluator

use of org.apache.poi.ss.usermodel.FormulaEvaluator in project poi by apache.

the class TestSubtotal method testCounta.

@Test
public 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 is string and not counted
    a4.setCellValue("POI");
    /*Cell a5 =*/
    // A5 is blank and not counted
    sh.createRow(5).createCell(1);
    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();
}

Example 39 with FormulaEvaluator

use of org.apache.poi.ss.usermodel.FormulaEvaluator in project poi by apache.

the class TestSubtotal method testFunctionsFromTestSpreadsheet.

@Test
public void testFunctionsFromTestSpreadsheet() throws IOException {
    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(), 0);
    assertEquals("B3", 20.0, sheet.getRow(2).getCell(1).getNumericCellValue(), 0);
    //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(), 0);
    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);
    workbook.close();
}

Example 40 with FormulaEvaluator

use of org.apache.poi.ss.usermodel.FormulaEvaluator in project poi by apache.

the class TestSubtotal method test50209.

@Test
public 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();
}