Reading Excel using POI in java

Question

I have an excel file which has a couple of String datatype columns, one numeric column and one date column. Am using Apache POI to read the file. Below is how am handling the datatypes

Cell cell = sheet.getRow(i).getCell(j);

                if(cell!=null){
                    switch(cell.getCellType()){
                    case Cell.CELL_TYPE_STRING: 
                        cellValue = cell.getStringCellValue();
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        DateFormat df = new SimpleDateFormat("MM/dd/yyyy");
                        Date cellDate = cell.getDateCellValue();
                        cellValue = df.format(cellDate);
                        break;                          
                    case Cell.CELL_TYPE_BLANK:
                        break;
                    default :
                    }                       
                }

This works fine with String and date datatypes. But for numeric, it's converting the value to a date. I know it's because the handling has issues. Can you please advise on how to accommodate handling of both numeric and date datatypes?

Thanks, Sam.

Why not just use DataFormatter to turn the Cell into a String, and have it handle the complexity for you? — Gagravarr
– Gagravarr, Commented Dec 8, 2015 at 21:33

AdamSkywalker · Accepted Answer · 2015-12-08 21:03:07Z

If you know what data types belong to each column, you don't even have to check cell type every time:

switch (columnIndex) {
    case 0:
    case 1:
    case 2: { 
       cellValue = cell.getStringCellValue();
       break; 
    }
    case 3: {
       Date cellDate = cell.getDateCellValue();
       // ...
       break;
    } 
    case 4: {
       cellValue = cell.getNumericCellValue();
       break;
    }    
}

If your column can contain both dates and numbers, you can try this

import org.apache.poi.ss.usermodel.DateUtil;

case Cell.CELL_TYPE_NUMERIC:
   if (DateUtil.isCellDateFormatted(cell)) {
      // your code for date handling
   } else {
      cellValue = cell.getNumericCellValue();
   }

Collectives™ on Stack Overflow

Reading Excel using POI in java

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related