Reading time values from spreadsheet using poi api

Question

I am trying to read a date column and a time column from a spreadsheet. I am able to retireve the date column from the sheet, but not the time column.

For example, my sheet will have rows of the form:

date time

11/2/2012 12:15:01

I have the following code to get the date column:

while(cellIterator.hasNext()) {
            HSSFCell cell = (HSSFCell)cellIterator.next();
            switch(cell.getCellType()){
                case HSSFCell.CELL_TYPE_NUMERIC:
                    HSSFCellStyle style = cell.getCellStyle();
                    if (HSSFDateUtil.isCellDateFormatted(cell))
                    {
                        d = (Date)getDateValue(cell);
                        SimpleDateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy");
                        System.out.println(dateFormat.format(d));

                    }


            }
        }  

protected static Object getDateValue(HSSFCell cellDate) 
{

         double numericDateValue = cellDate.getNumericCellValue();
         Date date = HSSFDateUtil.getJavaDate(numericDateValue);
         return date;

}

As you can see I use

HSSFDateUtil.isCellDateFormatted(cell)

to check if the cell has date value in it. I want to know if i can check if cell has time value using any function.

The excel sheet is coming from an external source. So,i will not be able to make any modifications to the format of it.

Right now, i get the correct date values for date columns. But, for time columns, i am getting

12/31/1899

as result for all rows

Solution 1

A quick primer on dates and times in Excel. For reasons that made a lot of sense back when the file format was created, but just seem confusing now, Excel doesn't have a Date or Time or DateTime type. Instead, what it has are numbers, and special formatting rules.

How to see this? Type 10.5 into an excel cell, then format it as date + time. You'll get midday on a date in January 1900.

So, when you ask POI if a cell is date formatted, there's no simple flag/type to check. Instead, all POI can do is read the formatting string applied to a cell, and look to see if it seems suspiciously like a date.

Bonus marks - try to store 28th Feb 1900, then add one - Excel faithfully reproduces a Lotus 123 bug around 1900 leap years...

As a general rule, DateUtil.isCellDateFormatted(cell) will return true for Date cells, Time cells, and Date Time cells. However, you can sometimes get a really odd format that Excel knows is a date or time, but POI can't spot as one. For those, you can still ask for the cell value as a date, and POI will convert it.

So, in your case, if POI says it's date formatted, get the value as a date. Now, look at the format string. If it's date looking, format as a date. Date and time looking? Format as your preferred date + time. Only time? Format as time? Timezones? You must be having a laugh...

Oh, and as long as you don't work with real dates from around 1900, you can largely just say DateUtil.isCellDateFormatted(cell) + value < 100 -> probably just a time (>1 if it's an elapsed time). If your number is an int, it probably is only a date (but it could be midnight). If it's non-int (ish - remember excel uses floating point numbers) it's probably got a time component.

Solution 2

This is my rough attempt for getting "Date-only", "Time-Only" or "Date-Time" values of POI Cells

    ...
private String getCellValueAsString(Cell poiCell){

    if (poiCell.getCellType()==Cell.CELL_TYPE_NUMERIC && DateUtil.isCellDateFormatted(poiCell)) {
        //get date
        Date date = poiCell.getDateCellValue();

        //set up formatters that will be used below
        SimpleDateFormat formatTime = new SimpleDateFormat("HH:mm:ss");
        SimpleDateFormat formatYearOnly = new SimpleDateFormat("yyyy");

        /*get date year.
        *"Time-only" values have date set to 31-Dec-1899 so if year is "1899"
        * you can assume it is a "time-only" value 
        */
        String dateStamp = formatYearOnly.format(date);

        if (dateStamp.equals("1899")){
            //Return "Time-only" value as String HH:mm:ss
            return formatTime.format(date);
        } else {
            //here you may have a date-only or date-time value

            //get time as String HH:mm:ss 
            String timeStamp =formatTime.format(date);

            if (timeStamp.equals("00:00:00")){
                //if time is 00:00:00 you can assume it is a date only value (but it could be midnight)
                //In this case I'm fine with the default Cell.toString method (returning dd-MMM-yyyy in case of a date value)
                return poiCell.toString();
            } else {
                //return date-time value as "dd-MMM-yyyy HH:mm:ss"
                return poiCell.toString()+" "+timeStamp;
            }
        }
    }

    //use the default Cell.toString method (returning "dd-MMM-yyyy" in case of a date value)
    return poiCell.toString();
}

Comments

[CuriousCoder]

Thank you very much for the detailed explanation. Solved my issue :)