/** * @param workbook The workbook to perform the formula evaluations in * @param stabilityClassifier used to optimise caching performance. Pass <code>null</code> * for the (conservative) assumption that any cell may have its definition changed after * evaluation begins. * @param udfFinder pass <code>null</code> for default (AnalysisToolPak only) */ public static HSSFFormulaEvaluator create(HSSFWorkbook workbook, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder) { return new HSSFFormulaEvaluator(workbook, stabilityClassifier, udfFinder); }
/** * Loops over all cells in all sheets of the supplied * workbook. * For cells that contain formulas, their formulas are * evaluated, and the results are saved. These cells * remain as formula cells. * For cells that do not contain formulas, no changes * are made. * This is a helpful wrapper around looping over all * cells, and calling evaluateFormulaCell on each one. */ public static void evaluateAllFormulaCells(HSSFWorkbook wb) { evaluateAllFormulaCells(wb, new HSSFFormulaEvaluator(wb)); }
/** * Loops over all cells in all sheets of the supplied * workbook. * For cells that contain formulas, their formulas are * evaluated, and the results are saved. These cells * remain as formula cells. * For cells that do not contain formulas, no changes * are made. * This is a helpful wrapper around looping over all * cells, and calling evaluateFormulaCell on each one. */ @Override public void evaluateAll() { evaluateAllFormulaCells(_book, this); }
/** * Returns a formula evaluator that is loaded with the functions that * have been supplied. * * @param fileName Specifies if XSSF or HSSF should be used for * the evaluator * @return A {@link FormulaEvaluator} constructed accordingly */ protected FormulaEvaluator getEvaluator(String fileName) { FormulaEvaluator evaluator; if (fileName.endsWith(".xlsx")) { if(xlsMacroList.size() > 0) { evaluator = XSSFFormulaEvaluator.create((XSSFWorkbook) workbook, null, getFunctions()); } evaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook); } else { if(xlsMacroList.size() > 0) { evaluator = HSSFFormulaEvaluator.create((HSSFWorkbook)workbook, null, getFunctions()); } evaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook); } return evaluator; }
/** * 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(Cell)}} */ public HSSFCell evaluateInCell(Cell cell) { if (cell == null) { return null; } HSSFCell result = (HSSFCell) cell; if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) { CellValue cv = evaluateFormulaCellValue(cell); setCellValue(cell, cv); setCellType(cell, cv); // cell will no longer be a formula cell } return result; } private static void setCellType(Cell cell, CellValue cv) {
/** * If cell contains formula, it evaluates the formula, and saves the result of the formula. The * cell remains as a formula cell. If the cell does not contain formula, this method returns -1 * and leaves the cell unchanged. * * Note that the type of the <em>formula result</em> is returned, so you know what kind of * cached formula result 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 #evaluateInCell(org.apache.poi.ss.usermodel.Cell)} * @param cell The cell to evaluate * @return -1 for non-formula cells, or the type of the <em>formula result</em> */ public int evaluateFormulaCell(Cell cell) { if (cell == null || cell.getCellType() != HSSFCell.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 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 may be <code>null</code> signifying that the cell is not present (or blank) * @return <code>null</code> if the supplied cell is <code>null</code> or blank */ public CellValue evaluate(Cell cell) { if (cell == null) { return null; } switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_BOOLEAN: return CellValue.valueOf(cell.getBooleanCellValue()); case HSSFCell.CELL_TYPE_ERROR: return CellValue.getError(cell.getErrorCellValue()); case HSSFCell.CELL_TYPE_FORMULA: return evaluateFormulaCellValue(cell); case HSSFCell.CELL_TYPE_NUMERIC: return new CellValue(cell.getNumericCellValue()); case HSSFCell.CELL_TYPE_STRING: return new CellValue(cell.getRichStringCellValue().getString()); case HSSFCell.CELL_TYPE_BLANK: return null; } throw new IllegalStateException("Bad cell type (" + cell.getCellType() + ")"); }
@Override public PoiWorkbook refreshFormulas() { HSSFFormulaEvaluator.evaluateAllFormulaCells(workbook); return this; }
/** * 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(Cell)}} */ public HSSFCell evaluateInCell(Cell cell) { if (cell == null) { return null; } HSSFCell result = (HSSFCell) cell; if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) { CellValue cv = evaluateFormulaCellValue(cell); setCellValue(cell, cv); setCellType(cell, cv); // cell will no longer be a formula cell } return result; } private static void setCellType(Cell cell, CellValue cv) {
/** * If cell contains formula, it evaluates the formula, and saves the result of the formula. The * cell remains as a formula cell. If the cell does not contain formula, this method returns -1 * and leaves the cell unchanged. * * Note that the type of the <em>formula result</em> is returned, so you know what kind of * cached formula result 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 #evaluateInCell(org.apache.poi.ss.usermodel.Cell)} * @param cell The cell to evaluate * @return -1 for non-formula cells, or the type of the <em>formula result</em> */ public int evaluateFormulaCell(Cell cell) { if (cell == null || cell.getCellType() != HSSFCell.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 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 may be <code>null</code> signifying that the cell is not present (or blank) * @return <code>null</code> if the supplied cell is <code>null</code> or blank */ public CellValue evaluate(Cell cell) { if (cell == null) { return null; } switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_BOOLEAN: return CellValue.valueOf(cell.getBooleanCellValue()); case HSSFCell.CELL_TYPE_ERROR: return CellValue.getError(cell.getErrorCellValue()); case HSSFCell.CELL_TYPE_FORMULA: return evaluateFormulaCellValue(cell); case HSSFCell.CELL_TYPE_NUMERIC: return new CellValue(cell.getNumericCellValue()); case HSSFCell.CELL_TYPE_STRING: return new CellValue(cell.getRichStringCellValue().getString()); case HSSFCell.CELL_TYPE_BLANK: return null; } throw new IllegalStateException("Bad cell type (" + cell.getCellType() + ")"); }
/** * Creates a HSSFFormulaEvaluator, the object that evaluates formula cells. * * @return a HSSFFormulaEvaluator instance */ @Override public HSSFFormulaEvaluator createFormulaEvaluator(){ return new HSSFFormulaEvaluator(workbook); }
/** * Loops over all cells in all sheets of the supplied * workbook. * For cells that contain formulas, their formulas are * evaluated, and the results are saved. These cells * remain as formula cells. * For cells that do not contain formulas, no changes * are made. * This is a helpful wrapper around looping over all * cells, and calling evaluateFormulaCell on each one. */ public static void evaluateAllFormulaCells(HSSFWorkbook wb) { evaluateAllFormulaCells(wb, new HSSFFormulaEvaluator(wb)); }
/** * Loops over all cells in all sheets of the supplied * workbook. * For cells that contain formulas, their formulas are * evaluated, and the results are saved. These cells * remain as formula cells. * For cells that do not contain formulas, no changes * are made. * This is a helpful wrapper around looping over all * cells, and calling evaluateFormulaCell on each one. */ @Override public void evaluateAll() { evaluateAllFormulaCells(_book, this); }
InputStream inp =getClass().getResourceAsStream("filename.xls")); Workbook wb = WorkbookFactory.create(inp); DataFormatter objDefaultFormat = new DataFormatter(); FormulaEvaluator objFormulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) wb); Sheet sheet= wb.getSheetAt(0); Iterator<Row> objIterator = sheet.rowIterator(); while(objIterator.hasNext()){ Row row = objIterator.next(); Cell cellValue = row.getCell(0); objFormulaEvaluator.evaluate(cellValue); // This will evaluate the cell, And any type of cell will return string value String cellValueStr = objDefaultFormat.formatCellValue(cellValue,objFormulaEvaluator); }
/** * Loops over all cells in all sheets of the supplied * workbook. * For cells that contain formulas, their formulas are * evaluated, and the results are saved. These cells * remain as formula cells. * For cells that do not contain formulas, no changes * are made. * This is a helpful wrapper around looping over all * cells, and calling evaluateFormulaCell on each one. */ public static void evaluateAllFormulaCells(HSSFWorkbook wb) { evaluateAllFormulaCells(wb, new HSSFFormulaEvaluator(wb)); }
/** * Loops over all cells in all sheets of the supplied * workbook. * For cells that contain formulas, their formulas are * evaluated, and the results are saved. These cells * remain as formula cells. * For cells that do not contain formulas, no changes * are made. * This is a helpful wrapper around looping over all * cells, and calling evaluateFormulaCell on each one. */ public void evaluateAll() { evaluateAllFormulaCells(_book, this); }
/** * @param workbook The workbook to perform the formula evaluations in * @param stabilityClassifier used to optimise caching performance. Pass <code>null</code> * for the (conservative) assumption that any cell may have its definition changed after * evaluation begins. * @param udfFinder pass <code>null</code> for default (AnalysisToolPak only) */ public static HSSFFormulaEvaluator create(HSSFWorkbook workbook, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder) { return new HSSFFormulaEvaluator(workbook, stabilityClassifier, udfFinder); }
/** * Loops over all cells in all sheets of the supplied * workbook. * For cells that contain formulas, their formulas are * evaluated, and the results are saved. These cells * remain as formula cells. * For cells that do not contain formulas, no changes * are made. * This is a helpful wrapper around looping over all * cells, and calling evaluateFormulaCell on each one. */ public static void evaluateAllFormulaCells(HSSFWorkbook wb) { evaluateAllFormulaCells(wb, new HSSFFormulaEvaluator(wb)); }
/** * Loops over all cells in all sheets of the supplied * workbook. * For cells that contain formulas, their formulas are * evaluated, and the results are saved. These cells * remain as formula cells. * For cells that do not contain formulas, no changes * are made. * This is a helpful wrapper around looping over all * cells, and calling evaluateFormulaCell on each one. */ public void evaluateAll() { HSSFFormulaEvaluator.evaluateAllFormulaCells(_book); }