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

Example 46 with FormulaEvaluator

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

the class CountifsTests method testCallFunction_invalidArgs.

/**
     * Test argument count check
     */
@Test
public void testCallFunction_invalidArgs() {
    Sheet sheet = workbook.createSheet("test");
    Row row1 = sheet.createRow(0);
    Cell cellA1 = row1.createCell(0, CellType.FORMULA);
    cellA1.setCellFormula("COUNTIFS()");
    FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    CellValue evaluate = evaluator.evaluate(cellA1);
    assertEquals(15, evaluate.getErrorValue());
    cellA1.setCellFormula("COUNTIFS(A1:C1)");
    evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    evaluate = evaluator.evaluate(cellA1);
    assertEquals(15, evaluate.getErrorValue());
    cellA1.setCellFormula("COUNTIFS(A1:C1,2,2)");
    evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    evaluate = evaluator.evaluate(cellA1);
    assertEquals(15, evaluate.getErrorValue());
}

Example 47 with FormulaEvaluator

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

the class TestSubtotal method testMax.

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

Example 48 with FormulaEvaluator

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

the class TestSubtotal method testStdev.

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

Example 49 with FormulaEvaluator

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

the class TestSubtotal method testCount.

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

Example 50 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();
}