Excel封装EasyExcel导入数据，采用validete，并且进行导出Excel文件上传

1，建立监听事件者

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.xxx.common.exception.ServiceException;
import lombok.Data;
import org.apache.commons.lang3.StringUtils;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Consumer;

/**
 * @Author:miaoshaoxuan
 * @Description:
 * @Date 2021/5/12
 */
public class JdbcEventListener<T> extends AnalysisEventListener<T> {
    /**
     * Excel总条数阈值
     */
    private static final Integer MAX_SIZE = 10000;
    /**
     * 校验工具
     */
    private final ExcelValidator<T> excelValidator;
    /**
     * 如果校验通过消费解析得到的excel数据
     */
    private final Consumer<T> consumer;
    /**
     * 解析数据的临时存储容器
     */
    private final List<T> list = new ArrayList<>();
    private final Map<Integer, ErrorData<T>> errorMap = new HashMap<>();

    /**
     * 上传数据消费者
     */
    private final Consumer<Map<Integer, ErrorData<T>>> uploadConsumer;

    public JdbcEventListener(ExcelValidator<T> excelValidator, Consumer<T> consumer, Consumer<Map<Integer, ErrorData<T>>> uploadConsumer){
        this.excelValidator = excelValidator;
        this.consumer = consumer;
        this.uploadConsumer = uploadConsumer;
    }

    @Override
    public void invoke(T data, AnalysisContext context) {
        if(this.list.size() > MAX_SIZE){
            throw new ServiceException("单次上传次数不能大于:" + MAX_SIZE);
        }
        this.list.add(data);
        Integer rowIndex = context.readRowHolder().getRowIndex();
        String str = excelValidator.doValidate(rowIndex, data);
        ErrorData<T> errorData = new ErrorData<>();
        if(StringUtils.isNotBlank(str)){
            errorData.setData(data);
            errorData.setErrorMsg(str);
            errorMap.put(rowIndex, errorData);
            return;
        }
        try {
            this.consumer.accept(data);
        } catch (Exception e){
            errorData.setData(data);
            errorData.setErrorMsg(str);
            errorMap.put(rowIndex, errorData);
            return;
        }
        errorData.setData(data);
        errorMap.put(rowIndex, errorData);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        uploadConsumer.accept(this.errorMap);
    }

    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {
        this.errorMap.clear();
        this.list.clear();
        throw exception;
    }


    @Data
    public static class ErrorData<T> {
        private T data;
        private String errorMsg = "";
    }
}

2，Excel-Validated

import cn.afterturn.easypoi.excel.annotation.Excel;
import com.google.common.collect.Lists;
import lombok.AllArgsConstructor;
import org.apache.commons.lang3.StringUtils;

import javax.validation.ConstraintViolation;
import javax.validation.Validator;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Set;
import java.util.stream.Collectors;

/**
 * @Author:miaoshaoxuan
 * @Description: Excel 校验
 * @Date 2021/5/12
 */
@AllArgsConstructor
public class ExcelValidator<T> {
    private final Validator validator;
    private final Integer beginIndex;

    public List<String> validate(Collection<T> data){
        int index = beginIndex + 1;
        ArrayList<String> messages = Lists.newArrayList();
        for (T datum : data) {
            String validated = this.doValidate(index, datum);
            if(StringUtils.isNotBlank(validated)){
                messages.add(validated);
            }
            index++;
        }
        return messages;
    }

    public String doValidate(int index, T data) {
        Set<ConstraintViolation<T>> validate = validator.validate(data, Excel.class);
        return validate.size() > 0 ? "第" + (index) + "行,触发约束：" + validate.stream()
                .map(ConstraintViolation::getMessage).collect(Collectors.joining(",")) : "";
    }

}

3，读Excel

import com.alibaba.excel.EasyExcel;
import lombok.AllArgsConstructor;
import lombok.Data;
import org.springframework.stereotype.Component;

import javax.validation.Validator;
import java.io.InputStream;
import java.util.Map;
import java.util.function.Consumer;

/**
 * @Author:miaoshaoxuan
 * @Description:
 * @Date 2021/5/12
 */
@AllArgsConstructor
@Component
public class ExcelReader {
    private final Validator validator;

    public <T> void read(Mate<T> mate){
        ExcelValidator<T> excelValidator = new ExcelValidator<T>(this.validator, mate.getHeadRowNumber());
        JdbcEventListener<T> readListener = new JdbcEventListener<>(excelValidator, mate.getConsumer(), mate.getUploadCustomer());
        EasyExcel.read(mate.getInputStream(), mate.getDomain(), readListener).headRowNumber(mate.getHeadRowNumber()).sheet().doRead();
    }

    @Data
    public static class Mate<T> {
        private InputStream inputStream;
        private Integer headRowNumber;
        private Class<T> domain;
        private Consumer<T> consumer;
        private Consumer<T> consumerValidator;
        private Consumer<Map<Integer, JdbcEventListener.ErrorData<T>>> uploadCustomer = o -> {};
    }

}

测试代码

  @Autowired
    private ExcelReader excelReader;

    @Autowired
    private IImportExcelTestService importExcelTestService;

    @PostMapping
    public Result<Boolean> importData(@RequestPart MultipartFile file) throws IOException {
        ExcelReader.Mate<ImportExcelTest> excelDataMate = new ExcelReader.Mate<>();
        excelDataMate.setInputStream(file.getInputStream());
        excelDataMate.setDomain(ImportExcelTest.class);
        excelDataMate.setHeadRowNumber(1);
        excelDataMate.setConsumer(importExcelTestService::saveImport);
        excelDataMate.setUploadCustomer(importExcelTestService::uploadExcelResult);
        excelReader.read(excelDataMate);
        return Result.success();
    }

/**
 * <p>
 *  服务类
 * </p>
 *
 * @author Miaoshaoxuan
 * @since 2021-05-17
 */
public interface IImportExcelTestService extends IService<ImportExcelTest> {

    void saveImport(ImportExcelTest importExcelTests);

    void uploadExcelResult(Map<Integer, JdbcEventListener.ErrorData<ImportExcelTest>> integerErrorDataMap);
}

/**
 * <p>
 *  服务实现类
 * </p>
 *
 * @author Miaoshaoxuan
 * @since 2021-05-17
 */
@Service
public class ImportExcelTestServiceImpl extends ServiceImpl<ImportExcelTestMapper, ImportExcelTest> implements IImportExcelTestService {

    @Override
    public void saveImport(ImportExcelTest importExcelTests) {
        System.out.println(JSON.toJSONString(importExcelTests));
    }

    @Override
    public void uploadExcelResult(Map<Integer, JdbcEventListener.ErrorData<ImportExcelTest>> integerErrorDataMap) {
        System.out.println(integerErrorDataMap);
    }
}

/**
 * <p>
 * 
 * </p>
 *
 * @author Miaoshaoxuan
 * @since 2021-05-17
 */
@Data
@EqualsAndHashCode(callSuper = true)
@TableName("t_import_excel_test")
public class ImportExcelTest extends AbstractEntity {

    private static final long serialVersionUID = 1L;

    private Long id;

    @NotBlank(message = "名称不可为空", groups = Excel.class)
    private String name;

    @Size(max = 10)
    private String comment;


    public static final String ID = "id";

    public static final String NAME = "name";

    public static final String COMMENT = "comment";

}