easyexcel 结合javax.validation实现excel导入校验

在使用easyexcel时发现是没有对单元格值校验的方式，我个人只发现了一个转换异常类ExcelDataConvertException，当单元格中的值转换不成实体类中改值所对应的属性类型时，会抛出改异常，onException中可以处理。但如果需要把一行中的所有错误的值都找到的话，就不能触发onException，官方文档原话如下：

在转换异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行

即如果触发了onException且不抛出异常，直接开始读取下一行数据

@Override
public void onException(Exception exception, AnalysisContext context){
    if(exception instanceof ExcelDataConvertException){
        ExcelDataConvertException e = (ExcelDataConvertException)exception;
        
    }
}

因为我们是要校验整个excel中所有错误数据，显然不能触发onException，所以我的做法是通过使用String类型接收所有单元格值，然后通过javax.validation注解的方式对值进行校验。在invoke中获取到data数据，然后对data进行校验，propertyNameMap是中的key是field的name，通过javax.validation中获取到的name从propertyNameMap中得到该属性在excel中的行号，列号等信息，用于最后的错误提示信息的封装

@ExcelProperty("地区")
@NotBlank(message = "区不能为空")
private String area;

public void invoke(E data, AnalysisContext analysisContext) {
    Map<String, ExcelCellBo> propertyNameMap = getPropertyNameMap(true,analysisContext);
    if (validate(data,propertyNameMap)) {
        dataList.add(data);
    }
}

 boolean validate(E e, Map<String, ExcelCellBo> propertyNameMap) {
    boolean validateResult = true;
    Set<ConstraintViolation<E>> validateSet = Validation.buildDefaultValidatorFactory().getValidator().validate(e, Default.class);
    if (validateSet != null && !validateSet.isEmpty()) {
        validateResult = false;
        ExcelErrorDTO errorDTO;
        for (ConstraintViolation<E> constraint : validateSet) {
            Path propertyPath = constraint.getPropertyPath();
            String propertyName = propertyPath.toString();
            ExcelCellBo bo = propertyNameMap.get(propertyName);
            errorDTO = new ExcelErrorDTO();
            errorDTO.setHeadName(bo.getHeadName());
            Object invalidValue = constraint.getInvalidValue();
            if (invalidValue != null) {
                errorDTO.setValue(invalidValue.toString());
            }else {
                errorDTO.setValue(null);
            }
            errorDTO.setColumnIndex(bo.getColumnIndex()+1);
            errorDTO.setRowIndex(bo.getRowIndex()+1);                  errorDTO.setErrMsg("第"+errorDTO.getRowIndex()+"第"+errorDTO.getColumnIndex()+"列,"+constraint.getMessage());
            errorList.add(errorDTO);
        }
    }
    return validateResult;
}

 

 Map<String, ExcelCellBo> getPropertyNameMap(boolean isSingleHeader, AnalysisContext analysisContext){
    Map<String, ExcelCellBo> propertyNameMap = new HashMap<>(16);
    ReadRowHolder readRowHolder = analysisContext.readRowHolder();
    Integer rowIndex = readRowHolder.getRowIndex();
    ReadHolder readHolder = analysisContext.currentReadHolder();
    ExcelReadHeadProperty excelReadHeadProperty = readHolder.excelReadHeadProperty();
    Collection<ExcelContentProperty> values;
    if (isSingleHeader){
        Map<Integer, ExcelContentProperty> contentPropertyMap = excelReadHeadProperty.getContentPropertyMap();
        values = contentPropertyMap.values();
    }else {
        //也适用于单行表头
        Map<String, ExcelContentProperty> fieldNameContentPropertyMap = excelReadHeadProperty.getFieldNameContentPropertyMap();
        values = fieldNameContentPropertyMap.values();
    }
    ExcelCellBo bo;
    for (ExcelContentProperty contentProperty : values) {
        bo = new ExcelCellBo();
        bo.setRowIndex(rowIndex);
        bo.setColumnIndex(contentProperty.getHead().getColumnIndex());
        bo.setFieldName(contentProperty.getHead().getFieldName());
        //多行表头
        bo.setHeadName(String.join(",",contentProperty.getHead().getHeadNameList()));
        bo.setField(contentProperty.getField());
        propertyNameMap.put(contentProperty.getHead().getFieldName(),bo);
    }
    return propertyNameMap;
}
@Data
public class ExcelCellBo {
    private Field field;
    private String fieldName;
    private String headName;
    private Integer columnIndex;
    private Integer rowIndex;
}

示例：

抽象父类代码：

package com.tzxx.common.domain.logic.excel;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.alibaba.excel.read.metadata.holder.ReadHolder;
import com.alibaba.excel.read.metadata.holder.ReadRowHolder;
import com.alibaba.excel.read.metadata.property.ExcelReadHeadProperty;
import com.tzxx.common.exception.BusinessException;
import lombok.Data;

import javax.validation.ConstraintViolation;
import javax.validation.Path;
import javax.validation.Validation;
import javax.validation.groups.Default;
import java.lang.reflect.Field;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author zhangliang
 * @date 2019/12/17.
 */
@Data
public abstract class AbstractDataListener<E> extends AnalysisEventListener<E>{
    protected List<E> dataList = new ArrayList<>();
    protected List<ExcelErrorDTO> errorList = new ArrayList<>();
    protected boolean success = true;
    protected ImportExcelResult<E, ExcelErrorDTO> result = new ImportExcelResult<>();
    protected List<String> headList = new ArrayList<>();
    protected boolean validate;


    protected Set<String> excelHeadNames = new HashSet<>();

    AbstractDataListener(List<String> headList,boolean validate){
        this.headList = headList;
        this.validate = validate;
    }

    AbstractDataListener(Class<E> c,boolean validate){
        Field[] declaredFields = c.getDeclaredFields();
        for (Field declaredField : declaredFields) {
            ExcelProperty annotation = declaredField.getAnnotation(ExcelProperty.class);
            if (annotation != null) {
                String[] value = annotation.value();
                headList.addAll(Arrays.asList(value));
            }
        }
        this.validate = validate;
    }

    @Override
    public void invokeHead(Map<Integer, CellData> headMap, AnalysisContext context) {
        Collection<CellData> head = headMap.values();
        excelHeadNames.addAll(head.stream().map(CellData::getStringValue).collect(Collectors.toList()));
        if (validate && !headList.containsAll(excelHeadNames)) {
            throw new BusinessException("导入的excel表头有误");
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        result.setData(getDataList());
        result.setError(getErrorList());
        result.setSuccess(getErrorList().isEmpty());
        if (getErrorList().isEmpty() && getDataList().isEmpty()){
            result.setSuccess(false);
            ExcelErrorDTO errorDTO = new ExcelErrorDTO();
            errorDTO.setErrMsg("excel无数据");
            errorList.add(errorDTO);
        }
    }

    @Override
    public void onException(Exception exception, AnalysisContext context){
        if(exception instanceof ExcelDataConvertException){
            ExcelDataConvertException e = (ExcelDataConvertException)exception;
            handleExcelDataConvertException(e);
        }else {
            throw new BusinessException(exception.getMessage());
        }
    }

    /**使用javax validate校验
     * @param e 需要校验的对象
     * @param propertyNameMap 对象excel数据
     * @return true
     */
     boolean validate(E e, Map<String, ExcelCellBo> propertyNameMap) {
        boolean validateResult = true;
        Set<ConstraintViolation<E>> validateSet = Validation.buildDefaultValidatorFactory().getValidator().validate(e, Default.class);
        if (validateSet != null && !validateSet.isEmpty()) {
            validateResult = false;
            ExcelErrorDTO errorDTO;
            List<ExcelErrorDTO> list  = new ArrayList<>();
            for (ConstraintViolation<E> constraint : validateSet) {
                Path propertyPath = constraint.getPropertyPath();
                String propertyName = propertyPath.toString();
                ExcelCellBo bo = propertyNameMap.get(propertyName);
                errorDTO = new ExcelErrorDTO();
                errorDTO.setHeadName(bo.getHeadName());
                Object invalidValue = constraint.getInvalidValue();
                if (invalidValue != null) {
                    errorDTO.setValue(invalidValue.toString());
                }else {
                    errorDTO.setValue(null);
                }
                errorDTO.setColumnIndex(bo.getColumnIndex()+1);
                errorDTO.setRowIndex(bo.getRowIndex()+1);
                errorDTO.setErrMsg("第"+errorDTO.getRowIndex()+"行第"+errorDTO.getColumnIndex()+"列,"+constraint.getMessage());
                list.add(errorDTO);
            }
            Collections.sort(list);
            errorList.addAll(list);
        }
        return validateResult;
    }

    /**处理ExcelDataConvertException
     * @param e 字段转换异常
     */
     private void handleExcelDataConvertException(ExcelDataConvertException e){
        ExcelErrorDTO errorDTO = new ExcelErrorDTO();
        errorDTO.setHeadName(e.getExcelContentProperty().getHead().getHeadNameList().get(0));
        errorDTO.setValue(e.getCellData().getStringValue());
        errorDTO.setColumnIndex(e.getColumnIndex()+1);
        errorDTO.setRowIndex(e.getRowIndex()+1);
        errorDTO.setErrMsg("第"+errorDTO.getRowIndex()+"行第"+errorDTO.getColumnIndex()+"列,"+errorDTO.getHeadName()+"值格式错误");
        errorList.add(errorDTO);
    }

    /**获取excel PropertyNameMap
     * @param isSingleHeader 是否单表头
     * @param analysisContext AnalysisContext
     * @return Map
     */
     Map<String, ExcelCellBo> getPropertyNameMap(boolean isSingleHeader, AnalysisContext analysisContext){
        Map<String, ExcelCellBo> propertyNameMap = new HashMap<>(16);
        ReadRowHolder readRowHolder = analysisContext.readRowHolder();
        Integer rowIndex = readRowHolder.getRowIndex();
        ReadHolder readHolder = analysisContext.currentReadHolder();
        ExcelReadHeadProperty excelReadHeadProperty = readHolder.excelReadHeadProperty();
         Map<String, ExcelContentProperty> fieldNameContentPropertyMap = excelReadHeadProperty.getFieldNameContentPropertyMap();
         Collection<ExcelContentProperty> values = fieldNameContentPropertyMap.values();
        ExcelCellBo bo;
        for (ExcelContentProperty contentProperty : values) {
            bo = new ExcelCellBo();
            bo.setRowIndex(rowIndex);
            bo.setColumnIndex(contentProperty.getHead().getColumnIndex());
            bo.setFieldName(contentProperty.getHead().getFieldName());
            bo.setHeadName(contentProperty.getHead().getHeadNameList().get(0));
            bo.setField(contentProperty.getField());
            propertyNameMap.put(contentProperty.getHead().getFieldName(),bo);
        }
        return propertyNameMap;
    }
}

@Data
public class ImportExcelResult<T,E> {
    boolean success;
    List<T> data;
    List<E> error;
}

 使用：

@Slf4j
public class SimpleDataListener<E> extends AbstractDataListener<E>{


    public SimpleDataListener(List<String> headList){
        super(headList,true);
    }

    public SimpleDataListener(Class<E> c){
       super(c,true);
    }

    @Override
    public void invoke(E data, AnalysisContext analysisContext) {
        Map<String, ExcelCellBo> propertyNameMap = getPropertyNameMap(true,analysisContext);
        if (validate(data,propertyNameMap)) {
            dataList.add(data);
        }
    }
}