Guide how date cell is read by Apache poi

Question

I'm importing an excel file with many rows. Some columns are dates and i have to read them as such but the format of the cell is not correctly read by Apache poi. Indeed the dates have to be dd/MM/YYYY but when i have 04/13/2017 the Apache poi library thinks it is in the format MM/dd/YYYY, and reads the date consequently.

How can i tell apache poi to read dates in the right format?

Below there is an example of my code:

                           case Cell.CELL_TYPE_NUMERIC:
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    Date d = cell.getDateCellValue());
                                    System.out.println(myWorkBook.getSheetName(i) + "  " + cell.getNumericCellValue() + "\t");
}

you do realize that 04/13/2017 is not a valid date int he format dd/MM/yyyy? — XtremeBaumer
yes that's the problem, i have to trow an exception but getDateCellValue reads it as a valid date in MM/dd/YYYY. So how can i distinguish between valid and not valid input? — softwareplay
another approach you can try is to add a new column with a formula which converts the date to a string and then read the string and check if it matches the format — XtremeBaumer
reading as a string i get just a number (like 12345), not the formatted date... — softwareplay

softwareplay softwareplay · Accepted Answer · 2017-06-06T15:11:33

The right way is using getDataFormatString as it is done in this answer Validating the excel date for MM/dd/yyyy format But the builtin formats are limited.

If the limitation of the built-in formats bothers you, casting the value as a string is done through Data formatter

DataFormatter formatter = new DataFormatter();
String formattedValue = formatter.formatCellValue(cell);

The validation of the date field is already performed from the Excel file, and the date is read as a string if it is not in the right format.

Guide how date cell is read by Apache poi

1 Answers