十一. excel校验数据工具类
在开发中, 我们会做过很多的excel导入操作, 数据的校验无非就是: 我们引入easyExcel工具, 用来读取excel的数据, 引入方法这里就不介绍了 我们准备两个实体类, 一个是用来直接读取excel数据的模板类, 另一个是储存业务数据的实体类 准备一个校验注解, 用来记录错误信息的识别及处理 说明 模板的数据类型全部为 约定一. 概述
非空判断
/ 数据类型判断
/ 数据有效性判断
, 结合我以往的校验经验总结了几个工具类, 省掉以往每次重复的校验工作二. 前期准备
2.1 校验注解
@Target({ElementType.FIELD}) //作用的位置@Retention(RetentionPolicy.RUNTIME) //作用域@Documentedpublic @interface ExcelParamCheck {
/**
* 是否必填,默认为否
* @return
*/
boolean ifRequired() default false;
/**
* 必填为空时报错提示
* @return
*/
String errorMsg() default "参数不能为空";
/**
* 字典值: 用来给对应字段做个值的转换, 从指定字典查找
* @return
*/
String dicCode() default "";
/**
* 字典校验报错提示
* @return
*/
String dicErrorMsg() default "字典编码不存在";
/**
* 填写格式错误提示
* @return
*/
String formatErrorMsg() default "填写格式错误";}
2.2 导入数据实体类
@Datapublic class ImportModel implements Serializable {
@ExcelParamCheck(ifRequired = true,errorMsg = "用户名不能为空")
@ExcelProperty(value = "用户名", index = 0)
private String username;
@ExcelParamCheck(formatErrorMsg = "年龄格式错误")
@ExcelProperty(value = "年龄", index = 1)
private String age;
@ExcelParamCheck(formatErrorMsg = "生日格式错误")
@ExcelProperty(value = "生日", index = 2)
private String birthday;
// 导进来的数据是名字, 根据字典转换为编码
@ExcelParamCheck(dicCode = "STATUS",dicErrorMsg = "状态字典值不存在")
@ExcelProperty(value = "状态", index = 3)
private String status;
@ExcelProperty(value = "错误提示", index = 4)
private String errorMsg;}
String
, 先把数据全部读取再校验数据类型错误提示
字段名为: errorMsg
2.3 业务数据实体类
@Datapublic class FormalData implements Serializable {
/**
* 用户名
*/
private String username;
/**
* 年龄
*/
private Integer age;
/**
* 生日
*/
private LocalDate birthday;
/**
* 状态
*/
private String status;}
三. 工具示例
3.1 用easyExcel读取数据
/**
* 测试简单导入
* @throws Exception
*/
@Test
public void simpleImport() throws Exception{
// 输入流
FileInputStream inputStream = new FileInputStream("D:/导入.xlsx");
//读取数据
List<ImportModel> datas = readExcelWithModelNew(inputStream, ImportModel.class);
// 打印
System.out.println(JSON.toJSONString(datas));
}
/**
* 使用 模型 来读取Excel
*
* @param fileInputStream Excel的输入流
* @param tClass 模型的类
* @return 返回 模型 的列表(为object列表,需强转)
*/
public static <T> List<T> readExcelWithModelNew(InputStream fileInputStream, Class<T> tClass) throws IOException {
AnalysisEventListenerImpl<T> listener = new AnalysisEventListenerImpl<T>();
ExcelReader excelReader = EasyExcel.read(fileInputStream, tClass, listener).build();
ReadSheet readSheet = EasyExcel.readSheet(0).build();
excelReader.read(readSheet);
excelReader.finish();
return listener.getDatas();
}
3.2 检查导入数据非空校验和字典
/**
* 检查导入数据非空校验和字典
* <<约定: 错误信息字段名为: errorMsg>></约定:>
* @param list
* @param errorFlag
* @return <T>
* @throws IOException
*/
public static <T> void checkParamNoNullAndDic(List<T> list, AtomicBoolean errorFlag) {
if(CollectionUtils.isNotEmpty(list)){
// 先获取所有的字典
Map<String, Map<String,String>> dicMap = new HashMap<>();
Class<?> aClass = list.get(0).getClass();
Field[] fields = aClass.getDeclaredFields();
// 有ExcelParamCheck注解的属性
List<Field> fieldsList = new ArrayList<>();
if(!ObjectUtils.isEmpty(fields)){
BaseClient baseClient = SpringContextHolder.getBean(BaseClient.class.getName());
Arrays.stream(fields).forEach(field -> {
// 参数检查注解
ExcelParamCheck excelParamCheck = field.getAnnotation(ExcelParamCheck.class);
if(null != excelParamCheck){
// 字典编码
String dicCode = excelParamCheck.dicCode();
if (!ObjectUtils.isEmpty(dicCode)) {
Map<String, String> dicNameCode = getDicNameCode(dicCode, baseClient);
dicMap.put(dicCode,dicNameCode);
}
fieldsList.add(field);
}
});
if (CollectionUtils.isNotEmpty(fieldsList)) {
list.forEach(obj -> {
StringBuffer errorMsg = new StringBuffer();
try {
for(Field field : fieldsList){
field.setAccessible(true);
Object value = field.get(obj);
ExcelParamCheck annotation = field.getAnnotation(ExcelParamCheck.class);
String dicCode = annotation.dicCode(); // 字典编码
boolean ifRequired = annotation.ifRequired(); // 是否必填
if(ObjectUtils.isEmpty(value)){
if(ifRequired){
if(ObjectUtils.isEmpty(value)){
String localeMsg = LocaleHandler.getLocaleMsg(annotation.errorMsg());
errorMsg.append(localeMsg+" ;");
errorFlag.set(true);
}
}
}else {
String valueStr = value.toString().trim();
if(!ObjectUtils.isEmpty(dicCode)){
Map<String, String> nameCodeMap = dicMap.get(dicCode);
String code = nameCodeMap.get(valueStr);
if(!ObjectUtils.isEmpty(code)){
field.set(obj,code);
}else {
String localeMsg = LocaleHandler.getLocaleMsg(annotation.dicErrorMsg());
errorMsg.append(localeMsg+" ;");
errorFlag.set(true);
}
}
}
}
Field field = aClass.getDeclaredField("errorMsg");
field.setAccessible(true);
if(errorMsg.length() > 0){
field.set(obj,errorMsg.toString());
}else {
field.set(obj,null);
}
} catch (Exception e) {
log.error("检查导入数据非空和字典工具类报错"+e.getMessage());
log.error("检查导入数据非空和字典工具类报错"+e);
throw new BaseException("检查导入数据非空和字典工具类报错!");
}
});
}
}
}
}
3.3 检查字段名一样的字段, 检查数据类型并赋值
/**
* 检查字段名一样的字段, 检查数据类型并赋值
* @param list
* @param aClass
* @param errorFlag
* @param <T>
* @param <V>
* @return
*/
public static <T,V> List<V> dataTransform(List<T> list,Class<V> aClass,AtomicBoolean errorFlag){
List<V> vList = new ArrayList<>();
if (CollectionUtils.isNotEmpty(list)) {
// 业务类字段
Field[] businessFields = aClass.getDeclaredFields();
// 导入实体类字段
Class importClass = list.get(0).getClass();
Field[] importFields = importClass.getDeclaredFields();
if(!ObjectUtils.isEmpty(businessFields) && !ObjectUtils.isEmpty(importFields)){
// 业务字段
Map<String, Field> businessFieldMap = Arrays.stream(businessFields).collect(Collectors.toMap(Field::getName, Function.identity()));
// 导入数据字段
Map<String, Field> importFieldMap = Arrays.stream(importFields).collect(Collectors.toMap(Field::getName, Function.identity()));
for(T obj:list){
// 手机错误信息
StringBuffer errorMsg = new StringBuffer();
V businessInstance = null;
try {
businessInstance = aClass.newInstance();
} catch (Exception e) {
log.error("创建实体类报错"+e.getMessage());
log.error("创建实体类报错"+e);
}
if (null != businessInstance) {
V finalBusinessInstance = businessInstance;
businessFieldMap.forEach((fieldName, field) -> {
field.setAccessible(true);
// 导入的字段
Field importField = importFieldMap.get(fieldName);
if(null != importField){
String typeName = field.getType().getSimpleName();
Object value = null;
try {
// 获取导入的值
importField.setAccessible(true);
value = importField.get(obj);
if (!ObjectUtils.isEmpty(value)) {
String valueStr = value.toString();
switch (typeName){
case "Long":
long aLong = Long.parseLong(valueStr);
field.set(finalBusinessInstance,aLong);
break;
case "Integer":
int i1 = Integer.parseInt(valueStr);
field.set(finalBusinessInstance,i1);
break;
case "BigDecimal":
BigDecimal decimal = new BigDecimal(valueStr);
field.set(finalBusinessInstance,decimal);
break;
case "Date":
Date date = DateUtil.parseDate(valueStr);
field.set(finalBusinessInstance,date);
break;
case "LocalDate":
Date date1 = DateUtil.parseDate(valueStr);
LocalDate localDate = DateUtil.dateToLocalDate(date1);
field.set(finalBusinessInstance,localDate);
break;
case "LocalDateTime":
Date date2 = DateUtil.parseDate(valueStr);
LocalDateTime localDateTime = DateUtil.dateToLocalDateTime(date2);
field.set(finalBusinessInstance,localDateTime);
break;
case "Double":
double aDouble = Double.parseDouble(valueStr);
field.set(finalBusinessInstance,aDouble);
break;
default:
field.set(finalBusinessInstance,valueStr);
}
}
} catch (Exception e) {
ExcelParamCheck annotation = importField.getAnnotation(ExcelParamCheck.class);
String formatErrorMsg = "字段格式错误";
if(null != annotation){
formatErrorMsg = annotation.formatErrorMsg();// String localeMsg = LocaleHandler.getLocaleMsg(formatErrorMsg);
}
errorMsg.append(formatErrorMsg+" ;");
errorFlag.set(true);
}
}
});
vList.add((V) finalBusinessInstance);
try {
Field errorMsgField = importClass.getDeclaredField("errorMsg");
errorMsgField.setAccessible(true);
if(errorMsg.length() > 0){
errorMsgField.set(obj,errorMsg.toString());
}else {
errorMsgField.set(obj,null);
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
return vList;
}
作者:任未然
链接:https://www.jianshu.com/p/0e329f917c13