读取Excel如何使用Validator进行校验,批量处理数据
EasyExcel读取文件如何使用Validator进行校验
我们通常使用EasyExcel读取数据时,需要写大量的if-else或者断言来确保excel中的数据格式以及内容是否符合我们的需要.而EasyExcel目前尚不支持Validator的校验,(我已经提了好久的issues,一直没加这个功能) https://github.com/alibaba/easyexcel/issues/2250
所以本次是通过ReadListener来实现通过Validator进行校验数据,批量处理数据,返回错误行数
基础环境
jdk17maven3.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);
}));
}