引言
在日常开发过程中，Excel 导入是非常常见的场景，而且也有很多开源的项目是针对Excel的读写的，如Apache 的poi ，最近用的比较好的还是阿里的EasyExcel 开源工具。平时我们只是简单的读取文件并写入数据库持久化即可，但是前段时间，产品搞了个需求，需要将导入失败的数据及原因写入Excel并下载，那这就有得玩了，废话不多说，上才艺。
产品需求
- 导入Excel数据
- 数据格式校验
- 数据合法性校验（校验数据库）
- 失败数据提供用户下载，并支持再次导入
技术选型
- https://github.com/alibaba/easyexcel ，Excel 读取/写入
- https://www.xuxueli.com/xxl-job/ ，做异步处理
需求实现
项目依赖(maven)
文件解析
解析导入文件，获取文件数据量，用于判定导入是否走异步导入。
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
其中，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
备注：@Data 属于 lombok 工具，简化Bean的封装，感兴趣的同学，可以自行查阅资料。
定义Excel导入校验返回的数据VO
@Data
// 省略其他注解
public class ExcelCheckResultVO
定义数据解析监听器EasyExcelListener
@Data
// 省略部分注解
public class EasyExcelListener
定义excel 业务校验管理器 ExcelCheckManager,需要做业务校验的（与数据库匹配等）需要实现该接口
public interface ExcelCheckManager
表头校验
使用EasyExcelListener 用来监听数据解析过程，其中，invokHeadMap 方法将在解析完成excel表头时将被执行
@Data
// 省略部分注解
public class EasyExcelListener
数据非空、格式校验
数据非空校验、格式校验，我们将使用hibernate-validator 校验器进行校验格式。
定义validator 工具类
@component
public class EasyExcelValidatorHelper {
private static Validtor validtor;
@Autowired
public EasyExcelValidatorHelper(Validtor validtor){
this.EasyExcelValidatroHelper.validtor = validtor;
}
public static
数据格式校验，使用EasyExcelListener 用来监听数据解析过程，其中，invok 方法将逐行解析excel数据的时候将被调用
@Data
// 省略部分注解
public class EasyExcelListener
对需要进行校验对字段添加注解
@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
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
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
数据导出
数据导出功能常指，客户想将系统中的相关（按照查询条件筛选）数据通过excel形式保存到自己本地。在数据导出过程中，需要通过数据筛选条件将数据从系统数据库中筛选出来，然后通过一定格式（excel导出模版格式）写入到excel中，最后输出到客户端（浏览器）提供客户下载保存到本地。
