英文:
How to correctly convert into a Date object this "strange" cell value retrieve parsing an Excel using Apache POI?
问题
我正在疯狂地尝试使用**Apache POI**从Java解析Excel文件，但在解析包含日期的特定列时遇到了以下问题。
这是我的Excel文件中的列：
[![enter image description here][1]][1]
如你所见，这一列包含日期字段，但是这一列的某些单元格包含**数字**值，其他单元格包含**文本**值（我可以使用**TYPE()** Excel函数看到）。我真的不知道这是如何可能的，因为所有单元格都包含日期字段。有什么想法吗？
无论如何，在我的代码中，我试图以以下方式处理这种奇怪的情况：
```java
if (currentRow.getCell(20) != null && !currentRow.getCell(20).toString().trim().isEmpty()) {
if (currentRow.getCell(20).getCellType().toString().equals("STRING")) {
System.out.println("Data sottoscrizione contratto 是 STRING: " + currentRow.getCell(20).toString());
} else if (currentRow.getCell(20).getCellType().toString().equals("NUMERIC")) {
System.out.println("Data sottoscrizione contratto 是 NUMERIC");
String dateAsString = currentRow.getCell(20).toString();
System.out.println("DATA SOTTOSCRIZIONE CONTRATTO: " + currentRow.getCell(20).toString());
}
}
通过这种方式，我可以处理这两种情况，试图转换为日期。
这里是我的问题。当它在Excel中找到一个数值时，会进入NUMERIC情况，并通过以下方式打印单元格的值：
System.out.println("DATA SOTTOSCRIZIONE CONTRATTO: " + currentRow.getCell(20).toString());
我得到打印值为16-ott-2017，对应于日期值16/10/2017。
这里有一些疑问：为什么我获得这种格式，而不是像16/10/2017这样的格式。
16-ott-2017 应该是日期的意大利格式。如何将其转换为正确的Date对象？
[1]: https://i.stack.imgur.com/RKo6k.png
<details>
<summary>英文:</summary>
I am going crazy trying to parse an Excel from Java using **Apache POI** and I ami finding the following problem parsing a specific column containin dates.
This is the column into my Excel file:
[![enter image description here][1]][1]
As you can see this column contains date fields but some cells of this column contains **number** values some other celles contains **text** values (I can see it using the **TYPE()** Excel function). I really don't know how it is possible because all the cell contains date fields. Some idea?
Anyway in my code I am trying to handle this strange situation in this way:
if(currentRow.getCell(20) != null && !currentRow.getCell(20).toString().trim().isEmpty()) {
if(currentRow.getCell(20).getCellType().toString().equals("STRING")) {
System.out.println("Data sottoscrizione contratto è STRING: " + currentRow.getCell(20).toString());
}
else if(currentRow.getCell(20).getCellType().toString().equals("NUMERIC")) {
System.out.println("Data sottoscrizione contratto è NUMERIC");
String dateAsString = currentRow.getCell(20).toString();
System.out.println("DATA SOTTOSCRIZIONE CONTRATTO: " + currentRow.getCell(20).toString());
}
}
In this way I can handle both the case trying to convert to a date.
And here my problem. When it found an Excel numeric value in enter into the if **NUMERIC** case
and printing the cell value by:
System.out.println("DATA SOTTOSCRIZIONE CONTRATTO: " + currentRow.getCell(20).toString());
I obtain printed the value **16-ott-2017** related to the date value **16/10/2017**
And here some doubts: Why am I obtaining in this format instead something like **16/10/2017**.
**16-ott-2017** should be the italian formattation of the date. How can I convert it into a propper Date object?
[1]: https://i.stack.imgur.com/RKo6k.png
</details>
# 答案1
**得分**: 2
Buon giorno!
您当前正在使用单元格的 `toString()` 方法，该方法在返回数值或日期方面可能不太准确。有时可能有效，但并不总是可行。
请使用能够获取实际值的方法，比如 [`Cell.getNumericCellValue()`](https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/Cell.html#getNumericCellValue--)，[`Cell.getDateCellValue()`](https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/Cell.html#getDateCellValue--)（已过时，因为它返回一个 `java.util.Date`），或 [`Cell.getLocalDateTimeCellValue()`](https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/Cell.html#getLocalDateTimeCellValue--)。如果您的单元格只包含文本，比如 `"16-ott-2020"`，请使用 [`getStringCellValue()`](https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/Cell.html#getStringCellValue--) 方法，并将返回的值转换为 `LocalDate`（或根据是否需要 *时间* 考虑使用 `LocalDateTime`）。
以下是转换为 `LocalDate` 的示例：
```Java
public static void main(String[] args) {
// 假设您已经将值作为字符串接收到
String cellStringValue = "16-ott-2020";
// 提供一个可以解析意大利月份名称（或星期几）的格式化器
DateTimeFormatter dtf = DateTimeFormatter.ofPattern("dd-MMM-uuuu", Locale.ITALIAN);
// 将字符串解析为 LocalDate
LocalDate sediciOttobreDuemilaEVenti = LocalDate.parse(cellStringValue, dtf);
// 并打印其默认值
System.out.println(sediciOttobreDuemilaEVenti);
// 或者可以将相同的格式化器用于输出
System.out.println(sediciOttobreDuemilaEVenti.format(dtf));
}
该代码的输出为：
2020-10-16
16-ott-2020
英文:
Buon giorno!
You are currently using the toString() method of the cell, which will not be very accurate in returning numeric values or even dates. It might work sometimes, but it won't do always.
Use the methods that get you a real value, like Cell.getNumericCellValue(), Cell.getDateCellValue() (outdated because it returns a java.util.Date) or Cell.getLocalDateTimeCellValue(). If your cell just contains text, like "16-ott-2020", use the getStringCellValue() and convert the value returned to a LocalDate (or LocalDateTime depends on if time of day matters for you).
Here's an example of the conversion (to a LocalDate):
public static void main(String[] args) {
// assuming you alread received the value as String
String cellStringValue = "16-ott-2020";
// provice a formatter that can parse Italian month names (or days of week)
DateTimeFormatter dtf = DateTimeFormatter.ofPattern("dd-MMM-uuuu", Locale.ITALIAN);
// parse the String to a LocalDate
LocalDate sediciOttobreDuemilaEVenti = LocalDate.parse(cellStringValue, dtf);
// and print its default value
System.out.println(sediciOttobreDuemilaEVenti);
// alternatively use the same formatter for output
System.out.println(sediciOttobreDuemilaEVenti.format(dtf));
}
The output of that code is
2020-10-16
16-ott-2020
答案2
得分: 0
case NUMERIC:
if(Cell.getLocalDateTimeCellValue()!=null) {
LocalDateTime actualdate = Cell.getLocalDateTimeCellValue();
String formattedDate = actualdate.format(DateTimeFormatter.ofPattern("MM/dd/yyyy"));
System.out.println(formattedDate);
}
break;
英文:
case NUMERIC:
if(Cell.getLocalDateTimeCellValue()!=null) {
LocalDateTime actualdate = Cell.getLocalDateTimeCellValue();
String formattedDate = actualdate.format(DateTimeFormatter.ofPattern("MM/dd/yyyy"));
System.out.println(formattedDate);
}
break;
答案3
得分: 0
switch (Cell.getCellType()) {
case STRING:
System.out.print(Cell.getStringCellValue());
break;
case NUMERIC:
DataFormatter df = new DataFormatter();
String CellValue = df.formatCellValue(Cell);
System.out.println(CellValue);
break;
case BOOLEAN:
System.out.print(Cell.getBooleanCellValue());
break;
case BLANK:
System.out.println("");
}
System.out.print(" | ");
}
英文:
switch (Cell.getCellType()) {
case STRING:
System.out.print(Cell.getStringCellValue());
break;
case NUMERIC:
DataFormatter df = new DataFormatter();
String CellValue = df.formatCellValue(Cell);
System.out.println(CellValue);
break;
case BOOLEAN:
System.out.print(Cell.getBooleanCellValue());
break;
case BLANK:
System.out.println("");
}
System.out.print(" | ");
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论