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

Example 26 with CellValue

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

the class BaseTestCircularReferences method testIntermediateCircularReferenceResults_bug46898.

@Test
public void testIntermediateCircularReferenceResults_bug46898() throws IOException {
    Workbook wb = _testDataProvider.createWorkbook();
    Sheet sheet = wb.createSheet("Sheet1");
    Row row = sheet.createRow(0);
    Cell cellA1 = row.createCell(0);
    Cell cellB1 = row.createCell(1);
    Cell cellC1 = row.createCell(2);
    Cell cellD1 = row.createCell(3);
    Cell cellE1 = row.createCell(4);
    cellA1.setCellFormula("IF(FALSE, 1+B1, 42)");
    cellB1.setCellFormula("1+C1");
    cellC1.setCellFormula("1+D1");
    cellD1.setCellFormula("1+E1");
    cellE1.setCellFormula("1+A1");
    FormulaEvaluator fe = _testDataProvider.createFormulaEvaluator(wb);
    CellValue cv;
    // Happy day flow - evaluate A1 first
    cv = fe.evaluate(cellA1);
    assertEquals(CellType.NUMERIC, cv.getCellTypeEnum());
    assertEquals(42.0, cv.getNumberValue(), 0.0);
    // no circ-ref-error because A1 result is cached
    cv = fe.evaluate(cellB1);
    assertEquals(CellType.NUMERIC, cv.getCellTypeEnum());
    assertEquals(46.0, cv.getNumberValue(), 0.0);
    // Show the bug - evaluate another cell from the loop first
    fe.clearAllCachedResultValues();
    cv = fe.evaluate(cellB1);
    // Identified bug 46898
    assertNotEquals(cv.getCellTypeEnum(), ErrorEval.CIRCULAR_REF_ERROR.getErrorCode());
    assertEquals(CellType.NUMERIC, cv.getCellTypeEnum());
    assertEquals(46.0, cv.getNumberValue(), 0.0);
    // start evaluation on another cell
    fe.clearAllCachedResultValues();
    cv = fe.evaluate(cellE1);
    assertEquals(CellType.NUMERIC, cv.getCellTypeEnum());
    assertEquals(43.0, cv.getNumberValue(), 0.0);
    wb.close();
}

Example 27 with CellValue

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

the class TestCountFuncs method testCountifExamples.

/**
     * Two COUNTIF examples taken from
     * http://office.microsoft.com/en-us/excel-help/countif-function-HP010069840.aspx?CTT=5&origin=HA010277524
     */
public void testCountifExamples() {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("countifExamples.xls");
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    HSSFSheet sheet1 = wb.getSheet("MSDN Example 1");
    for (int rowIx = 7; rowIx <= 12; rowIx++) {
        HSSFRow row = sheet1.getRow(rowIx - 1);
        // cell containing a formula with COUNTIF
        HSSFCell cellA = row.getCell(0);
        assertEquals(CellType.FORMULA, cellA.getCellTypeEnum());
        // cell with a reference value
        HSSFCell cellC = row.getCell(2);
        assertEquals(CellType.NUMERIC, cellC.getCellTypeEnum());
        CellValue cv = fe.evaluate(cellA);
        double actualValue = cv.getNumberValue();
        double expectedValue = cellC.getNumericCellValue();
        assertEquals("Problem with a formula at  " + new CellReference(cellA).formatAsString() + ": " + cellA.getCellFormula() + " :" + "Expected = (" + expectedValue + ") Actual=(" + actualValue + ") ", expectedValue, actualValue, 0.0001);
    }
    HSSFSheet sheet2 = wb.getSheet("MSDN Example 2");
    for (int rowIx = 9; rowIx <= 14; rowIx++) {
        HSSFRow row = sheet2.getRow(rowIx - 1);
        // cell containing a formula with COUNTIF
        HSSFCell cellA = row.getCell(0);
        assertEquals(CellType.FORMULA, cellA.getCellTypeEnum());
        // cell with a reference value
        HSSFCell cellC = row.getCell(2);
        assertEquals(CellType.NUMERIC, cellC.getCellTypeEnum());
        CellValue cv = fe.evaluate(cellA);
        double actualValue = cv.getNumberValue();
        double expectedValue = cellC.getNumericCellValue();
        assertEquals("Problem with a formula at " + new CellReference(cellA).formatAsString() + "[" + cellA.getCellFormula() + "]: " + "Expected = (" + expectedValue + ") Actual=(" + actualValue + ") ", expectedValue, actualValue, 0.0001);
    }
}

Example 28 with CellValue

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

the class TestCountFuncs method testCountifBug51498.

/**
    * Bug #51498 - Check that CountIf behaves correctly for GTE, LTE
    *  and NEQ cases
    */
public void testCountifBug51498() throws Exception {
    final int REF_COL = 4;
    final int EVAL_COL = 3;
    HSSFWorkbook workbook = HSSFTestDataSamples.openSampleWorkbook("51498.xls");
    FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    HSSFSheet sheet = workbook.getSheetAt(0);
    // numeric criteria
    for (int i = 0; i < 8; i++) {
        CellValue expected = evaluator.evaluate(sheet.getRow(i).getCell(REF_COL));
        CellValue actual = evaluator.evaluate(sheet.getRow(i).getCell(EVAL_COL));
        assertEquals(expected.formatAsString(), actual.formatAsString());
    }
    // boolean criteria
    for (int i = 0; i < 8; i++) {
        HSSFCell cellFmla = sheet.getRow(i).getCell(8);
        HSSFCell cellRef = sheet.getRow(i).getCell(9);
        double expectedValue = cellRef.getNumericCellValue();
        double actualValue = evaluator.evaluate(cellFmla).getNumberValue();
        assertEquals("Problem with a formula at " + new CellReference(cellFmla).formatAsString() + "[" + cellFmla.getCellFormula() + "] ", expectedValue, actualValue, 0.0001);
    }
    // string criteria
    for (int i = 1; i < 9; i++) {
        HSSFCell cellFmla = sheet.getRow(i).getCell(13);
        HSSFCell cellRef = sheet.getRow(i).getCell(14);
        double expectedValue = cellRef.getNumericCellValue();
        double actualValue = evaluator.evaluate(cellFmla).getNumberValue();
        assertEquals("Problem with a formula at " + new CellReference(cellFmla).formatAsString() + "[" + cellFmla.getCellFormula() + "] ", expectedValue, actualValue, 0.0001);
    }
}

Example 29 with CellValue

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

the class TestCountFuncs method testCountFunctionFromSpreadsheet.

private static void testCountFunctionFromSpreadsheet(String FILE_NAME, int START_ROW_IX, int COL_IX_ACTUAL, int COL_IX_EXPECTED, String functionName) {
    int failureCount = 0;
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook(FILE_NAME);
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    int maxRow = sheet.getLastRowNum();
    for (int rowIx = START_ROW_IX; rowIx < maxRow; rowIx++) {
        HSSFRow row = sheet.getRow(rowIx);
        if (row == null) {
            continue;
        }
        HSSFCell cell = row.getCell(COL_IX_ACTUAL);
        CellValue cv = fe.evaluate(cell);
        double actualValue = cv.getNumberValue();
        double expectedValue = row.getCell(COL_IX_EXPECTED).getNumericCellValue();
        if (actualValue != expectedValue) {
            System.err.println("Problem with test case on row " + (rowIx + 1) + " " + "Expected = (" + expectedValue + ") Actual=(" + actualValue + ") ");
            failureCount++;
        }
    }
    if (failureCount > 0) {
        throw new AssertionFailedError(failureCount + " " + functionName + " evaluations failed. See stderr for more details");
    }
}

Example 30 with CellValue

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

the class TestClean 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());
}