How to read cell value from formula cell using apache poi

Question

I am trying to read all the data from a excel file, which also have some formula cell, but i have no idea that which cell is formula cell. how can i read all the values from the cells irrespective of the type of the cell.

My code looks like this

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 am getting the formula cell value as 0

Answer 1

The method you're looking for is Cell.getCachedFormulaResultType - for a formula cell that'll tell you the type of the formula result

You code can then be something like:

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 the iterators only give the cells that are defined in the file, so there will be gaps if the cells have never been used. If you need to get every cell including missing ones, see the Iterating vs Fetching docs