Poi Excel表格导入与校验

最新推荐文章于 2024-07-25 13:23:20 发布

kidkid1208

最新推荐文章于 2024-07-25 13:23:20 发布

阅读量2.3k

点赞数 1

分类专栏：工作文章标签： poi excel

本文链接：https://blog.csdn.net/kidkid1208/article/details/115110795

版权

工作专栏收录该内容

1 篇文章

订阅专栏

这篇博客介绍了如何利用Easypoi进行Excel的导入和参数校验，通过注解定义列信息并进行数据有效性检查。文章详细展示了自定义的Column注解、Cell实体类、Excel导入工具类以及参数校验工具类的实现，适用于小规模数据导入场景。同时，文中提到虽然Easypoi支持大数据量导入，但该项目并未涉及。最后，文章提到了当前校验错误信息为无序，如需有序返回可参考相关资料。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Excel表格导入与校验

最近工作上要做一个Excel上传的内容，涉及到excel表格的导入的参数校验，就记录一下。目前主流的excel工具包括Apache POI以及基于poi开发的easypoi，easyexcel。easyexcel主要支持大数据量的高效导入，由于项目内目前还涉及不到千行以上数据导入，以后估计也不会涉及，就没有深入。easypoi提供了参数校验功能，因此本项目针对easypoi提供的导入方式和校验工具类做了一定的改造和简化。

以下是实战内容。

表格列注解

@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);
    }
}