一 引入
日常开发中,我们经常用到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 中取出参数的过程,方法的返回值就对应了接口中参数的值。既把这个最终的结果赋值给当前参数
三 自定义spingboot组件
下文中的excle导入组件基于easyexcel实现
申明一个导入注解
@Documented
@Target({ ElementType.PARAMETER })
@Retention(RetentionPolicy.RUNTIME)
public @interface RequestExcel {
/**
* 前端上传文件字段名称
* @return
*/
String fileName() default "file";
/**
* 是否跳过空行
*/
boolean ignoreEmptyRow() default false;
/**
* 数据读取监听器
* @return
*/
Class<? extends ListAnalysisEventListener<?>> readListener() default DefaultListAnalysisEventListener.class;
}
申明数据读取监听器
抽象一个list监听器,在里面定义一个获得数据和获取数据校验失败的结果集
public abstract class ListAnalysisEventListener<T> extends AnalysisEventListener<T> {
/**
* 获取excel解析结果的对象列表
* @return
*/
public abstract List<T> getList();
/**
* 校验错误信息
* @return
*/
public abstract List<ErrorMessage> getErrors();
}
@Slf4j
public class DefaultListAnalysisEventListener extends ListAnalysisEventListener<Object>{
private final List<Object> list = new ArrayList<Object>();
private final List<ErrorMessage> errorMessageList = new ArrayList<ErrorMessage>();
/**
* 行号
*/
private Long lineNum = 1L;
@Override
public List<Object> getList() {
return list;
}
@Override
public List<ErrorMessage> getErrors() {
return errorMessageList;
}
@Override
public void invoke(Object data, AnalysisContext context) {
lineNum++;
//校验当前数据
Set<ConstraintViolation<Object>> validates = Validators.validate(data);
if (!validates.isEmpty()){
Set<String> errors = validates.stream().map(ConstraintViolation::getMessage)
.collect(Collectors.toSet());
errorMessageList.add(new ErrorMessage(lineNum,errors));
}else {
list.add(data);
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
log.info("excel read analysed");
}
}
定义校验工具及错误信息
@Data
@NoArgsConstructor
@AllArgsConstructor
public class ErrorMessage {
/**
* 行号
*/
private Long lineNum;
private Set<String> errors = new HashSet<>();
public ErrorMessage(Set<String> errors) {
this.errors = errors;
}
public ErrorMessage(String error) {
HashSet<String> 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 <T> Set<ConstraintViolation<T>> validate(T object){
return validator.validate(object);
}
}
定义参数转换器
public enum LocalDateStringConverter implements Converter<LocalDate> {
/**
* 实例
*/
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<String> 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<LocalDateTime> {
/**
* 实例
*/
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<String> 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));
}
/**
* switch date format
* @param dateString dateString
* @return pattern
*/
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 {
/**
* 支持条件--接受参数中包含RequestExcel注解
* @param parameter
* @return
*/
@Override
public boolean supportsParameter(MethodParameter parameter) {
return parameter.hasParameterAnnotation(RequestExcel.class);
}
/**
* 当前解析结果
* @param parameter
* @param mavContainer
* @param webRequest
* @param binderFactory
* @return
* @throws Exception
*/
@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<? extends ListAnalysisEventListener<?>> 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<HandlerMethodArgumentResolver> argumentResolvers = requestMappingHandlerAdapter.getArgumentResolvers();
ArrayList<HandlerMethodArgumentResolver> 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;
/**
* 主键ID
*/
@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<PostExcelVO> list, BindingResult bindingResult){
// 获取错误信息
List<ErrorMessage> errorMessages = (List<ErrorMessage>) bindingResult.getTarget();
errorMessages.forEach(errorMessage -> log.info("校验错误信息:{}",errorMessage));
log.info("读取结果:{}",list);
return R.ok();
}
执行结果: