使用EasyExcel 导入数据，失败原因数据导出

引言

在日常开发过程中，Excel 导入是非常常见的场景，而且也有很多开源的项目是针对Excel的读写的，如Apache 的poi ，最近用的比较好的还是阿里的EasyExcel 开源工具。平时我们只是简单的读取文件并写入数据库持久化即可，但是前段时间，产品搞了个需求，需要将导入失败的数据及原因写入Excel并下载，那这就有得玩了，废话不多说，上才艺。

产品需求

• 导入Excel数据
• 数据格式校验
• 数据合法性校验（校验数据库）
• 失败数据提供用户下载，并支持再次导入

技术选型

• ，Excel 读取/写入
• ，做异步处理

需求实现

项目依赖(maven)

  com.alibaba
  easyexcle
  2.2.6



            com.xuxueli
            xxl-job-core
            ${xxl-job.version}
        

文件解析

解析导入文件，获取文件数据量，用于判定导入是否走异步导入。

public class EasyExcelUtils {
    
    /**
     *
     * 解析文件，获取最后一行
     * @param inputStream 文件流
     * @param sheetNum 读取excel表格的sheetNum 索引
     * @return 总行数
     */
    public static Integer lastNum(InputStream inputStream,Integer sheetNum){
    
        Workbook wb = null;
        sheetNum = sheetNum == null ? 0 : sheetNum;
        try {
            wb = WorkbookFactory.create(inputStream);
            Sheet sheet = wb.getSheetAt(sheetNum);
            CellReference cellReference = new CellReference("A4");
            // 处理空行
            for (int i = cellReference.getRow();i <= sheet.getLastRowNum();){
                // 省略部分代码
            }
            return sheet.getLastRowNum();
        } catch (Exception e){
            
        }
        return 0;
    }
}

判定导入数据文件是否为空，如果为空，将返回错误信息

@RestController
// 省略其他注解
public class ProjectInfoController {

    /**
     * 项目信息导入
     */
    @PostMapping("/import")
    public R projectInfoImport(MultipartFile file,HttpServletResponse response){
        InputStream inputStream = null;
        int lastNum = 0;
        try {
            lastNum = EasyExcelUtils.lastNum(file.getInputStream());
        }catch(IOException e){
            // 省略部分代码
        }
        if (lastNum <= 0 ){
            throw CustomExcetpoin(500,"导入文件数据为空，请重新上传");
        }
        
    }
}

文件解析拿到导入数据的数据量，与系统配置的文件导入上限值进行判定，如果大于上限值将走异步处理（异步导入，请查看异步“异步导入”导入内容）。

@RestController
// 省略其他注解
public class ProjectInfoController {
    
    @Resource
    private AsyncExcelService asyncExcelService;

    /**
     * 项目信息导入
     */
    @PostMapping("/import")
    public R projectInfoImport(MultipartFile file,HttpServletResponse response){
        InputStream inputStream = null;
        int lastNum = 0;
        try {
            lastNum = EasyExcelUtils.lastNum(file.getInputStream());
        }catch(IOException e){
            // 省略部分代码
        }
        if (lastNum <= 0 ){
            throw CustomExcetpoin(500,"导入文件数据为空，请重新上传");
        }
        // 获取系统配置的导入上限值
        Integer importMax = asyncExcelService.asyncProjectImportMax();
        if (lastNum > importMax ){
            // 达到上限，走异步
            asyncExcelService.asyncProjectImport(file,response);
            return R.success("数据导入成功，因数据量比较大，已转为异步导入");
        }
        // 省略其他代码
    }
}

AsyncExcelService 接口实现

/**
 * 异步导出/导入 service 
 */
public interface AsyncExcelService {
    
    /** 默认导入数据上限 **/
    Integer DEFAULT_IMPORT_DATA_MAX = 500;
    
    /**
     * 获取最大导入上限值，超过则走异步
     */
    Integer getImportMax();
    
    /**
     * 异步导入数据
     */
    void asyncProjectImport(MultipartFile file,HttpServletResponse response);
}

@Service
// 省略其他注解
public class AsyncExcelServiceImpl implements AsyncExcelService {

    @Resource
    private IParamtersClient paramtersClient;
    
    @Override
    public Integer getImportMax(){
        Integer value = getParamVaule("paramName",Integer.class);
        return value == null ? DEFAULT_IMPORT_DATA_MAX : value;
    }
    
    /**
     * 调用框架接口获取系统参数
     *
     */
    private  T getParamVaule(String name,Class clazz){
        CCBHousingUser user = SecureUtil.getUser();
        // 省略部分代码
        
        // 获取系统配置参数
        Parameters parameters = paramtersClient.getParamterByCodeAndOrg(name,user.getOrganizationId());
        
        // 省略部分代码
    }
}

其中，IParamtersClient 属于框架提供的feign 接口，也可以根据自己的实际场景实现相关逻辑。

数据合法校验

导入数据文件解析使用的是alibaba 提供的 EasyExcel 开源工具，我们需要在 EasyExcel 工具的基础上做一些增强处理，如：导入格式校验、导入表头校验、导入数据格式校验等，如果发生校验失败，将错误信息写入错误报告（excel）输出到客户端。

定义easyexcel 导入文件到列与实体映射关系，将使用到 easyexcel 到@ExcleProperty 注解进行关系绑定

@Data
// 省略其他注解
public class ProjectInfoExcelDTO {

    @ExcelProperty(index=0,value="序列号")
    private String number;
    
    @ExcelProperty(index=1,value="项目名称")
    private String name;
    
    // 省略其他字段属性

}

注解 @ExcleProperty 常用属性

• index，与excel文件中，表头列的索引位置对应（从0开始）
• value，与excel文件中，表头列的名称相对应
• converter,指定解析数据时，该列需要使用的数据转换器，转换器实现Converter接口

定义校验错误的数据结构类型

@Data
// 省略其他注解
public class ExcelChcekErrDTO {

    private T t;
    
    private String errMsg;
}

备注：@Data 属于 lombok 工具，简化Bean的封装，感兴趣的同学，可以自行查阅资料。

定义Excel导入校验返回的数据VO

@Data
// 省略其他注解
public class ExcelCheckResultVO {
    
    /** 校验成功的数据 **/
    private List successDatas;
    
    /** 校验失败的数据 **/
    private List errData;
}

定义数据解析监听器EasyExcelListener

@Data
// 省略部分注解
public class EasyExcelListener extends AnalysisEventListener {

    // 省略部分代码
}

定义excel 业务校验管理器 ExcelCheckManager,需要做业务校验的（与数据库匹配等）需要实现该接口

public interface ExcelCheckManager {
    
    ExcelCheckResultVO checkImportExcle(List datas);
}

表头校验
使用EasyExcelListener 用来监听数据解析过程，其中，invokHeadMap 方法将在解析完成excel表头时将被执行

@Data
// 省略部分注解
public class EasyExcelListener extends AnalysisEventListener {

    /** excel 对象的反射类 **/
    private Class clazz;
    
    private ExcelCheckManager excelCheckManager;
    
    public EasyExcelListener(ExcelCheckManager excelCheckManager,Class clazz){
        this.clazz = clazz;
        this.excelCheckManager = excelCheckManager;
    }
    
    @Override
    public void invokHeadMap(Map headMap,AnalysisContext context){
    
        super.invokHeadMap(headMap,context);
        // 反射获取实体到属性值
        Map indexNameMap = getIndexNameMap(clazz);
        // 将 headMap 与 indexNameMap 进行对比，是否完全匹配
        Set keySet = indexNameMap.keySet();
        for (Integer key : keySet ){
            if (StringUtils.isEmpty(headMap.get(key)){
                throw ExcelAnalysisExcetpion("数据解析错误，请传入正确的excel格式");
            }
            if (!headMap.get(key).equals(indexNameMap.get(key)){
                throw ExcelAnalysisExcetpion("数据解析错误，请传入正确的excel格式");
            }
        }
        
    }
    
    /**
     * 反射获取解析数据实体的@ExcleProperty 的value
     */
    public Map getIndexNameMap(Class clazz){
    
        Map 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 excleProperty = field.getAnnotation(ExcelProperty.class);
            if (excelProperty != null){
                int index = excleProperty.index();
                String[] values = excleProperty.value();
                StringBuilder value = new StringBuilder();
                for (String v : values ){
                    value.append(v);
                }
                result.put(index,value.toString());
            }
        }
        
        return result;
    }
    
}

数据非空、格式校验
数据非空校验、格式校验，我们将使用hibernate-validator 校验器进行校验格式。
定义validator 工具类

@component
public class EasyExcelValidatorHelper {

    private static Validtor validtor;
    
    @Autowired
    public EasyExcelValidatorHelper(Validtor validtor){
        this.EasyExcelValidatroHelper.validtor = validtor;
    }
    public static  String validateEntity(T obj) throws NoSuchFieldException{
        StringBuilder result = new StringBuilder();
        // 执行校验
        Set> set = validtor.validate(obj,Default.class);
        // 组装结果
        if(set != null && !set.isEmpty()){
            for (ConstraionViolation cv : set ){
                Field declaredField = obj.getClass.getDeclaredField(cv.getPropertiyPath().toString());
                ExcelProperty annotation = declaredField.getAnnotation(ExcelProperty.class);
                result.append(annotation.value[0]+":"+cv.getMessage()).append(";");
            }
        }
        return result;
    }
}

数据格式校验，使用EasyExcelListener 用来监听数据解析过程，其中，invok 方法将逐行解析excel数据的时候将被调用

@Data
// 省略部分注解
public class EasyExcelListener extends AnalysisEventListener {
    
    /** 标记是否执行数据解析 **/
    private boolean baseMatching = false;
    
    /** 解析成功的数据 **/
    private List successList = new ArrayList<>();
    
    /** 解析失败的数据 **/
    private List> errList = new ArrayList<>();

    /** excel 对象的反射类 **/
    private Class clazz;
    
    private List list;
    
    private ExcelCheckManager excelCheckManager;
    
    public EasyExcelListener(ExcelCheckManager excelCheckManager,Class clazz){
        this.clazz = clazz;
        this.excelCheckManager = excelCheckManager;
    }
    
    @Override
    public void invok(T t,AnalysisContext context){
        // 数据解析/转换完成，标记进入到解析起
        baseMatching = true;
        String errMsg;
        try {
            // 调用验证器验证数据格式
            errMsg = EasyExcelValidatorHelper.validateEntity(t);
        }catch(Exception e){
            errMsg = "解析数据出错";
            // 省略部分代码
        }
        // 校验不通过
        if (!StringUtils.isEmpty(errMsg){
            // 将错误数据放入错误列表中
            ExcelChcekErrDTO errDTO = new ExcelChcekErrDTO(t,errMsg);
            errList.add(errDTO);
        } else{
            // 校验成功
            list.add(t);
        }
        if (list.size() > 1000){
            // 业务校验
            ExcelCheckResultVO excelCheckResultVO = excelCheckManager.checkImportExcel(list);
            successList.addAll(excelCheckResultVO.getSuccessDatas());
            errList.addAll(excelCheckResultVO.getErrDatas());
            list.clear();
        }
    }
    
    /**
     * 所有数据解析完成后调用此方法
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context){
        ExcelCheckResultVO excelCheckResultVO = excelCheckManager.checkImportExcel(list);
        successList.addAll(excelCheckResultVO.getSuccessDatas());
        errList.addAll(excelCheckResultVO.getErrDatas());
        list.clear();
    }
    
    @Override
    public void invokHeadMap(Map headMap,AnalysisContext context){
    
        super.invokHeadMap(headMap,context);
        // 反射获取实体到属性值
        Map indexNameMap = getIndexNameMap(clazz);
        // 将 headMap 与 indexNameMap 进行对比，是否完全匹配
        Set keySet = indexNameMap.keySet();
        for (Integer key : keySet ){
            if (StringUtils.isEmpty(headMap.get(key)){
                throw ExcelAnalysisExcetpion("数据解析错误，请传入正确的excel格式");
            }
            if（!headMap.get(key).equals(indexNameMap.get(key)）{
                throw ExcelAnalysisExcetpion("数据解析错误，请传入正确的excel格式");
            }
        }
        
    }
    
    /**
     * 反射获取解析数据实体的@ExcleProperty 的value
     */
    public Map getIndexNameMap(Class clazz){
    
        Map 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 excleProperty = field.getAnnotation(ExcelProperty.class);
            if (excelProperty != null){
                int index = excleProperty.index();
                String[] values = excleProperty.value();
                StringBuilder value = new StringBuilder();
                for (String v : values ){
                    value.append(v);
                }
                result.put(index,value.toString());
            }
        }
        
        return result;
    }
    
    
    
}

对需要进行校验对字段添加注解

@Data
// 省略其他注解
public class ProjectInfoExcelDTO {

    @ExcelProperty(index=0,value="序列号")
    private String number;
    
    @ExcelProperty(index=1,value="项目名称")
    @NotBlank(message = "请填写项目名称")
    private String name;
    
    // 省略其他字段属性

}

validator 常用注解传送门（validator 常用注解）。

EasyExcel 读取数据，并调用格式校验

@RestController
// 省略其他注解
public class ProjectInfoController {
    
    @Resource
    private AsyncExcelService asyncExcelService;
    
    @Resource
    private ProjectInfoService projectInfoService;

    /**
     * 项目信息导入
     */
    @PostMapping("/import")
    public R projectInfoImport(MultipartFile file,HttpServletResponse response){
        InputStream inputStream = null;
        int lastNum = 0;
        try {
            lastNum = EasyExcelUtils.lastNum(file.getInputStream());
        }catch(IOException e){
            // 省略部分代码
        }
        if (lastNum <= 0 ){
            throw CustomExcetpoin(500,"导入文件数据为空，请重新上传");
        }
        // 获取系统配置的导入上限值
        Integer importMax = asyncExcelService.asyncProjectImportMax();
        if (lastNum > importMax ){
            // 达到上限，走异步
            asyncExcelService.asyncProjectImport(file,response);
            return R.success("数据导入成功，因数据量比较大，已转为异步导入");
        }
        // 省略部分代码
        
        // 实例数据解析监听器
        EasyExcelListener easyExcleListener = new EasyExcelListener(projectInfoService,ProjectInfoDTO.class);
        // 文件读取/解析，并注册监听器
        EasyExcle.read(file.getInputStream(),ProjectInfoDTO.class,easyExcleListener).sheet(1).doRead();
        // 获取错误数据
        List> errList = easyExcleListener.getErrList();
        // 获取解析成功到数据
        List successList = easyExcleListener.getSuccessList();
        // 如果错误数据不为空，将错误数据写入到excel文件，并输出到浏览器
        // 省略代码
        
        // 将成功到数据，批量写入到数据库中
        // 省略代码
        
        
        // 省略其他代码
    }
}

ProjectInfoService 声明与实现，因为需要做业务数据到校验，因此ProjectInfoService 需要继承 ExcelCheckManager 验证管理器

public interface ProjectInfoService extends ExcelCheckManager{
    
}

@Service
// 省略其他注解
public class ProjectInfoServiceImpl implements ProjectInfoService {

    // 省略部分代码
    
    @Override
    public ExcelCheckResultVO checkImportExcel(List datas){
        // 省略代码
    }
}

输出错误报告
文件校验完成之后，如果没有完全通过，需要将错误对数据以及错误信息通过easyExcel 输出到客户端。

@RestController
// 省略其他注解
public class ProjectInfoController {
    
    @Resource
    private AsyncExcelService asyncExcelService;
    
    @Resource
    private ProjectInfoService projectInfoService;

    /**
     * 项目信息导入
     */
    @PostMapping("/import")
    public R projectInfoImport(MultipartFile file,HttpServletResponse response){
        InputStream inputStream = null;
        int lastNum = 0;
        try {
            lastNum = EasyExcelUtils.lastNum(file.getInputStream());
        }catch(IOException e){
            // 省略部分代码
        }
        if (lastNum <= 0 ){
            throw CustomExcetpoin(500,"导入文件数据为空，请重新上传");
        }
        // 获取系统配置的导入上限值
        Integer importMax = asyncExcelService.asyncProjectImportMax();
        if (lastNum > importMax ){
            // 达到上限，走异步
            asyncExcelService.asyncProjectImport(file,response);
            return R.success("数据导入成功，因数据量比较大，已转为异步导入");
        }
        // 省略部分代码
        
        // 实例数据解析监听器
        EasyExcelListener easyExcleListener = new EasyExcelListener(projectInfoService,ProjectInfoDTO.class);
        // 文件读取/解析，并注册监听器
        EasyExcle.read(file.getInputStream(),ProjectInfoDTO.class,easyExcleListener).sheet(1).doRead();
        // 获取错误数据
        List> errList = easyExcleListener.getErrList();
        // 获取解析成功到数据
        List successList = easyExcleListener.getSuccessList();
        // 如果错误数据不为空，将错误数据写入到excel文件，并输出到浏览器
        if (errList.size() > 0 ){
            // 省略部分代码
        }        
        // 将成功到数据，批量写入到数据库中
        // 省略代码
        
        
        // 省略其他代码
    }
}

异步导入

异步导入操作，将思考几个问题：

• 导入文件存到什么地方？当一个同步请求结束之后，后续我们想再次拿到该请求到数据，我们应该考虑将文件放到某一个单独到地方，提供我们二次使用，比如：自己到文件服务器、oss 存储等，这里我们使用自己的文件服务器。
• 怎么异步执行？我们可以使用新启用一个本地线程去执行我们的操作，不影响当前请求主线程的操作，也是可以的，但是考虑到执行重试问题，我们将使用(#xxl-job)分布式调度系统，进行调度执行任务。
• 客户如何查看任务执行状态？我们需要提供一个任务执行日志列表，让用户可以清晰的看到本次导出的任务是否执行完成/是否存在导入错误。
• 怎么将错误报告输出给到客户？我们需要将导入到错误报告文件（excel）上传至文件服务器，提供用户二次或多次下载使用；同时，需要将文件信息保存至任务执行日志信息中，为用户提供下载入口。

定义通用的job handler 父类 AsyncTaskHandler ，所有需要使用xxl-job 发起异步任务和给xxl-job 发起回调，都需要继承AsyncTaskHandler ，并实现execute 抽象方法。

public abstract class AsyncTaskHandler  {
    
    /** xxl-job server 端提供的创建任务接口 uri **/
    private final static String JOB_ADMIN_URI = "/outapi/asyn/";
    
    /** 与xxl-job server 通讯的加密密钥对 **/
    @Setter
    protected String publicKey;
    
    /**
     * xxl-job server 回调对方法 
     */
    public abstract ReturnT execute(String params);
    
    /**
     * 向xxl-job 发起调度任务 
     */
    public JobResponseDTO sendTask(T prams){
        prams.setUser(null);
        
        // 省略部分代码，相关内容，请查询xxl-job server 端所提供的接口文档
        
        // 将 params 中的 user 对象保存至redis 中，xxl-job 接口有长度限制
    }
    
    public abstract RedisUtil getRedisUtil();
    
    public abstract JobProperties getJobProperties();
    
    /** 回调方法名称 **/
    public abstract String getHandlerName();
}

定义 AsyncTaskPramsDTO 异步参数实体

@Data
// 省略其他注解
public class AsyncTaskPramsDTO {
    
    private String requestId;
    
}

数据导出

数据导出功能常指，客户想将系统中的相关（按照查询条件筛选）数据通过excel形式保存到自己本地。在数据导出过程中，需要通过数据筛选条件将数据从系统数据库中筛选出来，然后通过一定格式（excel导出模版格式）写入到excel中，最后输出到客户端（浏览器）提供客户下载保存到本地。