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

PATRY Guillaume · Accepted Answer · 2011-04-07T13:11:13.110

44

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

edited Apr 07 '11 at 13:11

answered Apr 07 '11 at 09:11

PATRY Guillaume

4,287
1
32
41

1

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 – posdef Apr 07 '11 at 09:24
@posdef : 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. – PATRY Guillaume Apr 07 '11 at 09:38
@PATRY: you have a good point... I was pointing it out just to make sure there are no misunderstandings. – posdef Apr 07 '11 at 09:45
@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' ? – Ravi Parmar Apr 07 '11 at 09:58
1

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)... – PATRY Guillaume Apr 07 '11 at 10:29
plz look at my code . I have added code snippest in my question – Ravi Parmar Apr 07 '11 at 11:26
@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. – Steve Blackwell Aug 19 '11 at 16:35
You can use WorkbookFactory.create(fis); so you don't have to create HSSFWorkbook or XSSFWorkbook explicitely. – YamYamm Apr 05 '17 at 08:43
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 ? – M. Ozn Oct 30 '17 at 14:13
How to access values as we do in Pandas data frame? – Vraj Kotwala Jun 23 '20 at 08:58

score 12 · Answer 2 · answered Apr 07 '11 at 09:07

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;

}

Get Cell Value from Excel Sheet with Apache Poi

2 Answers2

Linked

Related