运用EasyExcel 导入数据，失败原由数据导出-天涯论坛

4lqedz 发表于 2024-10-6 10:00:00

运用EasyExcel 导入数据，失败原由数据导出

<h1 style="color: black; text-align: left; margin-bottom: 10px;">引言</h1>
在平常研发过程中，Excel 导入是非常平常的场景，况且亦有非常多开源的项目是针对Excel的读写的，如Apache 的poi ，近期用的比较好的还是阿里的EasyExcel 开源工具。平时咱们只是简单的读取文件并写入数据库持久化就可，然则前段时间，制品搞了个需要，需要将导入失败的数据及原由写入Excel并下载，那这就有得玩了，废话不多说，上才艺。
<h1 style="color: black; text-align: left; margin-bottom: 10px;">制品需要</h1>导入Excel数据数据格式校验数据合法性校验（校验数据库）失败数据供给用户下载，并支持再次导入
<h1 style="color: black; text-align: left; margin-bottom: 10px;">技术选型</h1>https://github.com/alibaba/easyexcel ，Excel 读取/写入https://www.xuxueli.com/xxl-job/ ，做异步处理
<h1 style="color: black; text-align: left; margin-bottom: 10px;">需要实现</h1>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">项目依赖(maven)</h1>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcle</artifactId>
<version>2.2.6</version>
</dependency>

<dependency>
<groupId>com.xuxueli</groupId>
<artifactId>xxl-job-core</artifactId>
<version>${xxl-job.version}</version>
</dependency>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">文件解析</h1>
解析导入文件，获取文件数据量，用于判定导入是不是走异步导入。public class EasyExcelUtils {

/**
 *
 * 解析文件，获取最后一行
 * @param inputStream 文件流
 * @paramsheetNum 读取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();){
// 省略部分代码
}
returnsheet.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 ){
throwCustomExcetpoin(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);
returnvalue ==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 接口，亦能够按照自己的实质场景实现关联规律。
<h1 style="color: black; text-align: left; margin-bottom: 10px;">数据合法校验</h1>
导入数据文件解析运用的是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;
}@Override
public 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 = newHashMap<>();
Field field;
Field[] fields = clazz.getDeclaredFields();for (int i = 0; i < fields.length; i++){
field = clazz.getDeclaredField(fields.getName());
field.setAccessible(true);
ExcelProperty excleProperty = field.getAnnotation(ExcelProperty.class);
if (excelProperty != null){
intindex = 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 <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());
ExcelPropertyannotation= 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 = newArrayList<>();/** excel 对象的反射类 **/
private Class<T> clazz;

private List<T> list;

private ExcelCheckManager<T> excelCheckManager;

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

@Override
public voidinvok(T t,AnalysisContext context){// 数据解析/转换完成，标记进入到解析起
baseMatching = true;
String errMsg;
try {
// 调用验证器验证数据格式
errMsg = EasyExcelValidatorHelper.validateEntity(t);
}catch(Exception e){
errMsg ="解析数据出错";
// 省略部分代码
}
// 校验不经过
if (!StringUtils.isEmpty(errMsg){
// 将错误数据放入错误列表中
ExcelChcekErrDTO errDTO = newExcelChcekErrDTO(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 voiddoAfterAllAnalysed(AnalysisContext context){
ExcelCheckResultVO excelCheckResultVO = excelCheckManager.checkImportExcel(list);
successList.addAll(excelCheckResultVO.getSuccessDatas());
errList.addAll(excelCheckResultVO.getErrDatas());
list.clear();
}@Override
public 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 = newHashMap<>();
Field field;
Field[] fields = clazz.getDeclaredFields();for (int i = 0; i < fields.length; i++){
field = clazz.getDeclaredField(fields.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 (Stringv : 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
privateAsyncExcelService 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<ProjectInfoDTO> easyExcleListener = newEasyExcelListener(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 {
// 省略部分代码

@Override
public ExcelCheckResultVO checkImportExcel(List<ProjectInfoExcelDTO> 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<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 ){
// 省略部分代码
}
// 将成功到数据，批量写入到数据库中
// 省略代码

// 省略其他代码
}
}<h1 style="color: black; text-align: left; margin-bottom: 10px;">异步导入</h1>
异步导入操作，将思考几个问题：导入文件存到什么地区？当一个同步请求结束之后，后续咱们想再次拿到该请求到数据，咱们应该思虑将文件放到某一个单独到地区，供给咱们二次运用，例如：自己到文件服务器、oss 存储等，这儿咱们运用自己的文件服务器。怎么异步执行？咱们能够运用新启用一个本地线程去执行咱们的操作，不影响当前请求主线程的操作，亦是能够的，然则思虑到执行重试问题，咱们将运用(#xxl-job)分布式调度系统，进行调度执行任务。客户怎样查看任务执行状态？咱们需要供给一个任务执行日志列表，让用户能够清晰的看到这次导出的任务是不是执行完成/是不是存在导入错误。怎么将错误报告输出给到客户？咱们需要将导入到错误报告文件（excel）上传至文件服务器，供给用户二次或多次下载运用；同期，需要将文件信息保留至任务执行日志信息中，为用户供给下载入口。
定义通用的job handler 父类 AsyncTaskHandler ，所有需要运用xxl-job 发起异步任务和给xxl-job 发起回调，都需要继承AsyncTaskHandler ，并实现execute 抽象办法。public abstract classAsyncTaskHandler <Textends AsyncTaskPramsDTO> {

/** 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<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;

}<h1 style="color: black; text-align: left; margin-bottom: 10px;">数据导出</h1>
数据导出功能常指，客户想将系统中的关联（根据查找要求筛选）数据经过excel形式保留到自己本地。在数据导出过程中，需要经过数据筛选要求将数据从系统数据库中筛选出来，而后经过必定格式（excel导出模版格式）写入到excel中，最后输出到客户端（浏览器）供给客户下载保留到本地。

页: [1]

天涯论坛's Archiver

运用EasyExcel 导入数据，失败原由数据导出