EasyExcel 批量导入并校验数据

前言

EasyExcel 批量导入并校验数据

一、pom

      <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.7</version>
        </dependency>

二、使用步骤

1.导入对象

日期形式的字段因为校验需要，提供了String类型的字段，再转换赋值给真正的数据库字段对象，如果不考虑校验问题可直接转换@ExcelProperty(value = "处罚信息公示日期", index = 5, converter = LocalDateConverter.class)

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.hibernate.validator.constraints.Length;

import javax.validation.constraints.NotBlank;
import javax.validation.constraints.Pattern;
import java.io.Serializable;
import java.time.LocalDate;

/**
 * 信用信息修复
 *
 * @author huaiyu.zhang
 * @since 2023-04-19 10:59:49
 */
@Builder
@Data
@AllArgsConstructor
@NoArgsConstructor
@ApiModel("信用信息修复")
public class CreditInfoExcelInReq implements Serializable {

    private static final long serialVersionUID = 370622351109421619L;


    @ApiModelProperty("${column.comment}")
    @ExcelIgnore
    private String id;

    @ApiModelProperty("企业名称")
    @Length(max = 30, message = "企业名称：最多可输入30个字")
    @NotBlank(message = "企业名称不能为空")
    @ExcelProperty(index = 0, value = "企业名称")
    private String companyName;

    @ApiModelProperty("统一社会信用代码")
    @Length(min = 18, max = 18, message = "统一社会信用代码必须18位")
    @NotBlank(message = "统一社会信用代码不能为空")
    @ExcelProperty(index = 1, value = "统一社会信用代码")
    private String creditCode;

    @ExcelProperty(index = 2, value = "行政区划--地市")
    @NotBlank(message = "行政区划--地市不能为空")
    private String districtCodeCity;

    @ExcelProperty(index = 3, value = "行政区划--区/县")
    private String districtCodeCountry;

    @ApiModelProperty("行政区划")
    @NotBlank(message = "行政区划不能为空")
    @ExcelIgnore
    private String districtCode;

    @NotBlank(message = "失信行为类别不能为空")
    @ExcelProperty(index = 4, value = "失信行为类别")
    private String typeCodeName;

    @ApiModelProperty("失信行为类别 0一般失信行为 1-严重失信行为")
    @NotBlank(message = "失信行为类别不能为空")
    @ExcelIgnore
    private String typeCode;

    @ExcelProperty(index = 5, value = "处罚信息公示日期")
    @Pattern(regexp = "[0-9]{4}-[0-9]{2}-[0-9]{2}", message = "处罚信息公示日期格式必须为yyyy-MM-dd")
    private String punishTimeOri;

    @ApiModelProperty("处罚信息公示日期")
    @JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
    @ExcelIgnore
    private LocalDate punishTime;

    @ApiModelProperty("信用修复部门")
    @Length(max = 20, message = "信用修复部门：最多可输入20个字")
    @ExcelIgnore
    private String repairDepartment;

    @ApiModelProperty("信用修复完成日期")
    @JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
    @ExcelIgnore
    private LocalDate repairTime;

    @ApiModelProperty("状态 0-未修复 1-已修复")
    @ExcelIgnore
    private String status;

    @ApiModelProperty("备注")
    @Length(max = 300, message = "备注：最多300字")
    @ExcelIgnore
    private String mark;

}

2.读入数据并保存

读取数据后Validation校验，校验通过直接保存（如果数据已经存在，则copy excel外其他字段后删除原数据，导入新数据），校验失败则返回失败行数（这里也可以导出校验失败详情）

默认规则：设置excel最大导入数据行数为LIST_COUNT = 1000;，
如果需要导入更多数据，改大这个值即可，也可invoke时分批读取数据
但是每次执行完invoke后都会执行doAfterAllAnalysed下的saveData，那么校验逻辑将只针对本批次数据进行校验，如校验失败，会直接返回给前端。后续批次由于异常被抛出不会执行（可更改校验逻辑，或错误信息返回形式）

Listener 端代码：

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelAnalysisException;
import com.gsafety.bg.sv.model.dto.req.CreditInfoExcelInReq;
import com.gsafety.bg.sv.model.dto.req.CreditInfoReq;
import com.gsafety.bg.sv.model.dto.resp.BasDistrictResp;
import com.gsafety.bg.sv.model.po.CreditInfoPO;
import com.gsafety.bg.sv.service.CreditInfoService;
import com.gsafety.bg.sv.service.constant.CreditTypeEnum;
import com.gsafety.bg.sv.service.utils.MappingConvertUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.ObjectUtils;
import org.springframework.beans.BeanUtils;

import javax.validation.ConstraintViolation;
import javax.validation.Validation;
import java.time.LocalDate;
import java.util.*;

/**
 * @author huaiyu.zhang
 * @since 2023-6-5 0005 17:10
 */

@Slf4j
public class CreditInfoExcelDataListener extends AnalysisEventListener<CreditInfoExcelInReq> {

    private final Integer LIST_COUNT = 1000;

    List<CreditInfoExcelInReq> list = new ArrayList<>(LIST_COUNT);

    // 由于监听器只能通过new的方式创建，所以可以通过构造器传入dao层对象
    private final CreditInfoService service;

    public CreditInfoExcelDataListener(CreditInfoService service) {
        this.service = service;
    }

    @Override
    public void invoke(CreditInfoExcelInReq req, AnalysisContext analysisContext) {
        //每读取一行数据都会调用一次
        list.add(req);
        if (list.size() >= LIST_COUNT) {
            throw new ExcelAnalysisException("当前excel数据量不得大于" + LIST_COUNT + "条！");
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        //所有数据解析完毕执行该方法
        // 防止导入空的Excel
        if (analysisContext.readRowHolder().getRowIndex() <= 0) {
            throw new ExcelAnalysisException("当前excel无数据!");
        }
        saveData();
    }

    protected void saveData() {
        Set<String> errorRow = new HashSet<>();
        List<BasDistrictResp> districtList = MappingConvertUtil.getDistrictList();
        list.forEach(l -> {
            Integer row = list.indexOf(l) + 2;
            if (ObjectUtils.isEmpty(l.getDistrictCodeCountry())) {
                Optional<BasDistrictResp> opt = districtList.stream().filter(d -> l.getDistrictCodeCity().equals(d.getDistName())).findFirst();
                if (!opt.isPresent()) {
                    errorRow.add(row.toString());
                } else {
                    l.setDistrictCode(opt.get().getDistCode());
                }
            } else {
                String parentCode = districtList.stream().filter(d -> l.getDistrictCodeCity().equals(d.getDistName())).findFirst()
                        .orElse(BasDistrictResp.builder().distCode("").build()).getDistCode();
                Optional<BasDistrictResp> opt = districtList.stream().filter(d -> parentCode.equals(d.getParentCode()) && l.getDistrictCodeCountry().equals(d.getDistName())).findFirst();
                if (!opt.isPresent()) {
                    errorRow.add(row.toString());
                } else {
                    l.setDistrictCode(opt.get().getDistCode());
                }
            }
            l.setTypeCode(CreditTypeEnum.getCode(l.getTypeCodeName()));
            Set<ConstraintViolation<Object>> validate = Validation.buildDefaultValidatorFactory().getValidator().validate(l);
            //用于存储验证后的错误信息
            if (validate.size() > 0) {
                errorRow.add(row.toString());
                //防止相同数据indexof定位错误
                l.setId(UUID.randomUUID().toString());
            } else {
                //日期格式校验成功后再转换punishTimeOri，否则直接报错
                l.setPunishTime(LocalDate.parse(l.getPunishTimeOri()));
                CreditInfoReq req = new CreditInfoReq();
                //构造新数据覆盖旧数据
                BeanUtils.copyProperties(l, req);
                Optional<CreditInfoPO> opt = service.loadByCreditCode(l.getCreditCode());
                if (opt.isPresent()) {
                    req.setRepairTime(opt.get().getRepairTime());
                    req.setStatus(opt.get().getStatus());
                    req.setMark(opt.get().getMark());
                    req.setRepairDepartment(opt.get().getRepairDepartment());
                    //删除旧数据
                    service.delete(opt.get().getId());
                }
                l.setId(service.add(req));
            }
            if (row - 1 == list.size() && errorRow.size() > 0) {
                throw new ExcelAnalysisException("部分导入成功，其中第" + String.join(",", errorRow) + "行导入失败！", null);
            }
        });
    }
}

service 端代码：

    public String importData(MultipartFile file) {
        CreditInfoExcelDataListener listener = new CreditInfoExcelDataListener(this);
        InputStream inputStream;
        try {
            inputStream = file.getInputStream();
            EasyExcel.read(inputStream, CreditInfoExcelInReq.class,
                    listener).sheet().doRead();
            return "全部导入成功！";
        } catch (IOException e) {
            throw new BusinessCheckException("Excel 文件流读取失败");
        } catch (ExcelAnalysisException e) {
            return e.getMessage();
        } catch (Exception e) {
            throw new BusinessException("数据导入失败", e);
        }
    }