Springboot+Vue+Easyexcel实现导入并存储数据库

需求：实现Vue上传文件，后端Springboot校验数据是否正确，如果错误，则返回错误数据详情给前端页面，展示给用户，并触发事务回滚。

一、Vue部分

其中action是后端的请求url；on-success 文件上传成功时的钩子 ；on-error 文件上传失败时的钩子

		<div v-if="ifSecond" class="dm_sure1" style="margin-right: 0.1rem" @click="importTable()">
            导入
        </div>

          <el-dialog :visible.sync="dialogImportVisible" title="导入" width="30%">
            <el-form label-width="120px">
              <el-form-item label="分类文件">
                <el-upload class="upload-demo" action="http://localhost:8083/dealer/import" :on-success="uploadSuccess"
                  :on-error="uploadError" :headers="headers">
                  <el-button type="primary">上传</el-button>
                </el-upload>
              </el-form-item>
            </el-form>
          </el-dialog>

methods:{
	importTable() {
        this.dialogImportVisible = true
      },
    uploadSuccess() {
        this.dialogImportVisible = false
        this.$message.success("上传成功")
      },
    uploadError(response) {
        this.$message.error("上传失败")
        //解析后端返回的异常信息，页面显示
        const jsonString = response.message.replace(/^Error: /, '');
        const res = JSON.parse(jsonString);
        //根据数据双向绑定，使用element的Dialog对话框显示异常信息
        this.errorMessage = res.message;
        this.dialogVisible1 = true
      },
}

二、Springboot部分

1、首先需要定义实体类，对应数据库和excel（excel表中没有的字段，例如删除状态，直接定义即可）。注意⚠️：如果数据库不是下划线命名方式，必须使用@TableField注解指定数据库字段名

@Data
@TableName("dealer_management")
public class ImportDealerManagement {
    /**
     * 总代理
     */
    @ExcelProperty("总代理名称")
    private String agent;

    /**
     * 总代理授权区域
     */
    @ExcelProperty("总代理授权区域")
    @TableField("agentEmpowerArea")
    private String agentEmpowerArea;
    /**
     * 星级授权月份
     */
    @ExcelProperty("授权月份")
    @TableField("secEmpowerYear")
    private String secEmpowerYear;
    
    /**
     * 删除状态
     */
	@TableField("state")
    private String state = "0";
}

2、Controller。其中EasyExcel.read方法的第三个参数为：调用easyexcel的监听器，通过监听器的构造方法，传入需要使用的mapper、service等，因为监听器不能被spring管理，不能使用Bean的自动注入，所以在controller注入后，通过构造方法传递给监听器。

	@Transactional(rollbackFor = Exception.class)
    @PostMapping("import")
    public ResponseEntity<Object> importData(MultipartFile file) throws IOException {
            EasyExcel.read(file.getInputStream(), ImportDealerManagement.class, new ExcelListener(importDealerManagementService,dealerManagementMapper,procityMapper,cityMapper)).sheet().doRead();
            return new ResponseEntity<>("导入成功",HttpStatus.OK);
    }

3、定义service接口，继承Iservice接口

public interface ImportDealerManagementService extends IService<ImportDealerManagement> {
}

4、定义service实现类

@Service
public class ImportDealerManagementServiceImpl extends ServiceImpl<ImportDealerManagementMapper, ImportDealerManagement> implements ImportDealerManagementService {
}

5、定义mapper

@Mapper
public interface ImportDealerManagementMapper extends BaseMapper<ImportDealerManagement> {
}

6、自定义监听器，继承ReadListener的子类

@Slf4j
public class ExcelListener<T> extends AnalysisEventListener<T> {
    /**
     * 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 20;
    /**
     * 缓存的数据
     */
    private List<ImportDealerManagement> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

    private ImportDealerManagementService importDealerManagementService;
    private DealerManagementMapper dealerManagementMapper;
    private ProcityMapper procityMapper;
    private CityMapper cityMapper;

    public ExcelListener(ImportDealerManagementService importDealerManagementService, DealerManagementMapper dealerManagementMapper, ProcityMapper procityMapper, CityMapper cityMapper) {
        this.importDealerManagementService = importDealerManagementService;
        this.dealerManagementMapper = dealerManagementMapper;
        this.procityMapper = procityMapper;
        this.cityMapper = cityMapper;
    }

    private String startAgent;

    //每读取一行执行一次
    @Override
    public void invoke(T t, AnalysisContext analysisContext) {
        ImportDealerManagement data = (ImportDealerManagement) t;
        //获取行号
        Integer rowIndex = analysisContext.readRowHolder().getRowIndex();
        //获取读取内容
        String agent = data.getAgent();
        //数据库操作，需求是覆盖掉原本的，所以先根据agent删除数据库数据
        if (StringUtils.isNotBlank(agent) && !agent.equals(startAgent)) {
            dealerManagementMapper.deleteByAgent(agent);
            startAgent = agent;
        }
        //调用方法，校验所有数据通过后，才可以执行添加操作，校验不通过则抛出异常，触发事物回滚
        if (agentExit(rowIndex, agent)
                && secNameExit(rowIndex, data.getSecDealerName(), agent) && provinceExitInTable(rowIndex, data.getSecProvince())
                && cityExitInTable(rowIndex, data.getSecCity())) {
            cachedDataList.add(data);
        }
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            //使用Iservice的saveBatch方法批量存数据库
            importDealerManagementService.saveBatch(cachedDataList);
            // 存储完成清理 list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    private Boolean agentExit(Integer rowIndex, String agent) {
        if (StringUtils.isBlank(agent)) {
            throw new MyException(500, "第" + rowIndex + "行，“总代理”名称不能为空");
        }
        Integer integer = dealerManagementMapper.countByAgent(agent);
        if (integer < 1) {
            throw new MyException(500, "第" + rowIndex + "行，”总代理”名称不存在");
        }
        return Boolean.TRUE;
    }

    private boolean secNameExit(Integer rowIndex, String secDealerName, String agent) {
        if (StringUtils.isBlank(secDealerName)) {
            throw new MyException(500, "第" + rowIndex + "行，“星级经销商”名称不能为空");
        }
        Integer integer = dealerManagementMapper.countSec(secDealerName, agent);
        if (integer > 0) {
            throw new MyException(500, "第" + rowIndex + "行，”星级经销商“名称已存在");
        }
        return Boolean.TRUE;
    }


    private boolean provinceExitInTable(Integer rowIndex, String procity) {
        if (StringUtils.isBlank(procity)) {
            throw new MyException(500, "第" + rowIndex + "行，”所属省份“不能为空");
        }
        QueryWrapper<Procity> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("name", procity);
        Long aLong = procityMapper.selectCount(queryWrapper);
        if (aLong > 0) {
            return Boolean.TRUE;
        }
        throw new MyException(500, "第" + rowIndex + "行，”所属省份“不存在");
    }

    private boolean cityExitInTable(Integer rowIndex, String city) {
        if (StringUtils.isBlank(city)) {
            throw new MyException(500, "第" + rowIndex + "行，”所属市“不能为空");
        }
        QueryWrapper<City> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("name", city);
        Long aLong = cityMapper.selectCount(queryWrapper);
        if (aLong > 0) {
            return Boolean.TRUE;
        }
        throw new MyException(500, "第" + rowIndex + "行，”所属市“不存在");
    }


    //读取所有内容后执行
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
    //再次执行save操作，保存剩余的数据
        importDealerManagementService.saveBatch(cachedDataList);
    }

}

7、全局异常处理，用于返回校验excel数据时的异常信息给前端，如：“第一行名称不为空”

首先，自定义异常类，继承RuntimeException（Exception的子类）。注意⚠️：这里不要重写message的getter 方法。

public class MyException extends RuntimeException {
    private Integer code;
    private String message;

    public MyException(Integer code, String message) {
        super(message);
        this.code = code;
    }

    public Integer getCode() {
        return code;
    }

    public void setCode(Integer code) {
        this.code = code;
    }
}

然后，定义一个controller，用于返回给前端

@RestControllerAdvice
public class GlobalExceptionHandler {

    /**
     * 处理自定义异常
     */
    @ExceptionHandler(MyException.class)
    public ResponseEntity<Object> handleMyCustomException(MyException ex) {
        // 这里可以构建你的响应体，包含错误消息、错误码等
        Map<String, Object> body = new HashMap<>();
        body.put("message", ex.getMessage());
        body.put("errorCode", ex.getCode());

        // 返回HTTP状态码和响应体
        return new ResponseEntity<>(body, HttpStatus.INTERNAL_SERVER_ERROR);
    }
}