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

Example 56 with CellValue

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

the class TestRangeEval method testRangeUsingOffsetFunc_bug46948.

public void testRangeUsingOffsetFunc_bug46948() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFRow row = wb.createSheet("Sheet1").createRow(0);
    HSSFCell cellA1 = row.createCell(0);
    HSSFCell cellB1 = row.createCell(1);
    // C1
    row.createCell(2).setCellValue(5.0);
    // D1
    row.createCell(3).setCellValue(7.0);
    // E1
    row.createCell(4).setCellValue(9.0);
    cellA1.setCellFormula("SUM(C1:OFFSET(C1,0,B1))");
    // range will be C1:D1
    cellB1.setCellValue(1.0);
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    CellValue cv;
    try {
        cv = fe.evaluate(cellA1);
    } catch (IllegalArgumentException e) {
        if (e.getMessage().equals("Unexpected ref arg class (org.apache.poi.ss.formula.LazyAreaEval)")) {
            throw new AssertionFailedError("Identified bug 46948");
        }
        throw e;
    }
    assertEquals(12.0, cv.getNumberValue(), 0.0);
    // range will be C1:E1
    cellB1.setCellValue(2.0);
    fe.notifyUpdateCell(cellB1);
    cv = fe.evaluate(cellA1);
    assertEquals(21.0, cv.getNumberValue(), 0.0);
    // range will be C1:C1
    cellB1.setCellValue(0.0);
    fe.notifyUpdateCell(cellB1);
    cv = fe.evaluate(cellA1);
    assertEquals(5.0, cv.getNumberValue(), 0.0);
}

Example 57 with CellValue

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

the class BaseTestCircularReferences method testIndexFormula.

/**
     * ASF Bugzilla Bug 44413
     * "INDEX() formula cannot contain its own location in the data array range"
     */
@Test
public void testIndexFormula() throws IOException {
    Workbook wb = _testDataProvider.createWorkbook();
    Sheet sheet = wb.createSheet("Sheet1");
    int colB = 1;
    sheet.createRow(0).createCell(colB).setCellValue(1);
    sheet.createRow(1).createCell(colB).setCellValue(2);
    sheet.createRow(2).createCell(colB).setCellValue(3);
    Row row4 = sheet.createRow(3);
    Cell testCell = row4.createCell(0);
    // This formula should evaluate to the contents of B2,
    testCell.setCellFormula("INDEX(A1:B4,2,2)");
    // However the range A1:B4 also includes the current cell A4.  If the other parameters
    // were 4 and 1, this would represent a circular reference.  Prior to v3.2 POI would
    // 'fully' evaluate ref arguments before invoking operators, which raised the possibility of
    // cycles / StackOverflowErrors.
    CellValue cellValue = evaluateWithCycles(wb, testCell);
    assertTrue(cellValue.getCellTypeEnum() == CellType.NUMERIC);
    assertEquals(2, cellValue.getNumberValue(), 0);
    wb.close();
}

Example 58 with CellValue

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

the class BaseTestCircularReferences method testSimpleCircularReference.

/**
     * Cell A1 has formula "=A1"
     */
@Test
public void testSimpleCircularReference() throws IOException {
    Workbook wb = _testDataProvider.createWorkbook();
    Sheet sheet = wb.createSheet("Sheet1");
    Row row = sheet.createRow(0);
    Cell testCell = row.createCell(0);
    testCell.setCellFormula("A1");
    CellValue cellValue = evaluateWithCycles(wb, testCell);
    confirmCycleErrorCode(cellValue);
    wb.close();
}

Example 59 with CellValue

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

the class TestMultiSheetEval method processFunctionRow.

/**
	 *
	 * @return a constant from the local Result class denoting whether there were any evaluation
	 * cases, and whether they all succeeded.
	 */
private int processFunctionRow(HSSFFormulaEvaluator evaluator, String targetFunctionName, String targetTestName, Row formulasRow, Cell expectedValueCell) {
    // so far
    int result = Result.NO_EVALUATIONS_FOUND;
    Cell c = formulasRow.getCell(SS.COLUMN_INDEX_ACTUAL_VALUE);
    if (c == null || c.getCellTypeEnum() != CellType.FORMULA) {
        return result;
    }
    CellValue actualValue = evaluator.evaluate(c);
    try {
        confirmExpectedResult("Function '" + targetFunctionName + "': Test: '" + targetTestName + "' Formula: " + c.getCellFormula() + " @ " + formulasRow.getRowNum() + ":" + SS.COLUMN_INDEX_ACTUAL_VALUE, expectedValueCell, actualValue);
        _evaluationSuccessCount++;
        if (result != Result.SOME_EVALUATIONS_FAILED) {
            result = Result.ALL_EVALUATIONS_SUCCEEDED;
        }
    } catch (AssertionFailedError e) {
        _evaluationFailureCount++;
        printShortStackTrace(System.err, e);
        result = Result.SOME_EVALUATIONS_FAILED;
    }
    return result;
}

Example 60 with CellValue

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

the class TestPercentEval method testInSpreadSheet.

public void testInSpreadSheet() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Sheet1");
    HSSFRow row = sheet.createRow(0);
    HSSFCell cell = row.createCell(0);
    cell.setCellFormula("B1%");
    row.createCell(1).setCellValue(50.0);
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    CellValue cv;
    try {
        cv = fe.evaluate(cell);
    } catch (RuntimeException e) {
        if (e.getCause() instanceof NullPointerException) {
            throw new AssertionFailedError("Identified bug 44608");
        }
        // else some other unexpected error
        throw e;
    }
    assertEquals(CellType.NUMERIC, cv.getCellTypeEnum());
    assertEquals(0.5, cv.getNumberValue(), 0.0);
}