Excel导入校验

校验监听器

/*** Excel 校验监听器* @param <T>*/
public class AnalysisValidReadListener<T> extends AnalysisEventListener<T> {private static final Logger logger = LoggerFactory.getLogger(AnalysisValidReadListener.class);private static final int BATCH_COUNT = 1;private final List<T> dataList = new ArrayList<>(BATCH_COUNT);private final Class<T> type; // 记录当前解析的类private final int headRowNum; // 记录表头行号public AnalysisValidReadListener(Class<T> type, int headRowNum) {this.type = Objects.requireNonNull(type, "Type parameter cannot be null");this.headRowNum = headRowNum;}@Overridepublic void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {int currentRow = context.readRowHolder().getRowIndex();if (currentRow == headRowNum - 1) { // 只处理第 headRowNum 行的表头logger.info("解析到表头数据: {}", JSON.toJSONString(headMap));validateHeader(headMap);}}@Overridepublic void invoke(T data, AnalysisContext context) {int rowNumber = context.readRowHolder().getRowIndex() + 1;// 进行 Excel 校验try {validateObject(data, rowNumber);dataList.add(data);} catch (RuntimeException e) {throw new RuntimeException("数据校验失败：" + e.getMessage());}}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {// 可在此处执行数据持久化操作logger.info("所有数据解析完成，共 {} 行", dataList.size());}@Overridepublic void onException(Exception exception, AnalysisContext context) throws Exception {if (exception instanceof ExcelDataConvertException) {ExcelDataConvertException ex = (ExcelDataConvertException) exception;String columnLetter = columnIndexToLetter(ex.getColumnIndex());throw new RuntimeException(String.format("Excel解析错误：第 %d 行, 第 %s 列数据转换异常。", ex.getRowIndex() + 1, columnLetter));}throw exception;}/*** 校验表头* @param headMap*/private void validateHeader(Map<Integer, String> headMap) {Map<Integer, String> expectedIndexHeaderMap = new HashMap<>();  // 存储 index >= 0 的字段Set<String> expectedNameHeaders = new HashSet<>();  // 存储没有 index 的字段for (Field field : type.getDeclaredFields()) {ExcelProperty fieldAnnotation = field.getAnnotation(ExcelProperty.class);if (fieldAnnotation != null) {String expectedHead = fieldAnnotation.value()[0];int index = fieldAnnotation.index();if (index >= 0) {// 指定了 index 的字段expectedIndexHeaderMap.put(index, expectedHead);} else {// 没有指定 index，按名称匹配expectedNameHeaders.add(expectedHead);}}}// 校验表头列数if (headMap.size() < expectedIndexHeaderMap.size() + expectedNameHeaders.size()) {throw new RuntimeException("导入模板错误，表头列数不匹配");}// 校验 index 绑定的列for (Map.Entry<Integer, String> entry : expectedIndexHeaderMap.entrySet()) {int expectedIndex = entry.getKey();String expectedHead = entry.getValue();if (!Objects.equals(headMap.get(expectedIndex), expectedHead)) {throw new RuntimeException(String.format("表头错误，第 %d 列【%s】应为【%s】", expectedIndex + 1, headMap.get(expectedIndex), expectedHead));}}// 校验未指定 index 的列（按名称匹配）for (String expectedHead : expectedNameHeaders) {if (!headMap.containsValue(expectedHead)) {throw new RuntimeException(String.format("表头错误，缺少列名【%s】", expectedHead));}}}/*** 校验对象属性* @param obj* @param rowNumber* @param <T>*/public static <T> void validateObject(T obj, int rowNumber) {Class<?> clazz = obj.getClass();Field[] fields = clazz.getDeclaredFields();for (Field field : fields) {field.setAccessible(true);Object fieldValue = null;try {fieldValue = field.get(obj);} catch (IllegalAccessException e) {throw new RuntimeException("无法访问字段: " + field.getName(), e);}// 获取 ExcelProperty 映射的列名ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);String fieldName = (excelProperty != null) ? String.join(", ", excelProperty.value()) : field.getName();// 获取自定义 ExcelValid 注解ExcelValid excelValid = field.getAnnotation(ExcelValid.class);if (excelValid == null) {continue; // 没有校验规则，跳过}// 必填校验if (excelValid.require() && Objects.isNull(fieldValue)) {throw new RuntimeException(String.format("第%d行，%s 数据不能为空！", rowNumber, fieldName));}// 数值范围校验if (fieldValue instanceof Number) {long numValue = ((Number) fieldValue).longValue();if (numValue < excelValid.min() || numValue > excelValid.max()) {throw new RuntimeException(String.format("第%d行，%s 数据必须在 %d 和 %d 之间！",rowNumber, fieldName, excelValid.min(), excelValid.max()));}}// 正则表达式校验（适用于字符串）if (!excelValid.pattern().isEmpty() && fieldValue instanceof String) {String regex = excelValid.pattern();String value = (String) fieldValue;// 正则校验if (!value.matches(regex)) {throw new RuntimeException(String.format("第%d行，%s 数据格式不正确！", rowNumber, fieldName));}}// 日期格式校验if (!excelValid.dateFormat().isEmpty()) {final String expectedFormat = excelValid.dateFormat();// 预编译日期格式final DateTimeFormatter formatter;try {formatter = DateTimeFormatter.ofPattern(expectedFormat);} catch (IllegalArgumentException e) {throw new RuntimeException("无效的日期格式配置: " + expectedFormat, e);}if (fieldValue instanceof String) {// 字符串类型：严格校验格式匹配String dateStr = (String) fieldValue;ParsePosition pos = new ParsePosition(0);formatter.parseUnresolved(dateStr, pos);if (pos.getErrorIndex() != -1 || pos.getIndex() != dateStr.length()) {throw new RuntimeException(String.format("第%d行，%s 数据格式不正确，应为 %s 格式！", rowNumber, fieldName, excelValid.dateFormat()));}} else {// 日期对象类型：校验能否用指定格式格式化TemporalAccessor temporal = convertToTemporalAccessor(fieldValue);try {formatter.format(temporal); // 尝试格式化} catch (DateTimeException e) {throw new RuntimeException(String.format("第%d行，%s 数据格式不正确，应为 %s 格式！", rowNumber, fieldName, excelValid.dateFormat()));}}}// 校验 "是/否" 选项if (excelValid.yesNo() && Objects.nonNull(fieldValue) &&!(Objects.equals(fieldValue, "是") || Objects.equals(fieldValue, "否"))) {throw new RuntimeException("第 " + rowNumber + " 行，" + fieldName + " 数据只能为【是/否】！");}// 赋默认值if (Objects.isNull(fieldValue) && !excelValid.defaultValue().isEmpty()) {try {field.set(obj, excelValid.defaultValue());} catch (IllegalAccessException e) {throw new RuntimeException(String.format("第%d行，%s 赋默认值失败！", rowNumber, fieldName));}}// 替换值逻辑：遍历替换规则，动态替换值if (Objects.nonNull(fieldValue)) {for (String rule : excelValid.replaceValues()) {String[] parts = rule.split("="); // 分割成替换对，如 "/" = " "if (parts.length == 2) {String target = parts[0].trim(); // 要替换的值String replacement = parts[1].trim(); // 替换成的值// 如果字段值与目标值相等，则进行替换if (Objects.equals(fieldValue, target)) {try {field.set(obj, replacement);  // 替换值} catch (IllegalAccessException e) {throw new RuntimeException(String.format("第%d行，%s 替换 '%s' 失败！", rowNumber, fieldName, target));}}}}}}}/*** 将列索引转换为列字母* @param columnIndex* @return*/public String columnIndexToLetter(int columnIndex) {StringBuilder columnName = new StringBuilder();while (columnIndex >= 0) {columnName.insert(0, (char) ('A' + columnIndex % 26));  // 计算当前位的字母columnIndex = columnIndex / 26 - 1;  // 获取上一级的索引}return columnName.toString();}/*** 将对象转换为 TemporalAccessor 以便格式化校验*/private static TemporalAccessor convertToTemporalAccessor(Object value) {if (value instanceof LocalDateTime) {return (LocalDateTime) value;} else if (value instanceof LocalDate) {return (LocalDate) value;} else if (value instanceof Date) {return ((Date) value).toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime();}throw new IllegalArgumentException("不支持的日期类型: " + value.getClass());}}

校验注解

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
@Documented
/*** Excel校验注解*/
public @interface ExcelValid {boolean require() default false;  // 是否必填long min() default Long.MIN_VALUE;  // 最小值（适用于数字）long max() default Long.MAX_VALUE;  // 最大值（适用于数字）String pattern() default "";  // 正则表达式（适用于字符串）boolean yesNo() default false; // 是否校验 "是/否"String dateFormat() default ""; // 日期格式，如 "yyyy-MM-dd"String[] replaceValues() default {}; // 替换规则，格式如 {"/=-", "X=Y"}String defaultValue() default ""; // 默认值}

通用日期转换器

package com.cloud.sa.base.common.convert;import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;import java.time.*;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeParseException;
import java.time.temporal.ChronoUnit;
import java.time.temporal.TemporalAccessor;
import java.util.Arrays;
import java.util.List;
import java.util.Locale;/*** 通用转换器*/
public class UniversalConverter implements Converter<Object> {// 支持多种日期格式列表private static final List<DateTimeFormatter> DATE_FORMATTERS = Arrays.asList(DateTimeFormatter.ofPattern("yyyy.MM"),           // yyyy.MMDateTimeFormatter.ofPattern("yyyy-MM-dd"),       // yyyy-MM-ddDateTimeFormatter.ofPattern("yyyy-MM"),          // yyyy-MMDateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"), // yyyy-MM-dd HH:mm:ss（24小时制）DateTimeFormatter.ofPattern("yyyy/MM/dd"),        // yyyy/MM/ddDateTimeFormatter.ofPattern("yyyyMMdd"),          // yyyyMMddDateTimeFormatter.ofPattern("yyyy年MM月dd日"),     // 中文格式DateTimeFormatter.ofPattern("MM/dd/yyyy"),        // MM/dd/yyyyDateTimeFormatter.ofPattern("dd-MMM-yyyy", Locale.ENGLISH) // 处理类似 15-May-2023);@Overridepublic Class<?> supportJavaTypeKey() {return Object.class;}@Overridepublic CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.STRING;}@Overridepublic Object convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {if (cellData == null) {return null;}switch (cellData.getType()) {case STRING:String value = cellData.getStringValue().trim();return parseStringToDateTime(value);case NUMBER:// 处理Excel数值日期（需转换为实际日期）return convertExcelNumberToDate(cellData.getNumberValue().doubleValue());default:throw new IllegalArgumentException("无法解析的Excel单元格类型: " + cellData.getType());}}/*** 将字符串解析为LocalDateTime（支持多种格式）*/private LocalDateTime parseStringToDateTime(String value) {for (DateTimeFormatter formatter : DATE_FORMATTERS) {try {TemporalAccessor temporal = formatter.parseBest(value, LocalDateTime::from, LocalDate::from, YearMonth::from);if (temporal instanceof LocalDateTime) {return (LocalDateTime) temporal;} else if (temporal instanceof LocalDate) {return ((LocalDate) temporal).atStartOfDay();} else if (temporal instanceof YearMonth) {return ((YearMonth) temporal).atDay(1).atStartOfDay();}} catch (DateTimeParseException ignored) {// 继续尝试下一个格式}}throw new IllegalArgumentException("无法识别的日期格式: " + value);}/*** 将Excel数值转换为LocalDateTime（基于1900-01-01的序列）*/private LocalDateTime convertExcelNumberToDate(double number) {// 调整Excel的日期偏移（1900-01-1为1，且包含错误的1900-02-29）int wholeDays = (int) Math.floor(number);int millisecondsInDay = (int) ((number - wholeDays) * 24 * 60 * 60 * 1000 + 0.5);// 基准日期为1899-12-31（因为Excel从1900-01-01开始计数）LocalDateTime base = LocalDateTime.of(1899, 12, 31, 0, 0);LocalDateTime dateTime = base.plusDays(wholeDays).plus(millisecondsInDay, ChronoUnit.MILLIS);// 修正Excel的闰年错误（数值>=60时减去1天）if (wholeDays > 59) {dateTime = dateTime.minusDays(1);}return dateTime;}
}

使用方法

// 设置表头行数
int headRowNum = 2;
// 设置监听器
AnalysisValidReadListener<ProjectFileEntrustedChildrenImportForm> listener = new AnalysisValidReadListener<>(ProjectFileEntrustedChildrenImportForm.class, headRowNum);
// 读取数据
List<ProjectFileEntrustedChildrenImportForm> dataList = EasyExcel.read(file.getInputStream(), ProjectFileEntrustedChildrenImportForm.class, listener)
.sheet()
.headRowNumber(headRowNum)
.doReadSync();

设置日期转换器

@Schema(description = "建设开始时间")
@ExcelProperty(value = "建设开始时间",converter = UniversalConverter.class)
@ExcelValid(require = true, dateFormat = "yyyy.MM")
private LocalDateTime buildStartDate;