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

Example 16 with CellType

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

the class TestWorkbookEvaluator method testIFEqualsFormulaEvaluation_String.

@Test
public void testIFEqualsFormulaEvaluation_String() {
    final String formula = "IF(A1=1, B1, C1)";
    final CellType cellType = CellType.STRING;
    final String expectedFormula = "IF(A1=1,B1,C1)";
    final double expectedValue = 3.0;
    testIFEqualsFormulaEvaluation_eval(formula, cellType, expectedFormula, expectedValue);
}

Example 17 with CellType

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

the class TestFormulasFromSpreadsheet 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 (int colnum = SS.COLUMN_INDEX_FIRST_TEST_VALUE; colnum < endcolnum; colnum++) {
        Cell c = formulasRow.getCell(colnum);
        if (c == null || c.getCellTypeEnum() != CellType.FORMULA) {
            continue;
        }
        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 cellType = expValue.getCellTypeEnum();
        switch(cellType) {
            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());
                assertEquals(msg, ErrorEval.getText(expValue.getErrorCellValue()), ErrorEval.getText(actValue.getErrorValue()));
                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);
                break;
            case STRING:
                assertEquals(msg, CellType.STRING, actValue.getCellTypeEnum());
                assertEquals(msg, expValue.getRichStringCellValue().getString(), actValue.getStringValue());
                break;
            default:
                fail("Unexpected cell type: " + cellType);
        }
    }
}

Example 18 with CellType

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

the class BaseTestFunctionsFromSpreadsheet method processFunctionRow.

@Test
public void processFunctionRow() throws Exception {
    HSSFRow r = sheet.getRow(formulasRowIdx);
    HSSFCell evalCell = r.getCell(SS.COLUMN_INDEX_EVALUATION);
    HSSFCell expectedCell = r.getCell(SS.COLUMN_INDEX_EXPECTED_RESULT);
    CellReference cr = new CellReference(sheet.getSheetName(), formulasRowIdx, evalCell.getColumnIndex(), false, false);
    String msg = String.format(Locale.ROOT, "In %s %s {=%s} '%s'", filename, cr.formatAsString(), evalCell.getCellFormula(), testName);
    CellValue actualValue = evaluator.evaluate(evalCell);
    assertNotNull(msg + " - Bad setup data expected value is null", expectedCell);
    assertNotNull(msg + " - actual value was null", actualValue);
    if (expectedCell.getCellTypeEnum() == CellType.ERROR) {
        int expectedErrorCode = expectedCell.getErrorCellValue();
        assertEquals(msg, CellType.ERROR, actualValue.getCellTypeEnum());
        assertEquals(msg, ErrorEval.getText(expectedErrorCode), actualValue.formatAsString());
        assertEquals(msg, expectedErrorCode, actualValue.getErrorValue());
        assertEquals(msg, ErrorEval.getText(expectedErrorCode), ErrorEval.getText(actualValue.getErrorValue()));
        return;
    }
    // unexpected error
    assertNotEquals(msg, CellType.ERROR, actualValue.getCellTypeEnum());
    assertNotEquals(msg, formatValue(expectedCell), ErrorEval.getText(actualValue.getErrorValue()));
    // wrong type error
    assertEquals(msg, expectedCell.getCellTypeEnum(), actualValue.getCellTypeEnum());
    final CellType expectedCellType = expectedCell.getCellTypeEnum();
    switch(expectedCellType) {
        case BOOLEAN:
            assertEquals(msg, expectedCell.getBooleanCellValue(), actualValue.getBooleanValue());
            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(expectedCell.getNumericCellValue(), actualValue.getNumberValue(), 0.0);
            break;
        case STRING:
            assertEquals(msg, expectedCell.getRichStringCellValue().getString(), actualValue.getStringValue());
            break;
        default:
            fail("Unexpected cell type: " + expectedCellType);
    }
}

Example 19 with CellType

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

use of org.apache.poi.ss.usermodel.CellType 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);
    }
}