Get Cell Value from Excel Sheet with Apache Poi

Question

How to get cell value with poi in java ?

My code is look like this

String cellformula_total__percentage= "(1-E" + (rowIndex + 2) + "/" + "D" + (rowIndex + 2) + ")*100";
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellStyle(this.valueRightAlignStyleLightBlueBackground);
cell.setCellFormula("abs(" + cellformula_total__percentage + ")");

But if there is in this case how can i check that my cell value contain error value like #DIV/0! and how can i replace it with N/A

Myotonia

Answer 1

You have to use the FormulaEvaluator, as shown here. This will return a value that is either the value present in the cell or the result of the formula if the cell contains such a formula :

FileInputStream fis = new FileInputStream("/somepath/test.xls");
Workbook wb = new HSSFWorkbook(fis); //or new XSSFWorkbook("/somepath/test.xls")
Sheet sheet = wb.getSheetAt(0);
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

// suppose your formula is in B3
CellReference cellReference = new CellReference("B3"); 
Row row = sheet.getRow(cellReference.getRow());
Cell cell = row.getCell(cellReference.getCol()); 

if (cell!=null) {
    switch (evaluator.evaluateFormulaCell(cell)) {
        case Cell.CELL_TYPE_BOOLEAN:
            System.out.println(cell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            System.out.println(cell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            System.out.println(cell.getStringCellValue());
            break;
        case Cell.CELL_TYPE_BLANK:
            break;
        case Cell.CELL_TYPE_ERROR:
            System.out.println(cell.getErrorCellValue());
            break;

        // CELL_TYPE_FORMULA will never occur
        case Cell.CELL_TYPE_FORMULA: 
            break;
    }
}

if you need the exact contant (ie the formla if the cell contains a formula), then this is shown here.

Edit : Added a few example to help you.

first you get the cell (just an example)

Row row = sheet.getRow(rowIndex+2);    
Cell cell = row.getCell(1);   

If you just want to set the value into the cell using the formula (without knowing the result) :

 String formula ="ABS((1-E"+(rowIndex + 2)+"/D"+(rowIndex + 2)+")*100)";    
 cell.setCellFormula(formula);    
 cell.setCellStyle(this.valueRightAlignStyleLightBlueBackground);

if you want to change the message if there is an error in the cell, you have to change the formula to do so, something like

IF(ISERR(ABS((1-E3/D3)*100));"N/A"; ABS((1-E3/D3)*100))

(this formula check if the evaluation return an error and then display the string "N/A", or the evaluation if this is not an error).

if you want to get the value corresponding to the formula, then you have to use the evaluator.

Hope this help,
Guillaume

Bacitracin

As far as I know, you don't "have to" use FormulaEvaluator, if you don't have a formula in the cell :) If your cells contain text or numeric values you can get them by cell.getStringCellValue(); or cell.getNumericCellValue();. Your answer applies to situations where the cell contains a formula, there is no indication that's the case for the OP – Annals

@Annals : you're right, i don't "have to". However, if the cell does not contains a formula, the method "evaluateFormulaCell" still returns the value. I find it simpler to call this method than to perform special condition check, since i then always get the value of the cell. However, this is indeed void if you're sure that you don't have a formula. – Bacitracin

@PATRY: you have a good point... I was pointing it out just to make sure there are no misunderstandings. – Annals

@PARTY.. Thanks for your help But if there is a cell type of formula and after evaluation of that that cell formula it contain error value like DIV/0! error than how can i know that cell contain this error and how can i replace this with 'N/A' ? – Myotonia

in case of an error, the type of the cell is Cell.CELL_TYPE_ERROR. You can get the value of the error by the getErrorCellValue() method. affter that, it depends onn your needs : if you just need an standard error message ("N/A"), then just chek the nature of the content. If you need different error messages, the use a HashMap mapping an excel error message to your own (this is just an exemple)... – Bacitracin

plz look at my code . I have added code snippest in my question – Myotonia

@PATRY: In my experience (POI 3.7), evaluateFormulaCell() returns -1 if the cell is not a formula, so you still have to handle formula and non-formula cells slightly differently, i.e., getting your cell type from getCellType() instead. I edited your code for the extra check. – Dolorous

You can use WorkbookFactory.create(fis); so you don't have to create HSSFWorkbook or XSSFWorkbook explicitely. – Jocelynjocelyne

Six years later it's still the only answer that speaks to that. But I have two problems with this method: First of all the methods "evaluateFormulaCell()" and all the CELL_TYPEs are now deprecated and then this method is very slow to browse a fairly small file (a few minutes for 150 lines of 6 cells). Maybe someone have a better way to do it nowaday ? – Rout

How to access values as we do in Pandas data frame? – Jansson

Answer 2

May be by:-

    for(Row row : sheet) {          
        for(Cell cell : row) {              
            System.out.print(cell.getStringCellValue());

        }
    }       

For specific type of cell you can try:

switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
    cellValue = cell.getStringCellValue();
    break;

case Cell.CELL_TYPE_FORMULA:
    cellValue = cell.getCellFormula();
    break;

case Cell.CELL_TYPE_NUMERIC:
    if (DateUtil.isCellDateFormatted(cell)) {
        cellValue = cell.getDateCellValue().toString();
    } else {
        cellValue = Double.toString(cell.getNumericCellValue());
    }
    break;

case Cell.CELL_TYPE_BLANK:
    cellValue = "";
    break;

case Cell.CELL_TYPE_BOOLEAN:
    cellValue = Boolean.toString(cell.getBooleanCellValue());
    break;

}

Ullrich