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 "Datetim开发者_如何学运维e";
}
}
I use ApachePOI 3.6
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.
It seems to me that the only good thing we can do here is - to see if time part has value other than zero.
I have tried to use style.getDataFormatString()
and style.getDataFormat()
to detect this, but they behave very wrongly for my test cases. Here's my code :
private boolean dateIncludesTime() // this method only tries to detect does time part exist, but it is not sure it will succeeed
{
Date javaDate= (Date)getValue();
if (javaDate.getHours() > 0 || javaDate.getMinutes() > 0)
return true;
int formatID = apacheCell.getCellStyle().getDataFormat();
if(formatID >= 18 && formatID <=22) // https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/BuiltinFormats.html
return true;
dateFormat = apacheCell.getCellStyle().getDataFormatString();
if(dateFormat.toLowerCase().contains("h")) // format mentions hours
return true;
return false;
}
Here's what I get for some test cases :
input 03/21/2015 21:30 getDataFormatString() returns MM/DD/YY formatID = 165
input 03/21/2016 getDataFormatString() returns MM/DD/YY formatID = 165
input 2017-04-21 10:20 getDataFormatString() returns YYYY-MM-DD formatID = 166
input 2017-05-21 getDataFormatString() returns YYYY-MM-DD formatID = 166
精彩评论