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

Example 46 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 47 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 48 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();
    }
}

Example 49 with CellValue

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

the class TestXSSFFormulaEvaluation method testBug55843a.

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

Example 50 with CellValue

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

the class TestXSSFFormulaEvaluation method testEvaluateColumnGreaterThan255.

/**
     * Evaluation of cell references with column indexes greater than 255. See bugzilla 50096
     */
@Test
public void testEvaluateColumnGreaterThan255() throws IOException {
    XSSFWorkbook wb = (XSSFWorkbook) _testDataProvider.openSampleWorkbook("50096.xlsx");
    XSSFFormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    /**
         *  The first row simply contains the numbers 1 - 300.
         *  The second row simply refers to the cell value above in the first row by a simple formula.
         */
    for (int i = 245; i < 265; i++) {
        XSSFCell cell_noformula = wb.getSheetAt(0).getRow(0).getCell(i);
        XSSFCell cell_formula = wb.getSheetAt(0).getRow(1).getCell(i);
        CellReference ref_noformula = new CellReference(cell_noformula.getRowIndex(), cell_noformula.getColumnIndex());
        CellReference ref_formula = new CellReference(cell_noformula.getRowIndex(), cell_noformula.getColumnIndex());
        String fmla = cell_formula.getCellFormula();
        // assure that the formula refers to the cell above.
        // the check below is 'deep' and involves conversion of the shared formula:
        // in the sample file a shared formula in GN1 is spanned in the range GN2:IY2,
        assertEquals(ref_noformula.formatAsString(), fmla);
        CellValue cv_noformula = evaluator.evaluate(cell_noformula);
        CellValue cv_formula = evaluator.evaluate(cell_formula);
        assertEquals("Wrong evaluation result in " + ref_formula.formatAsString(), cv_noformula.getNumberValue(), cv_formula.getNumberValue(), 0);
    }
    wb.close();
}