springboot导入Excel表格数据时日期格式数据解析错误的问题_springboot如何获取excel中是日期的

平时我们写日期格式的时候是这样2020-12-20 23:30:23
当你写进excel表格时候会自动变成这样2019/10/1 15:57:45
后台接收的时候打印出来是这样的，并且本来值为1的变成1.0

解决：

	/**
	 * 解析POI导入Excel中日期格式数据
	 * @param currentCell
	 * @return currentCellValue
	 */
	public static String importByExcelForDate(Cell currentCell) {
		String currentCellValue = "";
		// 判断单元格数据是否是数值型
		if (0 == currentCell.getCellType()) {
			// 判断单元格数据是否是日期
			if (DateUtil.isCellDateFormatted(currentCell)) {
				// 用于转化为日期格式
				Date d = currentCell.getDateCellValue();
				DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				currentCellValue = formater.format(d);
			} else {
				// 把单元格数据当做文本，解决数字1变成1.0的问题
				currentCell.setCellType(currentCell.CELL_TYPE_STRING);
				currentCellValue = currentCell.toString();
			}
		} else {
			// 不是日期原值返回
			currentCellValue = currentCell.toString();
		}
		return currentCellValue;
	}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

下面是完整代码

	/**
	 * @Title:
	 * @Description: 上传excel表格
	 * @param
	 * @return
	 * @throws
	 */
	@RequestMapping(value = "/upload", method = { RequestMethod.POST, RequestMethod.GET })
	public String uploadExcel(Model model, HttpServletRequest request, HttpSession session) throws Exception {

		MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;

		MultipartFile file = multipartRequest.getFile("filename");
		if (file.isEmpty()) {
			return "redirect:/product/toProduct";
		}
		InputStream inputStream = file.getInputStream();
		List<List<Object>> list = excelUtil.getBankListByExcel(inputStream, file.getOriginalFilename());
		inputStream.close();
		ProductInfo productInfo = new ProductInfo();
//下面是把处理好的结果一个一个放到对象中，保存数据库
		for (int i = 0; i < list.size(); i++) {
			try {
				List<Object> lo = list.get(i);
				productInfo.setVin(lo.get(0).toString());
productInfo.setProductType(Integer.parseInt(lo.get(1).toString()));
				productInfo.setProductName(lo.get(2).toString());
				productInfo.setProductNo(lo.get(3).toString());
				productInfo.setIccId(lo.get(4).toString());
				productInfo.setBatch(lo.get(5).toString());
				Date productTime = null;
				// String 日期转为 Date类型的日期
				if (lo.get(6).toString() != null || lo.get(6).toString() != "") {		
				productInfo.setProductTime(GetDateUtil.parseDate(lo.get(6).toString()));
				} else {
					productInfo.setProductTime(productTime);
				}
			} catch (Exception e) {
				// TODO: handle exception
				// 页面提示错误，重新填写再上传
			}
			productMapper.save(productInfo);
		}
		return "redirect:/product/toProduct";
	}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

@Service
public class ExcelUtil {

	/**
	 * 处理上传的文件
	 *
	 * @param in
	 * @param fileName
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unused")
	public List getBankListByExcel(InputStream in, String fileName) throws Exception {
		List list = new ArrayList<>();
		// 创建Excel工作薄
		Workbook work = this.getWorkbook(in, fileName);
		if (null == work) {
			throw new Exception("创建Excel工作薄为空！");
		}
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;

		for (int i = 0; i < work.getNumberOfSheets(); i++) {
			sheet = work.getSheetAt(i);
			if (sheet == null) {
				continue;
			}
			// 取每一行
			for (int j = 1; j <= sheet.getLastRowNum(); j++) {
				row = sheet.getRow(j);
				List<Object> li = new ArrayList<>();
				// 取每一行的每一格
				for (int y = 0; y < row.getLastCellNum(); y++) {
					cell = row.getCell(y);
					String string = "";
					if (cell != null) {
						string = importByExcelForDate(cell);
					}
					li.add(string);
				}
				list.add(li);
			}
		}
		work.close();
		return list;
	}

	/**
	 * 判断文件格式
	 *
	 * @param inStr
	 * @param fileName
	 * @return
	 * @throws Exception
	 */
	public Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
		Workbook workbook = null;
		String fileType = fileName.substring(fileName.lastIndexOf("."));
		if (".xls".equals(fileType)) {
			workbook = new HSSFWorkbook(inStr);
		} else if (".xlsx".equals(fileType)) {
			workbook = new XSSFWorkbook(inStr);
		} else {
			throw new Exception("请上传excel文件！");
		}
		return workbook;
	}

	/**
	 * 解析POI导入Excel中日期格式数据
	 * @param currentCell
	 * @return currentCellValue
	 */
	public static String importByExcelForDate(Cell currentCell) {
		String currentCellValue = "";
		// 判断单元格数据是否是数值型
		if (0 == currentCell.getCellType()) {
			// 判断单元格数据是否是日期
			if (DateUtil.isCellDateFormatted(currentCell)) {
				// 用于转化为日期格式
				Date d = currentCell.getDateCellValue();
				DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				currentCellValue = formater.format(d);
			} else {
				// 把单元格数据当做文本，解决数字1变成1.0的问题
				currentCell.setCellType(currentCell.CELL_TYPE_STRING);
				currentCellValue = currentCell.toString();
			}
		} else {
			// 不是日期原值返回
			currentCellValue = currentCell.toString();
		}
		return currentCellValue;
	}
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114