日常开发中,我们经常用到excel的导入,由于excel导入的的步骤相似度较大,本文基于自定义参数解析器实现对excel导入基本操作的抽离,在项目中快速实现excel的基本导入功能.
二 自定义参数解析器(HandlerMethodArgumentResolver )自定义参数解析器需要实现 HandlerMethodArgumentResolver 接口，我们先来看看该接口：
public interface HandlerMethodArgumentResolver {
boolean supportsParameter(MethodParameter parameter);
@Nullable
Object resolveArgument(MethodParameter parameter, @Nullable ModelAndViewContainer mavContainer,
NativeWebRequest webRequest, @Nullable WebDataBinderFactory binderFactory) throws Exception;
}
这个接口中就两个方法：
- supportsParameter：该方法表示是否启用这个参数解析器，返回 true 表示启用，返回 false 表示不启用。
- resolveArgument：这是具体的解析过程，就是从 request 中取出参数的过程，方法的返回值就对应了接口中参数的值。既把这个最终的结果赋值给当前参数
下文中的excle导入组件基于easyexcel实现
申明一个导入注解
@Documented
@Target({ ElementType.PARAMETER })
@Retention(RetentionPolicy.RUNTIME)
public @interface RequestExcel {
String fileName() default "file";
boolean ignoreEmptyRow() default false;
Class> readListener() default DefaultListAnalysisEventListener.class;
}
申明数据读取监听器
抽象一个list监听器,在里面定义一个获得数据和获取数据校验失败的结果集
public abstract class ListAnalysisEventListenerextends AnalysisEventListener { public abstract List getList(); public abstract List getErrors(); } @Slf4j public class DefaultListAnalysisEventListener extends ListAnalysisEventListener
定义校验工具及错误信息
@Data
@NoArgsConstructor
@AllArgsConstructor
public class ErrorMessage {
private Long lineNum;
private Set errors = new HashSet<>();
public ErrorMessage(Set errors) {
this.errors = errors;
}
public ErrorMessage(String error) {
HashSet objects = new HashSet<>();
objects.add(error);
this.errors = objects;
}
}
public class Validators {
public static final Validator validator;
static {
ValidatorFactory validatorFactory = Validation.buildDefaultValidatorFactory();
validator=validatorFactory.getValidator();
}
public static Set> validate(T object){
return validator.validate(object);
}
}
定义参数转换器
public enum LocalDateStringConverter implements Converter{ INSTANCE; @Override public Class supportJavaTypeKey() { return LocalDate.class; } @Override public CellDataTypeEnum supportExcelTypeKey() { return CellDataTypeEnum.STRING; } @Override public LocalDate convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws ParseException { if (contentProperty == null || contentProperty.getDateTimeFormatProperty() == null) { return LocalDate.parse(cellData.getStringValue()); } else { DateTimeFormatter formatter = DateTimeFormatter .ofPattern(contentProperty.getDateTimeFormatProperty().getFormat()); return LocalDate.parse(cellData.getStringValue(), formatter); } } @Override public CellData convertToExcelData(LocalDate value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) { DateTimeFormatter formatter; if (contentProperty == null || contentProperty.getDateTimeFormatProperty() == null) { formatter = DateTimeFormatter.ISO_LOCAL_DATE; } else { formatter = DateTimeFormatter.ofPattern(contentProperty.getDateTimeFormatProperty().getFormat()); } return new CellData<>(value.format(formatter)); } } public enum LocalDateTimeStringConverter implements Converter { INSTANCE; private static final String MINUS = "-"; @Override public Class supportJavaTypeKey() { return LocalDateTime.class; } @Override public CellDataTypeEnum supportExcelTypeKey() { return CellDataTypeEnum.STRING; } @Override public LocalDateTime convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws ParseException { String stringValue = cellData.getStringValue(); String pattern; if (contentProperty == null || contentProperty.getDateTimeFormatProperty() == null) { pattern = switchDateFormat(stringValue); } else { pattern = contentProperty.getDateTimeFormatProperty().getFormat(); } DateTimeFormatter formatter = DateTimeFormatter.ofPattern(pattern); return LocalDateTime.parse(cellData.getStringValue(), formatter); } @Override public CellData convertToExcelData(LocalDateTime value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) { String pattern; if (contentProperty == null || contentProperty.getDateTimeFormatProperty() == null) { pattern = DateUtils.DATE_FORMAT_19; } else { pattern = contentProperty.getDateTimeFormatProperty().getFormat(); } DateTimeFormatter formatter = DateTimeFormatter.ofPattern(pattern); return new CellData<>(value.format(formatter)); } private static String switchDateFormat(String dateString) { int length = dateString.length(); switch (length) { case 19: if (dateString.contains(MINUS)) { return DateUtils.DATE_FORMAT_19; } else { return DateUtils.DATE_FORMAT_19_FORWARD_SLASH; } case 17: return DateUtils.DATE_FORMAT_17; case 14: return DateUtils.DATE_FORMAT_14; case 10: return DateUtils.DATE_FORMAT_10; default: throw new IllegalArgumentException("can not find date format for：" + dateString); } } }
通过自定义参数解析器实现excel的导入
public class RequestExcelArgumentResolver implements HandlerMethodArgumentResolver {
@Override
public boolean supportsParameter(MethodParameter parameter) {
return parameter.hasParameterAnnotation(RequestExcel.class);
}
@Override
public Object resolveArgument(MethodParameter parameter, ModelAndViewContainer mavContainer, NativeWebRequest webRequest, WebDataBinderFactory binderFactory) throws Exception {
// 判断是否采用list接受参数
Class parameterType = parameter.getParameterType();
if (!parameterType.isAssignableFrom(List.class)) {
throw new IllegalArgumentException(
"Excel upload request resolver error, @RequestExcel parameter is not List " + parameterType);
}
RequestExcel requestExcel = parameter.getParameterAnnotation(RequestExcel.class);
assert requestExcel!=null;
//获取自定义读取处理器
Class> readListenerClass = requestExcel.readListener();
ListAnalysisEventListener readListener = BeanUtils.instantiateClass(readListenerClass);
// 获取请求文件流
HttpServletRequest request = webRequest.getNativeRequest(HttpServletRequest.class);
assert request!=null;
InputStream inputStream;
if (request instanceof MultipartRequest){
MultipartRequest multipartRequest = (MultipartRequest) request;
MultipartFile file = multipartRequest.getFile(requestExcel.fileName());
assert file!=null;
inputStream=file.getInputStream();
//FileUtils.writeToFile(new File("C:\Users\likun\Desktop\新建文件夹\lktest.xls"),inputStream);
}else {
inputStream=request.getInputStream();
}
//将数据读取到指定接受参数中
// 获取目标类型
Class excelModelClass = ResolvableType.forMethodParameter(parameter).getGeneric(0).resolve();
EasyExcel.read(inputStream,excelModelClass,readListener)
.registerConverter(LocalDateStringConverter.INSTANCE)
.registerConverter(LocalDateTimeStringConverter.INSTANCE)
.ignoreEmptyRow(requestExcel.ignoreEmptyRow())
.sheet().doRead();
//将校验失败的数据绑定给BindResult
WebDataBinder webDataBinder = binderFactory.createBinder(webRequest, readListener.getErrors(), "excel");
ModelMap model = mavContainer.getModel();
model.put(BindingResult.MODEL_KEY_PREFIX+"excel",webDataBinder.getBindingResult());
return readListener.getList();
}
}
配置自定义参数解析器
@RequiredArgsConstructor
public class RequestExcelConfig implements InitializingBean {
private final RequestMappingHandlerAdapter requestMappingHandlerAdapter;
@Override
public void afterPropertiesSet() throws Exception {
List argumentResolvers = requestMappingHandlerAdapter.getArgumentResolvers();
ArrayList resloves = new ArrayList<>();
resloves.add(new RequestExcelArgumentResolver());
resloves.addAll(argumentResolvers);
requestMappingHandlerAdapter.setArgumentResolvers(resloves);
}
}
四 组件调用交给springboot管理
@Data
public class PostExcelVO implements Serializable {
private static final long serialVersionUID = 1L;
@ExcelProperty("岗位编号")
private Long postId;
@NotBlank(message = "岗位名称不能为空")
@ExcelProperty("岗位名称")
private String postName;
@NotBlank(message = "岗位标识不能为空")
@ExcelProperty("岗位标识")
private String postCode;
@NotNull(message = "岗位排序不能为空")
@ExcelProperty("岗位排序")
private Integer postSort;
@NotBlank(message = "岗位描述不能为空")
@ExcelProperty(value = "岗位描述")
private String remark;
}
@PostMapping("/importExcel")
@Inner(false)
public R importExcel(@RequestExcel List list, BindingResult bindingResult){
// 获取错误信息
List errorMessages = (List) bindingResult.getTarget();
errorMessages.forEach(errorMessage -> log.info("校验错误信息:{}",errorMessage));
log.info("读取结果:{}",list);
return R.ok();
}
执行结果:
