Apache POI Time Cell

Question

I need to detect, whether cell format is Date, Time or Datetime.
My code is:

        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            if (DateUtil.isCellDateFormatted(cell)) {
                if (   ???   )
                    return "Time";
                else if (   ???   )
                    return "Date";
                else
                    return "Datetime";
            }
        }

I use ApachePOI 3.6

Answer 1

I don't see a built-in way to do this easily in POI. First, there doesn't seem to be a clear distinction between "Time", "Date", and "Datetime". The values are just stored as numbers and a format is applied to display it.

One thing you could do is get the style of the cell (HSSFCellStyle), then write your own method that reads either style.getDataFormatString() or style.getDataFormat() and tells you whether the format falls in the "Time", "Date", or "Datetime" category depending on how you define them. The second method returns an int so it might be easier to work with.

For example, the format highlighted below has a String data format of "[$-F400]h:mm:ss\ AM/PM" and an int value of 166.

I'm assuming this is what you mean by "Datetime" format, as it's one of Excel's built-in formats that displays both the date and the time.

The drawback of this approach is that I wouldn't expect it to work with cells that have custom date/time formats.