Java 如何使用Apache POI读取具有日期的Excel单元格？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3148535/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to read Excel cell having Date with Apache POI?
提问by Venkat
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.
我试过使用 Numeric Cell Type 虽然没有运气。
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?
我该如何解决我的问题？
采纳答案by JoseK
NOTE: HSSFDateUtil is deprecated
注意：HSSFDateUtil 已弃用
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).
更新：这是一个示例 - 您可以在上面的 switch case 代码中应用它。我正在检查和打印数字以及日期值。在这种情况下，我的工作表中的第一列有日期，因此我使用 row.getCell(0)。
You can use the if (HSSFDateUtil.isCellDateFormatted ..code block directly in your switch case.
您可以if (HSSFDateUtil.isCellDateFormatted ..直接在 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
回答by duffymo
You need the DateUtils: see this articlefor details.
您需要 DateUtils：有关详细信息，请参阅本文。
Or, better yet, use Andy Khan's JExcel instead of POI.
或者，更好的是，使用 Andy Khan 的 JExcel 而不是 POI。
回答by Chintan
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
回答by Abhishek Mishra
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);
}
回答by user1416932
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);
如果您知道单元格编号，那么我建议使用 getDateCellValue() 方法这是一个对我有用的相同示例 - java.util.Date date = row.getCell().getDateCellValue(); System.out.println(日期);
回答by yglodt
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;
}
回答by superup
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.
希望是帮助。
