Examples with HSSFFormulaEvaluator org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator used on opensource projects

Example 26 with HSSFFormulaEvaluator

use of org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator in project poi by apache.

the class TestWorkbookEvaluator method testMissingArg.

/**
     * This test makes sure that any {@link MissingArgEval} that propagates to
     * the result of a function gets translated to {@link BlankEval}.
     */
@Test
public void testMissingArg() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Sheet1");
    HSSFRow row = sheet.createRow(0);
    HSSFCell cell = row.createCell(0);
    cell.setCellFormula("1+IF(1,,)");
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    CellValue cv = null;
    try {
        cv = fe.evaluate(cell);
    } catch (RuntimeException e) {
        fail("Missing arg result not being handled correctly.");
    }
    assertEquals(CellType.NUMERIC, cv.getCellTypeEnum());
    // adding blank to 1.0 gives 1.0
    assertEquals(1.0, cv.getNumberValue(), 0.0);
    // check with string operand
    cell.setCellFormula("\"abc\"&IF(1,,)");
    fe.notifySetFormula(cell);
    cv = fe.evaluate(cell);
    assertEquals(CellType.STRING, cv.getCellTypeEnum());
    // adding blank to "abc" gives "abc"
    assertEquals("abc", cv.getStringValue());
    // check CHOOSE()
    cell.setCellFormula("\"abc\"&CHOOSE(2,5,,9)");
    fe.notifySetFormula(cell);
    cv = fe.evaluate(cell);
    assertEquals(CellType.STRING, cv.getCellTypeEnum());
    // adding blank to "abc" gives "abc"
    assertEquals("abc", cv.getStringValue());
}

Example 27 with HSSFFormulaEvaluator

use of org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator in project poi by apache.

the class TestWorkbookEvaluator method testEvaluateMultipleWorkbooks.

@Test
public void testEvaluateMultipleWorkbooks() {
    HSSFWorkbook wbA = HSSFTestDataSamples.openSampleWorkbook("multibookFormulaA.xls");
    HSSFWorkbook wbB = HSSFTestDataSamples.openSampleWorkbook("multibookFormulaB.xls");
    HSSFFormulaEvaluator evaluatorA = new HSSFFormulaEvaluator(wbA);
    HSSFFormulaEvaluator evaluatorB = new HSSFFormulaEvaluator(wbB);
    // Hook up the workbook evaluators to enable evaluation of formulas across books
    String[] bookNames = { "multibookFormulaA.xls", "multibookFormulaB.xls" };
    HSSFFormulaEvaluator[] evaluators = { evaluatorA, evaluatorB };
    HSSFFormulaEvaluator.setupEnvironment(bookNames, evaluators);
    HSSFCell cell;
    HSSFSheet aSheet1 = wbA.getSheetAt(0);
    HSSFSheet bSheet1 = wbB.getSheetAt(0);
    // Simple case - single link from wbA to wbB
    confirmFormula(wbA, 0, 0, 0, "[multibookFormulaB.xls]BSheet1!B1");
    cell = aSheet1.getRow(0).getCell(0);
    confirmEvaluation(35, evaluatorA, cell);
    // more complex case - back link into wbA
    // [wbA]ASheet1!A2 references (among other things) [wbB]BSheet1!B2
    confirmFormula(wbA, 0, 1, 0, "[multibookFormulaB.xls]BSheet1!$B$2+2*A3");
    // [wbB]BSheet1!B2 references (among other things) [wbA]AnotherSheet!A1:B2
    confirmFormula(wbB, 0, 1, 1, "SUM([multibookFormulaA.xls]AnotherSheet!$A$1:$B$2)+B3");
    cell = aSheet1.getRow(1).getCell(0);
    confirmEvaluation(264, evaluatorA, cell);
    // change [wbB]BSheet1!B3 (from 50 to 60)
    HSSFCell cellB3 = bSheet1.getRow(2).getCell(1);
    cellB3.setCellValue(60);
    evaluatorB.notifyUpdateCell(cellB3);
    confirmEvaluation(274, evaluatorA, cell);
    // change [wbA]ASheet1!A3 (from 100 to 80)
    HSSFCell cellA3 = aSheet1.getRow(2).getCell(0);
    cellA3.setCellValue(80);
    evaluatorA.notifyUpdateCell(cellA3);
    confirmEvaluation(234, evaluatorA, cell);
    // change [wbA]AnotherSheet!A1 (from 2 to 3)
    HSSFCell cellA1 = wbA.getSheetAt(1).getRow(0).getCell(0);
    cellA1.setCellValue(3);
    evaluatorA.notifyUpdateCell(cellA1);
    confirmEvaluation(235, evaluatorA, cell);
}

Example 28 with HSSFFormulaEvaluator

use of org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator in project poi by apache.

the class TestExternalFunctionFormulas method testEvaluate.

@Test
public void testEvaluate() throws Exception {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("externalFunctionExample.xls");
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    confirmCellEval(sheet, 0, 0, fe, "YEARFRAC(B1,C1)", 29.0 / 90.0);
    confirmCellEval(sheet, 1, 0, fe, "YEARFRAC(B2,C2)", 0.0);
    confirmCellEval(sheet, 2, 0, fe, "YEARFRAC(B3,C3,D3)", 0.0);
    confirmCellEval(sheet, 3, 0, fe, "IF(ISEVEN(3),1.2,1.6)", 1.6);
    confirmCellEval(sheet, 4, 0, fe, "IF(ISODD(3),1.2,1.6)", 1.2);
    wb.close();
}

Example 29 with HSSFFormulaEvaluator

use of org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator in project poi by apache.

the class TestRangeEval method testRangeUsingOffsetFunc_bug46948.

public void testRangeUsingOffsetFunc_bug46948() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFRow row = wb.createSheet("Sheet1").createRow(0);
    HSSFCell cellA1 = row.createCell(0);
    HSSFCell cellB1 = row.createCell(1);
    // C1
    row.createCell(2).setCellValue(5.0);
    // D1
    row.createCell(3).setCellValue(7.0);
    // E1
    row.createCell(4).setCellValue(9.0);
    cellA1.setCellFormula("SUM(C1:OFFSET(C1,0,B1))");
    // range will be C1:D1
    cellB1.setCellValue(1.0);
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    CellValue cv;
    try {
        cv = fe.evaluate(cellA1);
    } catch (IllegalArgumentException e) {
        if (e.getMessage().equals("Unexpected ref arg class (org.apache.poi.ss.formula.LazyAreaEval)")) {
            throw new AssertionFailedError("Identified bug 46948");
        }
        throw e;
    }
    assertEquals(12.0, cv.getNumberValue(), 0.0);
    // range will be C1:E1
    cellB1.setCellValue(2.0);
    fe.notifyUpdateCell(cellB1);
    cv = fe.evaluate(cellA1);
    assertEquals(21.0, cv.getNumberValue(), 0.0);
    // range will be C1:C1
    cellB1.setCellValue(0.0);
    fe.notifyUpdateCell(cellB1);
    cv = fe.evaluate(cellA1);
    assertEquals(5.0, cv.getNumberValue(), 0.0);
}

Example 30 with HSSFFormulaEvaluator

use of org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator in project poi by apache.

the class TestExternalFunction method testInvoke.

/**
	 * Checks that an external function can get invoked from the formula
	 * evaluator.
	 */
public void testInvoke() {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("testNames.xls");
    HSSFSheet sheet = wb.getSheetAt(0);
    /**
		 * register the two test UDFs in a UDF finder, to be passed to the evaluator
		 */
    UDFFinder udff1 = new DefaultUDFFinder(new String[] { "myFunc" }, new FreeRefFunction[] { new MyFunc() });
    UDFFinder udff2 = new DefaultUDFFinder(new String[] { "myFunc2" }, new FreeRefFunction[] { new MyFunc2() });
    UDFFinder udff = new AggregatingUDFFinder(udff1, udff2);
    HSSFRow row = sheet.getRow(0);
    // =myFunc("_")
    HSSFCell myFuncCell = row.getCell(1);
    // =myFunc2("_")
    HSSFCell myFunc2Cell = row.getCell(2);
    HSSFFormulaEvaluator fe = HSSFFormulaEvaluator.create(wb, null, udff);
    assertEquals("_abc", fe.evaluate(myFuncCell).getStringValue());
    assertEquals("_abc2", fe.evaluate(myFunc2Cell).getStringValue());
}