引言

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

产品需求导入Excel数据数据格式校验数据合法性校验（校验数据库）失败数据提供用户下载，并支持再次导入技术选型 ，Excel 读取/写入 ，做异步处理需求实现项目依赖(maven)

<!-- easyexcle --><dependency><groupId>com.alibaba</groupId><artifactId>easyexcle</artifactId><version>2.2.6</version></dependency><!-- xxl job --><dependency><groupId>com.xuxueli</groupId><artifactId>xxl-job-core</artifactId><version>${xxl-job.version}</version></dependency>

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

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 {@Resourceprivate 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 {@Resourceprivate IParamtersClient paramtersClient;@Overridepublic Integer getImportMax(){Integer value = getParamVaule("paramName",Integer.class);return value == null ? DEFAULT_IMPORT_DATA_MAX : value;}/*** 调用框架接口获取系统参数**/private <T> T getParamVaule(String name,Class<T> 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<T> {private T t;private String errMsg;}

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

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

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

定义数据解析监听器EasyExcelListener

@Data// 省略部分注解public class EasyExcelListener<T> extends AnalysisEventListener<T> {// 省略部分代码}

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

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

表头校验

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

@Data// 省略部分注解public class EasyExcelListener<T> extends AnalysisEventListener<T> {/** excel 对象的反射类 **/private Class<T> clazz;private ExcelCheckManager<T> excelCheckManager;public EasyExcelListener(ExcelCheckManager<T> excelCheckManager,Class<T> clazz){this.clazz = clazz;this.excelCheckManager = excelCheckManager;}@Overridepublic void invokHeadMap(Map<Integer,String> headMap,AnalysisContext context){super.invokHeadMap(headMap,context);// 反射获取实体到属性值Map<Integer,String> indexNameMap = getIndexNameMap(clazz);// 将 headMap 与 indexNameMap 进行对比，是否完全匹配Set<Integer> 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<Integer,String> getIndexNameMap(Class clazz){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 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 工具类

@componentpublic class EasyExcelValidatorHelper {private static Validtor validtor;@Autowiredpublic EasyExcelValidatorHelper(Validtor validtor){this.EasyExcelValidatroHelper.validtor = validtor;}public static <T> String validateEntity(T obj) throws NoSuchFieldException{StringBuilder result = new StringBuilder();// 执行校验Set<ConstraionViolation<T>> set = validtor.validate(obj,Default.class);// 组装结果if(set != null && !set.isEmpty()){for (ConstraionViolation<T> 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<T> extends AnalysisEventListener<T> {/** 标记是否执行数据解析 **/private boolean baseMatching = false;/** 解析成功的数据 **/private List<T> successList = new ArrayList<>();/** 解析失败的数据 **/private List<ExcelCheckErrDTO<T>> errList = new ArrayList<>();/** excel 对象的反射类 **/private Class<T> clazz;private List<T> list;private ExcelCheckManager<T> excelCheckManager;public EasyExcelListener(ExcelCheckManager<T> excelCheckManager,Class<T> clazz){this.clazz = clazz;this.excelCheckManager = excelCheckManager;}@Overridepublic 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();}}/*** 所有数据解析完成后调用此方法*/@Overridepublic void doAfterAllAnalysed(AnalysisContext context){ExcelCheckResultVO excelCheckResultVO = excelCheckManager.checkImportExcel(list);successList.addAll(excelCheckResultVO.getSuccessDatas());errList.addAll(excelCheckResultVO.getErrDatas());list.clear();}@Overridepublic void invokHeadMap(Map<Integer,String> headMap,AnalysisContext context){super.invokHeadMap(headMap,context);// 反射获取实体到属性值Map<Integer,String> indexNameMap = getIndexNameMap(clazz);// 将 headMap 与 indexNameMap 进行对比，是否完全匹配Set<Integer> 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<Integer,String> getIndexNameMap(Class clazz){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 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 {@Resourceprivate AsyncExcelService asyncExcelService;@Resourceprivate 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<ProjectInfoDTO> easyExcleListener = new EasyExcelListener(projectInfoService,ProjectInfoDTO.class);// 文件读取/解析，并注册监听器EasyExcle.read(file.getInputStream(),ProjectInfoDTO.class,easyExcleListener).sheet(1).doRead();// 获取错误数据List<ExcelCheckErrDTO<ProjectInfoExcelDTO>> errList = easyExcleListener.getErrList();// 获取解析成功到数据List<ProjectinfoExcelDTO> successList = easyExcleListener.getSuccessList();// 如果错误数据不为空，将错误数据写入到excel文件，并输出到浏览器// 省略代码// 将成功到数据，批量写入到数据库中// 省略代码// 省略其他代码}}

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

public interface ProjectInfoService extends ExcelCheckManager{}@Service// 省略其他注解public class ProjectInfoServiceImpl implements ProjectInfoService {// 省略部分代码@Overridepublic ExcelCheckResultVO checkImportExcel(List<ProjectInfoExcelDTO> datas){// 省略代码}}

输出错误报告

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

@RestController// 省略其他注解public class ProjectInfoController {@Resourceprivate AsyncExcelService asyncExcelService;@Resourceprivate 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<ProjectInfoDTO> easyExcleListener = new EasyExcelListener(projectInfoService,ProjectInfoDTO.class);// 文件读取/解析，并注册监听器EasyExcle.read(file.getInputStream(),ProjectInfoDTO.class,easyExcleListener).sheet(1).doRead();// 获取错误数据List<ExcelCheckErrDTO<ProjectInfoExcelDTO>> errList = easyExcleListener.getErrList();// 获取解析成功到数据List<ProjectinfoExcelDTO> 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 <T extends AsyncTaskPramsDTO> {/** xxl-job server 端提供的创建任务接口 uri **/private final static String JOB_ADMIN_URI = "/outapi/asyn/";/** 与xxl-job server 通讯的加密密钥对 **/@Setterprotected String publicKey;/*** xxl-job server 回调对方法 */public abstract ReturnT<String> 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中，最后输出到客户端（浏览器）提供客户下载保存到本地。