Read integer from numeric cell using Apache POI in java

Question

I have an application which reads xls sheet using apache poi. When the cell has numeric value, i read it by row.getCell(i).getNumericValue(). But it returns floating point digit. like if the cell value is 1, it returns 1.0. Can i convert it to int ? Any helpwould be appreciated. I tried Integer.parseInt(value)- but it throws NumberFormat exception.Any help is appreciated.Here is the pseudo code:

FileInputStream file = new FileInputStream(new File("C:\\test.xls"));
HSSFWorkbook workbook = new HSSFWorkbook(file);
HSSFSheet sheet = workbook.getSheetAt(0);
Iterator<Row> rowIterator = sheet.iterator();
while(rowIterator.hasNext()) {
    Row row = rowIterator.next();
    Iterator<Cell> cellIterator = row.cellIterator();
    while(cellIterator.hasNext()) {
        Cell cell = cellIterator.next();
        switch(cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                String value= String.valueOf(cell.getNumericCellValue());
                int intVal = Integer.parseInt(value)-->>throws Exception

Bui

Got it, fetch the value as double as conert to int :Double val = cell.getNumericCellValue(); val.intValue will give the answer – Bui

Here's a simple solution: double dval = row.getCell(cellCounter, XSSFRow.CREATE_NULL_AS_BLANK ).getNumericCellValue(); System.out.println(new Integer(new Double(dval).intValue()).toString()); – Onagraceous

Answer 1

You can read int value as string apache poi using simple steps

First count rows in sheets using below method

private  int getRowCount(Sheet currentSheet) {
        int rowCount = 0;
        Iterator<Row> rowIterator = currentSheet.iterator();

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

            if(row == null || row.getCell(0) == null || row.getCell(0).getStringCellValue().trim().equals("") || row.getCell(0).toString().trim().equals("") 
                    || row.getCell(0).getCellType()==Cell.CELL_TYPE_BLANK){
                break;
            }
            else
                rowCount=rowCount + 1;
        }       
        return rowCount;
    }

Then use below code in your method

    Workbook workbook = WorkbookFactory.create(new File("c:/test.xls");
    Sheet marksSheet = (Sheet) workbook.getSheet("Sheet1");
            int zoneLastCount = 0;
            if(marksSheet !=null ) {
                zoneLastCount = getRowCount(marksSheet);
            }
    int zone = zoneLastCount-1;
    int column=1

    for(int i = 0; i < zone; i++) {         
        Row currentrow = marksSheet.getRow(i);
        double year = Double.parseDouble(currentrow.getCell(columnno).toString());
        int year1 = (int)year;
        String str = String.valueOf(year1);
    }

Sekyere

This code will fail for numeric cells, as you're trying to read the string value without checking/ensuring that your cells are numeric – Postscript

now its working , your cell value general also its working no need to check – Sekyere

Answer 2

Numbers in Excel are (except for a few edge cases) stored as floating point numbers. Floating point numbers in Java, when formatted as a string, are printed with a trailing decimal point, as you're seeing

Assuming what you really wanted was "give me a string that looks like what Excel shows for this cell", then do not call cell.toString(). This will not give you what you want in most cases

Instead, you need to use the DataFormatter class, which provides methods which read the Excel format rules applied to a cell, then re-creates (as best it can) those in Java

Your code should be:

Workbook wb = WorkbookFactory.create(new File("c:/test.xls");
DataFormatter fmt = new DataFormatter();
Sheet sheet = wb.getSheetAt(0);
for (Row row : sheet) {
     Cell cell = row.getcell(0, Row.RETURN_BLANK_AS_NULL);
     if(cell!=null) {
          String value = fmt.formatCellValue(cell);
          if (! value.trim().isEmpty()) {
             System.out.println("Cell as string is " + value);
          }
     }
 }

You may notice I've also fixed a bunch of other stuff as well....!

Postscript

Very clean approach. Thanks. – Noyade

Answer 3

  // to extract the exact numerical value either integer/double
  DataFormatter fmt = new DataFormatter();
  Iterator<Cell> cellIterator = row.cellIterator();
  while (cellIterator.hasNext()) {
    Cell cell = cellIterator.next();
    switch (cell.getCellType()) {
      case NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
          Date date = cell.getDateCellValue();
          DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
          //DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
          fetchedRow.add(dateFormat.format(date));
        } else {
          fetchedRow.add(fmt.formatCellValue(cell));
        }
        rowEmpty = false;
        break;
      case STRING:
        fetchedRow.add(cell.toString());
        rowEmpty = false;
        break;
      case BOOLEAN:
        fetchedRow.add(cell.toString());
        rowEmpty = false;
        break;
      case FORMULA:
        fetchedRow.add(Double.toString(cell.getNumericCellValue()));
        rowEmpty = false;
        break;
      case BLANK:
        fetchedRow.add("");
        break;
    }
  }
  if (!rowEmpty) {
    allRows.add(fetchedRow.toArray(new String[0]));
    count++;
  }
  if (count >= limit) {
    break;
  }
}
return allRows;

}

eaxmple to read limitted no.of rows on and use DataFormatter to give exact numeric value either integer/ double value. This will work.

Meda

Answer 4

Someone might find this hack useful:

cell.setCellType(CellType.STRING);
int value = Integer.parseInt(cell.getStringCellValue());

Remember you alter the cell type here, so be sure this does not cause any side effect. In a single-threaded app you can just read the type before and restore it after.

Touchandgo

setCellType is deprecated in new versions. – Mold

Answer 5

Try doing the following (to obtain a long):

long value = (long) currentCell.getNumericValue();

Practicable

Answer 6

You can just type cast float to int like :

String value = String.valueOf((int)cell.getNumericCellValue());

Number

Answer 7

Cell cell = row.getCell(cellCpt);

String cellContent = "";
if (cell != null) {
    cell.setCellType(CellType.STRING);
    cellContent = fmt.formatCellValue(cell);
}

Sandrocottus

Answer 8

Below implementation worked for me:

private Object getValue(Cell cell) {
    switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            return cell.getStringCellValue();
        case Cell.CELL_TYPE_NUMERIC:
            return String.valueOf((int) cell.getNumericCellValue());
        case Cell.CELL_TYPE_BOOLEAN:
            return cell.getBooleanCellValue();
        case Cell.CELL_TYPE_ERROR:
            return cell.getErrorCellValue();
        case Cell.CELL_TYPE_FORMULA:
            return cell.getCellFormula();
        case Cell.CELL_TYPE_BLANK:
            return null;
    }
    return null;
}

Pollaiuolo