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

Answer 1

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....!