java 使用 poi api 从电子表格中读取时间值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15710888/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Reading time values from spreadsheet using poi api
提问by CuriousCoder
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
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)
HSSFDateUtil.isCellDateFormatted(单元格)
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.
Excel 表格来自外部来源。因此，我将无法对其格式进行任何修改。
Right now, i get the correct date values for date columns. But, for time columns, i am getting
现在，我得到了日期列的正确日期值。但是，对于时间列，我得到
12/31/1899
12/31/1899
as result for all rows
作为所有行的结果
采纳答案by Gagravarr
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.
Excel 中日期和时间的快速入门。出于在创建文件格式时很有意义的原因，但现在看起来很混乱，Excel 没有日期或时间或日期时间类型。相反，它拥有的是数字和特殊的格式规则。
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.
这个怎么看？在 Excel 单元格中键入 10.5，然后将其格式化为日期 + 时间。您将在 1900 年 1 月的某个日期获得正午。
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.
因此，当您询问 POI 单元格是否为日期格式时，没有简单的标志/类型可供检查。相反，POI 所能做的就是读取应用于单元格的格式字符串，并查看它是否看起来像一个日期。
Bonus marks - try to store 28th Feb 1900, then add one - Excel faithfully reproduces a Lotus 123 bug around 1900 leap years...
奖励标记 - 尝试存储 1900 年 2 月 28 日，然后添加一个 - Excel 忠实地再现了 1900 年闰年左右的 Lotus 123 错误...
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.
作为一般规则，DateUtil.isCellDateFormatted(cell)对于日期单元格、时间单元格和日期时间单元格将返回 true。但是，有时您会得到一种非常奇怪的格式，Excel 知道它是日期或时间，但 POI 无法识别为一种格式。对于那些，您仍然可以要求将单元格值作为日期，POI 会对其进行转换。
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...
因此，在您的情况下，如果 POI 说它是日期格式，请将该值作为日期获取。现在，查看格式字符串。如果是日期查找，请格式化为日期。查找日期和时间？格式为您的首选日期 + 时间。只有时间？格式化为时间？时区？你一定会笑...
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.
哦，只要您不使用 1900 年左右的真实日期，您就可以在很大程度上说DateUtil.isCellDateFormatted(cell)+ value < 100 -> 可能只是一个时间（如果是经过的时间，则 >1）。如果您的号码是整数，则它可能只是一个日期（但可能是午夜）。如果它是非整数（ish - 记住 excel 使用浮点数）它可能有一个时间组件。
回答by Federico Destefanis
This is my rough attempt for getting "Date-only", "Time-Only" or "Date-Time" values of POI Cells
这是我获取 POI 单元格的“仅日期”、“仅时间”或“日期-时间”值的粗略尝试
...
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();
}
