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

Example 6 with FormulaEvaluator

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

the class TestSXSSFFormulaEvaluation method testEvaluateAllFails.

/**
     * EvaluateAll will normally fail, as any reference or
     *  formula outside of the window will fail, and any
     *  non-active sheets will fail
     */
@Test
public void testEvaluateAllFails() throws IOException {
    SXSSFWorkbook wb = new SXSSFWorkbook(5);
    SXSSFSheet s = wb.createSheet();
    FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();
    s.createRow(0).createCell(0).setCellFormula("1+2");
    s.createRow(1).createCell(0).setCellFormula("A21");
    for (int i = 2; i < 19; i++) {
        s.createRow(i);
    }
    // Cells outside window will fail, whether referenced or not
    s.createRow(19).createCell(0).setCellFormula("A1+A2");
    s.createRow(20).createCell(0).setCellFormula("A1+A11+100");
    try {
        eval.evaluateAll();
        fail("Evaluate All shouldn't work, as some cells outside the window");
    } catch (SXSSFFormulaEvaluator.RowFlushedException e) {
    // Expected
    }
    // Inactive sheets will fail
    XSSFWorkbook xwb = new XSSFWorkbook();
    xwb.createSheet("Open");
    xwb.createSheet("Closed");
    wb.close();
    wb = new SXSSFWorkbook(xwb, 5);
    s = wb.getSheet("Closed");
    s.flushRows();
    s = wb.getSheet("Open");
    s.createRow(0).createCell(0).setCellFormula("1+2");
    eval = wb.getCreationHelper().createFormulaEvaluator();
    try {
        eval.evaluateAll();
        fail("Evaluate All shouldn't work, as sheets flushed");
    } catch (SXSSFFormulaEvaluator.SheetsFlushedException e) {
    }
    wb.close();
}

Example 7 with FormulaEvaluator

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

the class TestSXSSFFormulaEvaluation method testEvaluateRefInsideWindow.

@Test
public void testEvaluateRefInsideWindow() throws IOException {
    SXSSFWorkbook wb = new SXSSFWorkbook(5);
    SXSSFSheet s = wb.createSheet();
    FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();
    SXSSFCell c = s.createRow(0).createCell(0);
    c.setCellValue(1.5);
    c = s.createRow(1).createCell(0);
    c.setCellFormula("A1*2");
    assertEquals(0, (int) c.getNumericCellValue());
    eval.evaluateFormulaCellEnum(c);
    assertEquals(3, (int) c.getNumericCellValue());
    wb.close();
}

Example 8 with FormulaEvaluator

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

the class TestSXSSFFormulaEvaluation method testEvaluateRefOutsideWindowFails.

@Test
public void testEvaluateRefOutsideWindowFails() throws IOException {
    SXSSFWorkbook wb = new SXSSFWorkbook(5);
    SXSSFSheet s = wb.createSheet();
    s.createRow(0).createCell(0).setCellFormula("1+2");
    assertEquals(false, s.areAllRowsFlushed());
    assertEquals(-1, s.getLastFlushedRowNum());
    for (int i = 1; i <= 19; i++) {
        s.createRow(i);
    }
    Cell c = s.createRow(20).createCell(0);
    c.setCellFormula("A1+100");
    assertEquals(false, s.areAllRowsFlushed());
    assertEquals(15, s.getLastFlushedRowNum());
    FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();
    try {
        eval.evaluateFormulaCellEnum(c);
        fail("Evaluate shouldn't work, as reference outside the window");
    } catch (SXSSFFormulaEvaluator.RowFlushedException e) {
    // Expected
    }
    wb.close();
}

Example 9 with FormulaEvaluator

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

the class TestXSSFDataFormat method testConditionalFormattingEvaluation.

@Test
public void testConditionalFormattingEvaluation() throws IOException {
    final Workbook wb = XSSFTestDataSamples.openSampleWorkbook("61060-conditional-number-formatting.xlsx");
    final DataFormatter formatter = new DataFormatter();
    final FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    final ConditionalFormattingEvaluator cfEvaluator = new ConditionalFormattingEvaluator(wb, (WorkbookEvaluatorProvider) evaluator);
    CellReference ref = new CellReference("A1");
    Cell cell = wb.getSheetAt(0).getRow(ref.getRow()).getCell(ref.getCol());
    assertEquals("0.10", formatter.formatCellValue(cell, evaluator, cfEvaluator));
    // verify cell format without the conditional rule applied
    assertEquals("0.1", formatter.formatCellValue(cell, evaluator));
    ref = new CellReference("A3");
    cell = wb.getSheetAt(0).getRow(ref.getRow()).getCell(ref.getCol());
    assertEquals("-2.00E+03", formatter.formatCellValue(cell, evaluator, cfEvaluator));
    // verify cell format without the conditional rule applied
    assertEquals("-2000", formatter.formatCellValue(cell, evaluator));
    ref = new CellReference("A4");
    cell = wb.getSheetAt(0).getRow(ref.getRow()).getCell(ref.getCol());
    assertEquals("100", formatter.formatCellValue(cell, evaluator, cfEvaluator));
    ref = new CellReference("A5");
    cell = wb.getSheetAt(0).getRow(ref.getRow()).getCell(ref.getCol());
    assertEquals("$1,000", formatter.formatCellValue(cell, evaluator, cfEvaluator));
    // verify cell format without the conditional rule applied
    assertEquals("1000", formatter.formatCellValue(cell, evaluator));
    wb.close();
}

Example 10 with FormulaEvaluator

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

the class TestXSSFFormulaEvaluation method testMultiSheetReferencesHSSFandXSSF.

/**
     * A handful of functions (such as SUM, COUNTA, MIN) support
     *  multi-sheet references (eg Sheet1:Sheet3!A1 = Cell A1 from
     *  Sheets 1 through Sheet 3).
     * This test, based on common test files for HSSF and XSSF, checks
     *  that we can correctly evaluate these
     */
@Test
public void testMultiSheetReferencesHSSFandXSSF() throws Exception {
    Workbook wb1 = HSSFTestDataSamples.openSampleWorkbook("55906-MultiSheetRefs.xls");
    Workbook wb2 = XSSFTestDataSamples.openSampleWorkbook("55906-MultiSheetRefs.xlsx");
    for (Workbook wb : new Workbook[] { wb1, wb2 }) {
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        Sheet s1 = wb.getSheetAt(0);
        // Simple SUM over numbers
        Cell sumF = s1.getRow(2).getCell(0);
        assertNotNull(sumF);
        assertEquals("SUM(Sheet1:Sheet3!A1)", sumF.getCellFormula());
        assertEquals("Failed for " + wb.getClass(), "66.0", evaluator.evaluate(sumF).formatAsString());
        // Various Stats formulas on numbers
        Cell avgF = s1.getRow(2).getCell(1);
        assertNotNull(avgF);
        assertEquals("AVERAGE(Sheet1:Sheet3!A1)", avgF.getCellFormula());
        assertEquals("22.0", evaluator.evaluate(avgF).formatAsString());
        Cell minF = s1.getRow(3).getCell(1);
        assertNotNull(minF);
        assertEquals("MIN(Sheet1:Sheet3!A$1)", minF.getCellFormula());
        assertEquals("11.0", evaluator.evaluate(minF).formatAsString());
        Cell maxF = s1.getRow(4).getCell(1);
        assertNotNull(maxF);
        assertEquals("MAX(Sheet1:Sheet3!A$1)", maxF.getCellFormula());
        assertEquals("33.0", evaluator.evaluate(maxF).formatAsString());
        Cell countF = s1.getRow(5).getCell(1);
        assertNotNull(countF);
        assertEquals("COUNT(Sheet1:Sheet3!A$1)", countF.getCellFormula());
        assertEquals("3.0", evaluator.evaluate(countF).formatAsString());
        // Various CountAs on Strings
        Cell countA_1F = s1.getRow(2).getCell(2);
        assertNotNull(countA_1F);
        assertEquals("COUNTA(Sheet1:Sheet3!C1)", countA_1F.getCellFormula());
        assertEquals("3.0", evaluator.evaluate(countA_1F).formatAsString());
        Cell countA_2F = s1.getRow(2).getCell(3);
        assertNotNull(countA_2F);
        assertEquals("COUNTA(Sheet1:Sheet3!D1)", countA_2F.getCellFormula());
        assertEquals("0.0", evaluator.evaluate(countA_2F).formatAsString());
        Cell countA_3F = s1.getRow(2).getCell(4);
        assertNotNull(countA_3F);
        assertEquals("COUNTA(Sheet1:Sheet3!E1)", countA_3F.getCellFormula());
        assertEquals("3.0", evaluator.evaluate(countA_3F).formatAsString());
    }
    wb2.close();
    wb1.close();
}