Java POI - read date from Excel file

Question

I want to read the date (yyyy-MM-dd) in Java POI.

Cell cell = cellIterator.next();
cell.setCellType(Cell.CELL_TYPE_STRING);

switch(cell.getCellType()){
    case Cell.CELL_TYPE_STRING:
         if(DateUtil.isCellDateFormatted(cell)){  [ERROR HERE]
              System.out.print(cell.getDateCellValue() + "\t\t");
         }else{
              System.out.print(cell.getStringCellValue() + "\t\t");
         }
    case Cell.CELL_TYPE_NUMERIC:
        System.out.print(cell.getNumericCellValue() + "\t\t");
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        System.out.print(cell.getBooleanCellValue() + "\t\t");
        break;
}

However, I get the below error:

Exception in thread "main" java.lang.IllegalStateException: Cannot get a numeric value from a text cell
at org.apache.poi.xssf.usermodel.XSSFCell.typeMismatch(XSSFCell.java:882)
at org.apache.poi.xssf.usermodel.XSSFCell.getNumericCellValue(XSSFCell.java:220)
at org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(DateUtil.java:495)
at others.ReadDateTime.main(ReadDateTime.java:44)
Java Result: 1

How should I corrected it?

Answer 1

Dates cannot be stored in CELL_TYPE_STRING cell. You should store it in CELL_TYPE_NUMERIC cell. See here for details.

You also missed break keyword after first case. So if cell is Cell.CELL_TYPE_STRING then also

System.out.print(cell.getNumericCellValue() + "\t\t");

is called.

So it should be:

switch(cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        System.out.print(cell.getStringCellValue() + "\t\t");
        break;
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy");
            System.out.print(dateFormat.format(cell.getDateCellValue()) + "\t\t");
        } else {
            System.out.print(cell.getNumericCellValue() + "\t\t");
        }
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        System.out.print(cell.getBooleanCellValue() + "\t\t");
        break;
}

Answer 2

This is direct pick from Apache POI tutorial, you make like to visit and get more details.

switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    System.out.println(cell.getRichStringCellValue().getString());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        System.out.println(cell.getDateCellValue());
                    } else {
                        System.out.println(cell.getNumericCellValue());
                    }
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    System.out.println(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    System.out.println(cell.getCellFormula());
                    break;
                default:
                    System.out.println();
            }

Formatting date: This thread may answer your follow up question.