从s preadsheet使用POI空气污染指数的时间值 [英] Reading time values from spreadsheet using poi api

问题描述

我试图读取A S preadsheet日期列和时间列。我能够retireve从表日期列，但不是时间列。

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:

2012年11月2日12时15分01秒

11/2/2012 12:15:01

我有以下的code来获得日期栏：

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（单元）

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.

Excel工作表是从外部源的到来。所以，我将不能够进行任何修改它的格式。

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

1899年12月31日

12/31/1899

作为结果所有行

推荐答案

上的日期和时间在Excel中快速底漆。对于做了一个很大的意义回创建文件格式时，但似乎只是现在混乱的原因，Excel没有日期或时间或日期时间类型。相反，它有什么是数字和特殊格式规则。

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.

怎么看这一点？ 10.5输入到Excel单元格，然后将其格式化为日期+时间。你会得到一个日期午盘1900年1月

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.

所以，当你问POI如果单元格被格式化的日期，有没有简单的标志/类型检查。相反，所有的POI可以阅读应用于单元格的格式字符串，看看是否看起来很像一个日期。

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.

加分 - 尝试存储1900年2月28日，然后添加一个 - Excel中忠实再现1900年左右闰年莲花123错误...

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

作为一般规则， DateUtil.isCellDateFormatted（单元）将返回日期的细胞，细胞的时间和日期时间细胞真。不过，有时你可以得到的Excel知道是日期或时间一个非常奇怪的格式，但POI不能当场为一体。对于那些，你仍然可以要求单元格的值作为一个日期，POI将其转换。

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.

所以，你的情况，如果POI说，这是迄今为止格式化，获得的价值为日期。现在，看看格式字符串。如果它的日期来看，格式的日期。日期和时间去寻找？格式为您的preferred日期+时间。只有时间？格式随着时间的？时区？您必须有笑...

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...

哦，只要你不与真正的历史可以追溯到1900年左右工作，你可以在很大程度上只是说 DateUtil.isCellDateFormatted（单元） +价值＆LT; 100 - >可能只是一个时间（> 1，如果它是一个经过的时间）。如果您的号码是一个int，它可能只是一个日期（但它可能是午夜）。如果它是不-INT。（ISH - 记住Excel使用浮点数），它可能有一个时间成分

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.

这篇关于从s preadsheet使用POI空气污染指数的时间值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！