EasyExcel使用Validator进行读取校验,批量处理数据,返回处理异常的行以及列

EasyExcel使用Validator进行读取校验,批量处理数据,返回处理异常的行以及列

我们通常使用EasyExcel读取数据时,需要写大量的if-else或者断言来确保excel中的数据格式以及内容是否符合我们的需要.而EasyExcel目前尚不支持Validator的校验,(我已经提了好久的issues,一直没加这个功能) https://github.com/alibaba/easyexcel/issues/2250

所以本次是通过ReadListener来实现通过Validator进行校验数据,批量处理数据,返回错误行数

基础环境

• jdk17
• maven3.6+
• springboot3.0+|springboot2.0+
• easy-excel:3.2.0

依赖

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-validation</artifactId>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
</dependency>

自定义数据处理Listener

通常我们使用EasyExcel读取数据时,都需要自定义一 个ReadListener

当EasyExcel读取到数据的时候,会调用invoke方法，读取完成之后会调用doAfterAllAnalysed方法

我们具体的实现就是当EasyExcel调用invoke方法时,此时将得到的数据通过Validator进行校验完成之后,然后统一汇总然后再去处理(比如插入mysql等操作)

如果读校验出现异常,将具体错误的数据格的行与列返回给前端,让用户重新编辑再次上传

具体实现

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.ListUtils;
import com.github.mpcloud.framework.core.consts.SuppressWarningsName;
import com.github.mpcloud.framework.core.utils.excel.exception.ExcelDataValidException;
import jakarta.validation.ConstraintViolation;
import jakarta.validation.Validation;
import jakarta.validation.Validator;
import jakarta.validation.groups.Default;
import org.hibernate.validator.HibernateValidator;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.*;
import java.util.function.Consumer;

/**
 * The type Abstract valid listener.
 *
 * @param <T> the type parameter
 * @author : Milo
 */
@SuppressWarnings(SuppressWarningsName.UNUSED)
public class AnalysisValidReadListener<T> extends AnalysisEventListener<T> {
    private static final Logger log = LoggerFactory.getLogger(AnalysisValidReadListener.class);

    private final Integer batch;
    private final Consumer<List<T>> consumer;
    private final List<T> buffer;
    private final Validator validator;
    private Map<String, Integer> fieldColumnMapping;

    /**
     * Instantiates a new Abstract valid listener.
     */
    public AnalysisValidReadListener(final Consumer<List<T>> consumer) {
        this(500, consumer);
    }

    /**
     * Instantiates a new Jdbc event listener.
     *
     * @param batch the batch
     */
    public AnalysisValidReadListener(final Integer batch, final Consumer<List<T>> consumer) {
        this(batch, consumer, Validation.byProvider(HibernateValidator.class)
                .configure()
                .failFast(false)
                .buildValidatorFactory()
                .getValidator());
    }

    /**
     * Instantiates a new Jdbc event listener.
     *
     * @param batch     the batch
     * @param validator the validator
     */
    public AnalysisValidReadListener(final Integer batch, final Consumer<List<T>> consumer, final Validator validator) {
        this.buffer = ListUtils.newArrayListWithExpectedSize(batch);
        this.batch = batch;
        this.validator = validator;
        this.consumer = consumer;
    }

    @Override
    public void invoke(final T data, final AnalysisContext context) {
        this.validate(data, context);
        this.buffer.add(data);
        if (this.buffer.size() >= this.batch) {
            this.consumer.accept(this.buffer);
            this.buffer.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(final AnalysisContext context) {
        if (!this.buffer.isEmpty()) {
            this.consumer.accept(this.buffer);
            this.buffer.clear();
        }
    }

    /**
     * Valid.
     *
     * @param data    the data
     * @param context the context
     */
    protected final void validate(final T data, final AnalysisContext context) {
        final Optional<ConstraintViolation<T>> violationOptional = this.validator.validate(data, Default.class).stream().findFirst();
        if (violationOptional.isEmpty()) {
            return;
        }
        final ConstraintViolation<T> violation = violationOptional.get();
        final String field = violation.getPropertyPath().toString();
        final String message = violation.getMessage();
        final Object value = violation.getInvalidValue();
        final Integer rowIndex = context.readRowHolder().getRowIndex();
        final Integer columnIndex = this.getColumnIndex(field, context);
        if (log.isWarnEnabled()) {
            log.warn("Validate field:[{}],message:[{}],value:[{}]", field, message, value);
        }
        throw new ExcelDataValidException(rowIndex, columnIndex, message);
    }

    /**
     * 听过属性从context获取列号
     * @param field 属性名称
     * @param context excel上下文
     * @return 列号
     */
    private Integer getColumnIndex(final String field, final AnalysisContext context) {
        if (Objects.nonNull(this.fieldColumnMapping)) {
            return this.fieldColumnMapping.get(field);
        }
        synchronized (this) {
            if (Objects.nonNull(this.fieldColumnMapping)) {
                return this.fieldColumnMapping.get(field);
            }
            this.fieldColumnMapping = new HashMap<>(16);
            final Map<Integer, Head> excelFileHead = context.readSheetHolder().excelReadHeadProperty().getHeadMap();
            for (final Map.Entry<Integer, Head> entry : excelFileHead.entrySet()) {
                this.fieldColumnMapping.put(entry.getValue().getFieldName(), entry.getKey());
            }
            return this.fieldColumnMapping.get(field);
        }
    }
}

具体使用

目前就是使用自定义的Listener去读取Excel文件
具体代码

读取实体

import jakarta.validation.constraints.Email;
import jakarta.validation.constraints.NotEmpty;
import jakarta.validation.constraints.NotNull;
import lombok.Data;

import java.io.Serializable;
import java.util.Objects;
import java.util.StringJoiner;

/**
 * @author : Milo
 * @description :
 */
public class Student implements Serializable {
    @NotEmpty
    private String username;
    @NotNull
    private Integer age;
    @Email
    private String email;

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) {
            return true;
        }
        if (o == null || getClass() != o.getClass()) {
            return false;
        }
        Student student = (Student) o;
        return Objects.equals(getUsername(), student.getUsername()) && Objects.equals(getAge(), student.getAge()) && Objects.equals(getEmail(), student.getEmail());
    }

    @Override
    public int hashCode() {
        return Objects.hash(getUsername(), getAge(), getEmail());
    }

    @Override
    public String toString() {
        return new StringJoiner(", ", Student.class.getSimpleName() + "[", "]")
                .add("username='" + username + "'")
                .add("age=" + age)
                .add("email='" + email + "'")
                .toString();
    }
}

读取操作

注意:我这里的ReadMeta,ReadResult,是自定义的，这个可以根据你自己需要去实现
ReadMeta里面主要包含了一些数据,如数据流,实体映射的Excel类型,Excel密码(直接传入EasyExcelFactory也是可以的)

import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.github.mpcloud.framework.core.consts.Constant;
import com.github.mpcloud.framework.core.consts.SuppressWarningsName;
import com.github.mpcloud.framework.core.utils.excel.model.ReadMeta;
import com.github.mpcloud.framework.core.utils.excel.model.ReadResult;
import com.github.mpcloud.framework.core.utils.excel.model.WriteMeta;
import com.github.mpcloud.framework.core.utils.http.HttpMediaType;
import com.github.mpcloud.framework.core.utils.list.CollectionUtil;
import com.github.mpcloud.framework.core.utils.math.MathUtils;
import jakarta.servlet.http.HttpServletResponse;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.IOException;
import java.io.OutputStream;
import java.io.Serializable;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.List;
import java.util.Optional;

/**
 * The type Excel util.
 *
 * @author : Milo
 */
@SuppressWarnings(SuppressWarningsName.UNUSED)
public class ExcelUtil {
    private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);

    private ExcelUtil() {
    }

    /**
     * Read excel read result.
     *
     * @param <T>      the type parameter
     * @param meta     the meta {@link ReadMeta <T>}
     * @param listener the listener {@link AnalysisEventListener}
     * @return the read result
     */
    public static <T extends Serializable> ReadResult readExcel(final ReadMeta<T> meta, final AnalysisEventListener<T> listener) {
        ExcelReader reader = null;
        final ReadResult result = new ReadResult(Boolean.TRUE);
        try {
            reader = EasyExcelFactory.read(meta.getExcelStream(), meta.getClazz(), listener)
                    .headRowNumber(meta.getHeadRowNumber())
                    .password(meta.getPassword())
                    .ignoreEmptyRow(Boolean.TRUE)
                    .autoCloseStream(Boolean.TRUE)
                    .build();
            reader.readAll();
        } catch (final ExcelDataConvertException ex) {
            result.setSuccess(Boolean.FALSE);
            result.setFailMessage(ex.getRowIndex(), ex.getColumnIndex(), ex.getMessage());
            log.error(result.getMessage(), ex.getCause());
        } catch (final Exception ex) {
            result.setSuccess(Boolean.FALSE);
        } finally {
        Optional.ofNullable(reader).ifPresent(ExcelReader::finish);
        }
        return result;
    }
}

使用

public static void main(String[] args) throws FileNotFoundException {
    ReadMeta<Student> readMeta = ReadMeta.<Student>builder().clazz(Student.class).excelStream(new FileInputStream("")).build();
    ExcelUtil.readExcel(readMeta, new AnalysisValidReadListener<>(500, demos -> {
        // insert batch 
        // 这里标识每读取到500条数据进行打印
        // 生产中可以换成500条数据进行数据库插入
        System.out.println(demos);
    }));
}

具体代码地址

gitee地址

掘金地址

优快云地址

B站地址

我的博客