springboot整合poi导入excel案例--导入错误信息友好提示

1.在pom.xml中添加poi依赖

<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.13</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.13</version> </dependency>

2.在业务层添加导入代码

package com.iflytek.edu.hnezzhxy.service; import com.iflytek.edu.hnezzhxy.common.config.Constants; import com.iflytek.edu.hnezzhxy.common.enums.ResponseCodeEnum; import com.iflytek.edu.hnezzhxy.dao.ImportExcelDao; import com.iflytek.edu.hnezzhxy.model.ZsbmStudentResult; import com.iflytek.edu.hnezzhxy.util.StringUtils; import com.iflytek.edu.hnezzhxy.vo.ResultVO; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; 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.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import org.springframework.util.CollectionUtils; import org.springframework.web.multipart.MultipartFile; import java.io.InputStream; import java.util.*; /** * @version 1.0 * @description 导入excel * @create 2020/06/30 10:16 */ @Service public class ImportExcelService { @Autowired private ImportExcelDao importExcelDao; /** * @description 导入座位号 * @param file excel文件 */ public ResultVO importExcelZsbmStudentResult(MultipartFile file) { int flag = 0; StringJoiner buffer = new StringJoiner("\n"); String fileName = file.getOriginalFilename(); List<ZsbmStudentResult> list=new ArrayList<ZsbmStudentResult>(); try { if (fileName != null) { InputStream inputStream = file.getInputStream(); Workbook workbook = null; if (fileName.endsWith(Constants.EXCEL2007)) { workbook = new XSSFWorkbook(inputStream); } if (fileName.endsWith(Constants.EXCEL2003)) { workbook = new HSSFWorkbook(inputStream); } Sheet sheet = workbook.getSheetAt(0); int allRowNum = sheet.getLastRowNum(); if (allRowNum == 0) { //flag是进度条的值 flag = 100; buffer.add("导入文件数据为空"); } for (int i = 1; i <= allRowNum; i++) { Boolean hasItemError=false; if (flag < 100) { flag = flag + (100 / i); } else { flag = 100; //我需要插入的数据类型 ZsbmStudentResult bean = new ZsbmStudentResult(); //获取第i行 Row row = sheet.getRow(i); boolean rowEmpty = this.isRowEmpty(row); if (rowEmpty) { //忽略空白行 continue; }else{ //学生id if (row.getCell(0)!=null||StringUtils.isBlank(row.getCell(0).toString())) { buffer.add("第" + (i-1) + "行的第1列的学生id不能为空!"); hasItemError=true; } else { row.getCell(0).setCellType(Cell.CELL_TYPE_STRING); String value=row.getCell(0).getStringCellValue(); if(value.length()>32){ buffer.add("第" + (i-1)+ "行的第1列的学生id长度不能超过32位!"); hasItemError=true; }else{ bean.setSid(value); } } //考场号 if (row.getCell(1)!=null||StringUtils.isBlank(row.getCell(1).toString())) { buffer.add("第" + (i-1) + "行的第2列的考场号不能为空"); hasItemError=true; } else { row.getCell(1).setCellType(Cell.CELL_TYPE_STRING); String value=row.getCell(1).getStringCellValue(); if(value.length()>20){ buffer.add("第" + (i-1) + "行的第2列的考场号长度不能大于20！"); hasItemError=true; }else{ bean.setExaminationRoomNum(value); } } //座位号 if (StringUtils.isBlank(row.getCell(2).toString())) { buffer.add("第" + (i-1) + "行的第3列的座位号不能为空"); hasItemError=true; } else { row.getCell(2).setCellType(Cell.CELL_TYPE_STRING); String value=row.getCell(2).getStringCellValue(); if(value.length()>20){ buffer.add("第" + (i-1) + "行的第3列的座位号长度不能大于20！"); hasItemError=true; }else{ bean.setNum(value); } } //考试成绩 if (row.getCell(3)!=null||StringUtils.isBlank(row.getCell(3).toString())){ buffer.add("第" + (i-1) + "行的第4列的考试成绩不能为空!"); hasItemError=true; } else { try { row.getCell(3).setCellType(Cell.CELL_TYPE_STRING); String value = row.getCell(3).getStringCellValue(); double num = Double.parseDouble(value); if(num<0){ buffer.add("第" + (i-1) + "行的第4列的考试成绩必须大于等于0 ！"); }else{ bean.setScore(num); } }catch (Exception e){ buffer.add("第" + (i-1) + "行的第4列的考试成绩必须为double类型,例如:653.5 || 701 ！"); hasItemError=true; } } if (!hasItemError) { list.add(bean); } } } } } } catch(Exception e){ e.printStackTrace(); } if(StringUtils.isBlank(buffer.toString())){ //保存sid不重复 List<String> sidList=new ArrayList<>(); //保存考场号和座位号拼接后的值不重复 List<String> examinationRoomNumAndNumList =new ArrayList(); //excel填写重复错误信息 Set<String> ExcelWriteError=new HashSet<>(); if(!CollectionUtils.isEmpty(list)){ for (ZsbmStudentResult z:list) { String sid=z.getSid(); String examinationRoomNumAndNum=new StringBuilder().append(z.getExaminationRoomNum()).append(":").append(z.getNum()).toString(); if(sidList.contains(sid)){ ExcelWriteError.add(sid); }else{ sidList.add(sid); } if(examinationRoomNumAndNumList.contains(examinationRoomNumAndNum)){ ExcelWriteError.add(examinationRoomNumAndNum); }else{ examinationRoomNumAndNumList.add(examinationRoomNumAndNum); } } if(ExcelWriteError.isEmpty()){ //查询sid是否有重复 if(!CollectionUtils.isEmpty(sidList)){ for (String sid:sidList) { //判断学生表是否存在该学生 boolean studentFlag=importExcelDao.isExistSid(sid); if(studentFlag){ //学生表学生存在，但是座位号表sid跟即将要导入的数据重复 Integer sidCount =importExcelDao.isRepeatSid(sid); if(sidCount!=null&&sidCount>0){ buffer.add("学生sid值必须唯一不能重复导入！数据库中座位号表中已经存在学生编号为"+sid+"的学生编号！"); } }else{ buffer.add("数据库中学生表不存在编号为"+sid+"的学生编号，不能为其导入座位号！"); } } } //查询考场号和座位号是否重复 if(!CollectionUtils.isEmpty(examinationRoomNumAndNumList)){ for (String item:examinationRoomNumAndNumList) { String[] value = item.split(":"); //判断是否有相同考场号和座位号 Integer examinationRoomNumAndNumCount=importExcelDao.isExistExaminationRoomNumAndNum(value[0],value[1]); if(examinationRoomNumAndNumCount!=null&&examinationRoomNumAndNumCount>0){ buffer.add("相同的考场号座位号必须唯一!数据库中已经存在考场号为"+value[0]+",并且座位号为"+value[1]+"的记录！"); } } } }else{ Iterator<String> it = ExcelWriteError.iterator(); while(it.hasNext()){ String next = it.next(); //考场号和座位号 if(next.contains(":")){ String[] item = next.split(":"); String value="相同的考场号座位号必须唯一!请检查下考场号为"+item[0]+",并且座位号为"+item[1]+"的座位号！"; buffer.add(value); }else{ //sid学生编号 String item="学生sid值必须唯一不能重复导入！请检查下学生sid为"+next+"的编号！"; buffer.add(item); } } } if(StringUtils.isBlank(buffer.toString())){ //无错批量新增 importExcelDao.batchImportZbsmStudentResult(list); return new ResultVO(ResponseCodeEnum.IMPORT_SUCCESS.getCode(),ResponseCodeEnum.IMPORT_SUCCESS.getMessage(),null,true); } } } return new ResultVO(ResponseCodeEnum.IMPORT_ERROR.getCode(),ResponseCodeEnum.IMPORT_ERROR.getMessage(),buffer.toString(),false); } /** * 判断是否有空行 * @param row * @return */ private boolean isRowEmpty(Row row) { for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) { Cell cell = row.getCell(c); if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) { return false; } } return true; } }

3.准备好的excel文件 4.postman测试导入

注意:由于测试excel导入所以需要选择将text改为file

5.数据库数据导入成功！