springboot项目配置导入excel文件将数据保存至数据库

参考博客：https://blog.youkuaiyun.com/qq_28288835/article/details/109025117

pom.xml

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

java代码：

@Override
    @Transactional(rollbackFor = Exception.class)
    public ResultJson importAssess(MultipartFile file, Map<String, Object> params) throws IOException {
		if(file == null){
            return new ResultJson<>(400, "导入文件不能为空");
        }
        String fileName=file.getOriginalFilename();
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            return new ResultJson<>(400, "上传文件格式不正确");
        }
        Map<String, Object> listResult = importDataHandle(file);
        Boolean state = (Boolean) listResult.get("state");
        if(!state){
            return new ResultJson<>(Constant.ERROR_CODE400, listResult.get("msg").toString());
        }
        List<Assess> assessList = (List<Assess>) listResult.get("data");
        List<Assess> insertList = new ArrayList<>();
        List<Assess> updateList = new ArrayList<>();
        for(Assess assess : assessList){
	        // 处理数据...
		}

        try{
            if(!insertList.isEmpty()){
                int res = assessMapper.insertAssessAll(insertList);
                if(res <= 0){
                    throw new IOException("添加信息失败");
                }
            }
            if(!updateList.isEmpty()){
                int res = assessMapper.updateAssessAll(updateList);
                if(res <= 0){
                    throw new IOException("修改信息失败");
                }
            }
        }catch (RuntimeException e){
            return new ResultJson<>(Constant.ERROR_CODE600, Constant.ERROR_MSG600);
        }
        return null;
	}
/**
     * 导入数据处理
     */
    private Map<String, Object> importDataHandle(MultipartFile file){
        List<Assess> assessList = new ArrayList<>();

        Map<String, Object> result = new HashMap<>();
        result.put("state", true);
        result.put("msg", "");

        InputStream is = null;
        Workbook wb = null;
        try {
            //读取文件流
            is = file.getInputStream();
            //文件名
            String fileName=file.getOriginalFilename();
            boolean notNull = false;
            if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
                //xlsx格式
                wb = new XSSFWorkbook(is);
            } else {
                //xls格式
                wb = new HSSFWorkbook(is);
            }
            if (wb != null) {
                //默认读取第一个sheet
                Sheet sheet = wb.getSheetAt(0);
                if (sheet != null) {
                    //最先读取首行
                    boolean firstRow = true;
                    boolean isThrow = false;

                    try {
                        if (sheet.getLastRowNum() > 0) {
                            for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
                                //循环行
                                Assess assess = new Assess();
                                Row row = sheet.getRow(i);
                                //首行  提取注解 验证格式是否正确
                                if (firstRow) {
                                    firstRow = false;
                                }else{
                                    //忽略空白行
                                    if (row == null) {
                                        continue;
                                    }
                                    int theRow = i + 1;

                                    if (row.getCell(0) != null) {
                                        row.getCell(0).setCellType(CellType.STRING);
                                        String name = row.getCell(0).getStringCellValue();
                                        if (ValidationForm.isNullOrEmpty(name)) {
                                            isThrow = true;
                                            result.put("msg", "导入失败(第" + theRow + "行,评估人不能为空)");
                                            throw new RuntimeException("导入失败(第" + theRow + "行,评估人不能为空)");
                                        } else {
                                            assess.setAssessUsername(name);
                                        }
                                    } else {
                                        isThrow = true;
                                        result.put("msg", "导入失败(第" + theRow + "行,评估人不能为空)");
                                        throw new RuntimeException("导入失败(第" + theRow + "行,评估人不能为空)");
                                    }

                                    // 姓名
                                    if (row.getCell(1) != null) {
                                        row.getCell(1).setCellType(CellType.STRING);
                                        String name = row.getCell(1).getStringCellValue();
                                        if (ValidationForm.isNullOrEmpty(name)) {
                                            isThrow = true;
                                            result.put("msg", "导入失败(第" + theRow + "行,姓名不能为空)");
                                            throw new RuntimeException("导入失败(第" + theRow + "行,姓名不能为空)");
                                        } else {
                                            assess.setName(name);
                                        }
                                    } else {
                                        isThrow = true;
                                        result.put("msg", "导入失败(第" + theRow + "行,姓名不能为空)");
                                        throw new RuntimeException("导入失败(第" + theRow + "行,姓名不能为空)");
                                    }
                                    
                                    // 民族
                                    if (row.getCell(2) != null) {
                                        row.getCell(2).setCellType(CellType.STRING);
                                        String value = row.getCell(2).getStringCellValue();
                                        if (!ValidationForm.isNullOrEmpty(value)) {
                                            assess.setNation(value);
                                        }
                                    }
                                    
                                    assessList.add(assess);
                                }

                            }
                        }else {
                            isThrow = true;
                            throw new RuntimeException("导入失败,数据为空");
                        }
                    }catch (Exception e) {
                        e.printStackTrace();
                        System.out.println(e.getMessage());
                    }
                    if(isThrow){
                        result.put("state", false);
                        return result;
                    }

                }
            }
        } catch (IOException e) {
            result.put("state", false);
            result.put("msg", "文件读取失败!");
            return result;
        } finally {
            // 关闭资源
            try {
                if (is != null) {
                    is.close();
                }
                if (wb != null) {
                    wb.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        if(!assessList.isEmpty()){
            result.put("data", assessList);
            return result;
        }else{
            result.put("state", false);
            result.put("msg", "导入失败,数据为空");
        }
        return result;
    }

批量新增、修改xml

<insert id="insertAssessAll" parameterType="com.example.server_assess.pros.assess.model.Assess">
        insert into tm_assess(
                              idno,
                              ...
                              name

        )
        values
        <foreach collection="assessList" separator="," index="index" item="item">
            (
                #{item.idno},
                ...
                #{item.name}
                
            )
        </foreach>
    </insert>
    <update id="updateAssessAll" parameterType="com.example.server_assess.pros.assess.model.Assess">
        <foreach collection="assessList" separator=";" item="item">
            update tm_assess
            <set>
                <if test="item.idno != null">
                    idno = #{item.idno},
                </if>
                <if test="item.name != null">
                    name = #{item.name},
                </if>
            </set>
            where id = #{item.id}
        </foreach>
    </update>