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

Example 16 with CellValue

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

the class TestSubtotal method confirmExpectedResult.

private static void confirmExpectedResult(FormulaEvaluator evaluator, String msg, Cell cell, double expected) {
    CellValue value = evaluator.evaluate(cell);
    if (value.getErrorValue() != 0)
        throw new RuntimeException(msg + ": " + value.formatAsString());
    assertEquals(msg, expected, value.getNumberValue(), 0);
}

Example 17 with CellValue

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

the class TestNpv method testNpvFromSpreadsheet.

/**
     * evaluate formulas with NPV and compare the result with
     * the cached formula result pre-calculated by Excel
     */
public void testNpvFromSpreadsheet() {
    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 cellB = row.getCell(1);
        try {
            CellValue cv = fe.evaluate(cellB);
            assertFormulaResult(cv, cellB);
        } catch (Throwable e) {
            if (failures.length() > 0)
                failures.append('\n');
            failures.append("Row[" + (cellB.getRowIndex() + 1) + "]: " + cellB.getCellFormula() + " ");
            failures.append(e.getMessage());
            failureCount++;
        }
    }
    if (failures.length() > 0) {
        throw new AssertionFailedError(failureCount + " IRR evaluations failed:\n" + failures);
    }
}

Example 18 with CellValue

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

the class TestExternalFunctionFormulas method confirmCellEval.

private static void confirmCellEval(HSSFSheet sheet, int rowIx, int colIx, HSSFFormulaEvaluator fe, String expectedFormula, double expectedResult) {
    HSSFCell cell = sheet.getRow(rowIx).getCell(colIx);
    assertEquals(expectedFormula, cell.getCellFormula());
    CellValue cv = fe.evaluate(cell);
    assertEquals(expectedResult, cv.getNumberValue(), 0.0);
}

Example 19 with CellValue

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

the class TestFunctionRegistry method testRegisterInRuntime.

public void testRegisterInRuntime() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Sheet1");
    HSSFRow row = sheet.createRow(0);
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    HSSFCell cellA = row.createCell(0);
    cellA.setCellFormula("FISHER(A5)");
    CellValue cv;
    try {
        cv = fe.evaluate(cellA);
        fail("expectecd exception");
    } catch (NotImplementedException e) {
    }
    FunctionEval.registerFunction("FISHER", new Function() {

        @Override
        public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) {
            return ErrorEval.NA;
        }
    });
    cv = fe.evaluate(cellA);
    assertEquals(ErrorEval.NA.getErrorCode(), cv.getErrorValue());
    HSSFCell cellB = row.createCell(1);
    cellB.setCellFormula("CUBEMEMBERPROPERTY(A5)");
    try {
        cv = fe.evaluate(cellB);
        fail("expectecd exception");
    } catch (NotImplementedException e) {
    }
    AnalysisToolPak.registerFunction("CUBEMEMBERPROPERTY", new FreeRefFunction() {

        @Override
        public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
            return ErrorEval.NUM_ERROR;
        }
    });
    cv = fe.evaluate(cellB);
    assertEquals(ErrorEval.NUM_ERROR.getErrorCode(), cv.getErrorValue());
}

Example 20 with CellValue

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

the class TestWorkbookEvaluator method testResultOutsideRange.

/**
     * Functions like IF, INDIRECT, INDEX, OFFSET etc can return AreaEvals which
     * should be dereferenced by the evaluator
     */
@Test
public void testResultOutsideRange() throws IOException {
    Workbook wb = new HSSFWorkbook();
    try {
        Cell cell = wb.createSheet("Sheet1").createRow(0).createCell(0);
        // IF(TRUE,D2:D5,D2) or  OFFSET(D2:D5,0,0) would work too
        cell.setCellFormula("D2:D5");
        FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
        CellValue cv;
        try {
            cv = fe.evaluate(cell);
        } catch (IllegalArgumentException e) {
            if ("Specified row index (0) is outside the allowed range (1..4)".equals(e.getMessage())) {
                fail("Identified bug in result dereferencing");
            }
            throw new RuntimeException(e);
        }
        assertEquals(CellType.ERROR, cv.getCellTypeEnum());
        assertEquals(ErrorEval.VALUE_INVALID.getErrorCode(), cv.getErrorValue());
        // verify circular refs are still detected properly
        fe.clearAllCachedResultValues();
        cell.setCellFormula("OFFSET(A1,0,0)");
        cv = fe.evaluate(cell);
        assertEquals(CellType.ERROR, cv.getCellTypeEnum());
        assertEquals(ErrorEval.CIRCULAR_REF_ERROR.getErrorCode(), cv.getErrorValue());
    } finally {
        wb.close();
    }
}