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:
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! :(
Is there any way to read the date as it is in HSSF POI !?
You could take a look at:
HSSFDateUtil.isCellDateFormatted()
See the POI Horrible Spreadsheet Format API for more details on HSSFDateUtil:
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...
If you want to reference the date in the same format in as in the Excel file, you should use the CellDateFormatter. Sample code:
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
}
Excel treats dates and times as numbers... Jon said it better, so I won't echo him here...
However, sample code for what you've put in the question is at http://poi.apache.org/spreadsheet/quick-guide.html#CellContents
If you using the POI 3.5 you can use the following
cell.getDateCellValue() method. This will work for excel 2007 as well.
Since POI 3.15 beta3 some functions are deprecated
.
You can check data format and retrieve as Java Date
.
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());
}