如何读取具有 ApachePOI 日期的 Excel 单元格？

我使用的是 Apache POI 3.6，我想读取一个 Excel 文件，它有一个类似 8/23/1991的日期。

 switch (cell.getCellType()) {


...
...


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


...


}

但是它接受数值类型并返回类似于 33473.0的值。

我尝试使用数字单元格类型，虽然没有运气。

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();
}

我怎样才能解决我的问题？

187501 次浏览
小开

You need the DateUtils: see this article for details.

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

小开
最佳答案

NOTE: HSSFDateUtil is deprecated

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()

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

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

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
小开

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.

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
小开

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

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);
}
小开

If you know the cell number, then i would recommend using getDateCellValue() method Here's an example for the same that worked for me - java.util.Date date = row.getCell().getDateCellValue(); System.out.println(date);

小开

For reading date cells this method has proven to be robust so far:

private LocalDate readCellAsDate(final Row row, final int pos) {
if (pos == -1) {
return null;
}
final Cell cell = row.getCell(pos - 1);
if (cell != null) {
cell.setCellType(CellType.NUMERIC);
} else {
return null;
}
if (DateUtil.isCellDateFormatted(cell)) {
try {
return cell.getDateCellValue().toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
} catch (final NullPointerException e) {
logger.error(e.getMessage());
return null;
}
}
return null;
}
小开

Try this code.

XSSFWorkbook workbook = new XSSFWorkbook(new File(result));
XSSFSheet sheet = workbook.getSheetAt(0);


// Iterate through each rows one by one
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
// For each row, iterate through all the columns
Iterator<Cell> cellIterator = row.cellIterator();


while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
if (cell.getNumericCellValue() != 0) {
//Get date
Date date = row.getCell(0).getDateCellValue();






//Get datetime
cell.getDateCellValue()




System.out.println(date.getTime());
}
break;
}
}
}

Hope is help.

小开 
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;




Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
if(cell.getCellTypeEnum() == CellType.NUMERIC||cell.getCellTypeEnum() == CellType.FORMULA)
{
    



String cellValue=String.valueOf(cell.getNumericCellValue());
if(HSSFDateUtil.isCellDateFormatted(cell))
{
DateFormat df = new SimpleDateFormat("MM/dd/yyyy");
Date date = cell.getDateCellValue();
cellValue = df.format(date);
}
System.out.println(cellValue);
}
小开

Apache Poi has a DateUtil.isCellDateFormatted(XSSFCell) it works great.

Object objData = switch (cell.getCellType()){
case NUMERIC ->{
if(DateUtil.isCellDateFormatted(cell)){
yield cell.getDateCellValue();
}else{
yield cell.getNumericCellValue();
}
} //The rest of the cellTypes need to be implemented.
}

objData can now be tested for Date or Double.


提交答案