Java中使用easyExcel做导入--封装aop使用

其实像导入这样比较公共的功能,抽取出来使用是比较优雅的 

1.创建注解

package com.sanshi.property.common.excel.annotation;

import com.sanshi.property.common.excel.handler.DefaultAnalysisEventListener;
import com.sanshi.property.common.excel.handler.ListAnalysisEventListener;

import java.lang.annotation.*;

/**
 * 导入excel
 *
 * @author property
 * @author L.cm
 * @date 2021/4/16
 */
@Documented
@Target({ElementType.PARAMETER})
@Retention(RetentionPolicy.RUNTIME)
public @interface RequestExcel {

	/**
	 * 前端上传字段名称 file
	 */
	String fileName() default "file";

	/**
	 * 读取的监听器类
	 *
	 * @return readListener
	 */
	Class<? extends ListAnalysisEventListener<?>> readListener() default DefaultAnalysisEventListener.class;

	/**
	 * 是否跳过空行
	 *
	 * @return 默认跳过
	 */
	boolean ignoreEmptyRow() default false;

}

2.监视器

package com.sanshi.property.common.excel.handler;

import com.alibaba.excel.context.AnalysisContext;
import com.sanshi.property.common.excel.kit.Validators;
import com.sanshi.property.common.excel.vo.ErrorMessage;
import lombok.extern.slf4j.Slf4j;

import javax.validation.ConstraintViolation;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;
import java.util.stream.Collectors;

/**
 * 默认的 AnalysisEventListener
 *
 * @author property
 * @author L.cm
 * @date 2021/4/16
 */
@Slf4j
public class DefaultAnalysisEventListener extends ListAnalysisEventListener<Object> {

	private final List<Object> list = new ArrayList<>();

	private final List<ErrorMessage> errorMessageList = new ArrayList<>();

	private Long lineNum = 1L;

	@Override
	public void invoke(Object o, AnalysisContext analysisContext) {
		lineNum++;

		Set<ConstraintViolation<Object>> violations = Validators.validate(o);
		if (!violations.isEmpty()) {
			Set<String> messageSet = violations.stream().map(ConstraintViolation::getMessage)
					.collect(Collectors.toSet());
			errorMessageList.add(new ErrorMessage(lineNum, messageSet));
		} else {
			list.add(o);
		}
	}

	@Override
	public void doAfterAllAnalysed(AnalysisContext analysisContext) {
		log.debug("Excel read analysed");
	}

	@Override
	public List<Object> getList() {
		return list;
	}

	@Override
	public List<ErrorMessage> getErrors() {
		return errorMessageList;
	}

}

package com.sanshi.property.common.excel.handler;

import com.alibaba.excel.event.AnalysisEventListener;
import com.sanshi.property.common.excel.vo.ErrorMessage;

import java.util.List;

/**
 * list analysis EventListener
 *
 * @author L.cm
 */
public abstract class ListAnalysisEventListener<T> extends AnalysisEventListener<T> {

	/**
	 * 获取 excel 解析的对象列表
	 *
	 * @return 集合
	 */
	public abstract List<T> getList();

	/**
	 * 获取异常校验结果
	 *
	 * @return 集合
	 */
	public abstract List<ErrorMessage> getErrors();

}

package com.sanshi.property.common.excel.aop;

import com.alibaba.excel.EasyExcel;
import com.sanshi.property.common.excel.annotation.RequestExcel;
import com.sanshi.property.common.excel.converters.LocalDateStringConverter;
import com.sanshi.property.common.excel.converters.LocalDateTimeStringConverter;
import com.sanshi.property.common.excel.handler.ListAnalysisEventListener;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanUtils;
import org.springframework.core.MethodParameter;
import org.springframework.core.ResolvableType;
import org.springframework.ui.ModelMap;
import org.springframework.validation.BindingResult;
import org.springframework.web.bind.WebDataBinder;
import org.springframework.web.bind.support.WebDataBinderFactory;
import org.springframework.web.context.request.NativeWebRequest;
import org.springframework.web.method.support.HandlerMethodArgumentResolver;
import org.springframework.web.method.support.ModelAndViewContainer;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartRequest;

import javax.servlet.http.HttpServletRequest;
import java.io.InputStream;
import java.util.List;

/**
 * 上传excel 解析注解
 *
 * @author property
 * @author L.cm
 * @date 2021/4/16
 */
@Slf4j
public class RequestExcelArgumentResolver implements HandlerMethodArgumentResolver {

	@Override
	public boolean supportsParameter(MethodParameter parameter) {
		return parameter.hasParameterAnnotation(RequestExcel.class);
	}

	@Override
	@SneakyThrows
	public Object resolveArgument(MethodParameter parameter, ModelAndViewContainer modelAndViewContainer,
								  NativeWebRequest webRequest, WebDataBinderFactory webDataBinderFactory) {
		Class<?> parameterType = parameter.getParameterType();
		if (!parameterType.isAssignableFrom(List.class)) {
			throw new IllegalArgumentException(
					"Excel upload request resolver error, @RequestExcel parameter is not List " + parameterType);
		}

		// 处理自定义 readListener
		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) {
			MultipartFile file = ((MultipartRequest) request).getFile(requestExcel.fileName());
			assert file != null;
			inputStream = file.getInputStream();
		} else {
			inputStream = request.getInputStream();
		}

		// 获取目标类型
		Class<?> excelModelClass = ResolvableType.forMethodParameter(parameter).getGeneric(0).resolve();

		// 这里需要指定读用哪个 class 去读，然后读取第一个 sheet 文件流会自动关闭
		EasyExcel.read(inputStream, excelModelClass, readListener).registerConverter(LocalDateStringConverter.INSTANCE)
				.registerConverter(LocalDateTimeStringConverter.INSTANCE).ignoreEmptyRow(requestExcel.ignoreEmptyRow())
				.sheet().doRead();

		// 校验失败的数据处理 交给 BindResult
		WebDataBinder dataBinder = webDataBinderFactory.createBinder(webRequest, readListener.getErrors(), "excel");
		ModelMap model = modelAndViewContainer.getModel();
		model.put(BindingResult.MODEL_KEY_PREFIX + "excel", dataBinder.getBindingResult());

		return readListener.getList();
	}

}

转换

package com.sanshi.property.common.excel.converters;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.alibaba.excel.util.DateUtils;

import java.text.ParseException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

/**
 * LocalDateTime and string converter
 *
 * @author L.cm
 */
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);
		}
	}

}

校验错误信息

package com.sanshi.property.common.excel.vo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.HashSet;
import java.util.Set;

/**
 * 校验错误信息
 *
 * @author property
 * @date 2021/8/4
 */
@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;
	}

}