easyExcel + validation + regular实现excel导入验证

导入excel时，除了验证数据外，还将进行数据格式验证。如果直接报告错误，则表明客户体验很差。我的想法是传递java附带的验证+正则表达式首先确定excel数据的每一行是否符合格式，然后通过业务验证，导入正确的数据，重新导出错误的数据，以及在后面添加错误消息。

环境准备：

spring：springboot2.X

easyExcel：2.1.4

龙目岛（不是必需的）

easyExcel官方网站：

参考代码：

代码

介绍easyExcel，lombok，fastjson和其他依赖项

        
        
            com.alibaba
            easyexcel
            2.1.4
        
        
            org.apache.poi
            poi
            3.17
        
        
            org.apache.poi
            poi-ooxml
            3.17
        
        
            cglib
            cglib
            3.1
        
        
            org.apache.poi
            ooxml-schemas
            1.1
        
        
            org.projectlombok
            lombok
            provided
        
        
            com.alibaba
            fastjson
            1.2.60
        

这里应该注意，poi的每个版本都需要对应，如果版本错误，则会导致代码错误。

正如我们从easyExcel官方文档中了解的那样，easyExcel具有用于读取excel数据的侦听器，并且该侦听器无法在spring之前进行管理。每次阅读excel时，它都必须是新的，然后可以使用spring构造传递它的方法。进去

以下是如何编写我的器

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelAnalysisException;
import com.alibaba.excel.util.StringUtils;
import lombok.Data;
import lombok.EqualsAndHashCode;
import java.lang.reflect.Field;
import java.util.*;
/**
 * @author zhy
 * @title: EasyExcelListener
 * @projectName cec-moutai-bd-display
 * @description: easyExcel器
 * @date 2019/12/2318:28
 */
@Data
@EqualsAndHashCode(callSuper=false)
public class EasyExcelListener   extends AnalysisEventListener {
    //成功结果集
    private List successList = new ArrayList<>();
    //失败结果集
    private List> errList = new ArrayList<>();
    //处理逻辑service
    private ExcelCheckManager excelCheckManager;
    private List list = new ArrayList<>();
    //excel对象的反射类
    private Class clazz;
    public EasyExcelListener(ExcelCheckManager excelCheckManager){
        this.excelCheckManager = excelCheckManager;
    }
    public EasyExcelListener(ExcelCheckManager excelCheckManager,Class clazz){
        this.excelCheckManager = excelCheckManager;
        this.clazz = clazz;
    }
    @Override
    public void invoke(T t, AnalysisContext analysisContext) {
        String errMsg;
        try {
            //根据excel数据实体中的javax.validation + 正则表达式来校验excel数据
            errMsg = EasyExcelValiHelper.validateEntity(t);
        } catch (NoSuchFieldException e) {
            errMsg = "解析数据出错";
            e.printStackTrace();
        }
        if (!StringUtils.isEmpty(errMsg)){
            ExcelCheckErrDto excelCheckErrDto = new ExcelCheckErrDto(t, errMsg);
            errList.add(excelCheckErrDto);
        }else{
            list.add(t);
        }
        //每1000条处理一次
        if (list.size() > 1000){
            //校验
            ExcelCheckResult result = excelCheckManager.checkImportExcel(list);
            successList.addAll(result.getSuccessDtos());
            errList.addAll(result.getErrDtos());
            list.clear();
        }
    }
    //所有数据解析完成了 都会来调用
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        ExcelCheckResult result = excelCheckManager.checkImportExcel(list);
        successList.addAll(result.getSuccessDtos());
        errList.addAll(result.getErrDtos());
        list.clear();
    }
    /**
      * @description: 校验excel头部格式，必须完全匹配
      * @param headMap 传入excel的头部（第一行数据）数据的index,name
      * @param context
      * @throws
      * @return void
      * @author zhy
      * @date 2019/12/24 19:27
      */
    @Override
    public void invokeHeadMap(Map headMap, AnalysisContext context) {
        super.invokeHeadMap(headMap, context);
        if (clazz != null){
            try {
                Map indexNameMap = getIndexNameMap(clazz);
                Set keySet = indexNameMap.keySet();
                for (Integer key : keySet) {
                    if (StringUtils.isEmpty(headMap.get(key))){
                        throw new ExcelAnalysisException("解析excel出错，请传入正确格式的excel");
                    }
                    if (!headMap.get(key).equals(indexNameMap.get(key))){
                        throw new ExcelAnalysisException("解析excel出错，请传入正确格式的excel");
                    }
                }
            } catch (NoSuchFieldException e) {
                e.printStackTrace();
            }
        }
    }
    /**
      * @description: 获取注解里ExcelProperty的value，用作校验excel
      * @param clazz 
      * @throws
      * @return java.util.Map 
      * @author zhy
      * @date 2019/12/24 19:21 
      */
    @SuppressWarnings("rawtypes")
    public Map getIndexNameMap(Class clazz) throws NoSuchFieldException {
        Map result = new HashMap<>();
        Field field;
        Field[] fields=clazz.getDeclaredFields();
        for (int i = 0; i 

从上到下分别解释，或粘贴代码

import lombok.Data;
/**
 * @author zhy
 * @title: ExcelCheckErrDto
 * @projectName cec-moutai-bd-display
 * @description: excel数据导入错误结果（单条）
 * @date 2019/12/2318:23
 */
@Data
public class ExcelCheckErrDto {
    private T t;
    private String errMsg;
    public ExcelCheckErrDto(){}
    public ExcelCheckErrDto(T t, String errMsg){
        this.t = t;
        this.errMsg = errMsg;
    }
}

import com.alibaba.excel.annotation.ExcelProperty;
import javax.validation.ConstraintViolation;
import javax.validation.Validation;
import javax.validation.Validator;
import javax.validation.groups.Default;
import java.lang.reflect.Field;
import java.util.Set;
public class EasyExcelValiHelper {
    private EasyExcelValiHelper(){}
 
    private static Validator validator = Validation.buildDefaultValidatorFactory().getValidator();
 
    public static  String validateEntity(T obj) throws NoSuchFieldException {
        StringBuilder result = new StringBuilder();
        Set> set = validator.validate(obj, Default.class);
        if (set != null && !set.isEmpty()) {
            for (ConstraintViolation cv : set) {
                Field declaredField = obj.getClass().getDeclaredField(cv.getPropertyPath().toString());
                ExcelProperty annotation = declaredField.getAnnotation(ExcelProperty.class);
                //拼接错误信息，包含当前出错数据的标题名字+错误信息
                result.append(annotation.value()[0]+cv.getMessage()).append(";");
            }
        }
        return result.toString();
    }
}

常规验证完成后，将执行业务验证。通过服务方法执行业务验证。验证的服务接口必须继承ExcelCheckManager类，并在实现类中实现checkImportExcel方法。

import java.util.List;
/**
 * @author zhy
 * @title: ExcelCheckManager
 * @projectName cec-moutai-bd-display
 * @description: excel校验接口
 * @date 2019/12/2317:44
 */
public interface ExcelCheckManager {
    /**
     * @description: 校验方法
     * @param objects
     * @throws
     * @return com.cec.moutai.common.easyexcel.ExcelCheckResult
     * @author zhy
     * @date 2019/12/24 14:57
     */
    ExcelCheckResult checkImportExcel(List objects);
}

需要验证excel业务的服务接口可以继承此接口，并在实现类中实现其自己的方法。返回值为ExcelCheckResult，其中包含成功+失败结果集。当然，不必返回成功结果集，通常，“处理”是指可以将业务验证传递的数据直接保留下来，也可以将其放置在SuccessDtos中以进行批处理操作，但应注意，oom

import lombok.Data;
import java.util.ArrayList;
import java.util.List;
/**
 * @author zhy
 * @title: ExcelCheckErrDto
 * @projectName cec-moutai-bd-display
 * @description: excel数据导入结果
 * @date 2019/12/2318:23
 */
@Data
public class ExcelCheckResult {
    private List successDtos;
    private List> errDtos;
    public ExcelCheckResult(List successDtos, List> errDtos){
        this.successDtos =successDtos;
        this.errDtos = errDtos;
    }
    public ExcelCheckResult(List> errDtos){
        this.successDtos =new ArrayList<>();
        this.errDtos = errDtos;
    }
}

import com.alibaba.fastjson.annotation.JSONField;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import java.io.Serializable;
import java.util.Date;
/**
 * @title: ExcelImportTest
 * @projectName easyexceldemo
 * @description: 用户
 * @author zhy
 * @date 2020/1/1610:41
 */
@Data
public class User implements Serializable {
    private static final long serialVersionUID = 1L;
    //名称
    private String name;
    //性别
    private String sex;
    //年龄
    private Integer age;
    //生日
    @JSONField(format="yyyy-MM-dd HH:mm:ss")
    @JsonFormat( pattern="yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
    private Date birthday;

Excel数据实体类（数据字段名称与pojo的名称相同，但应注意，属性类型均为String类型，便于常规判断）

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.zhy.easyexceldemo.easyexcel.ExcelPatternMsg;
import lombok.Data;
import org.hibernate.validator.constraints.Length;
import javax.validation.constraints.Pattern;
import java.io.Serializable;
/**
 * @title: UserExcelDto
 * @projectName easyexceldemo
 * @description: 用户excel类
 * @author zhy
 * @date 2020/1/1610:45
 */
@Data
public class UserExcelDto implements Serializable {
    private static final long serialVersionUID = 1L;
    //名称
    @ExcelProperty(index = 0,value = "名称")
    @ColumnWidth(30)
    @Length(max = 10)
    private String name;
    //性别
    @ExcelProperty(index = 1,value = "性别")
    @ColumnWidth(30)
    @Length(max = 2)
    private String sex;
    //年龄
    @ExcelProperty(index = 2,value = "年龄")
    @ColumnWidth(30)
    @Pattern(regexp = ExcelPatternMsg.NUMBER,message = ExcelPatternMsg.NUMBER_MSG)
    private String age;
    //生日
    @ExcelProperty(index = 3,value = "生日")
    @Pattern(regexp = ExcelPatternMsg.DATE_TIME1,message = ExcelPatternMsg.DATE_TIME1_MSG)
    private String birthday;

@ExcelProperty是easyExcel随附的评论

@ColumnWidth也是easyExcel的注释，用于表示单元格宽度

@Length表示字符串的长度，max表示最大允许长度

@Pattern是正则表达式注释，regexp代表正则表达式，消息代表yes，成功匹配不会返回错误消息

有关验证的评论，请参阅老板的以下博客：

可以发现这里的所有接收对象都是String类型的，因此，我们可以通过正则表达式验证各种格式。

所以我用fastjson的JSON.parseObject在excel类和真实实体类之间进行转换。因此，必须确保excel类和实体的字段名称必须一致，并且数据格式（尤其是日期格式）必须一致。

以下是我到目前为止使用的正则表达式，即ExcelPatternMsg

import java.util.regex.Pattern;
/**
 * @author zhy
 * @title: ExcelPatternMsg
 * @projectName cec-moutai-bd-display
 * @description: excel正则表达式，以及错误信息
 * @date 2019/12/2614:22
 */
public class ExcelPatternMsg {
    //只能输入整数或者小数
    public static final String DECIMAL = "^[0-9]+\\.{0,1}[0-9]{0,2}$";
    public static final String DECIMAL_MSG = "只能输入整数或者小数";
    //日期格式 yyyy/MM/dd
    public static final String DATE1 = "(([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})\\/(((0[13578]|1[02])\\/(0[1-9]|[12][0-9]|3[01]))|"+
            "((0[469]|11)\\/(0[1-9]|[12][0-9]|30))|(02\\/(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|"+
            "((0[48]|[2468][048]|[3579][26])00))\\/02\\/29)$";
    public static final String DATE1_MSG = "输入正确的日期格式：yyyy/MM/dd";
    //日期格式 yyyy-MM-dd
    public static final String DATE2 = "(([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})-(((0[13578]|1[02])-(0[1-9]|[12][0-9]|3[01]))|"+
            "((0[469]|11)-(0[1-9]|[12][0-9]|30))|(02-(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|"+
            "((0[48]|[2468][048]|[3579][26])00))-02-29)$";
    public static final String DATE2_MSG = "输入正确的日期格式：yyyy-MM-dd";
    //日期格式 yyyyMMdd
    public static final String DATE3 = "(([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})(((0[13578]|1[02])(0[1-9]|[12][0-9]|3[01]))|"+
            "((0[469]|11)(0[1-9]|[12][0-9]|30))|(02(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|"+
            "((0[48]|[2468][048]|[3579][26])00))0229)$";
    public static final String DATE3_MSG = "输入正确的日期格式：yyyyMMdd";
    //日期格式 yyyy-MM-dd HH:mm:ss
    public static final String DATE_TIME1 = "^((([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})-(((0[13578]|1[02])-(0[1-9]|[12][0-9]|3[01]))|" +
            "((0[469]|11)-(0[1-9]|[12][0-9]|30))|(02-(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|((0[48]|[2468][048]|[3579][26])00))-02-29))\\s+([0-1]?[0-9]|2[0-3]):([0-5][0-9]):([0-5][0-9])$";
    public static final String DATE_TIME1_MSG = "输入正确的日期格式：yyyy-MM-dd HH:mm:ss";
    //日期格式 yyyy/MM/dd HH:mm:ss
    public static final String DATE_TIME2 = "((([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})\\/(((0[13578]|1[02])\\/(0[1-9]|[12][0-9]|3[01]))|"+
            "((0[469]|11)\\/(0[1-9]|[12][0-9]|30))|(02\\/(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|"+
            "((0[48]|[2468][048]|[3579][26])00))\\/02\\/29))\\s([0-1][0-9]|2[0-3]):([0-5][0-9]):([0-5][0-9])$";
    public static final String DATE_TIME2_MSG = "输入正确的日期格式：yyyy/MM/dd HH:mm:ss";
    //日期格式 yyyyMMddHHmmss
    public static final String DATE_TIME3 = "((([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})(((0[13578]|1[02])(0[1-9]|[12][0-9]|3[01]))|"+
            "((0[469]|11)(0[1-9]|[12][0-9]|30))|(02(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|((0[48]|[2468][048]|[3579][26])00))0229))"+
            "([0-1][0-9]|2[0-3])([0-5][0-9])([0-5][0-9])$";
    public static final String DATE_TIME3_MSG = "输入正确的日期格式：yyyyMMddHHmmss";
    //日期格式 yyyyMMddHHmmssSSS
    public static final String DATE_TIME4 = "((([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})(((0[13578]|1[02])(0[1-9]|[12][0-9]|3[01]))|"+
            "((0[469]|11)(0[1-9]|[12][0-9]|30))|(02(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|"+
            "((0[48]|[2468][048]|[3579][26])00))0229))([0-1][0-9]|2[0-3])([0-5][0-9])([0-5][0-9])([0-9]{3})$";
    public static final String DATE_TIME4_MSG = "输入正确的日期格式：yyyyMMddHHmmssSSS";
    //日期格式 yyyyMMdd HH:mm:ss
    public static final String DATE_TIME5 = "((([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})(((0[13578]|1[02])(0[1-9]|[12][0-9]|3[01]))|"+
            "((0[469]|11)(0[1-9]|[12][0-9]|30))|(02(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|"+
            "((0[48]|[2468][048]|[3579][26])00))0229))\\s([0-1][0-9]|2[0-3]):([0-5][0-9]):([0-5][0-9])$";
    public static final String DATE_TIME5_MSG = "输入正确的日期格式：yyyyMMdd HH:mm:ss";
    //数字和字母
    public static final String NUMBER_LETTER = "^[a-z0-9A-Z]+$";
    public static final String NUMBER_LETTER_MSG = "只能输入数字和字母";
    //数字
    public static final String NUMBER = "^[0-9]*$";
    public static final String NUMBER_MSG = "只能输入数字";
    public static void main(String[] args) {
        System.out.println(Pattern.matches(DATE1,"2020/02/30"));
    }
}

import com.zhy.easyexceldemo.dto.UserExcelDto;
import com.zhy.easyexceldemo.easyexcel.ExcelCheckManager;
/**
 * @title: UserService
 * @projectName easyexceldemo
 * @description: 用户service
 * @author zhy
 * @date 2020/1/1610:56
 */
public interface UserService extends ExcelCheckManager {
}

import com.alibaba.excel.util.StringUtils;
import com.zhy.easyexceldemo.dto.UserExcelDto;
import com.zhy.easyexceldemo.easyexcel.ExcelCheckErrDto;
import com.zhy.easyexceldemo.easyexcel.ExcelCheckResult;
import com.zhy.easyexceldemo.service.UserService;
import org.springframework.stereotype.Service;
import java.util.ArrayList;
import java.util.List;
/**
 * @title: UserService
 * @projectName easyexceldemo
 * @description: 用户service
 * @author zhy
 * @date 2020/1/1610:56
 */
@Service
public class UserServiceImpl implements UserService {
    //不合法名字
    public static final String ERR_NAME = "史珍香";
    /**
     * @description: 校验方法
     * @param userExcelDtos 用户信息
     * @throws
     * @return com.cec.moutai.common.easyexcel.ExcelCheckResult
     * @author zhy
     * @date 2019/12/24 14:57
     */
    @Override
    public ExcelCheckResult checkImportExcel(List userExcelDtos) {
        //成功结果集
        List successList = new ArrayList<>();
        //错误数组
        List> errList = new ArrayList<>();
        for (UserExcelDto userExcelDto : userExcelDtos) {
            //错误信息
            StringBuilder errMsg = new StringBuilder();
            //根据自己的业务去做判断
            if (ERR_NAME.equals(userExcelDto.getName()))
                errMsg.append("请输入正确的名字").append(";");
            if (StringUtils.isEmpty(errMsg.toString())){
                //这里有两个选择，1、一个返回成功的对象信息，2、进行持久化操作
                successList.add(userExcelDto);
            }else{//添加错误信息
                errList.add(new ExcelCheckErrDto(userExcelDto,errMsg.toString()));
            }
        }
        return new ExcelCheckResult(successList,errList);
    }
}

import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.fastjson.JSON;
import com.zhy.easyexceldemo.common.BaseRest;
import com.zhy.easyexceldemo.common.Result;
import com.zhy.easyexceldemo.dto.UserExcelDto;
import com.zhy.easyexceldemo.dto.UserExcelErrDto;
import com.zhy.easyexceldemo.easyexcel.EasyExcelListener;
import com.zhy.easyexceldemo.easyexcel.EasyExcelUtils;
import com.zhy.easyexceldemo.easyexcel.ExcelCheckErrDto;
import com.zhy.easyexceldemo.pojo.User;
import com.zhy.easyexceldemo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;
/**
 * @title: UserRest
 * @projectName easyexceldemo
 * @description: 用户rest
 * @author zhy
 * @date 2020/1/1611:30
 */
@RestController
@RequestMapping("/user")
public class UserRest extends BaseRest {
    @Autowired
    private UserService userService;
    /**
      * @description: 导出测试
      * @param response
      * @throws
      * @return void
      * @author zhy
      * @date 2020/1/16 11:58
      */
    @GetMapping("/exportExcel")
    public void exportExcel(HttpServletResponse response) throws IOException {
        List userList = new ArrayList<>();
        User user1 = new User();
        user1.setName("张三");
        user1.setAge(10);
        user1.setBirthday(new Date());
        user1.setSex("男");
        userList.add(user1);
        List userExcelDtos = JSON.parseArray(JSON.toJSONString(userList),UserExcelDto.class);
        EasyExcelUtils.webWriteExcel(response,userExcelDtos, UserExcelDto.class,"用户基本信息");
    }
    /**
      * @description: 导入测试
      * @param response
      * @param file
      * @throws
      * @return com.zhy.easyexceldemo.common.Result
      * @author zhy
      * @date 2020/1/16 11:59
      */
    @PostMapping("/importExcel")
    public Result importExcel(HttpServletResponse response, @RequestParam MultipartFile file) throws IOException {
        EasyExcelListener easyExcelListener = new EasyExcelListener(userService,UserExcelDto.class);
        EasyExcelFactory.read(file.getInputStream(),UserExcelDto.class,easyExcelListener).sheet().doRead();
        List> errList = easyExcelListener.getErrList();
        if (!errList.isEmpty()){//如果包含错误信息就导出错误信息
            List excelErrDtos = errList.stream().map(excelCheckErrDto -> {
                UserExcelErrDto userExcelErrDto = JSON.parseObject(JSON.toJSONString(excelCheckErrDto.getT()), UserExcelErrDto.class);
                userExcelErrDto.setErrMsg(excelCheckErrDto.getErrMsg());
                return userExcelErrDto;
            }).collect(Collectors.toList());
            EasyExcelUtils.webWriteExcel(response,excelErrDtos, UserExcelErrDto.class,"用户导入错误信息");
        }
        return addSucResult();
    }
}

最终结果是：

image.png