Reading date values from excel cell using POI HSSF API
Solution 1
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...
Solution 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:
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
}
Solution 3
If you using the POI 3.5 you can use the following
cell.getDateCellValue() method. This will work for excel 2007 as well.
Solution 4
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
Comments
-
Veera almost 4 years
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 !?
-
Jonathan Holloway about 15 years<rant> I wish those POI developers would comment their source code... </rant>
-
Aram Paronikyan about 10 yearsCalling DateUtil.isCellDateFormatted() will be a step ahead in the way of freeing your code from HSSFs and XSSFs. By using global parents instead of XLS/XLSX dependant HSSF/XSSF children, you can make your code much universal.
-
nantitv almost 7 yearsThanks for the link
-
AYRM1112013 almost 4 yearsIn the last version of POI lib HSSFDateUtil is deprecated, use DateUtil instead.