使用 poi api 从电子表格中读取时间值 [英] Reading time values from spreadsheet using poi api

问题描述

我正在尝试从电子表格中读取日期列和时间列.我可以从工作表中退出日期列，但不能退出时间列.

例如，我的工作表将包含以下形式的行:

日期时间

11/2/2012 12:15:01

我有以下代码来获取日期列:

while(cellIterator.hasNext()) {HSSFCell 单元格 = (HSSFCell)cellIterator.next();开关(cell.getCellType()){案例 HSSFCell.CELL_TYPE_NUMERIC:HSSFCellStyle 样式 = cell.getCellStyle();if (HSSFDateUtil.isCellDateFormatted(cell)){d = (Date)getDateValue(cell);SimpleDateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy");System.out.println(dateFormat.format(d));}}}受保护的静态对象 getDateValue(HSSFCell cellDate){double numericDateValue = cellDate.getNumericCellValue();日期日期 = HSSFDateUtil.getJavaDate(numericDateValue);归期;}

如你所见，我使用

<块引用>

HSSFDateUtil.isCellDateFormatted(cell)

检查单元格中是否有日期值.我想知道是否可以使用任何函数检查单元格是否具有时间值.

Excel 工作表来自外部来源.因此，我将无法对其格式进行任何修改.

现在，我得到了日期列的正确日期值.但是，对于时间列，我得到 <块引用>

12/31/1899

作为所有行的结果

解决方案

Excel 中日期和时间的快速入门.出于在创建文件格式时很有意义的原因，但现在看起来很混乱，Excel 没有日期或时间或日期时间类型.相反，它拥有的是数字和特殊的格式规则.

怎么看?在 Excel 单元格中键入 10.5，然后将其格式化为日期 + 时间.您将在 1900 年 1 月的某个日期获得正午.

因此，当您询问 POI 单元格是否为日期格式时，没有简单的标志/类型可供检查.相反，POI 所能做的就是读取应用于单元格的格式字符串，并查看它是否看起来像一个可疑的日期.

奖励标记 - 尝试存储 1900 年 2 月 28 日，然后添加一个 - Excel 忠实地再现了 1900 年闰年左右的 Lotus 123 错误...

作为一般规则，DateUtil.isCellDateFormatted(cell) 将为日期单元格、时间单元格和日期时间单元格返回 true.但是，有时您会得到一种非常奇怪的格式，Excel 知道它是日期或时间，但 POI 无法识别为一种格式.对于那些，您仍然可以要求将单元格值作为日期，POI 会对其进行转换.

因此，在您的情况下，如果 POI 说它是日期格式，请获取日期形式的值.现在，查看格式字符串.如果是日期查找，请格式化为日期.查找日期和时间?格式为您的首选日期 + 时间.只有时间?格式化为时间?时区?你一定在笑...

哦，只要您不使用 1900 年左右的真实日期，您基本上可以只说 DateUtil.isCellDateFormatted(cell) + value <100 -> 可能只是一段时间(如果是经过的时间，则 >1).如果您的号码是 int，则它可能只是一个日期(但可能是午夜).如果它是非整数(ish - 记住 excel 使用浮点数)它可能有一个时间组件.

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

解决方案

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.

这篇关于使用 poi api 从电子表格中读取时间值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！