How to read cell value from formula cell using apache poi

Question

I am trying to read all the data from an excel file that also has some formula cell, but I don't know which cell is the formula cell. how can I read all values ​​from cells, regardless of cell type.

My code is as follows

FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); while (rows.hasNext()) { row = (HSSFRow) rows.next(); Iterator cells = row.cellIterator(); while (cells.hasNext()) { cell = (HSSFCell) cells.next(); if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { ar.add(cell.getStringCellValue()); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { ar.add(cell.getNumericCellValue()); }else if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) { ar.add(evaluator.evaluateFormulaCell(cell)); } else { ar.add(""); } } } 

I get the cell value of the formula as 0

Answer 1

The method you are looking for is Cell.getCachedFormulaResultType - for a formula cell that tells you the type of formula result

In this case, the code might look something like this:

 private void handleCell(int type, Cell cell) { if (type == HSSFCell.CELL_TYPE_STRING) { ar.add(cell.getStringCellValue()); } else if (type == HSSFCell.CELL_TYPE_NUMERIC) { ar.add(cell.getNumericCellValue()); } else if (type == HSSFCell.CELL_TYPE_BOOLEAN) { ar.add(cell.getBooleanCellValue()); } else if (type == HSSFCell.CELL_TYPE_FORMULA) { // Re-run based on the formula type handleCell(cell.getCachedFormulaResultType(), cell); } else { ar.add(""); } } public void handleSheet(Sheet sheet) { for (Row row : sheet) { for (Cell cell : row) { handleCell(cell.getCellType(), cell); } } } 

Note that iterators provide only the cells that are defined in the file, so there will be spaces if the cells have never been used. If you need every cell, including missing ones, see Iterating vs Fetching docs

Answer 2

With Cell.getCachedFormulaResultType we get the value of the cached formula; therefore, we must evaluate the formula to get a new value

 public static void main(String args[]) { FileInputStream inp = null; try { inp = new FileInputStream("E:\\Hasangi\\tets\\test\\book.xls"); HSSFWorkbook workbook = new HSSFWorkbook(inp); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); HSSFSheet sheet = workbook.getSheetAt(0); Row row = sheet.getRow(2); Cell cell = row.getCell(0); cell.setCellValue(1235487845); handleSheet(sheet, evaluator); FileOutputStream fileOut = new FileOutputStream("E:\\Hasangi\\tets\\test\\book.xls"); workbook.write(fileOut); fileOut.close(); } catch (IOException ex) { Logger.getLogger(xlreader.class.getName()).log(Level.SEVERE, null, ex); } } private static void handleCell(int type, Cell cell, FormulaEvaluator evaluator) { if (type == HSSFCell.CELL_TYPE_STRING) { System.out.println(cell.getStringCellValue()); } else if (type == HSSFCell.CELL_TYPE_NUMERIC) { System.out.println(cell.getNumericCellValue()); } else if (type == HSSFCell.CELL_TYPE_BOOLEAN) { System.out.println(cell.getBooleanCellValue()); } else if (type == HSSFCell.CELL_TYPE_FORMULA) { // Re-run based on the formula type evaluator.evaluateFormulaCell(cell); handleCell(cell.getCachedFormulaResultType(), cell, evaluator); } else { System.out.println(""); } } public static void handleSheet(Sheet sheet,FormulaEvaluator evaluator) { for (Row row : sheet) { for (Cell cell : row) { handleCell(cell.getCellType(), cell,evaluator); } } } 

Answer 3

Here is my function:

 public void getValueOfFormulaCell() throws IOException { FileInputStream xlsfile = new FileInputStream(new File("D:\\Desktop\\Temp\\marks.xls")); HSSFWorkbook objWorkbook = new HSSFWorkbook(xlsfile); Sheet sheet = objWorkbook.getSheetAt(0); FormulaEvaluator evaluator = objWorkbook.getCreationHelper().createFormulaEvaluator(); // suppose your formula is in B4 CellReference cellReference = new CellReference("B4"); Row row = sheet.getRow(cellReference.getRow()); Cell cell = row.getCell(cellReference.getCol()); CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: System.out.println(cellValue.getBooleanValue()); break; case Cell.CELL_TYPE_NUMERIC: System.out.println(cellValue.getNumberValue()); break; case Cell.CELL_TYPE_STRING: System.out.println(cellValue.getStringValue()); break; case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_ERROR: break; // CELL_TYPE_FORMULA will never happen case Cell.CELL_TYPE_FORMULA: break; } }