/** * 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> * int evaluatedCellType = evaluator.evaluateFormulaCell(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 HSSFCell.CELL_TYPE_FORMULA however) */ public int evaluateFormulaCell(Cell cell) { if (cell == null || cell.getCellType() != XSSFCell.CELL_TYPE_FORMULA) { return -1; } CellValue cv = evaluateFormulaCellValue(cell); // cell remains a formula cell, but the cached value is changed setCellValue(cell, cv); return cv.getCellType(); }
/** * If cell contains formula, it evaluates the formula, and * puts the formula result back into the cell, in place * of the old formula. * Else if cell does not contain formula, this method leaves * the cell unchanged. * Note that the same instance of HSSFCell is returned to * allow chained calls like: * <pre> * int evaluatedCellType = evaluator.evaluateInCell(cell).getCellType(); * </pre> * Be aware that your cell value will be changed to hold the * result of the formula. If you simply want the formula * value computed for you, use {@link #evaluateFormulaCell(org.apache.poi.ss.usermodel.Cell)} } * @param cell */ public XSSFCell evaluateInCell(Cell cell) { if (cell == null) { return null; } XSSFCell result = (XSSFCell) cell; if (cell.getCellType() == XSSFCell.CELL_TYPE_FORMULA) { CellValue cv = evaluateFormulaCellValue(cell); setCellType(cell, cv); // cell will no longer be a formula cell setCellValue(cell, cv); } return result; } private static void setCellType(Cell cell, CellValue cv) {
/** * If cell contains a formula, the formula is evaluated and returned, * else the CellValue simply copies the appropriate cell value from * the cell and also its cell type. This method should be preferred over * evaluateInCell() when the call should not modify the contents of the * original cell. * @param cell */ public CellValue evaluate(Cell cell) { if (cell == null) { return null; } switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_BOOLEAN: return CellValue.valueOf(cell.getBooleanCellValue()); case XSSFCell.CELL_TYPE_ERROR: return CellValue.getError(cell.getErrorCellValue()); case XSSFCell.CELL_TYPE_FORMULA: return evaluateFormulaCellValue(cell); case XSSFCell.CELL_TYPE_NUMERIC: return new CellValue(cell.getNumericCellValue()); case XSSFCell.CELL_TYPE_STRING: return new CellValue(cell.getRichStringCellValue().getString()); case XSSFCell.CELL_TYPE_BLANK: return null; } throw new IllegalStateException("Bad cell type (" + cell.getCellType() + ")"); }