SpringBoot+SpringDataJPA解析excel文件，实现批量上传功能

导入依赖

<!--SpringBoot整合jpa-->
<dependency>
	<groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<!--报表导入导出-->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.14</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.14</version>
</dependency>

前端HTML5代码：

<form th:action="@{/admin/upload}" method="post" enctype="multipart/form-data">
	<div class="form-group">
		<label>Excel文件</label>
		<input type="file" name="file" id="file" class="form-control">
	</div>
	<input type="button" onclick="sendFile()" value="上传" class="btn btn-primary"/>
</form>

JavaScript 代码：

<script type="text/javascript">
    function sendFile() {
        var file = $("input[name='file']").val();
        if (file == "") {
            alert("请选择上传的目标文件! ")
            return false;
        }
        //判断文件类型,我这里根据业务需求判断的是Excel文件
        var file1 = file.substring(file.lastIndexOf(".") + 1).toLowerCase();
        if(file1 != "xls" && file1 !="xlsx"){
            alert("请选择Execl文件!");
            $("input[name='file']").val("");
            return false;
        }
        //判断文件大小
        var size1 = $("input[name='file']")[0].files[0].size;
        if (size1>104857600) {
            alert("上传文件不能大于100M!");
            return false;
        }
        //这里需要实例化一个FormData来进行文件上传
        var formData = new FormData();
        formData.append("file",$("#file")[0].files[0]);
        $.ajax({
            type : "post",
            url : "http://localhost:8080/admin/upload",
            data : formData,
            processData : false,
            contentType : false,
            success : function(data){
                if (data.code == "0" && data.code == "5") {
                    alert("文件上传成功!");
                    window.location.href = "http://localhost:8080/admin/listStudent";
                }else if (data.code == "1") {
                    alert("导入的Excel表中学生年龄数据格式不正确!");
                }else if (data.code == "2") {
                    alert("导入的Excel表中学生数据存在空值!");
                    $("input[name='file']").val("");
                }else{
                    alert("导入的Excel表中数据有误，请检查上传的Excel文档（没用数据或字段格式有误或字段存在空值!");
                    $("input[name='file']").val("");
                }
            }
        });
    }

dao层代码：

public interface StudentDao extends JpaRepository<Student,Integer>, JpaSpecificationExecutor<Student> {
    /**
     * 根据学生Id查询出对应学生实体信息
     * @param id
     * @return
     */
    @Query(value = "select * from Student where id = ?1",nativeQuery = true)
    Student findBystuId(Integer id);

    /**
     * 根据学生身份证号码查询(身份证号码 唯一)
     * @param idCard
     * @return
     */
    @Query(value = "select * from Student where id_card = ?1",nativeQuery = true)
    Student findByIdCard(String idCard);
}

后端Service实现类代码：

@Override
    public Map<String,String> addStudent(MultipartFile file, HttpSession session) throws IOException, AgeFormatException, DataNullPointerException {
        Map<String,String> map = new HashMap();
        //获取当前时间
        Timestamp timestamp = new Timestamp(System.currentTimeMillis());
        //读取上传的excel文件，解析成list集合
        List<String[]> list = POIUtil.readExcel(file);
        //判断excel文件中是否有数据
        if (CollectionUtils.isEmpty(list)) {
            map.put("code", MessageConstantUtil.EXCEL_NULL);
            throw new NullPointerException("数据为空");
        }else {
            //用于存储学生的姓名,身份证号码,年龄,地址字段
            List<Student> data = new ArrayList<>();
            //遍历,将对应的值赋值给Student对象,再存储到list集合中
            for (String[] strings : list) {
                //姓名  excel的第一列数据
                String name = strings[0];
                //身份证号码  excel的第二列数据
                String idCard = strings[1];
                //年龄  excel的第三列数据
                String ageStr = strings[2];
                //地址  excel的第四列数据
                String address = strings[3];
                map = operateCommon.addStudentCommons(name, idCard, ageStr, address, timestamp, session);
            }
        }
        return map;
    }

提取的公共部分代码：

@Component
public class OperateCommon {
    @Autowired
    private StudentDao studentDao;
    @Autowired
    private RedisTemplate<String,String> redisTemplate;
    public  Map<String,String> addStudentCommons(String name, String idCard, String ageStr, String address, Timestamp timestamp, HttpSession session) throws AgeFormatException, DataNullPointerException {
        Map<String,String> map = new HashMap();
        //获取session域中的管理员Id
        int adminId = (int) session.getAttribute("adminId");
        boolean compareAll = compareAll(name, idCard, ageStr, address);
        //姓名、身份证号码、年龄、地址都不为空
        if (compareAll) {
            //判断年龄格式是否正确
            if (ageStr.matches(ConstantUtil.AGEREG) && idCard.matches(ConstantUtil.IDCARDREG)) {
                //根据学生身份证号码查询学生（身份证号码唯一）
                Student stuIdCard = studentDao.findByIdCard(idCard);
                //将年龄转为整形
                int age = Integer.parseInt(ageStr);
                //表示此学生数据未存在,添加学生数据
                if (stuIdCard == null) {
                    Student student = new Student(name, idCard, age, address, ConstantUtil.UNSTUDENTSTATUS);
                    //设置创建人Id
                    student.setCreateId(adminId);
                    //设置创建时间
                    student.setCreateTime(timestamp);
                    studentDao.save(student);
                    //此时再次查询，是因为学生已生成学号
                    Student byIdCard = studentDao.findByIdCard(idCard);
                    //将学生对象转换为JSON字符串,存入redis中
                    String s = JSON.toJSONString(byIdCard);
                    redisTemplate.boundSetOps("studentInfo").add(s);
                    map.put("code", MessageConstantUtil.ADD_STUDENT_SUCCESS);
                    //此学生数据存在
                } else {
                    //更改学生信息，保存数据库
                    stuIdCard.setName(name);
                    stuIdCard.setIdCard(idCard);
                    stuIdCard.setAge(age);
                    stuIdCard.setAddress(address);
                    stuIdCard.setUpdateId(adminId);
                    stuIdCard.setUpdateTime(timestamp);
                    stuIdCard.setStudentStatus(ConstantUtil.UNSTUDENTSTATUS);
                    //获取redis中所有数据
                    Set<String> studentInfo = redisTemplate.boundSetOps("studentInfo").members();
                    //遍历
                    for (String stu : studentInfo) {
                        //将JSON数据转换为对象
                        Student student = JSON.parseObject(stu, Student.class);
                        boolean compareStudents = compareStudents(stuIdCard, student);
                        //判断数据是否一致，不一致就修改
                        if (compareStudents){
                            //将信息一致的学生信息进行JSON字符串转换操作
                            String stu1 = JSON.toJSONString(student);
                            //redis删除对应数据
                            redisTemplate.boundSetOps("studentInfo").remove(stu1);
                            //更改学生姓名
                            student.setName(name);
                            //更改学生身份证号码
                            student.setIdCard(idCard);
                            //更改年龄
                            student.setAge(age);
                            //更改地址
                            student.setAddress(address);
                            //设置修改人Id
                            student.setUpdateId(adminId);
                            //设置修改时间
                            student.setUpdateTime(timestamp);
                            //将状态更改为未操作状态
                            student.setStudentStatus(ConstantUtil.UNSTUDENTSTATUS);
                            //将学生对象转换为JSON字符串,存入redis中
                            String newStu = JSON.toJSONString(student);
                            redisTemplate.boundSetOps("studentInfo").add(newStu);
                        }
                    }
                    studentDao.save(stuIdCard);
                    map.put("code", MessageConstantUtil.UPDATE_STUDENT_SUCCESS);
                }
            }else {
                map.put("code",MessageConstantUtil.NUM_FORMAT_FAIT);
                throw new AgeFormatException("表中学生年龄或身份证数据格式不正确！");
            }
        }else {
            map.put("code",MessageConstantUtil.DATA_NULL_POINT);
            throw new DataNullPointerException("表中学生数据存在空值！");
        }
        return map;
    }

    public boolean compareAll(String name, String idCard, String ageStr, String address){
        boolean nameFlag = true;
        boolean idCardFlag = true;
        boolean ageFlag = true;
        boolean addressFlag = true;
        //判断姓名是否为空
        if (name == "" || name == null){
            nameFlag = false;
        }
        //判断身份证号码是否为空
        if (idCard == "" || idCard == null){
            idCardFlag = false;
        }
        //判断年龄是否为空
        if (ageStr == "" || ageStr == null){
            ageFlag = false;
        }
        //判断地址是否为空
        if (address == "" || address == null){
            addressFlag = false;
        }
        //姓名、身份证号码、年龄、地址都不为空
        if (nameFlag && idCardFlag && ageFlag && addressFlag) {
            return true;
        }else {
            return false;
        }
    }
    public boolean compareStudents(Student student1, Student student2){
        boolean nameFlag = true;
        boolean idCardFlag = true;
        boolean ageFlag = true;
        boolean addressFlag = true;
        boolean statusFlag = true;
        //判断姓名是否相同
        if (student1.getName().equals(student2.getName())){
            nameFlag = false;
        }
        //判断身份证号码是否相同
        if (student1.getIdCard().equals(student2.getIdCard())){
            idCardFlag = false;
        }
        //判断年龄是否相同
        if (student1.getAge().equals(student2.getAge())){
            ageFlag = false;
        }
        //判断地址是否相同
        if (student1.getAddress().equals(student2.getAddress())){
            addressFlag = false;
        }
        //判断数据是否一致，不一致就修改
        if (student1.getId().equals(student2.getId()) &&(nameFlag||idCardFlag||ageFlag||addressFlag)){
            return true;
        }else {
            return false;
        }
    }
}

controller 代码：

@Transactional(rollbackFor = Exception.class)
    @PostMapping("/upload")
    @ResponseBody
    public Map<String,String> upload(@RequestParam("file") MultipartFile file, HttpSession session){
        try {
            return studentService.addStudent(file, session);
        } catch (AgeFormatException e) {
            e.printStackTrace();
        } catch (DataNullPointerException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

POIUtil工具包：

public class POIUtil {
    private final static String xls = "xls";
    private final static String xlsx = "xlsx";
    private final static String DATE_FORMAT = "yyyy/MM/dd";
    /**
     * 读入excel文件，解析后返回
     * @param file
     * @throws IOException
     */
    public static List<String[]> readExcel(MultipartFile file) throws IOException {
        //检查文件
        checkFile(file);
        //获得Workbook工作薄对象
        Workbook workbook = getWorkBook(file);
        //创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
        List<String[]> list = new ArrayList<String[]>();
        if(workbook != null){
            for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
                //获得当前sheet工作表
                Sheet sheet = workbook.getSheetAt(sheetNum);
                if(sheet == null){
                    continue;
                }
                //获得当前sheet的开始行
                int firstRowNum  = sheet.getFirstRowNum();
                //获得当前sheet的结束行
                int lastRowNum = sheet.getLastRowNum();
                //循环除了第一行的所有行
                for(int rowNum = firstRowNum+1;rowNum <= lastRowNum;rowNum++){
                    //获得当前行
                    Row row = sheet.getRow(rowNum);
                    if(row == null){
                        continue;
                    }
                    //获得当前行的开始列
                    int firstCellNum = row.getFirstCellNum();
                    //获得当前行的列数
                    int lastCellNum = row.getPhysicalNumberOfCells();
                    String[] cells = new String[row.getPhysicalNumberOfCells()];
                    //循环当前行
                    for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
                        Cell cell = row.getCell(cellNum);
                        cells[cellNum] = getCellValue(cell);
                    }
                    list.add(cells);
                }
            }
            workbook.close();
        }
        return list;
    }

    //校验文件是否合法
    public static void checkFile(MultipartFile file) throws IOException{
        //判断文件是否存在
        if(null == file){
            throw new FileNotFoundException("文件不存在！");
        }
        //获得文件名
        String fileName = file.getOriginalFilename();
        //判断文件是否是excel文件
        if(!fileName.endsWith(xls) && !fileName.endsWith(xlsx)){
            throw new IOException(fileName + "不是excel文件");
        }
    }
    public static Workbook getWorkBook(MultipartFile file) {
        //获得文件名
        String fileName = file.getOriginalFilename();
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        try {
            //获取excel文件的io流
            InputStream is = file.getInputStream();
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if(fileName.endsWith(xls)){
                //2003
                workbook = new HSSFWorkbook(is);
            }else if(fileName.endsWith(xlsx)){
                //2007
                workbook = new XSSFWorkbook(is);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return workbook;
    }
    public static String getCellValue(Cell cell){
        String cellValue = "";
        if(cell == null){
            return cellValue;
        }
        //如果当前单元格内容为日期类型，需要特殊处理
        String dataFormatString = cell.getCellStyle().getDataFormatString();
        if(dataFormatString.equals("m/d/yy")){
            cellValue = new SimpleDateFormat(DATE_FORMAT).format(cell.getDateCellValue());
            return cellValue;//"2019/10/10"
        }
        //把数字当成String来读，避免出现1读成1.0的情况
        if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
            cell.setCellType(Cell.CELL_TYPE_STRING);
        }
        //判断数据的类型
        switch (cell.getCellType()){
            //数字
            case Cell.CELL_TYPE_NUMERIC:
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            //字符串
            case Cell.CELL_TYPE_STRING:
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            //Boolean
            case Cell.CELL_TYPE_BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            //公式
            case Cell.CELL_TYPE_FORMULA:
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            //空值
            case Cell.CELL_TYPE_BLANK:
                cellValue = "";
                break;
            //故障
            case Cell.CELL_TYPE_ERROR:
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }
}

excel模板样式