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

Example 41 with CellValue

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

the class TestFind method confirmResult.

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

Example 42 with CellValue

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

the class TestIrr method testIrrFromSpreadsheet.

public void testIrrFromSpreadsheet() {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("IrrNpvTestCaseData.xls");
    HSSFSheet sheet = wb.getSheet("IRR-NPV");
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    StringBuffer failures = new StringBuffer();
    int failureCount = 0;
    // FormulaEvaluator as of r1041407 throws "Unexpected ptg class (org.apache.poi.ss.formula.ptg.ArrayPtg)"
    for (int rownum = 9; rownum <= 15; rownum++) {
        HSSFRow row = sheet.getRow(rownum);
        HSSFCell cellA = row.getCell(0);
        try {
            CellValue cv = fe.evaluate(cellA);
            assertFormulaResult(cv, cellA);
        } catch (Throwable e) {
            if (failures.length() > 0)
                failures.append('\n');
            failures.append("Row[" + (cellA.getRowIndex() + 1) + "]: " + cellA.getCellFormula() + " ");
            failures.append(e.getMessage());
            failureCount++;
        }
        //IRR-NPV relationship: NPV(IRR(values), values) = 0
        HSSFCell cellC = row.getCell(2);
        try {
            CellValue cv = fe.evaluate(cellC);
            // should agree within 0.01%
            assertEquals(0, cv.getNumberValue(), 0.0001);
        } catch (Throwable e) {
            if (failures.length() > 0)
                failures.append('\n');
            failures.append("Row[" + (cellC.getRowIndex() + 1) + "]: " + cellC.getCellFormula() + " ");
            failures.append(e.getMessage());
            failureCount++;
        }
    }
    if (failures.length() > 0) {
        throw new AssertionFailedError(failureCount + " IRR assertions failed:\n" + failures);
    }
}

Example 43 with CellValue

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

the class BaseFormulaEvaluator method evaluateFormulaCellEnum.

/**
     * If cell contains formula, it evaluates the formula,
     *  and saves the result of the formula. The cell
     *  remains as a formula cell.
     * Else if cell does not contain formula, this method leaves
     *  the cell unchanged.
     * Note that the type of the formula result is returned,
     *  so you know what kind of value is also stored with
     *  the formula.
     * <pre>
     * CellType evaluatedCellType = evaluator.evaluateFormulaCellEnum(cell);
     * </pre>
     * Be aware that your cell will hold both the formula,
     *  and the result. If you want the cell replaced with
     *  the result of the formula, use {@link #evaluate(org.apache.poi.ss.usermodel.Cell)} }
     * @param cell The cell to evaluate
     * @return The type of the formula result (the cell's type remains as CellType.FORMULA however)
     *         If cell is not a formula cell, returns {@link CellType#_NONE} rather than throwing an exception.
     * @since POI 3.15 beta 3
     */
@Override
public CellType evaluateFormulaCellEnum(Cell cell) {
    if (cell == null || cell.getCellTypeEnum() != CellType.FORMULA) {
        return CellType._NONE;
    }
    CellValue cv = evaluateFormulaCellValue(cell);
    // cell remains a formula cell, but the cached value is changed
    setCellValue(cell, cv);
    return cv.getCellTypeEnum();
}

Example 44 with CellValue

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

the class TestXSSFFormulaEvaluation method testBug55843e.

@Test
public void testBug55843e() 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());
    } finally {
        wb.close();
    }
}

Example 45 with CellValue

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

the class TestXSSFFormulaEvaluation method testBug55843.

@Test
public void testBug55843() 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()-ROW(A$1))*12))");
        CellValue evaluate = formulaEvaluator.evaluate(cellA2);
        assertEquals("12.0", evaluate.formatAsString());
        cellA2.setCellFormula("IF(NOT(B1=0),((ROW()-ROW(A$1))*12),\"\")");
        CellValue evaluateN = formulaEvaluator.evaluate(cellA2);
        assertEquals(evaluate.toString(), evaluateN.toString());
        assertEquals("12.0", evaluateN.formatAsString());
    } finally {
        wb.close();
    }
}