導(dǎo)入excel的時候，除了數(shù)據(jù)庫校驗數(shù)據(jù)以外，還會碰到數(shù)據(jù)格式校驗，如果直接報錯，顯得客戶體驗很不好，我想到的做法是，通過java自帶的validation+正則表達(dá)式先去判斷excel每一行數(shù)據(jù)是否符合格式，然后通過業(yè)務(wù)校驗，導(dǎo)入正確的數(shù)據(jù)，錯誤的數(shù)據(jù)重新導(dǎo)出，并在后面加上錯誤信息。

環(huán)境準(zhǔn)備：

spring:springboot2.X
easyExcel:2.1.4
lombok(非必須)
easyExcel官網(wǎng)：https://alibaba-easyexcel.github.io/index.html
參考代碼：https://github.com/zhyhuayong/easyexcelTest

代碼

引入easyExcel、lombok、fastjson等依賴

        <!--easyExcel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.4</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>cglib</groupId>
            <artifactId>cglib</artifactId>
            <version>3.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>ooxml-schemas</artifactId>
            <version>1.1</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <scope>provided</scope>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.60</version>
        </dependency>

這里需要注意的是各個版本的poi需要對應(yīng)，如果版本不對會導(dǎo)致代碼出錯。
通過easyExcel官方文檔我們知道，easyExcel有一個監(jiān)聽器，用來讀取excel數(shù)據(jù)，并且監(jiān)聽器不能被spring管理，要每次讀取excel都要new,然后里面用到spring可以構(gòu)造方法傳進(jìn)去
下面是我的監(jiān)聽器的寫法

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監(jiān)聽器
 * @date 2019/12/2318:28
 */
@Data
@EqualsAndHashCode(callSuper=false)
public class EasyExcelListener <T>  extends AnalysisEventListener<T> {

    //成功結(jié)果集
    private List<T> successList = new ArrayList<>();

    //失敗結(jié)果集
    private List<ExcelCheckErrDto<T>> errList = new ArrayList<>();

    //處理邏輯service
    private ExcelCheckManager<T> excelCheckManager;

    private List<T> list = new ArrayList<>();

    //excel對象的反射類
    private Class<T> clazz;

    public EasyExcelListener(ExcelCheckManager<T> excelCheckManager){
        this.excelCheckManager = excelCheckManager;
    }

    public EasyExcelListener(ExcelCheckManager<T> excelCheckManager,Class<T> clazz){
        this.excelCheckManager = excelCheckManager;
        this.clazz = clazz;
    }

    @Override
    public void invoke(T t, AnalysisContext analysisContext) {
        String errMsg;
        try {
            //根據(jù)excel數(shù)據(jù)實體中的javax.validation + 正則表達(dá)式來校驗excel數(shù)據(jù)
            errMsg = EasyExcelValiHelper.validateEntity(t);
        } catch (NoSuchFieldException e) {
            errMsg = "解析數(shù)據(jù)出錯";
            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();
        }
    }

    //所有數(shù)據(jù)解析完成了 都會來調(diào)用
    @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的頭部（第一行數(shù)據(jù)）數(shù)據(jù)的index,name
      * @param context
      * @throws
      * @return void
      * @author zhy
      * @date 2019/12/24 19:27
      */
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        super.invokeHeadMap(headMap, context);
        if (clazz != null){
            try {
                Map<Integer, String> indexNameMap = getIndexNameMap(clazz);
                Set<Integer> 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<java.lang.Integer,java.lang.String> 
      * @author zhy
      * @date 2019/12/24 19:21 
      */
    @SuppressWarnings("rawtypes")
    public Map<Integer,String> getIndexNameMap(Class clazz) throws NoSuchFieldException {
        Map<Integer,String> result = new HashMap<>();
        Field field;
        Field[] fields=clazz.getDeclaredFields();
        for (int i = 0; i <fields.length ; i++) {
            field=clazz.getDeclaredField(fields[i].getName());
            field.setAccessible(true);
            ExcelProperty excelProperty=field.getAnnotation(ExcelProperty.class);
            if(excelProperty!=null){
                int index = excelProperty.index();
                String[] values = excelProperty.value();
                StringBuilder value = new StringBuilder();
                for (String v : values) {
                    value.append(v);
                }
                result.put(index,value.toString());
            }
        }
        return result;
    }


}

從上到下分別解釋一下，或者貼一下代碼

• 失敗結(jié)果ExcelCheckErrDto，里面存放的是校驗失敗的excel實體+錯誤信息

import lombok.Data;

/**
 * @author zhy
 * @title: ExcelCheckErrDto
 * @projectName cec-moutai-bd-display
 * @description: excel數(shù)據(jù)導(dǎo)入錯誤結(jié)果（單條）
 * @date 2019/12/2318:23
 */
@Data
public class ExcelCheckErrDto<T> {

    private T t;

    private String errMsg;

    public ExcelCheckErrDto(){}

    public ExcelCheckErrDto(T t, String errMsg){
        this.t = t;
        this.errMsg = errMsg;
    }
}

• excel正則校驗方法EasyExcelValiHelper的寫法，該方法會根據(jù)實體類中的注解來通過正則表達(dá)式判斷當(dāng)前單元格內(nèi)的數(shù)據(jù)是否符合標(biāo)準(zhǔn)，例如只能是數(shù)字之類的，返回的是檢查的錯誤信息

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 <T> String validateEntity(T obj) throws NoSuchFieldException {
        StringBuilder result = new StringBuilder();
        Set<ConstraintViolation<T>> set = validator.validate(obj, Default.class);
        if (set != null && !set.isEmpty()) {
            for (ConstraintViolation<T> cv : set) {
                Field declaredField = obj.getClass().getDeclaredField(cv.getPropertyPath().toString());
                ExcelProperty annotation = declaredField.getAnnotation(ExcelProperty.class);
                //拼接錯誤信息，包含當(dāng)前出錯數(shù)據(jù)的標(biāo)題名字+錯誤信息
                result.append(annotation.value()[0]+cv.getMessage()).append(";");
            }
        }
        return result.toString();
    }
}

正則校驗完畢之后就是業(yè)務(wù)校驗，通過service方法進(jìn)行業(yè)務(wù)校驗，校驗的service接口必須繼承ExcelCheckManager類，在實現(xiàn)類中實現(xiàn)checkImportExcel方法

• excel數(shù)據(jù)業(yè)務(wù)校驗接口ExcelCheckManager

import java.util.List;

/**
 * @author zhy
 * @title: ExcelCheckManager
 * @projectName cec-moutai-bd-display
 * @description: excel校驗接口
 * @date 2019/12/2317:44
 */
public interface ExcelCheckManager<T> {

    /**
     * @description: 校驗方法
     * @param objects
     * @throws
     * @return com.cec.moutai.common.easyexcel.ExcelCheckResult
     * @author zhy
     * @date 2019/12/24 14:57
     */
    ExcelCheckResult checkImportExcel(List<T> objects);
}

需要校驗excel業(yè)務(wù)的service接口可以繼承這個接口，并在實現(xiàn)類中實現(xiàn)自己的方法，返回的是ExcelCheckResult，里面包含成功+失敗的結(jié)果集，當(dāng)然可以不必返回成功結(jié)果集，一般的處理就是業(yè)務(wù)校驗通過的數(shù)據(jù)可以直接持久化操作，也可以放在successDtos里面到最后進(jìn)行批量操作，但是要注意的是oom

import lombok.Data;

import java.util.ArrayList;
import java.util.List;

/**
 * @author zhy
 * @title: ExcelCheckErrDto
 * @projectName cec-moutai-bd-display
 * @description: excel數(shù)據(jù)導(dǎo)入結(jié)果
 * @date 2019/12/2318:23
 */
@Data
public class ExcelCheckResult<T> {
    private List<T> successDtos;

    private List<ExcelCheckErrDto<T>> errDtos;

    public ExcelCheckResult(List<T> successDtos, List<ExcelCheckErrDto<T>> errDtos){
        this.successDtos =successDtos;
        this.errDtos = errDtos;
    }

    public ExcelCheckResult(List<ExcelCheckErrDto<T>> errDtos){
        this.successDtos =new ArrayList<>();
        this.errDtos = errDtos;
    }
}

• 測試pojo

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數(shù)據(jù)實體類（數(shù)據(jù)字段名和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就是正則表達(dá)式注解了，regexp代表的是正則表達(dá)式，message代表是，沒有匹配成功返回的錯誤信息
關(guān)于validation的注解可以參考此篇大佬的博客：https://blog.csdn.net/weixin_42546729/article/details/89364431

可以發(fā)現(xiàn)，我這里的接收對象都是String類型的，也正因為是這樣，才能通過正則表達(dá)式去校驗各種格式。
所以在excel類和真正的實體類之間的轉(zhuǎn)換，我是用fastjson的JSON.parseObject來進(jìn)行轉(zhuǎn)換的。所以需要保證excel類和數(shù)據(jù)庫實體的字段名要保持一致，數(shù)據(jù)格式，尤其是日期格式，要保持一致。

下面是我目前為止用到的正則表達(dá)式，也就是ExcelPatternMsg

import java.util.regex.Pattern;

/**
 * @author zhy
 * @title: ExcelPatternMsg
 * @projectName cec-moutai-bd-display
 * @description: excel正則表達(dá)式，以及錯誤信息
 * @date 2019/12/2614:22
 */
public class ExcelPatternMsg {

    //只能輸入整數(shù)或者小數(shù)
    public static final String DECIMAL = "^[0-9]+\\.{0,1}[0-9]{0,2}$";
    public static final String DECIMAL_MSG = "只能輸入整數(shù)或者小數(shù)";

    //日期格式 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";


    //數(shù)字和字母
    public static final String NUMBER_LETTER = "^[a-z0-9A-Z]+$";
    public static final String NUMBER_LETTER_MSG = "只能輸入數(shù)字和字母";

    //數(shù)字
    public static final String NUMBER = "^[0-9]*$";
    public static final String NUMBER_MSG = "只能輸入數(shù)字";



    public static void main(String[] args) {
        System.out.println(Pattern.matches(DATE1,"2020/02/30"));
    }
}

• excel錯誤信息實體，繼承excel數(shù)據(jù)實體、多了一個錯誤信息errMsg屬性

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.zhy.easyexceldemo.easyexcel.ExcelPatternMsg;
import lombok.Data;
import lombok.EqualsAndHashCode;
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
@EqualsAndHashCode(callSuper=false)
public class UserExcelErrDto extends UserExcelDto {


    //錯誤信息
    @ExcelProperty(index = 4,value = "錯誤信息")
    @ColumnWidth(50)
    private String errMsg;


}

• UserService

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<UserExcelDto> {
}

• UserServiceImpl

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<UserExcelDto> userExcelDtos) {
        //成功結(jié)果集
        List<UserExcelDto> successList = new ArrayList<>();
        //錯誤數(shù)組
        List<ExcelCheckErrDto<UserExcelDto>> errList = new ArrayList<>();
        for (UserExcelDto userExcelDto : userExcelDtos) {
            //錯誤信息
            StringBuilder errMsg = new StringBuilder();
            //根據(jù)自己的業(yè)務(wù)去做判斷
            if (ERR_NAME.equals(userExcelDto.getName()))
                errMsg.append("請輸入正確的名字").append(";");
            if (StringUtils.isEmpty(errMsg.toString())){
                //這里有兩個選擇，1、一個返回成功的對象信息，2、進(jìn)行持久化操作
                successList.add(userExcelDto);
            }else{//添加錯誤信息
                errList.add(new ExcelCheckErrDto(userExcelDto,errMsg.toString()));
            }
        }
        return new ExcelCheckResult(successList,errList);
    }
}

• web的導(dǎo)入導(dǎo)出

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: 導(dǎo)出測試
      * @param response
      * @throws
      * @return void
      * @author zhy
      * @date 2020/1/16 11:58
      */
    @GetMapping("/exportExcel")
    public void exportExcel(HttpServletResponse response) throws IOException {
        List<User> userList = new ArrayList<>();
        User user1 = new User();
        user1.setName("張三");
        user1.setAge(10);
        user1.setBirthday(new Date());
        user1.setSex("男");
        userList.add(user1);
        List<UserExcelDto> userExcelDtos = JSON.parseArray(JSON.toJSONString(userList),UserExcelDto.class);
        EasyExcelUtils.webWriteExcel(response,userExcelDtos, UserExcelDto.class,"用戶基本信息");
    }

    /**
      * @description: 導(dǎo)入測試
      * @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<ExcelCheckErrDto<UserExcelDto>> errList = easyExcelListener.getErrList();
        if (!errList.isEmpty()){//如果包含錯誤信息就導(dǎo)出錯誤信息
            List<UserExcelErrDto> 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,"用戶導(dǎo)入錯誤信息");
        }
        return addSucResult();
    }
}

最終的結(jié)果是：

image.png

轉(zhuǎn)載請注明出處！

本人為（weixin）恭（gong）祝（zhong）號：一吱小確幸。歡迎大家關(guān)注