业务上需求,前端上传excel到后端服务器,后端服务器解析excel,再把excel中的数据插入数据库中,再这个过程中,要对excel中的数据进行校验,所以就有了以下功能;
该功能是基于策略+工厂模式实现的,通过对excel中数据对应的实体类中的字段加注解,完成对数据的校验工作,具体实现逻辑如下:
1 定义注解
1.1 策略实现注解
该注解是加在不同校验策略实现类上的;
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.TYPE)
@Inherited
public @interface Strategy {
ExcelCheckType value();
}
1.2 校验注解
该注解是加在excel对应实体类字段上;
@Documented
@Target({METHOD, FIELD, ANNOTATION_TYPE, CONSTRUCTOR, PARAMETER, TYPE_USE})
@Retention(RUNTIME)
public @interface CheckExcel {
String message() default "Excel单元格校验不正确";
String name() default "";
ExcelCheckType[] rules();
}
2 定义枚举
该枚举对应不同校验策略;
public enum ExcelCheckType {
/**
* 邮箱校验
*/
MAIL("Mail", qualify("校验错误"), 2);
/**
* 校验规则
*/
private String ruleName;
/**
* 校验规则排序
*/
private int order;
/**
* 默认报错信息
*/
private String message;
ExcelCheckType(String ruleName, String message, int order) {
this.ruleName = ruleName;
this.message = message;
this.order = order;
}
private static String qualify(String attr) {
return "第{0}行,{1}" + attr;
}
public String getMessage() {
return message;
}
public void setMessage(String message) {
this.message = message == null ? null : message.trim();
}
public int getOrder() {
return order;
}
public void setOrder(int order) {
this.order = order;
}
public String getRuleName() {
return ruleName;
}
public void setRuleName(String ruleName) {
this.ruleName = ruleName == null ? null : ruleName.trim();
}
}
3 定义策略接口
所有的校验策略实现以下接口;校验顺序通过getOrder排序;
public interface ExcelStrategy<T> {
boolean check(T param);
int getOrder();
}
4 实现校验策略
@Component // 交给spring容器管理
@Strategy(ExcelCheckType.MAIL) // 策略实现注解,注解中传枚举值
public class MailStrategyExcel implements ExcelStrategy<String> {
@Override
public boolean check(String param) {
if (StrUtil.isBlank(param)) return true;
return ValidatorUtil.isMail(param);
}
@Override
public int getOrder() {
return ExcelCheckType.MAIL.getOrder();
}
}
5 校验策略注册器和工厂
5.1 策略注册器
抽象注册器
public abstract class AbstractEnumRegistryBuilder<E extends Enum<E>> {
public <T> Map<E, T> build(T[] beans) {
MapBuilder<E, T> builder = MapUtil.builder();
for (T bean : beans) {
Class<?> builderClass = bean.getClass();
E keys = extractKeys(builderClass);
builder.put(keys, bean);
}
return builder.build();
}
public abstract E extractKeys(Class<?> builderClass);
}
注册逻辑实现
@Component
public class CheckExcelRegistryBuilder extends AbstractEnumRegistryBuilder<ExcelCheckType> {
@Override
public ExcelCheckType extractKeys(Class<?> builderClass) {
Strategy forStrategyType = builderClass.getAnnotation(Strategy.class);
Preconditions.checkNotNull(forStrategyType, "Cannot find @Strategy annotation on %s",
builderClass.getName());
return forStrategyType.value();
}
}
5.2 获取策略的工厂类
@Component
public class CheckExcelFactory {
private final Map<ExcelCheckType, ExcelStrategy> strategyMap;
public CheckExcelFactory(CheckExcelRegistryBuilder registryBuilder, ExcelStrategy[] checkStrategies) {
strategyMap = registryBuilder.build(checkStrategies);
}
/**
* 根据规则获取单个策略
*
* @param checkType 规则
* @return 策略
*/
public ExcelStrategy getStrategy(ExcelCheckType checkType) {
ExcelStrategy strategy = strategyMap.get(checkType);
if (strategy == null) {
throw new RuntimeException("未找到对应excel单元格校验规则");
}
return strategy;
}
/**
* 获取一个策略列表
*
* @param checkTypes 规则列表
* @return 策略列表
*/
public List<ExcelStrategy> getStrategy(ExcelCheckType[] checkTypes) {
List<ExcelStrategy> list = new ArrayList<>();
for (ExcelCheckType checkType : checkTypes) {
ExcelStrategy strategy = strategyMap.get(checkType);
if (strategy == null) {
throw new RuntimeException(MessageFormat.format("未找到{0}对应excel单元格校验规则", checkType));
}
list.add(strategy);
}
return CollUtil.sort(list, Comparator.comparing(ExcelStrategy::getOrder));
}
}
6 校验逻辑
通过以上步骤,把校验策略都生成好了,下面是具体使用校验策略的逻辑;
@Component
public class ExcelService {
private final CheckExcelFactory factory;
public ExcelService(CheckExcelFactory factory) {
this.factory = factory;
}
public <T> void checkExcelFile(List<T> excelList) throws Exception {
int count = 0;
StrJoiner joiner = StrJoiner.of(";</br>");
for (int i = 0; i < excelList.size(); i++) {
T rows = excelList.get(i);
// 获取反射对象
Class<?> clazz = rows.getClass();
// 获取excel对应实体类所有属性
Field[] fields = clazz.getDeclaredFields();
/*
遍历所有属性,校验属性值是否符合规范
*/
for (Field field : fields) {
// 设置当前属性操作权限
field.setAccessible(true);
String name = field.getName();
// 根据属性名获取属性值
String value = Convert.toStr(clazz.getMethod(StrUtil.genGetter(name)).invoke(rows));
// 获取属性值上的excel校验注解
CheckExcel checkExcel = field.getAnnotation(CheckExcel.class);
// 根据注解中的策略枚举值列表获取该属性有哪些校验,并把策略排序
List<ExcelCheckType> rules = CollUtil.sort(CollUtil.toList(checkExcel.rules()), Comparator.comparing(ExcelCheckType::getOrder));
// 遍历排序后的枚举值
for (ExcelCheckType rule : rules) {
// 根据枚举值去策略工厂中获取具体策略
ExcelStrategy strategy = factory.getStrategy(rule);
// 校验通过true不通过false
boolean check = strategy.check(value);
// 有10条错误信息,跳出校验逻辑,抛出异常
if (count >= 10) {
throw new BaseException(ErrorEnum.EXCEL_ERROR.getCode(), joiner.toString());
}
// 校验未通过,增加错误信息
if (!check) {
joiner.append(MessageFormat.format(rule.getMessage(), i + 2, checkExcel.name()));
count++;
break;
}
}
}
}
if (count > 0) {
throw new BaseException(ErrorEnum.EXCEL_ERROR.getCode(), joiner.toString());
}
}
}
7 业务逻辑中使用
/**
* @see Api 生成接口文档名注解
* @see Slf4j 打印日志注解
* @see CrossOrigin 支持跨域
* @see RestController 响应参数转json
* @see Validated 参数校验
* @see RequestMapping 请求URL
*/
@Api(tags = {"示例控制器"})
@Slf4j
@CrossOrigin
@RestController
@Validated
@RequestMapping("/example")
public class ExampleApi {
@Autowired
private ExcelService excelService;
@ApiOperation("测试单元格校验")
@PostMapping("/checkExcel")
public RespResult<Object> checkExcel(@ApiParam(value = "excel") MultipartFile excel) throws Exception {
// 延迟解析比率
ZipSecureFile.setMinInflateRatio(-1.0d);
ExcelReader excelReader = ExcelUtil.getReader(excel.getInputStream());
List<String> first = excelReader.setSheet(0).readRow(0).stream().map(Convert::toStr).collect(Collectors.toList());
excelReader.setHeaderAlias(getHeaderAlias(first));
excelReader.setIgnoreEmptyRow(true);
List<OrderInfoExcel> excelList = excelReader.readAll(OrderInfoExcel.class);
excelService.checkExcelFile(excelList);
return new RespResult<>(ErrorEnum.SUCCESS.getCode(), ErrorEnum.SUCCESS.getMessage());
}
private Map<String, String> getHeaderAlias(List<String> first) {
Map<String, String> map = new HashMap<>();
// 获取表别名,excel表头对应实体类属性
return map;
}
}
以上代码使用了hutool工具包和knife4j依赖