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

Example 51 with CellValue

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

the class TestXSSFFormulaEvaluation method testBug55843b.

@Test
public void testBug55843b() throws IOException {
    XSSFWorkbook wb = new XSSFWorkbook();
    try {
        XSSFSheet sheet = wb.createSheet("test");
        XSSFRow row = sheet.createRow(0);
        XSSFRow row2 = sheet.createRow(1);
        XSSFCell cellA2 = row2.createCell(0, CellType.FORMULA);
        XSSFCell cellB1 = row.createCell(1, CellType.NUMERIC);
        cellB1.setCellValue(10);
        XSSFFormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();
        cellA2.setCellFormula("IF(B1=0,\"\",((ROW())))");
        CellValue evaluate = formulaEvaluator.evaluate(cellA2);
        assertEquals("2.0", evaluate.formatAsString());
        cellA2.setCellFormula("IF(NOT(B1=0),((ROW())),\"\")");
        CellValue evaluateN = formulaEvaluator.evaluate(cellA2);
        assertEquals(evaluate.toString(), evaluateN.toString());
        assertEquals("2.0", evaluateN.formatAsString());
    } finally {
        wb.close();
    }
}

Example 52 with CellValue

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

the class TestSharedFormulaRecord method confirmCellEvaluation.

private static void confirmCellEvaluation(HSSFWorkbook wb, HSSFCell cell, double expectedValue) {
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    CellValue cv = fe.evaluate(cell);
    assertEquals(CellType.NUMERIC, cv.getCellTypeEnum());
    assertEquals(expectedValue, cv.getNumberValue(), 0.0);
}

Example 53 with CellValue

use of org.apache.poi.ss.usermodel.CellValue 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 54 with CellValue

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

the class TestWorkbookEvaluator method testIFEqualsFormulaEvaluation_evaluate.

private void testIFEqualsFormulaEvaluation_evaluate(String formula, CellType cellType, String expectedFormula, double expectedResult) {
    Workbook wb = testIFEqualsFormulaEvaluation_setup(formula, cellType);
    Cell D1 = wb.getSheet("IFEquals").getRow(0).getCell(3);
    FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();
    CellValue result = eval.evaluate(D1);
    // Call should not modify the contents
    assertEquals(CellType.FORMULA, D1.getCellTypeEnum());
    assertEquals(expectedFormula, D1.getCellFormula());
    assertEquals(CellType.NUMERIC, result.getCellTypeEnum());
    assertEquals(expectedResult, result.getNumberValue(), EPSILON);
    testIFEqualsFormulaEvaluation_teardown(wb);
}

Example 55 with CellValue

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

the class BaseXSSFFormulaEvaluator method evaluateFormulaCellValue.

/**
     * Returns a CellValue wrapper around the supplied ValueEval instance.
     */
protected CellValue evaluateFormulaCellValue(Cell cell) {
    EvaluationCell evalCell = toEvaluationCell(cell);
    ValueEval eval = _bookEvaluator.evaluate(evalCell);
    if (eval instanceof NumberEval) {
        NumberEval ne = (NumberEval) eval;
        return new CellValue(ne.getNumberValue());
    }
    if (eval instanceof BoolEval) {
        BoolEval be = (BoolEval) eval;
        return CellValue.valueOf(be.getBooleanValue());
    }
    if (eval instanceof StringEval) {
        StringEval ne = (StringEval) eval;
        return new CellValue(ne.getStringValue());
    }
    if (eval instanceof ErrorEval) {
        return CellValue.getError(((ErrorEval) eval).getErrorCode());
    }
    throw new RuntimeException("Unexpected eval class (" + eval.getClass().getName() + ")");
}