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

Got it, fetch the value as double as conert to int :Double val = cell.getNumericCellValue(); val.intValue will give the answer — Parameswar
– Parameswar, Commented Sep 10, 2014 at 17:39
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()); — Mike Preston
– Mike Preston, Commented Aug 19, 2016 at 14:22

Gagravarr · Accepted Answer · 2014-09-10 21:26:58Z

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

Mallikharjuna · Accepted Answer · 2019-10-14 09:10:06Z

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

no id · Accepted Answer · 2017-05-24 08:20:37Z

1

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.

answered May 24, 2017 at 8:20

no id

1,7223 gold badges25 silver badges37 bronze badges

1 Comment

Pranjal Choladhara Over a year ago

setCellType is deprecated in new versions.

Mohamed Maalej · Accepted Answer · 2019-02-04 13:13:05Z

1

Try doing the following (to obtain a long):

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

answered Feb 4, 2019 at 13:13

Mohamed Maalej

112 bronze badges

Comments

marc_s · Accepted Answer · 2017-12-28 16:58:24Z

0

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

edited Dec 28, 2017 at 16:58

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Aug 5, 2016 at 13:21

VISHWANATH N P

3041 gold badge7 silver badges11 bronze badges

2 Comments

Gagravarr Over a year ago

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

VISHWANATH N P Over a year ago

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

CHW · Accepted Answer · 2017-08-24 08:28:00Z

0

You can just type cast float to int like :

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

edited Aug 24, 2017 at 8:28

CHW

2,6934 gold badges32 silver badges34 bronze badges

answered Aug 24, 2017 at 8:05

Sani Shrestha

11 bronze badge

Comments

Mehdiway · Accepted Answer · 2018-11-16 03:04:19Z

0

Cell cell = row.getCell(cellCpt);

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

answered Nov 16, 2018 at 3:04

Mehdiway

10.7k8 gold badges38 silver badges68 bronze badges

Comments

Miss Chanandler Bong · Accepted Answer · 2020-04-20 20:38:33Z

0

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

edited Apr 20, 2020 at 20:38

Miss Chanandler Bong

4,27811 gold badges30 silver badges40 bronze badges

answered Apr 20, 2020 at 15:03

M T

13 bronze badges

Collectives™ on Stack Overflow

Read integer from numeric cell using Apache POI in java

8 Answers 8

Comments

Comments

1 Comment

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

Comments

Comments

1 Comment

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related