How to correctly convert into a Date object this "strange" cell value retrieve parsing an Excel using Apache POI?

Question

I am going crazy trying to parse an Excel from Java using Apache POI and I ami finding the following problem parsing a specific column containin dates.

This is the column into my Excel file:

As you can see this column contains date fields but some cells of this column contains number values some other celles contains text values (I can see it using the TYPE() Excel function). I really don't know how it is possible because all the cell contains date fields. Some idea?

Anyway in my code I am trying to handle this strange situation in this way:

if(currentRow.getCell(20) != null && !currentRow.getCell(20).toString().trim().isEmpty()) {
    
    if(currentRow.getCell(20).getCellType().toString().equals("STRING")) {
        System.out.println("Data sottoscrizione contratto è STRING: " + currentRow.getCell(20).toString());
        
    }
    else if(currentRow.getCell(20).getCellType().toString().equals("NUMERIC")) {
        System.out.println("Data sottoscrizione contratto è NUMERIC");
        String dateAsString = currentRow.getCell(20).toString();
        System.out.println("DATA SOTTOSCRIZIONE CONTRATTO: " + currentRow.getCell(20).toString());
        
        
    }
}

In this way I can handle both the case trying to convert to a date.

And here my problem. When it found an Excel numeric value in enter into the if NUMERIC case

and printing the cell value by:

System.out.println("DATA SOTTOSCRIZIONE CONTRATTO: " + currentRow.getCell(20).toString());

I obtain printed the value 16-ott-2017 related to the date value 16/10/2017

And here some doubts: Why am I obtaining in this format instead something like 16/10/2017.

16-ott-2017 should be the italian formattation of the date. How can I convert it into a propper Date object?

Answer 1

Buon giorno!

You are currently using the toString() method of the cell, which will not be very accurate in returning numeric values or even dates. It might work sometimes, but it won't do always.

Use the methods that get you a real value, like Cell.getNumericCellValue(), Cell.getDateCellValue() (outdated because it returns a java.util.Date) or Cell.getLocalDateTimeCellValue(). If your cell just contains text, like "16-ott-2020", use the getStringCellValue() and convert the value returned to a LocalDate (or LocalDateTime depends on if time of day matters for you).

Here's an example of the conversion (to a LocalDate):

public static void main(String[] args) {
    // assuming you alread received the value as String
    String cellStringValue = "16-ott-2020";
    // provice a formatter that can parse Italian month names (or days of week)
    DateTimeFormatter dtf = DateTimeFormatter.ofPattern("dd-MMM-uuuu", Locale.ITALIAN);
    // parse the String to a LocalDate
    LocalDate sediciOttobreDuemilaEVenti = LocalDate.parse(cellStringValue, dtf);
    // and print its default value
    System.out.println(sediciOttobreDuemilaEVenti);
    // alternatively use the same formatter for output
    System.out.println(sediciOttobreDuemilaEVenti.format(dtf));
}

The output of that code is

2020-10-16
16-ott-2020

Answer 2

case NUMERIC:
                if(Cell.getLocalDateTimeCellValue()!=null) {
                    LocalDateTime actualdate = Cell.getLocalDateTimeCellValue();
                    String formattedDate = actualdate.format(DateTimeFormatter.ofPattern("MM/dd/yyyy"));
                    System.out.println(formattedDate);

                }
                break;

Answer 3

switch (Cell.getCellType()) {
            case STRING: 
                 System.out.print(Cell.getStringCellValue());
                 break;
            case NUMERIC:
                DataFormatter df = new DataFormatter();
                String CellValue = df.formatCellValue(Cell);
                System.out.println(CellValue);
                break;
            case BOOLEAN: 
                System.out.print(Cell.getBooleanCellValue());
                break;          
            
            case BLANK:
                System.out.println("");
            }   
            System.out.print(" | ");
        }