How to read cell value from formula cell using apa

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

Answer 2

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;
    }               
    }

Answer 3

With Cell.getCachedFormulaResultType we are getting the cached formula value; therefore we have to evaluate the formula to get the 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);
        }
    }
}