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

Example 21 with CellValue

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

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

the class TestMissingArgEval method testEvaluateMissingArgs.

public void testEvaluateMissingArgs() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    HSSFSheet sheet = wb.createSheet("Sheet1");
    HSSFCell cell = sheet.createRow(0).createCell(0);
    cell.setCellFormula("if(true,)");
    fe.clearAllCachedResultValues();
    CellValue cv;
    try {
        cv = fe.evaluate(cell);
    } catch (EmptyStackException e) {
        throw new AssertionFailedError("Missing args evaluation not implemented (bug 43354");
    }
    // MissingArg -> BlankEval -> zero (as formula result)
    assertEquals(0.0, cv.getNumberValue(), 0.0);
    // MissingArg -> BlankEval -> empty string (in concatenation)
    cell.setCellFormula("\"abc\"&if(true,)");
    fe.clearAllCachedResultValues();
    assertEquals("abc", fe.evaluate(cell).getStringValue());
}

Example 23 with CellValue

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

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

the class TestAddress method confirmResult.

private static void confirmResult(HSSFFormulaEvaluator fe, HSSFCell cell, String formulaText, String expectedResult) {
    cell.setCellFormula(formulaText);
    fe.notifyUpdateCell(cell);
    CellValue result = fe.evaluate(cell);
    assertEquals(result.getCellTypeEnum(), CellType.STRING);
    assertEquals(expectedResult, result.getStringValue());
}

Example 25 with CellValue

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

the class BaseTestCircularReferences method testMultiLevelCircularReference.

/**
     * A1=B1, B1=C1, C1=D1, D1=A1
     */
@Test
public void testMultiLevelCircularReference() throws IOException {
    Workbook wb = _testDataProvider.createWorkbook();
    Sheet sheet = wb.createSheet("Sheet1");
    Row row = sheet.createRow(0);
    row.createCell(0).setCellFormula("B1");
    row.createCell(1).setCellFormula("C1");
    row.createCell(2).setCellFormula("D1");
    Cell testCell = row.createCell(3);
    testCell.setCellFormula("A1");
    CellValue cellValue = evaluateWithCycles(wb, testCell);
    confirmCycleErrorCode(cellValue);
    wb.close();
}