easy-excel导入
张可人
使用easy excel导入excel数据到系统
1、自定义校验注解
/**
* <p>Excel导入Decimal类型校验</p>
*/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelDecimalValid {
String min();
String max();
String message() default "小数类型数字填写超出范围";
}
/**
* <p>Excel导入正则表达式校验</p>
*/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelPatternValid {
String regexp() default "";
String name() default "";
String message() default "不符合规则";
}
/**
* <p>Excel导入Int类型校验</p>
*/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelIntValid {
int min();
int max();
String message() default "整数数字填写超出范围";
}
/**
* <p>Excel导入字符串长度校验</p>
*/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelStrValid {
int length() default 0;
String message() default "文字填写超出长度要求";
}
/**
* <p>Excel导入必填校验注解</p>
*/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelValid {
String message() default "导入有未填入的字段";
/**
* 表头字段
*/
String name() default "";
}
2、校验类
/**
* <p>Excel导入字段校验</p
*/
public class ExcelImportValid {
/**
* Excel导入字段校验
*
* @param object 校验的JavaBean 其属性须有自定义注解
*/
@SneakyThrows
public static void valid(Object object) throws ExcelValidException {
Field[] fields = object.getClass().getDeclaredFields();
for (Field field : fields) {
//设置可访问
field.setAccessible(true);
//属性的值
Object fieldValue = null;
try {
fieldValue = field.get(object);
} catch (IllegalAccessException e) {
throw new RuntimeException(e);
}
//是否包含必填校验注解
boolean isExcelValid = field.isAnnotationPresent(ExcelValid.class);
if (isExcelValid && Objects.isNull(fieldValue)) {
String name = field.getAnnotation(ExcelValid.class).name();
throw new ExcelValidException("【" + name + "】列有未填值项");
}
//是否包含字符串长度校验注解
boolean isExcelStrValid = field.isAnnotationPresent(ExcelStrValid.class);
if (isExcelStrValid) {
String cellStr = fieldValue.toString();
int length = field.getAnnotation(ExcelStrValid.class).length();
if (StringUtils.isNotBlank(cellStr) && cellStr.length() > length) {
throw new ExcelValidException(field.getAnnotation(ExcelStrValid.class).message());
}
}
//是否包含int类型校验注解
boolean isExcelIntValid = field.isAnnotationPresent(ExcelIntValid.class);
if (isExcelIntValid) {
if (fieldValue instanceof Integer) {
int cellInt = Integer.parseInt(fieldValue.toString());
int min = field.getAnnotation(ExcelIntValid.class).min();
int max = field.getAnnotation(ExcelIntValid.class).max();
if (cellInt < min || cellInt > max) {
throw new ExcelValidException(field.getAnnotation(ExcelIntValid.class).message());
}
}
}
//是否包含decimal类型注解
boolean isExcelDecimalValid = field.isAnnotationPresent(ExcelDecimalValid.class);
if (isExcelDecimalValid) {
if (isBigDecimal(fieldValue.toString())) {
BigDecimal cellDecimal = new BigDecimal(fieldValue.toString());
BigDecimal min = new BigDecimal(field.getAnnotation(ExcelDecimalValid.class).min());
BigDecimal max = new BigDecimal(field.getAnnotation(ExcelDecimalValid.class).max());
if (cellDecimal.compareTo(min) < 0 || cellDecimal.compareTo(max) > 0) {
throw new ExcelValidException(field.getAnnotation(ExcelDecimalValid.class).message());
}
} else {
throw new ExcelValidException("不是小数数字类型");
}
}
// 是否包含正则校验
boolean isExcelPatternValid = field.isAnnotationPresent(ExcelPatternValid.class);
if (isExcelPatternValid) {
String cellStr = fieldValue.toString();
String pattern = field.getAnnotation(ExcelPatternValid.class).regexp();
String name = field.getAnnotation(ExcelPatternValid.class).name();
if (StringUtils.isBlank(name)) {
throw new ExcelValidException("【" + name + "】列有未填值项");
}
if (StringUtils.isNotBlank(cellStr) && !Pattern.matches(pattern, cellStr)) {
throw new ExcelValidException("【" + name + "】不符合规则");
}
}
}
}
private static boolean isBigDecimal(String decimal) {
try {
BigDecimal bd = new BigDecimal(decimal);
return true;
} catch (NumberFormatException e) {
return false;
}
}
}
3、自定义异常处理
/**
* 导入excel自定义异常类
*/
public class ExcelValidException extends RuntimeException {
private static final long serialVersionUID = 1L;
private int code;
public ExcelValidException(String msg) {
this(100, msg, null);
}
public ExcelValidException(int code, String msg) {
this(code, msg, null);
}
public ExcelValidException(int code, String msg, Exception nestedEx) {
super(msg, nestedEx);
this.code = code;
}
}
4、监听器
@Slf4j
public class ImportExcelDataListener<T> extends AnalysisEventListener<T> {
//service的名称
private final String serviceName;
// 校验通过数据处理方法
private final String accessMethodName;
// 异常数据处理
private final String errorMethodName;
private final String version;
private Map<String, Object> paramMap = new HashMap<>();
public ImportExcelDataListener(String serviceName, Map<String, Object> param) {
this.serviceName = serviceName;
this.accessMethodName = "savaSuccessForExcel";
this.errorMethodName = "saveErrorForExcel";
version = UUID.randomUUID().toString();
paramMap = param;
}
public ImportExcelDataListener(String serviceName) {
this.serviceName = serviceName;
this.accessMethodName = "savaSuccessForExcel";
this.errorMethodName = "saveErrorForExcel";
version = UUID.randomUUID().toString();
paramMap = new HashMap<>();
}
// 校验通过数据
private final List<T> successList = new LinkedList<>();
//异常数据 （key：原因，value：data项）
private final Map<String, List<T>> errorMap = new HashMap<>();
/**
* <p>所有数据解析完成后调用此方法</p>
*/
@SneakyThrows
@Override
public void doAfterAllAnalysed(AnalysisContext context) throws ExcelValidException {
//保存数据，确保最后的遗留数据也能保存到数据库或者进行其他操作
this.execute();
if (errorMap.size() > 0) {
this.executeError();
}
}
/**
* <p>数据转换异常时的处理，比如required是Integer，provided是"sss"的字符串</p>
*/
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
exception.printStackTrace();
if (exception instanceof ExcelDataConvertException) {
Integer columnIndex = ((ExcelDataConvertException) exception).getColumnIndex() + 1;
Integer rowIndex = ((ExcelDataConvertException) exception).getRowIndex() + 1;
String message = String.format("第%s行，第%s列数据格式有误，请核实", rowIndex, columnIndex);
throw new RuntimeException(message);
} else {
super.onException(exception, context);
}
}
@Override
public void invoke(T data, AnalysisContext analysisContext) {
//通用方法数据校验
try {
ExcelImportValid.valid(data);
} catch (ExcelValidException e) {
List<T> ts = errorMap.get(e.getMessage());
if (CollectionUtils.isEmpty(ts)) {
ts = new LinkedList<>();
}
ts.add(data);
errorMap.put(e.getMessage(), ts);
return;
}
//将解析完的数据加入到list中
successList.add(data);
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if (successList.size() >= 600) {
try {
this.execute();
} catch (Exception e) {
e.printStackTrace();
}
// 存储完成清理 list
successList.clear();
}
}
//执行数据保存的操作
public void execute() throws Exception {
try {
Object beanService = SpringUtil.getBean(serviceName + "Impl");
Class<?> beanClass = beanService.getClass();
Method method = beanClass.getMethod(accessMethodName, List.class, String.class, Map.class);
method.invoke(beanService, successList, version, paramMap);
} catch (NoSuchMethodException | IllegalAccessException e) {
log.warn("import error:", e);
throw new RuntimeException("导入执行保存失败");
} catch (InvocationTargetException e) {
e.printStackTrace();
throw new ExcelValidException("有异常数据");
}
}
//执行错误数据处理的操作
public void executeError() {
try {
Object beanService = SpringUtil.getBean(serviceName + "Impl");
Class<?> beanClass = beanService.getClass();
Method method = beanClass.getMethod(errorMethodName, Map.class, String.class);
method.invoke(beanService, errorMap, version);
} catch (NoSuchMethodException | IllegalAccessException e) {
log.warn("execute error:", e);
throw new RuntimeException("处理错误数据失败");
} catch (InvocationTargetException e) {
throw new ExcelValidException("有异常数据");
}
}
}
5、数据处理接口，使用范型
/**
* 导入数据处理接口
*/
public interface EasyExcelImportService<T> {
/**
* 异常数据处理方法 <errorMsg,data>
* @param errorMap
*/
void saveErrorForExcel(Map<String, List<T>> errorMap,String version) throws ExcelValidException;
/**
* 正常数据处理
* @param entityList
* @param param 参数
*/
void savaSuccessForExcel(List<T> entityList,String version, Map<String,Object> param);
}
6、异常数据实体类（把校验失败的数据存入数据库，方便做导出，根据自己的具体业务自定义字段）
public class ImportErrorLog {
@TableId(type = IdType.ASSIGN_UUID)
private String id;
/**
* 服务名称
*/
private String service;
/**
* json数据
*/
private String data;
/**
* 唯一标识
*/
private String version;
/**
* 创建时间
*/
@TableField(fill = FieldFill.INSERT)
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private Date createTime;
}
7、日期类型需要自定义转换器
/**
* 日期转换类
*/
public class LocalDateConverter implements Converter<LocalDate> {
@Override
public Class<LocalDate> supportJavaTypeKey() {
return LocalDate.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public LocalDate convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
if (cellData.getType().equals(CellDataTypeEnum.NUMBER)) {
LocalDate localDate = LocalDate.of(1900, 1, 1);
//excel 有些奇怪的bug, 导致日期数差2
localDate = localDate.plusDays(cellData.getNumberValue().longValue() - 2);
return localDate;
}else {
return LocalDate.parse(cellData.getStringValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd"));
}
}
@Override
public WriteCellData<?> convertToExcelData(LocalDate value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
return new WriteCellData<>(value.format(DateTimeFormatter.ofPattern("yyyy-MM-dd")));
}
}
8、业务处理类实现EasyExcelImportService接口即可
public class DemoServiceImpl implements EasyExcelImportService<Demo> {
/**
* 异常数据处理
*/
@Override
public void saveErrorForExcel(Map<String, List<Demo>> errorMap, String version) throws ExcelValidException {
LinkedList<Demo> errorList = new LinkedList<>();
/**
* errorMap 的key是错误信息，value是excel的一行数据
*/
ImportErrorLog errorLog = importErrorLogMapper.selectOne(new QueryWrapper<ImportErrorLog>()
.eq("version", version)
.last("limit 1")
);
// 因为注解只能校验数据的合法性，并不能校验是否符合业务逻辑，所以这里加了版本号，同一批次的错误信息保存一条记录
if (errorLog == null){
errorLog = ImportErrorLog.builder()
.service("service")
.version(version)
.data(JSON.toJSONString(errorList))
.build();
// 入库
importErrorLogMapper.insert(errorLog);
}else {
String data = errorLog.getData();
List<Demo> importDemoList = JSONObject.parseArray(data, Demo.class);
errorList.addAll(importDemoList);
errorLog.setData(JSON.toJSONString(errorList));
// 入库
importErrorLogMapper.updateById(errorLog);
}
// 为了提醒前端有异常数据
throw new ExcelValidException("have error data");
}
// 正常信息处理
@Override
public void savaSuccessForExcel(List<Demo> entityList, String version, Map<String,Object> param) {
if (CollectionUtils.isEmpty(entityList)) {
return;
}
// 业务逻辑校验
Map<String,List<Demo>> errorMap = new HashMap<>();
Iterator<Demo> iterator = entityList.iterator();
while (iterator.hasNext()){
Demo next = iterator.next();
if (projectTempByCodeMap.get(next.getProjectCode()) == null){
iterator.remove();
List<Demo> importDemoList = errorMap.get("项目编码错误");
if (CollectionUtils.isEmpty(importDemoList)){
importDemoList = new LinkedList<>();
}
importPmDailyList.add(next);
errorMap.put("项目编码错误",importPmDailyList);
}
}
if (CollectionUtils.isEmpty(entityList)){
// 存储错误信息
if (errorMap.size()>0){
saveErrorForExcel(errorMap, version);
}
return;
}
// 业务逻辑处理。。。。。。
}
9、如果想导出某一批次的异常数据，直接查询数据库，做导出即可
类似资料：