使用POI HSSF API从excel计算单元读取日期值

I'm using POI HSSF API for my excel manipulations in Java. I've a date value "8/1/2009" in one of my excel cell and while I try to read this value using HSSF API, it detects the cell type as Numeric and returns the 'Double' value of my date. See the sample code below:

我使用POI HSSF API来进行Java中的excel操作。我在我的一个excel计算单元中有一个日期值“8/1/2009”，当我尝试使用HSSF API读取这个值时，它会检测到cell类型为数值，并返回日期的“Double”值。参见下面的示例代码:

cell = row.getCell(); // date in the cell '8/1/2009'
switch (cell.getCellType()) {

case HSSFCell.CELL_TYPE_STRING:
    cellValue = cell.getRichStringCellValue().getString();
    break;
case HSSFCell.CELL_TYPE_NUMERIC:
    cellValue = new Double(cell.getNumericCellValue()).toString();
    break;
default:
}

Cell.getCellType() returns NUMERIC_TYPE and thus this code converts the date to double! :(

getcelltype()返回NUMERIC_TYPE，因此这段代码将日期转换为double!:(

Is there any way to read the date as it is in HSSF POI !?

有什么方法可以读出这个日期，因为它是在HSSF POI !?

5 个解决方案

#1

You could take a look at:

你可以看看:

HSSFDateUtil.isCellDateFormatted()

See the POI Horrible Spreadsheet Format API for more details on HSSFDateUtil:

有关HSSFDateUtil的更多细节，请参阅POI糟糕的电子表格格式API:

http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFDateUtil.html

That also provides some helper methods for returning Excel getExcelDate() and Java dates getJavaDate(). You need to be somewhat wary of different date formats though...

它还提供了一些帮助方法来返回Excel getExcelDate()和Java dates getJavaDate()。你需要对不同的日期格式保持警惕。

#2

If you want to reference the date in the same format in as in the Excel file, you should use the CellDateFormatter. Sample code:

如果要以与Excel文件相同的格式引用日期，应该使用CellDateFormatter。示例代码:

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

    String dateFmt = cell.getCellStyle().getDataFormatString();
    /* strValue = new SimpleDateFormat(dateFmt).format(date); - won't work as 
    Java fmt differs from Excel fmt. If Excel date format is mm/dd/yyyy, Java 
    will always be 00 for date since "m" is minutes of the hour.*/

    strValue = new CellDateFormatter(dateFmt).format(date); 
    // takes care of idiosyncrasies of Excel
}

#3

Excel treats dates and times as numbers... Jon said it better, so I won't echo him here...

Excel将日期和时间视为数字……琼恩说得更好，所以我不会在这里回应他……

However, sample code for what you've put in the question is at http://poi.apache.org/spreadsheet/quick-guide.html#CellContents

但是，您输入问题的示例代码是http://poi.apache.org/spreadsheet/quick-guide.html#CellContents

#4

If you using the POI 3.5 you can use the following

如果您使用POI 3.5，您可以使用以下内容

cell.getDateCellValue() method. This will work for excel 2007 as well.

cell.getDateCellValue()方法。这也适用于excel 2007。

#5

Since POI 3.15 beta3 some functions are deprecated. You can check data format and retrieve as Java Date.

由于POI 3.15 beta3，一些函数被废弃了。您可以检查数据格式并检索为Java日期。

SimpleDateFormat format = new SimpleDateFormat("");
String cellValue;
if(cell != null && cell.getCellTypeEnum() != CellType.STRING &&
    cell.getCellTypeEnum() == CellType.NUMERIC && DateUtil.isCellDateFormatted(cell)){

    cellValue = format.format(cell.getDateCellValue());
}

#1