Bug 53474

Summary: How to validate and format a date value
Product: POI Reporter: Jeevraj <jeevraj>
Component: XSSFAssignee: POI Developers List <dev>
Status: RESOLVED INVALID    
Severity: major    
Priority: P2    
Version: 3.6-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   

Description Jeevraj 2012-06-27 10:57:42 UTC

    
Comment 1 Jeevraj 2012-06-27 11:00:59 UTC
I have a date value (10/11/2012) in my excel sheet in format (mm/dd/yyyy i.e. US format). I have to validate this date to confirm it is a valid date. How do I do that?
Comment 2 Yegor Kozlov 2012-06-27 11:18:53 UTC
POI issue tracker is not a place to ask usage questions, please ask on the @poi-user mailing list.

The answer depends on how you store date values in Excel: as date or as string.
If the value is stored as a date then use cell.getDateCellValue() to get value of  cell as a java.util.Date. In this case there is nothing to validate.

If the vaue is string then it is up to you how to check: you can fetch the string value and check it against a regex or write a validator yourself.

the code might look as follows:

        switch(cell.getCellType()){
            case Cell.CELL_TYPE_NUMERIC:
                date = cell.getDateCellValue();
                break;
            case Cell.CELL_TYPE_STRING:
                String sval = cell.getStringCellValue();
                if(sval.matches("\\d\\d/\\d\\d/\\d\\d\\d\\d")){  // dd/mm/yyyy
                    // parse as a date
                }
        }
 
note that internally Excel stores dates as numbers, that is why the first case is Cell.CELL_TYPE_NUMERIC'

Yegor