原先的导入功能只支持使用固定模板导入,模板格式如下:
@Getter
@Setter
@ToString
public class TestCaseExcelData {
@ExcelProperty(value = "所属功能模块")
private String module;
@ExcelProperty(value = "用例编号")
private String code;
@NotBlank(message = "必填项不能为空")
@ExcelProperty(value = "*用例名称")
private String name;
@NotBlank(message = "必填项不能为空")
@ExcelProperty(value = "*优先级")
private String caseLevel;
@NotBlank(message = "必填项不能为空")
@ExcelProperty(value = "*用例类型")
private String caseType;
@ExcelProperty(value = "用例标签")
private String tags;
@ExcelProperty(value = "前置条件")
private String preSteps;
@NotBlank(message = "必填项不能为空")
@ExcelProperty(value = "*操作步骤/场景描述")
private String stepDesc;
@NotBlank(message = "必填项不能为空")
@ExcelProperty(value = "*预期结果")
private String expectResult;
@ExcelProperty(value = "关联需求类型")
private String requirementType;
@ExcelProperty(value = "关联需求ID")
private String requirementId;
@ExcelProperty(value = "用例版本")
private String caseVersion;
}
EasyExcel 导入监听器直接使用AnalysisEventListener 即可实现导入校验,校验规则较为复杂,不在此处展开。
现在要求用户配置了自定义字段之后,还可以导入自定义字段,同时保留对固定字段的校验逻辑。因此原有的适用对象的监听器不再适用,需要使用无对象的方式做数据校验。
@Override
public void invoke(Map<Integer, String> data, AnalysisContext context) {
data.forEach((index, value) -> {
// 获取表头
String headName = headMap.get(index);
});
}
Field[] fields = TestCaseExcelData.class.getDeclaredFields();
for (Field field : fields) {
if (field.isAnnotationPresent(ExcelProperty.class)) {
ExcelProperty declaredAnnotation = field.getDeclaredAnnotation(ExcelProperty.class);
String headValue = declaredAnnotation.value()[0];
this.fieldStringMap.put(headValue, field);
}
}
// 当前行的数据 <列索引, 单元格值>
Map<Integer, String> data;
// 表头的数据 <列索引, 单元格值>
Map<Integer, String> headMap;
// 实体对象表头和对应字段的数据 <表头名称, 表头对应的属性>
Map<Stirng, Field> fieldStringMap;
@Override
public void invoke(Map<Integer, String> data, AnalysisContext context) {
// 创建实体对象
TestCaseExcelData rawData = new TestCaseExcelData();
try {
data.forEach((index, value) -> {
// 获取到当前单元格的表头
String headName = headMap.get(index);
// 根据表头获取实体类的属性
Field field = fieldStringMap.get(headName);
try {
// 判断实体类是否有此属性
if (field != null) {
field.setAccessible(true);
// 通过反射直接赋值
field.set(rawData, value);
}
} catch (IllegalAccessException e) {
throw new RuntimeException(e);
}
// 解析自定义字段,只有系统配置的字段才会被缓存
List<CustomFieldPO> customFieldPOS = systemCustomFieldMap.get(headName);
if (CollectionUtils.isNotEmpty(customFieldPOS)) {
customFieldMap.put(customFieldPOS.get(0).getFieldKey(), value);
}
});
// 固定字段校验
ExcelValidateHelper.validateEntity(rawData);
} catch (NoSuchFieldException e) {
e.printStackTrace();
}
}
最后,附上自定义模板校验表头的代码
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
super.invokeHeadMap(headMap, context);
// 限制文件行数不超过5000行
if (context.readSheetHolder().getApproximateTotalRowNumber() > 5000) {
throw new ServiceException(CommonException.EXCEL_ROW_EXCEEDED);
}
// 校验excel模版是否正确
ExcelImportUtil.validateHeadLoosely(headMap, this.dynamicCaseHeader.get(0));
this.headMap = headMap;
Field[] fields = TestCaseExcelData.class.getDeclaredFields();
for (Field field : fields) {
if (field.isAnnotationPresent(ExcelProperty.class)) {
ExcelProperty declaredAnnotation = field.getDeclaredAnnotation(ExcelProperty.class);
String headValue = declaredAnnotation.value()[0];
this.fieldStringMap.put(headValue, field);
}
}
}
/**
* 表头宽松校验
* <p>只校验表头是否存在模板中的字段</p>
* <p>不允许存在重复的表头</p>
* <p>导入文件中可以包含多余的列名</p>
*
* @param headMap 实际读到的表头
* @param expectedHeadMapFiled 期望的表头
*/
public static void validateHeadLoosely(Map<Integer, String> headMap, List<String> expectedHeadMapFiled) {
try {
if (CollectionUtils.isEmpty(expectedHeadMapFiled) || MapUtils.isEmpty(headMap)) {
throw new ServiceException(CommonException.EXCEL_TEMPLATE_IS_NOT_CORRECT);
}
// 移除没有内容的表头
headMap.entrySet().removeIf(entry -> entry.getValue() == null);
// 判断是否存在重复列
Collection<String> headValues = headMap.values();
Set<String> headValuesSet = new HashSet<>(headValues);
if (headValues.size() != headValuesSet.size()) {
throw new ServiceException(CommonException.EXCEL_HEADS_DUPLICATED);
}
// 判断模板字段是否都包含在表头里
for (String value : expectedHeadMapFiled) {
if (!headMap.containsValue(value)) {
throw new ServiceException(CommonException.EXCEL_TEMPLATE_IS_NOT_CORRECT);
}
}
} catch (Exception e) {
throw new ServiceException("Excel表头校验失败,异常详情:" + ExceptionUtil.getErrorMessage(e));
}
}