如何读取与Apache POI有日期的Excel单元格?

I'm using Apache POI 3.6, I want to read an excel file which has a date like this 8/23/1991.

我使用Apache POI 3.6，我想读一个excel文件，它有一个日期，比如这个8/23/1991。

 switch (cell.getCellType()) {

   ...
   ...

   case HSSFCell.CELL_TYPE_NUMERIC:
     value = "NUMERIC value=" + cell.getNumericCellValue();
     break;

   ...

 }

But it takes the numeric value type and returns the value like this 33473.0.

但是它使用数值类型并返回像33473.0这样的值。

I've tried to use Numeric Cell Type although with no luck.

我试过使用数字单元格类型，尽管运气不好。

dbltemp=row.getCell(c, Row.CREATE_NULL_AS_BLANK).getNumericCellValue();

if (c == 6 || c == 9) {
    strTemp= new String(dbltemp.toString().trim());

    long tempDate = Long.parseLong(strTemp);
    Date date = new Date(tempDate);

    strVal = date.toString();
}

How can I fix my problem?

我如何解决我的问题?

4 个解决方案

#1

If you know which cell i.e. column position say 0 in each row is going to be a date, you can go for row.getCell(0).getDateCellValue() directly.
http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFCell.html#getDateCellValue()

如果您知道每行中哪个单元格(即列位置为0)是一个日期，您可以直接使用row. getcell (0). getdatecellvalue()。http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFCell.html getDateCellValue()

UPDATE: Here is an example - you can apply this in your switch case code above. I am checking and printing the Numeric as well as Date value. In this case the first column in my sheet has dates, hence I use row.getCell(0).

更新:这里有一个例子——您可以在上面的开关用例代码中应用它。我正在检查和打印数值和日期值。在这种情况下，我的表中的第一列有日期，因此我使用row.getCell(0)。

You can use the if (HSSFDateUtil.isCellDateFormatted .. code block directly in your switch case.

您可以使用if (HSSFDateUtil)。isCellDateFormatted . .代码块直接在你的开关情况。

if (row.getCell(0).getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
    System.out.println ("Row No.: " + row.getRowNum ()+ " " +
        row.getCell(0).getNumericCellValue());

    if (HSSFDateUtil.isCellDateFormatted(row.getCell(0))) {
        System.out.println ("Row No.: " + row.getRowNum ()+ " " + 
            row.getCell(0).getDateCellValue());
    }
}

The output is

输出是

Row No.: 0 39281.0
Row No.: 0 Wed Jul 18 00:00:00 IST 2007
Row No.: 1 39491.0
Row No.: 1 Wed Feb 13 00:00:00 IST 2008
Row No.: 2 39311.0
Row No.: 2 Fri Aug 17 00:00:00 IST 2007

#2

Yes, I understood your problem. If is difficult to identify cell has Numeric or Data value.

是的，我理解你的问题。如果很难识别单元格有数值或数据值。

If you want data in format that shows in Excel, you just need to format cell using DataFormatter class.

如果想要以Excel中显示的格式显示数据，只需使用DataFormatter类来格式化单元格。

DataFormatter dataFormatter = new DataFormatter();
String cellStringValue = dataFormatter.formatCellValue(row.getCell(0));
System.out.println ("Is shows data as show in Excel file" + cellStringValue);  // Here it automcatically format data based on that cell format.
// No need for extra efforts

#3

You need the DateUtils: see this article for details.

您需要DateUtils:详细信息请参阅本文。

Or, better yet, use Andy Khan's JExcel instead of POI.

或者，更好的是，使用安迪汗的JExcel而不是POI。

#4

You can use CellDateFormatter to fetch the Date in the same format as in excel cell. See the following code:

您可以使用CellDateFormatter以与excel计算单元相同的格式获取日期。看下面的代码:

CellValue cv = formulaEv.evaluate(cell);
double dv = cv.getNumberValue();
if (HSSFDateUtil.isCellDateFormatted(cell)) {
    Date date = HSSFDateUtil.getJavaDate(dv);

    String df = cell.getCellStyle().getDataFormatString();

    strValue = new CellDateFormatter(df).format(date); 
}

#1