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}")@ExcelIgnoreprivate 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 = "行政区划不能为空")@ExcelIgnoreprivate String districtCode;@NotBlank(message = "失信行为类别不能为空")@ExcelProperty(index = 4, value = "失信行为类别")private String typeCodeName;@ApiModelProperty("失信行为类别 0一般失信行为 1-严重失信行为")@NotBlank(message = "失信行为类别不能为空")@ExcelIgnoreprivate 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")@ExcelIgnoreprivate LocalDate punishTime;@ApiModelProperty("信用修复部门")@Length(max = 20, message = "信用修复部门：最多可输入20个字")@ExcelIgnoreprivate String repairDepartment;@ApiModelProperty("信用修复完成日期")@JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")@ExcelIgnoreprivate LocalDate repairTime;@ApiModelProperty("状态 0-未修复 1-已修复")@ExcelIgnoreprivate String status;@ApiModelProperty("备注")@Length(max = 300, message = "备注：最多300字")@ExcelIgnoreprivate 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;}@Overridepublic void invoke(CreditInfoExcelInReq req, AnalysisContext analysisContext) {//每读取一行数据都会调用一次list.add(req);if (list.size() >= LIST_COUNT) {throw new ExcelAnalysisException("当前excel数据量不得大于" + LIST_COUNT + "条！");}}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {//所有数据解析完毕执行该方法// 防止导入空的Excelif (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);}}