Springboot使用excel导入mysql数据

首先项目结构

1.首先需要引入poi相关的jar包

<!--POI-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.16</version>
</dependency>

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

2.自定义异常类ExcelImportUtils

public class ExcelImportUtils {


    // @描述：是否是2003的excel，返回true是2003
    public static boolean isExcel2003(String filePath)  {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    //@描述：是否是2007的excel，返回true是2007
    public static boolean isExcel2007(String filePath)  {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

    /**
     * 验证EXCEL文件
     * @param filePath
     * @return
     */
    public static boolean validateExcel(String filePath){
        if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))){
            return false;
        }
        return true;
    }


}

3.controller

   @ResponseBody
    @RequestMapping(value = "/import",method = RequestMethod.POST)
    public String exImport(@RequestParam(value = "filename")MultipartFile file, HttpSession session) {

        boolean a = false;

        String fileName = file.getOriginalFilename();

        try {
            a = studentService.batchImport(fileName, file);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "1";   //这里需要修改，此处存在bug
    }

4.service

    List<Student> selectBynumber(String studentnumber);

    int updateByNumber(Student record);


    int insertStudentByExcel(Student record);

    boolean batchImport(String fileName, MultipartFile file) throws Exception;

5.serviceimpl

    @Transactional(readOnly = false,rollbackFor = Exception.class)
    @Override
    public boolean batchImport(String fileName, MultipartFile file) throws Exception {
        boolean notNull = false;
        List<Student> studentList = new ArrayList<>();
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            throw new MyException("上传文件格式不正确");
        }
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        InputStream is = file.getInputStream();
        Workbook wb = null;
        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        Sheet sheet = wb.getSheetAt(0);
        if(sheet!=null){
            notNull = true;
        }
        Student student;
        for (int r = 1; r <= sheet.getLastRowNum(); r++) {//r = 2 表示从第三行开始循环 如果你的第三行开始是数据
            Row row = sheet.getRow(r);//通过sheet表单对象得到 行对象
            if (row == null){
                continue;
            }

            //sheet.getLastRowNum() 的值是 10，所以Excel表中的数据至少是10条；不然报错 NullPointerException

            student = new Student();

            if( row.getCell(0).getCellType() !=1){//循环时，得到每一行的单元格进行判断
                throw new MyException("导入失败(第"+(r+1)+"行,请设为文本格式)");
            }

            String studentdepartments = row.getCell(0).getStringCellValue();//得到每一行第一个单元格的值


            if(studentdepartments == null || studentdepartments.isEmpty()){//判断是否为空
                throw new MyException("导入失败(第"+(r+1)+"行,学院未填写)");
            }


            row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第二个单元格的值

            String studentclass = row.getCell(1).getStringCellValue();

            if(studentclass==null || studentclass.isEmpty()){
                throw new MyException("导入失败(第"+(r+1)+"行,班级未填写)");
            }

            row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第二个单元格的值

            String studentprofessional = row.getCell(2).getStringCellValue();

            if(studentprofessional==null || studentprofessional.isEmpty()){
                throw new MyException("导入失败(第"+(r+1)+"行,学院未填写)");
            }

            row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第二个单元格的值

            String studentnumber = row.getCell(3).getStringCellValue();

            if(studentnumber==null || studentnumber.isEmpty()){
                throw new MyException("导入失败(第"+(r+1)+"行,学号未填写)");
            }

            row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第二个单元格的值

            String studentname = row.getCell(4).getStringCellValue();

            if(studentname==null || studentname.isEmpty()){
                throw new MyException("导入失败(第"+(r+1)+"行,姓名未填写)");
            }

            row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第二个单元格的值

            String studentsex = row.getCell(5).getStringCellValue();

            if(studentsex==null || studentsex.isEmpty()){
                throw new MyException("导入失败(第"+(r+1)+"行,性别未填写)");
            }

            row.getCell(6).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第二个单元格的值

            String studentgrade = row.getCell(6).getStringCellValue();

            if(studentgrade==null || studentgrade.isEmpty()){
                throw new MyException("导入失败(第"+(r+1)+"行,年级未填写)");
            }

            //完整的循环一次 就组成了一个对象
            student.setStudentdepartments(studentdepartments);
            student.setStudentclass(studentclass);
            student.setStudentprofessional(studentprofessional);
            student.setStudentnumber(studentnumber);
            student.setStudentname(studentname);
            student.setStudentsex(studentsex);
            student.setStudentgrade(studentgrade);
            student.setStudentpassword("123456");

            studentList.add(student);
        }
        for (Student userResord : studentList) {

            String studentnumber = userResord.getStudentnumber();  
            int cnt = studentMapper.selectBynumber(studentnumber).size();  //根据学生学号查询是否有此条信息
            if (cnt == 0) {
                studentMapper.insertStudentByExcel(userResord);  //如果没有此条信息则插入操作
                System.out.println(" 插入 "+userResord);
            } else {
                studentMapper.updateByNumber(userResord);  //如果有此条信息则更新操作
                System.out.println(" 更新 "+userResord);
            }
        }
        return notNull;
    }

 

6.mybatis

 <select id="selectBynumber" parameterType="com.sun.student.model.Student" resultMap="BaseResultMap">
    select *
    from student
    where StudentNumber = #{studentnumber,jdbcType=VARCHAR}
  </select>

  <update id="updateByNumber" parameterType="com.sun.student.model.Student">
    update student
    set StudentName = #{studentname,jdbcType=VARCHAR},
      StudentSex = #{studentsex,jdbcType=VARCHAR},
      StudentGrade = #{studentgrade,jdbcType=VARCHAR},
      StudentProfessional = #{studentprofessional,jdbcType=VARCHAR},
      StudentDepartments = #{studentdepartments,jdbcType=VARCHAR},
      StudentClass = #{studentclass,jdbcType=VARCHAR}
    where StudentNumber = #{studentnumber,jdbcType=VARCHAR}
  </update>


  <insert id="insertStudentByExcel" parameterType="com.sun.student.model.Student">
    insert into student (StudentNumber, StudentName, StudentSex,
      StudentGrade, StudentProfessional, StudentDepartments,
      StudentClass, StudentPassword)
    values (#{studentnumber,jdbcType=VARCHAR}, #{studentname,jdbcType=VARCHAR}, #{studentsex,jdbcType=VARCHAR},
      #{studentgrade,jdbcType=VARCHAR}, #{studentprofessional,jdbcType=VARCHAR}, #{studentdepartments,jdbcType=VARCHAR},
      #{studentclass,jdbcType=VARCHAR}, #{studentpassword,jdbcType=VARCHAR})
  </insert>

7.表格结构

8.前端  本人使用的是vue.js

<form id="uploadForm" enctype="multipart/form-data">
    <input type="file" name="filename" class="fileinput btn-lg" @change="test"/>
    <button type="button" @click="onUpload"
            class="fileinput_button btn btn-lg btn-success">开始上传
    </button>
</form>

 //批量导入学生信息
        onUpload: function (e) {
            console.log(app.fileStudentSplit)
            if (app.fileStundetSize >= 2000) {
                alert("文件过大，请小于2M！");
                return
            } else if (app.fileStudentSplit != "xls" && app.fileStudentSplit != "xlsx") {
                alert("上传格式错误！");
                return
            } else {
                $.ajax({
                    url: 'http://localhost:8080/Student/import',
                    type: 'POST',
                    cache: false,
                    data: new FormData($('#uploadForm')[0]),
                    processData: false,
                    contentType: false
                }).done(function (res) {
                    console.log(res.toLowerCase().split('.').splice(-1));
                    if (res == 1) {
                        alert("恭喜你上传成功！");
                        $.ajax({
                            type: "GET",
                            url: 'http://localhost:8080/Student/selectAll?' + 'currentPage=' + app.currentPage + '&pageSize=' + app.pageSize,
                            dataType: "json",
                            contentType: "application/json",
                            success: function (e) {
                                console.log(e);
                                app.option = 1;
                                app.students = e;
                            }
                        });
                    }
                }).fail(function (res) {
                    console.log(res);
                });
            }

        },

 

可以根据项目需求更改代码