这个问题虽然也比较常见,解决办法也比较简单,但是网上有一些代码不全,思路混乱,乱七八糟的办法,容易误导大家,特地来为大家开路
这里分享一下我的一个思路
Maven依赖
<!--POI-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<!--fastjson-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.60</version>
</dependency>
date.xlsx
excel的格式如下图
id date
A 2020-12-01
B 2020-12-02
C 2020-05-03
D 2020-07-04
E 2020-11-05
F 2020-09-06
G 2020-12-07
H 2020-12-08
ConvertExcelDate.java
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.ss.usermodel.*;
import java.io.FileInputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
/**
* @Author Daniel
* @Description 解决poi读取excel日期为数字的问题
**/
public class ConvertExcelDate {
public static void main(String[] args) {
try {
FileInputStream fis = new FileInputStream("date.xlsx");
Workbook workbook = WorkbookFactory.create(fis);
// 获取第一张表
Sheet sheet = workbook.getSheetAt(0);
// 获取行数
int rows = sheet.getPhysicalNumberOfRows();
// 获取第一行的表头
Row firstRow = sheet.getRow(0);
// 获取列数
JSONArray jsonArray = new JSONArray();
// i=1,忽略表头
for (int i = 1; i < rows; i++) {
Row row = sheet.getRow(i);
if (row != null) {
JSONObject rowObj = new JSONObject();
//循环列
for (int j = 0; j < firstRow.getPhysicalNumberOfCells(); j++) {
Cell cellData = row.getCell(j);
if (cellData != null) {
// 日期会被当作数字
if (cellData.getCellType() == Cell.CELL_TYPE_NUMERIC) {
// 设置要求的日期格式
SimpleDateFormat sdf = new SimpleDateFormat("MM/d/yyyy");
// 拿到数字格式的日期
double value = cellData.getNumericCellValue();
// 这里的关键就是使用DateUtil类将数字转化为日期
Date date = DateUtil.getJavaDate(value);
rowObj.put(firstRow.getCell(j).getStringCellValue(), sdf.format(date));
} else {
row.getCell(j).setCellType(Cell.CELL_TYPE_STRING);
rowObj.put(firstRow.getCell(j).getStringCellValue(), cellData.getStringCellValue());
}
} else {
rowObj.put(firstRow.getCell(j).getStringCellValue(), "");
}
}
jsonArray.add(rowObj);
}
}
System.out.println(jsonArray);
} catch (Exception e) {
e.printStackTrace();
}
}
}
输出结果
[
{
"date": "12/1/2020",
"id": "A"
},
{
"date": "12/2/2020",
"id": "B"
},
{
"date": "05/3/2020",
"id": "C"
},
{
"date": "07/4/2020",
"id": "D"
},
{
"date": "11/5/2020",
"id": "E"
},
{
"date": "09/6/2020",
"id": "F"
},
{
"date": "12/7/2020",
"id": "G"
},
{
"date": "12/8/2020",
"id": "H"
}
]