easyExcel+validation+正则实现excel导入校验

easyExcel+validation+正则实现excel导入校验

导入excel的时候，除了数据库校验数据以外，还会碰到数据格式校验，如果直接报错，显得客户体验很不好，我想到的做法是，导入正确的数据，错误的数据重新导出，并在后面加上错误信息。

环境准备：
spring:springboot2.X
easyExcel:2.1.4
lombok(非必须)
easyExcel官网：https://alibaba-easyexcel.github.io/index.html

代码
引入easyExcel

<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>

这里需要注意的是各个版本的poi需要对应，如果版本不对会导致代码出错。
通过easyExcel官方文档我们知道，easyExcel有一个监听器，用来读取excel数据，并且监听器不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
下面是我的监听器的写法

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 lombok.Data;
import org.apache.commons.lang3.StringUtils;import java.lang.reflect.Field;
import java.util.*;/*** @author zhy* @title: EasyExcelListener* @projectName cec-moutai-bd-display* @description: easyExcel监听器* @date 2019/12/2318:28*/
@Data
public class EasyExcelListener <T>  extends AnalysisEventListener<T> {//成功结果集private List<ExcelImportSucObjectDto> successList = new ArrayList<>();//失败结果集private List<ExcelImportErrObjectDto> errList = new ArrayList<>();//处理逻辑serviceprivate ExcelCheckManager excelCheckManager;private List<T> list = new ArrayList<>();//excel对象的反射类private Class<T> clazz;public EasyExcelListener(ExcelCheckManager excelCheckManager){this.excelCheckManager = excelCheckManager;}public EasyExcelListener(ExcelCheckManager excelCheckManager,Class<T> clazz){this.excelCheckManager = excelCheckManager;this.clazz = clazz;}@Overridepublic void invoke(T t, AnalysisContext analysisContext) {String errMsg;try {//根据excel数据实体中的javax.validation + 正则表达式来校验excel数据errMsg = EasyExcelValiHelper.validateEntity(t);} catch (NoSuchFieldException e) {errMsg = "解析数据出错";e.printStackTrace();}if (!StringUtils.isBlank(errMsg)){ExcelImportErrObjectDto excelImportErrObjectDto = new ExcelImportErrObjectDto(t, errMsg);errList.add(excelImportErrObjectDto);}else{list.add(t);}//每1000条处理一次if (list.size() > 1000){//校验ExcelImportResult result = excelCheckManager.checkImportExcel(list);successList.addAll(result.getSuccessDtos());errList.addAll(result.getErrDtos());list.clear();}}//所有数据解析完成了 都会来调用@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {ExcelImportResult result = excelCheckManager.checkImportExcel(list);successList.addAll(result.getSuccessDtos());errList.addAll(result.getErrDtos());list.clear();}/*** @description: 校验excel头部格式，必须完全匹配* @param headMap 传入excel的头部（第一行数据）数据的index,name* @param context* @throws* @return void* @author zhy* @date 2019/12/24 19:27*/@Overridepublic 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.isBlank(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 */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;}
}

从上到下分别解释一下，或者贴一下代码

成功结果集ExcelImportSucObjectDto

import lombok.Data;/*** @author zhy* @title: ExcelImportErrObjectDto* @projectName cec-moutai-bd-display* @description: excel单条数据导入结果* @date 2019/12/2318:23*/
@Data
public class ExcelImportSucObjectDto {private Object object;}

失败结果集ExcelImportErrObjectDto

import lombok.Data;/*** @author zhy* @title: ExcelImportErrObjectDto* @projectName cec-moutai-bd-display* @description: excel单条数据导入结果* @date 2019/12/2318:23*/
@Data
public class ExcelImportErrObjectDto {private Object object;private String errMsg;public ExcelImportErrObjectDto(){}public ExcelImportErrObjectDto(Object object,String errMsg){this.object = object;this.errMsg = errMsg;}
}

excel数据业务校验接口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.ExcelImportResult * @author zhy* @date 2019/12/24 14:57 */<T> ExcelImportResult checkImportExcel(List<T> objects);
}

需要校验excel业务的service接口可以继承这个接口，并在实现类中实现自己的方法，返回的是成功失败的结果集ExcelImportResult

import lombok.Data;import java.util.ArrayList;
import java.util.List;/*** @author zhy* @title: ExcelImportErrObjectDto* @projectName cec-moutai-bd-display* @description: excel数据导入结果* @date 2019/12/2318:23*/
@Data
public class ExcelImportResult {private List<ExcelImportSucObjectDto> successDtos;private List<ExcelImportErrObjectDto> errDtos;public ExcelImportResult(List<ExcelImportSucObjectDto> successDtos,List<ExcelImportErrObjectDto> errDtos){this.successDtos =successDtos;this.errDtos = errDtos;}public ExcelImportResult(List<ExcelImportErrObjectDto> errDtos){this.successDtos =new ArrayList<>();this.errDtos = errDtos;}
}

业务校验通过的也就是成功结果，可以在实现类校验完毕的方法最后进行持久化操作，并不需要返回

下面最重要的就是EasyExcelValiHelper.validateEntity(t)方法了
该类实现了通过正则校验excel数据的功能，在此之前，看看我的excel数据实体类是怎么写的：

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.cec.moutai.common.easyexcel.ExcelPatternMsg;
import lombok.Data;
import org.hibernate.validator.constraints.Length;import javax.validation.constraints.DecimalMin;
import javax.validation.constraints.Pattern;/*** @author zhy* @title: CustomerExcelDto* @projectName cec-moutai-bd-display* @description: 客户excel对象* @date 2019/12/2410:35*/
@Data
public class CustomerExcelDto {@ExcelProperty(index = 0,value = "客户全称")@ColumnWidth(30)private String name;//客户简称@ExcelProperty(index = 1,value = "客户简称")@ColumnWidth(30)@Length(max = 100)private String shortName;// 客户编码@ExcelProperty(index = 2,value = "客户编码(导入必填)")@ColumnWidth(30)private String code;@ExcelProperty(index = 3,value = "客户分类")private String custclassName;// 法人@ExcelProperty(index = 6,value = "法定代表人")@Length(max = 100)private String legalbody;// 营业期限@ExcelProperty(index = 24,value = "营业期限(导入格式：yyyy-MM-dd)")@Pattern(regexp = ExcelPatternMsg.DATE2,message = ExcelPatternMsg.DATE2_MSG)private String businessEndDate;}

@ExcelProperty是easyExcel自带的注解
@ColumnWidth也是easyExcel的注解代表单元格宽度
@Length代表的是字符串长度，max代表的是最长允许多长
@Pattern就是正则表达式注解了，regexp代表的是正则表达式，message代表是，没有匹配成功返回的错误信息
关于validation的注解可以参考此篇大佬的博客：https://blog.csdn.net/weixin_42546729/article/details/89364431

可以发现，我这里的接收对象都是String类型的，也正因为是这样，才能通过正则表达式去校验各种格式。
所以在excel类和真正的实体类之间的转换，我是用fastjson的JSON.parseObject来进行转换的。所以需要保证excel类和数据库实体的字段名要保持一致，数据格式，尤其是日期格式，要保持一致。

关于输出的错误结果集的实体类，我这里就不赘述了，其实就是一个类继承这个excel实体，然后多一个String类型的errMsg字段，用于存放错误信息

下面是我目前为止用到的正则表达式，也就是ExcelPatternMsg

import java.util.regex.Pattern;/*** @author zhy* @title: ExcelPatternMsg* @projectName cec-moutai-bd-display* @description: excel正则表达式，以及错误信息* @date 2019/12/2614:22*/
public class ExcelPatternMsg {//只能输入整数或者小数public static final String DECIMAL = "^[0-9]+\\.{0,1}[0-9]{0,2}$";public static final String DECIMAL_MSG = "只能输入整数或者小数";//日期格式 yyyy/MM/ddpublic 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-ddpublic 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";//日期格式 yyyyMMddpublic 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:sspublic 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:sspublic 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";//日期格式 yyyyMMddHHmmsspublic 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";//日期格式 yyyyMMddHHmmssSSSpublic 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:sspublic 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";//数字和字母public static final String NUMBER_LETTER = "^[a-z0-9A-Z]+$";public static final String NUMBER_LETTER_MSG = "只能输入数字和字母";//数字public static final String NUMBER = "^[0-9]*$";public static final String NUMBER_MSG = "只能输入数字";public static void main(String[] args) {System.out.println(Pattern.matches(DATE1,"2020/02/30"));}
}

言归正传，看看EasyExcelValiHelper.validateEntity(t)的写法

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.ArrayList;
import java.util.List;
import java.util.Set;public class EasyExcelValiHelper {private static Validator validator = Validation.buildDefaultValidatorFactory().getValidator();public static <T> String validateEntity(T obj) throws NoSuchFieldException, SecurityException {StringBuilder result = new StringBuilder();Set<ConstraintViolation<T>> set = validator.validate(obj, Default.class);if (set != null && set.size() != 0) {for (ConstraintViolation<T> cv : set) {Field declaredField = obj.getClass().getDeclaredField(cv.getPropertyPath().toString());ExcelProperty annotation = declaredField.getAnnotation(ExcelProperty.class);result.append(annotation.value()[0]+cv.getMessage()).append(";");}}return result.toString();}}

返回的是String也就是错误信息，在

处调用

导入时的写法

@PostMapping(value = "importExcel")
@ApiOperation(value = "客户信息导入")
@SysServiceLog(operateContent = "客户信息导入", operateType = Globals.Log_Type_UPLOAD)
public Result importExcel(HttpServletResponse response,@RequestParam MultipartFile file) throws IOException {EasyExcelListener easyExcelListener = new EasyExcelListener(customerManager,CustomerExcelDto.class);EasyExcel.read(file.getInputStream(),CustomerExcelDto.class,easyExcelListener).sheet().doRead();//错误结果集List<ExcelImportErrObjectDto> errList = easyExcelListener.getErrList();if (errList.size() > 0){List<CustomerCompleteDtoImprotResult> completeDtoImprotResults = errList.stream().map(excelImportErrObjectDto -> {CustomerCompleteDtoImprotResult customerCompleteDtoImprotResult = BeanUtils.convert(excelImportErrObjectDto.getObject(), CustomerCompleteDtoImprotResult.class);customerCompleteDtoImprotResult.setErrMsg(excelImportErrObjectDto.getErrMsg());return customerCompleteDtoImprotResult;}).collect(Collectors.toList());//导出excelEasyExcelUtils.webWriteExcel(response,completeDtoImprotResults,CustomerCompleteDtoImprotResult.class,"客户信息");}return addSucResult();
}

最终的结果是：

image.png
最后贴一下我的excel web导出工具类

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.IndexedColors;import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;/*** @author zhy* @title: EasyExcelUtils* @projectName cec-moutai-bd-display* @description: easyExcel工具类* @date 2019/12/2411:35*/
public class EasyExcelUtils {public static void webWriteExcel(HttpServletResponse response, List objects, Class clazz, String fileName) throws IOException {String sheetName = fileName;webWriteExcel(response,objects,clazz,fileName,sheetName);}public static void webWriteExcel(HttpServletResponse response, List objects, Class clazz, String fileName, String sheetName) throws IOException {response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");// 头的策略WriteCellStyle headWriteCellStyle = new WriteCellStyle();// 背景设置为白headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());// 内容的策略WriteCellStyle contentWriteCellStyle = new WriteCellStyle();HorizontalCellStyleStrategy horizontalCellStyleStrategy =new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);ServletOutputStream outputStream = response.getOutputStream();try {EasyExcel.write(outputStream, clazz).registerWriteHandler(horizontalCellStyleStrategy).sheet(sheetName).doWrite(objects);}catch (Exception e){e.printStackTrace();}finally {outputStream.close();}}
}