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

Example 36 with CellValue

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

the class TestFormulaEvaluatorOnXSSF method processFunctionRow.

@Test
public void processFunctionRow() {
    Row formulasRow = sheet.getRow(formulasRowIdx);
    Row expectedValuesRow = sheet.getRow(expectedValuesRowIdx);
    short endcolnum = formulasRow.getLastCellNum();
    // iterate across the row for all the evaluation cases
    for (short colnum = SS.COLUMN_INDEX_FIRST_TEST_VALUE; colnum < endcolnum; colnum++) {
        Cell c = formulasRow.getCell(colnum);
        assumeNotNull(c);
        assumeTrue(c.getCellTypeEnum() == CellType.FORMULA);
        ignoredFormulaTestCase(c.getCellFormula());
        CellValue actValue = evaluator.evaluate(c);
        Cell expValue = (expectedValuesRow == null) ? null : expectedValuesRow.getCell(colnum);
        String msg = String.format(Locale.ROOT, "Function '%s': Formula: %s @ %d:%d", targetFunctionName, c.getCellFormula(), formulasRow.getRowNum(), colnum);
        assertNotNull(msg + " - Bad setup data expected value is null", expValue);
        assertNotNull(msg + " - actual value was null", actValue);
        final CellType expectedCellType = expValue.getCellTypeEnum();
        switch(expectedCellType) {
            case BLANK:
                assertEquals(msg, CellType.BLANK, actValue.getCellTypeEnum());
                break;
            case BOOLEAN:
                assertEquals(msg, CellType.BOOLEAN, actValue.getCellTypeEnum());
                assertEquals(msg, expValue.getBooleanCellValue(), actValue.getBooleanValue());
                break;
            case ERROR:
                assertEquals(msg, CellType.ERROR, actValue.getCellTypeEnum());
                //	              }
                break;
            case // will never be used, since we will call method after formula evaluation
            FORMULA:
                fail("Cannot expect formula as result of formula evaluation: " + msg);
            case NUMERIC:
                assertEquals(msg, CellType.NUMERIC, actValue.getCellTypeEnum());
                TestMathX.assertEquals(msg, expValue.getNumericCellValue(), actValue.getNumberValue(), TestMathX.POS_ZERO, TestMathX.DIFF_TOLERANCE_FACTOR);
                //	              assertTrue(msg, delta <= pctExpValue);
                break;
            case STRING:
                assertEquals(msg, CellType.STRING, actValue.getCellTypeEnum());
                assertEquals(msg, expValue.getRichStringCellValue().getString(), actValue.getStringValue());
                break;
            default:
                fail("Unexpected cell type: " + expectedCellType);
        }
    }
}

Example 37 with CellValue

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

the class TestMultiSheetFormulaEvaluatorOnXSSF method processFunctionRow.

/**
    *
    * @return a constant from the local Result class denoting whether there were any evaluation
    * cases, and whether they all succeeded.
    */
@Test
public void processFunctionRow() {
    Row r = sheet.getRow(formulasRowIdx);
    Cell expValue = r.getCell(SS.COLUMN_INDEX_EXPECTED_VALUE);
    assertNotNull("Missing expected values cell for function '" + targetFunctionName + ", test" + targetTestName + " (row " + formulasRowIdx + 1 + ")", expValue);
    Cell c = r.getCell(SS.COLUMN_INDEX_ACTUAL_VALUE);
    assumeNotNull(c);
    assumeTrue(c.getCellTypeEnum() == CellType.FORMULA);
    CellValue actValue = evaluator.evaluate(c);
    String msg = String.format(Locale.ROOT, "Function '%s': Test: '%s': Formula: %s @ %d:%d", targetFunctionName, targetTestName, c.getCellFormula(), formulasRowIdx, SS.COLUMN_INDEX_ACTUAL_VALUE);
    assertNotNull(msg + " - actual value was null", actValue);
    final CellType expectedCellType = expValue.getCellTypeEnum();
    switch(expectedCellType) {
        case BLANK:
            assertEquals(msg, CellType.BLANK, actValue.getCellTypeEnum());
            break;
        case BOOLEAN:
            assertEquals(msg, CellType.BOOLEAN, actValue.getCellTypeEnum());
            assertEquals(msg, expValue.getBooleanCellValue(), actValue.getBooleanValue());
            break;
        case ERROR:
            assertEquals(msg, CellType.ERROR, actValue.getCellTypeEnum());
            //              }
            break;
        case // will never be used, since we will call method after formula evaluation
        FORMULA:
            fail("Cannot expect formula as result of formula evaluation: " + msg);
        case NUMERIC:
            assertEquals(msg, CellType.NUMERIC, actValue.getCellTypeEnum());
            TestMathX.assertEquals(msg, expValue.getNumericCellValue(), actValue.getNumberValue(), TestMathX.POS_ZERO, TestMathX.DIFF_TOLERANCE_FACTOR);
            //              assertTrue(msg, delta <= pctExpected);
            break;
        case STRING:
            assertEquals(msg, CellType.STRING, actValue.getCellTypeEnum());
            assertEquals(msg, expValue.getRichStringCellValue().getString(), actValue.getStringValue());
            break;
        default:
            fail("Unexpected cell type: " + expectedCellType);
    }
}

Example 38 with CellValue

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

the class TestFixed method confirm.

private void confirm(String formulaText, String expectedResult) {
    cell11.setCellFormula(formulaText);
    evaluator.clearAllCachedResultValues();
    CellValue cv = evaluator.evaluate(cell11);
    assertEquals("Wrong result type: " + cv.formatAsString(), CellType.STRING, cv.getCellTypeEnum());
    String actualValue = cv.getStringValue();
    assertEquals(expectedResult, actualValue);
}

Example 39 with CellValue

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

the class TestLogicalFunction method testIsError.

public void testIsError() {
    cell1 = row3.createCell(0);
    // produces #DIV/0!
    cell1.setCellFormula("ISERROR(B1)");
    cell2 = row3.createCell(1);
    // produces #N/A
    cell2.setCellFormula("ISERROR(B2)");
    CellValue cell1Value = evaluator.evaluate(cell1);
    CellValue cell2Value = evaluator.evaluate(cell2);
    assertEquals(true, cell1Value.getBooleanValue());
    assertEquals(true, cell2Value.getBooleanValue());
}

Example 40 with CellValue

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

the class TestLogicalFunction method testIsErr.

public void testIsErr() {
    cell1 = row3.createCell(0);
    // produces #DIV/0!
    cell1.setCellFormula("ISERR(B1)");
    cell2 = row3.createCell(1);
    // produces #N/A
    cell2.setCellFormula("ISERR(B2)");
    CellValue cell1Value = evaluator.evaluate(cell1);
    CellValue cell2Value = evaluator.evaluate(cell2);
    assertEquals(true, cell1Value.getBooleanValue());
    assertEquals(false, cell2Value.getBooleanValue());
}