Poi Excel表格导入与校验

以下是实战内容。

表格列注解

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
@Inherited
public @interface Column {

    /**
     * 排序
     */
    int index();


    /**
     * 是否必须
     *
     * @return
     */
    boolean notNull() default false;

    /**
     * 列名
     *
     */
    String name();
}

Cell

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Cell {

    @Column(index = 0, name = "第一列")
    @NotBlank(message = "不能为空")
    private String first;

    @Column(index = 1, name = "第二列")
    @NotBlank(message = "不能为空")
    private String second;

    @Column(index = 2, name = "第三列")
    @NotBlank(message = "不能为空")
    private String third;

}

Excel导入工具类

@Slf4j
public final class ExcelUtils {

    /**
     * Office（-，2003],扩展名是.xls
     */
    public final static String OLD_EXCEL_FILE_FORMAT = "xls";

    /**
     * Office[2007，+),扩展名是.xlsx
     */
    public final static String NEW_EXCEL_FILE_FORMAT = "xlsx";

    private final static LoadingCache<Class,Map<Integer, Field>> CLASS_ANNOTATION_FIELD_MAP = Caffeine.newBuilder().expireAfterWrite(Duration.ofHours(1L)).maximumSize(10).build(key -> buildColumnIndexToBeanFieldMap(key));

    private ExcelUtils() throws IllegalAccessException {
        throw new IllegalAccessException("禁止访问ExcelUtils私有构造方法");
    }

    /**
     * 读取excel文件
     * @param file
     * @param clazz
     * @param <T>
     * @return
     */
    public static <T> List<T> readFile(File file, Class<T> clazz) {
        List<T> resultList = null;
        try (FileInputStream fileInputStream = new FileInputStream(file)){
            Workbook workbook = file.getName().endsWith(OLD_EXCEL_FILE_FORMAT) ? new HSSFWorkbook(fileInputStream) : new XSSFWorkbook(fileInputStream);;
            int rowIndex = 1;
            Sheet sheet = workbook.getSheetAt(0);
            Map<Integer, Field> columnIndexToPropertyMap = CLASS_ANNOTATION_FIELD_MAP.get(clazz);
            resultList = new ArrayList<>();
            for (; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                if (sheet.getRow(rowIndex) == null) {
                    break;
                }
                T t = buildObject(clazz,sheet.getRow(rowIndex), columnIndexToPropertyMap);
                if (t != null) {
                    resultList.add(t);
                }
            }

        } catch (Exception e) {
            log.error(e.getMessage());
        }
        finally {
            file.delete();
        }
        return resultList;
    }


    private static Map<Integer, Field> buildColumnIndexToBeanFieldMap(Class<?> clazz) {
        Map<Integer, Field>  itfMap = new HashMap<>();
        Arrays.stream(clazz.getDeclaredFields()).forEach(field -> {
            Column  column = field.getAnnotation(Column.class);
            if(column!=null){
                field.setAccessible(true);
                itfMap.put(column.index(),field);
            }
        });
        return itfMap;
    }

    private static <T> T buildObject(Class<T> clazz,Row row, Map<Integer, Field> columnIndexToBeanFieldMap) throws IllegalAccessException, InstantiationException {
        T t = clazz.newInstance();
        for (int columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++) {
            Cell cell = row.getCell(columnIndex);
            if (cell != null) {
               Field field= columnIndexToBeanFieldMap.get(columnIndex);
               field.set(t, cell.getStringCellValue());
            }
        }
        return t;
    }
}

参数校验工具类

public class ValidationUtils {

    private final static Validator VALIDATOR;

    static {
        ValidatorFactory factory = Validation.buildDefaultValidatorFactory();
        VALIDATOR = factory.getValidator();
    }

    /**
     * 参数校验校验
     *
     * @param obj
     *
     * @date 2021/3/22
     */
    public static String validation(Object obj) {
        Set<ConstraintViolation<Object>> set = null;
        set = VALIDATOR.validate(obj);
        if (set!= null && set.size() > 0) {
            return getValidateErrMsg(set);
        }
        return null;
    }

    private static String getValidateErrMsg(Set<ConstraintViolation<Object>> set) {
        StringBuilder builder = new StringBuilder();
        for (ConstraintViolation<Object> constraintViolation : set) {
            Class cls = constraintViolation.getRootBean().getClass();
            String fieldName = constraintViolation.getPropertyPath().toString();
            List<Field> fields = new ArrayList<>(Arrays.asList(cls.getDeclaredFields()));
            Class superClass = cls.getSuperclass();
            if (superClass != null) {
                fields.addAll(Arrays.asList(superClass.getDeclaredFields()));
            }
            String name = null;
            for (Field field: fields) {
                if (field.getName().equals(fieldName) && field.isAnnotationPresent(Column.class)) {
                    name = field.getAnnotation(Column.class).name();
                    break;
                }
            }
            if (name == null) {
                name = fieldName;
            }
            builder.append(name).append(constraintViolation.getMessage()).append(",");
        }
        return builder.substring(0, builder.length() - 1);
    }
}

需要注意的是表格在删除内容后需要将相应的行清除，否则sheet.getLastRowNum()方法返回最末行可能为空行。

这里的做法是返回了所有的参数校验错误信息拼接成的字符串，但这些校验错误信息是无序的。如果想要有序的返回，可以参考

链接: spring validation校验对象多个字段返回的消息内容顺序随机问题.