前言

EasyExcel 批量導(dǎo)入并校驗(yàn)數(shù)據(jù)

一、pom

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

二、使用步驟

1.導(dǎo)入對(duì)象

日期形式的字段因?yàn)樾ｒ?yàn)需要，提供了String類型的字段，再轉(zhuǎn)換賦值給真正的數(shù)據(jù)庫(kù)字段對(duì)象，如果不考慮校驗(yàn)問(wèn)題可直接轉(zhuǎn)換@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;

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

    private static final long serialVersionUID = 370622351109421619L;


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

    @ApiModelProperty("企業(yè)名稱")
    @Length(max = 30, message = "企業(yè)名稱：最多可輸入30個(gè)字")
    @NotBlank(message = "企業(yè)名稱不能為空")
    @ExcelProperty(index = 0, value = "企業(yè)名稱")
    private String companyName;

    @ApiModelProperty("統(tǒng)一社會(huì)信用代碼")
    @Length(min = 18, max = 18, message = "統(tǒng)一社會(huì)信用代碼必須18位")
    @NotBlank(message = "統(tǒng)一社會(huì)信用代碼不能為空")
    @ExcelProperty(index = 1, value = "統(tǒng)一社會(huì)信用代碼")
    private String creditCode;

    @ExcelProperty(index = 2, value = "行政區(qū)劃--地市")
    @NotBlank(message = "行政區(qū)劃--地市不能為空")
    private String districtCodeCity;

    @ExcelProperty(index = 3, value = "行政區(qū)劃--區(qū)/縣")
    private String districtCodeCountry;

    @ApiModelProperty("行政區(qū)劃")
    @NotBlank(message = "行政區(qū)劃不能為空")
    @ExcelIgnore
    private String districtCode;

    @NotBlank(message = "失信行為類別不能為空")
    @ExcelProperty(index = 4, value = "失信行為類別")
    private String typeCodeName;

    @ApiModelProperty("失信行為類別 0一般失信行為 1-嚴(yán)重失信行為")
    @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("信用修復(fù)部門(mén)")
    @Length(max = 20, message = "信用修復(fù)部門(mén)：最多可輸入20個(gè)字")
    @ExcelIgnore
    private String repairDepartment;

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

    @ApiModelProperty("狀態(tài) 0-未修復(fù) 1-已修復(fù)")
    @ExcelIgnore
    private String status;

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

}

2.讀入數(shù)據(jù)并保存

讀取數(shù)據(jù)后Validation校驗(yàn)，校驗(yàn)通過(guò)直接保存（如果數(shù)據(jù)已經(jīng)存在，則copy excel外其他字段后刪除原數(shù)據(jù)，導(dǎo)入新數(shù)據(jù)），校驗(yàn)失敗則返回失敗行數(shù)（這里也可以導(dǎo)出校驗(yàn)失敗詳情）

默認(rèn)規(guī)則：設(shè)置excel最大導(dǎo)入數(shù)據(jù)行數(shù)為LIST_COUNT = 1000;，
如果需要導(dǎo)入更多數(shù)據(jù)，改大這個(gè)值即可，也可invoke時(shí)分批讀取數(shù)據(jù)
但是每次執(zhí)行完invoke后都會(huì)執(zhí)行doAfterAllAnalysed下的saveData，那么校驗(yàn)邏輯將只針對(duì)本批次數(shù)據(jù)進(jìn)行校驗(yàn)，如校驗(yàn)失敗，會(huì)直接返回給前端。后續(xù)批次由于異常被拋出不會(huì)執(zhí)行（可更改校驗(yàn)邏輯，或錯(cuò)誤信息返回形式）

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);

    // 由于監(jiān)聽(tīng)器只能通過(guò)new的方式創(chuàng)建，所以可以通過(guò)構(gòu)造器傳入dao層對(duì)象
    private final CreditInfoService service;

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

    @Override
    public void invoke(CreditInfoExcelInReq req, AnalysisContext analysisContext) {
        //每讀取一行數(shù)據(jù)都會(huì)調(diào)用一次
        list.add(req);
        if (list.size() >= LIST_COUNT) {
            throw new ExcelAnalysisException("當(dāng)前excel數(shù)據(jù)量不得大于" + LIST_COUNT + "條！");
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        //所有數(shù)據(jù)解析完畢執(zhí)行該方法
        // 防止導(dǎo)入空的Excel
        if (analysisContext.readRowHolder().getRowIndex() <= 0) {
            throw new ExcelAnalysisException("當(dāng)前excel無(wú)數(shù)據(jù)!");
        }
        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);
            //用于存儲(chǔ)驗(yàn)證后的錯(cuò)誤信息
            if (validate.size() > 0) {
                errorRow.add(row.toString());
                //防止相同數(shù)據(jù)indexof定位錯(cuò)誤
                l.setId(UUID.randomUUID().toString());
            } else {
                //日期格式校驗(yàn)成功后再轉(zhuǎn)換punishTimeOri，否則直接報(bào)錯(cuò)
                l.setPunishTime(LocalDate.parse(l.getPunishTimeOri()));
                CreditInfoReq req = new CreditInfoReq();
                //構(gòu)造新數(shù)據(jù)覆蓋舊數(shù)據(jù)
                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());
                    //刪除舊數(shù)據(jù)
                    service.delete(opt.get().getId());
                }
                l.setId(service.add(req));
            }
            if (row - 1 == list.size() && errorRow.size() > 0) {
                throw new ExcelAnalysisException("部分導(dǎo)入成功，其中第" + String.join(",", errorRow) + "行導(dǎo)入失??！", 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 "全部導(dǎo)入成功！";
        } catch (IOException e) {
            throw new BusinessCheckException("Excel 文件流讀取失敗");
        } catch (ExcelAnalysisException e) {
            return e.getMessage();
        } catch (Exception e) {
            throw new BusinessException("數(shù)據(jù)導(dǎo)入失敗", e);
        }
    }

文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-479936.html

到了這里，關(guān)于EasyExcel 批量導(dǎo)入并校驗(yàn)數(shù)據(jù)的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！