1.需要使用 multipartFile 包 package org.springframework.web.multipart;
2.数据校验
public String exportVehicleViol(MultipartFile multipartFile) {
try {
//对前端传递的文件进行校验
if (multipartFile == null && multipartFile.getSize() == 0) {
return "文件上传错误，重新上传";
}
//获取文件名称 判断文件是否为 Execl
String filename = multipartFile.getOriginalFilename();
if (!(filename.endsWith(".xls") || filename.endsWith(".xlsx"))) {
return "文件上传格式有误，请重新上传";
}
List<EhicleViolation> ehicleViolations = null;
InputStream inputStream = multipartFile.getInputStream();
//根据文件格式 对应不同的api解析
if (filename.endsWith(".xlsx")) {
ehicleViolations = readXlsx(inputStream);
} else {
ehicleViolations = readXls(inputStream);
}
//数据保存
saveBatch(ehicleViolations);
} catch (IOException e) {
e.printStackTrace();
}
return JsonResult.Success("导入成功");
}
3.主要解析的业务逻辑
①解析xls
//解析xls
private List<EhicleViolation> readXls(InputStream inputStream) throws IOException {
HSSFWorkbook sheets = new HSSFWorkbook(inputStream);
//读取第一张sheet
HSSFSheet sheetAt = sheets.getSheetAt(0);
List<EhicleViolation> ehicleViolatsion = new ArrayList<>();
//rowNum = 3 从第三行开始获取值
for (int rowNum = 3; rowNum < sheetAt.getLastRowNum(); rowNum++) {
EhicleViolation ehicleViolation = new EhicleViolation();
HSSFRow row = sheetAt.getRow(rowNum);
if (row != null) {
//使用了getStringCellValue()方法来获取值，POI会判断单元格的类型，如果非字符串类型就会抛出上面的异常。
//所以先使用setCellType()方法先将该单元格的类型设置为STRING
//然后poi会根据字符串读取它
row.getCell(0).setCellType(CellType.STRING);
row.getCell(1).setCellType(CellType.STRING);
row.getCell(2).setCellType(CellType.STRING);
row.getCell(3).setCellType(CellType.STRING);
row.getCell(4).setCellType(CellType.STRING);
row.getCell(5).setCellType(CellType.STRING);
row.getCell(6).setCellType(CellType.STRING);
row.getCell(7).setCellType(CellType.STRING);
row.getCell(8).setCellType(CellType.STRING);
String stringCellValue0 = row.getCell(0).getStringCellValue();
String stringCellValue1 = row.getCell(1).getStringCellValue();
if (StringUtils.isNotBlank(stringCellValue1)) {
ehicleViolation.setUserDept(stringCellValue1);
}
//根据自己需要 获取表格中的数据
String stringCellValue2 = row.getCell(2).getStringCellValue();
if (StringUtils.isNotBlank(stringCellValue2)) {
ehicleViolation.setVehicleNumber(stringCellValue2);
} else {
continue;
}
}
EehicleViolations.add(EehicleViolation);
}
return EehicleViolations;
}
②解析xlsx
//解析xlsx
private List<VmsVehicleViolation> readXlsx(InputStream inputStream) throws IOException {
XSSFWorkbook sheets1 = new XSSFWorkbook(inputStream);
XSSFSheet sheetAt1 = sheets1.getSheetAt(0);
List<EhicleViolation> ehicleViolatsion = new ArrayList<>();
//rowNum = 3 从第三行开始获取值
for (int rowNum = 3; rowNum < sheetAt.getLastRowNum(); rowNum++) {
EhicleViolation ehicleViolation = new EhicleViolation();
HSSFRow row = sheetAt.getRow(rowNum);
if (row != null) {
//使用了getStringCellValue()方法来获取值，POI会判断单元格的类型，如果非字符串类型就会抛出上面的异常。
//所以先使用setCellType()方法先将该单元格的类型设置为STRING
//然后poi会根据字符串读取它
row.getCell(0).setCellType(CellType.STRING);
row.getCell(1).setCellType(CellType.STRING);
row.getCell(2).setCellType(CellType.STRING);
row.getCell(3).setCellType(CellType.STRING);
row.getCell(4).setCellType(CellType.STRING);
row.getCell(5).setCellType(CellType.STRING);
row.getCell(6).setCellType(CellType.STRING);
row.getCell(7).setCellType(CellType.STRING);
row.getCell(8).setCellType(CellType.STRING);
String stringCellValue0 = row.getCell(0).getStringCellValue();
String stringCellValue1 = row.getCell(1).getStringCellValue();
if (StringUtils.isNotBlank(stringCellValue1)) {
ehicleViolation.setUserDept(stringCellValue1);
}
//根据自己需要 获取表格中的数据
String stringCellValue2 = row.getCell(2).getStringCellValue();
if (StringUtils.isNotBlank(stringCellValue2)) {
ehicleViolation.setVehicleNumber(stringCellValue2);
} else {
continue;
}
}
EehicleViolations.add(EehicleViolation);
}
return EehicleViolations;
}
注意：对于不同的Execl Java提供了不同的解析对象
xls使用HSSFWorkbook 对象进行解析
xlsx使用XSSWorkbook 对象进行解析