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

Answer 2

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

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

Answer 4

The above post works and it helped me to write following program that also works. Following code reads Excel file (.xlsx) that has formulas, errors, strings, numbers. It reads Excel file and coverts it to PIPE delimited string to be written in a Text file.

public String readExcelFile(String filepathname)
{
    String extractedText = null;
    try 
    {

        InputStream is = new FileInputStream(filepathname);
        XSSFWorkbook myExcelBook = new XSSFWorkbook(is);
        XSSFSheet myExcelSheet = myExcelBook.getSheet("VendorCentral");

        // Get iterator to all the rows in current sheet

        Iterator<Row> rowIterator = myExcelSheet.iterator();

        //Traversing through each row of xlsx file

        int rowNum=0;
        StringBuffer rowContents;

        StringBuffer sheetContents = new StringBuffer();

        XSSFFormulaEvaluator evaluator = myExcelBook.getCreationHelper().createFormulaEvaluator();

        while (rowIterator.hasNext())
        {
            Row row = rowIterator.next();
            rowNum++;

            rowContents = new StringBuffer();

            //For each row, iterate through each column
            Iterator<Cell> cellIterator = row.cellIterator();

            while (cellIterator.hasNext())
            {
                Cell cell = cellIterator.next();

                switch (cell.getCellType())
                {
                    case Cell.CELL_TYPE_STRING:
                        rowContents.append(cell.getStringCellValue() + DELIM_CHAR); 
                        break;

                    case Cell.CELL_TYPE_NUMERIC:
                        rowContents.append(cell.getNumericCellValue()  + DELIM_CHAR); 
                        break;

                    case Cell.CELL_TYPE_BOOLEAN:
                        rowContents.append(cell.getBooleanCellValue()  + DELIM_CHAR); 
                        break;

                    case Cell.CELL_TYPE_BLANK:
                        rowContents.append(""  + DELIM_CHAR); 
                        break;

                    case Cell.CELL_TYPE_ERROR:
                        rowContents.append("#Error"  + DELIM_CHAR); 
                        break;

                    case Cell.CELL_TYPE_FORMULA:
                        CellValue cellValue = evaluator.evaluate(cell );
                        System.out.println("cellValue.formatAsString: " + cellValue.formatAsString());
                        System.out.println("cellValue.getStringValue: " + cellValue.getStringValue()  );

                        rowContents.append(cellValue.formatAsString()  + DELIM_CHAR); 
                        break;

                    default:
                        rowContents.append(""  + DELIM_CHAR); 

                }

            }

            if (rowContents.length() > 0)
                rowContents.deleteCharAt(rowContents.length()-1);

            System.out.println("Row " + rowNum + ": " + rowContents.toString() );
            sheetContents.append("Row " + rowNum + ": " + rowContents.toString() + "\n" );
        }


        myExcelBook.close();
        if (sheetContents.length() > 0)
            sheetContents.deleteCharAt(sheetContents.length()-1);

        extractedText = sheetContents.toString();

    } catch (Exception e) 
    {
        e.printStackTrace();
    }
    return extractedText;
}