Java 通过注解方式超简单实现excel导入自动解析并返回指定类型的List 对象

 1、自定义注解类用于自定义类对象InExcelProperty

import java.lang.annotation.*;

/**
 * @description: 导入Excel解析字段
 * @create: 2023-05-24 08:33
 **/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface InExcelProperty {
    /**
     * 表头下标
     *
     * @return
     */
    int index() default -1;

    /**
     * 表头标题
     *
     * @return
     */
    String name() default "";

    /**
     * 对应属性值，如果不填默认被注解的字段属性值
     *
     * @return
     */
    String value() default "";

    /**
     * 类型单元格
     * CELL_TYPE_STRING 1 InexceUtils中的枚举
     *
     * @return
     */
    int type() default 1;

    /**
     * 是否校验为null 默认不校验可为空
     *
     * @return
     */
    boolean require() default false;

    /**
     * 翻译前的值
     *
     * @return
     */
    String[] transBefore() default {};

    /**
     * 翻译后的值
     *
     * @return
     */
    String[] transAfter() default {};
}

2、解析工具类

import org.apache.poi.ss.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.*;

/**
 *
 * @description: 导入解析excel文件解析工具
 * @create: 2023-05-23 17:59
 **/
public class InexcelUtils {
    private static final int maxCount = 200000;

    public final static int CELL_TYPE_NUMERIC = 0;
    public final static int CELL_TYPE_STRING = 1;
    public final static int CELL_TYPE_FORMULA = 2;
    public final static int CELL_TYPE_BLANK = 3;
    public final static int CELL_TYPE_BOOLEAN = 4;
    public final static int CELL_TYPE_ERROR = 5;
    public final static int CELL_TYPE_STRING_DATE = 6;//可能是date可能是string

    /**
     * 解析excel，并返回对应data集合
     *
     * @param excelFile        待解析的excel
     * @param excelPropertyClz 用于解析的excel clz
     * @param retClz           要返回的clz
     * @return
     * @throws Exception
     */
    public static <T> List<T> excelAnalysis(MultipartFile excelFile, Class<?> excelPropertyClz, Class<T> retClz)
            throws Exception {
        /*
         * 解析前常规校验
         */
        String fileName = excelFile.getOriginalFilename();
        boolean xls = fileName.matches("^.+\\.(?i)(xls)$");
        boolean xlsx = fileName.matches("^.+\\.(?i)(xlsx)$");
        if (!xls && !xlsx) {
            throw new Exception("文件格式不正确");
        }
        List<T> retList = new ArrayList<>();
        InputStream is = null;
        try {
            is = excelFile.getInputStream();
            Workbook wb = WorkbookFactory.create(is);
            Sheet sheet = wb.getSheetAt(0);
            if (sheet == null) {
                throw new Exception("上传失败sheet为空");
            }
            int count = sheet.getLastRowNum();
            if (count > maxCount) {
                throw new Exception("亲，数据有点大，有点处理不过来~~");
            }

            excelAnalysisBySheet(excelPropertyClz, retClz, retList, sheet, count);
        } catch (Exception e) {
            throw e;
        } finally {
            if (is != null) is.close();
        }
        return retList;
    }

    private static <T> void excelAnalysisBySheet(Class<?> excelPropertyClz, Class<T> retClz, List<T> retList, Sheet sheet, int count) throws Exception {
        List<Map.Entry<String, InExcelProperty>> inExcelPropertyList = findInExcelPropertyByClass(excelPropertyClz);
        for (int r = 1; r <= count; r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            //一行为一个对象，初始化
            T t = retClz.newInstance();//
            excelAnalysisByRow(inExcelPropertyList, t, row, r);
            retList.add(t);
        }


    }


    /**
     * 解析每行数据的每个单元
     * 一个单元格对应一个属性
     *
     * @param inExcelPropertyList
     * @param t
     * @param row
     * @param r
     * @param <T>
     * @throws Exception
     */
    private static <T> void excelAnalysisByRow(List<Map.Entry<String, InExcelProperty>> inExcelPropertyList, T t, Row row, int r) throws Exception {

        for (int n = 0; n < inExcelPropertyList.size(); n++) {
            Map.Entry<String, InExcelProperty> entry = inExcelPropertyList.get(n);
            String keyPropName = entry.getKey();
            InExcelProperty inExcelProperty = entry.getValue();
            //具体单元格数据
            Cell cell = row.getCell(n);
            excelAnalysisByCell(keyPropName, inExcelProperty, t, cell, r);
        }


    }

    /**
     * 赋值待赋值字段
     *
     * @param keyPropName
     * @param inExcelProperty
     * @param t
     * @param cell
     * @param r
     * @param <T>
     * @throws Exception
     */
    private static <T> void excelAnalysisByCell(String keyPropName, InExcelProperty inExcelProperty, T t, Cell cell, int r) throws Exception {
        if (cell == null) {
            //  判断属性是否可为空
            if (!inExcelProperty.require()) {
                //跳过可为空的单元格
                return;
            } else {
                throw new Exception("文件第" + (r + 1) + "行," + inExcelProperty.name() + "列属性不能为空");
            }

        }
        //获取单元格的值
        int cellType = inExcelProperty.type();
        cell.setCellType(CellType.forInt(cellType));
        Object value = getValue(cell, cellType);
        //待赋值的属性名称
        String propName = inExcelProperty.value();//属性名称
        if (propName == null || propName.length() == 0) {
            propName = keyPropName;
        }
        //判断值是否为空
        isNullFlag(value, inExcelProperty.require(), inExcelProperty.name(), r);
        //需要值码转换的做处理
        String[] bef = inExcelProperty.transBefore();
        String[] aft = inExcelProperty.transAfter();
        if (bef != null && bef.length > 0 && aft != null && aft.length > 0) {
            for (int i = 0; i < bef.length; i++) {
                if (String.valueOf(value).equals(bef[i])) {
                    value = aft[i];
                }
            }
        }
        //判断值是否为空
        isNullFlag(value, inExcelProperty.require(), inExcelProperty.name(), r);


        //开始为属性赋值--嵌套对象处理
        if (propName.contains(".")) {
            String[] propNames = propName.split(".");
            String supPropName = propNames[0];
            Field fieId = getField(t.getClass(), supPropName);
            T tin = (T) fieId.get(t);
            if (tin == null) {
                tin = (T) fieId.getType().newInstance();
                setFieldValue(t, supPropName, tin);
            } else {

            }
            propName = propNames[1];
            t = tin;
        }
        //最终赋值
        setFieldValue(t, propName, value);
    }


    /**
     * 校验是否为空
     *
     * @param value
     * @param require
     * @param name
     * @param r
     * @throws Exception
     */
    private static void isNullFlag(Object value, boolean require, String name, int r) throws Exception {
        //判断值是否为空
        if (value == null || String.valueOf(value).length() == 0) {
            if (!require) {
                return;
            } else {
                throw new Exception("文件第" + (r + 1) + "行," + name + "列属性不能为空");
            }
        }
    }

    /**
     * 获取对应值
     *
     * @param cell
     * @param cellType
     * @return
     */
    private static Object getValue(Cell cell, int cellType) {
        Object value = null;
        switch (cellType) {
            case CELL_TYPE_NUMERIC:
                value = cell.getNumericCellValue();
                break;
            case CELL_TYPE_STRING:
                value = cell.getStringCellValue();
                break;
            case CELL_TYPE_FORMULA:
                value = cell.getCellFormula();
                break;
            case CELL_TYPE_BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case CELL_TYPE_STRING_DATE:
                try {
                    value = cell.getStringCellValue();
                } catch (Exception e) {
                    value = DateUtils.format(cell.getDateCellValue(), DateUtils.DATE_PATTERN);
                }
                break;
        }
        return value;

    }


    /**
     * 给对象属性赋值
     *
     * @param t
     * @param propName
     * @param value
     * @param <T>
     * @throws Exception
     */
    private static <T> void setFieldValue(T t, String propName, Object value) throws Exception {
        Class<?> classIs = t.getClass();
        Field nameFieId = getField(classIs, propName);
        if (nameFieId == null) {
            return;
        }
        //开启权限
        nameFieId.setAccessible(true);
        String fieldType = nameFieId.getType().getSimpleName();
        if ("String".equals(fieldType)) {
            nameFieId.set(t, value.toString());
        } else if ("long".equals(fieldType) || "Long".equals(fieldType)) {
            nameFieId.set(t, Long.valueOf(value.toString()));
        } else if ("int".equals(fieldType) || "Integer".equals(fieldType)) {
            nameFieId.set(t, Integer.valueOf(value.toString()));
        } else if ("double".equals(fieldType) || "Double".equals(fieldType)) {
            nameFieId.set(t, Double.valueOf(value.toString()));
        } else if ("float".equals(fieldType) || "Float".equals(fieldType)) {
            nameFieId.set(t, Float.valueOf(value.toString()));
        } else if ("boolean".equals(fieldType) || "Boolean".equals(fieldType)) {
            if (value.getClass().equals(Boolean.class)) {
                nameFieId.set(t, (Boolean) value);
            } else {
                nameFieId.set(t, Boolean.valueOf(value.toString()));
            }
        } else if ("Date".equals(fieldType)) {
            if (value.getClass().equals(Date.class)) {
                nameFieId.set(t, (Date) value);

            } else {
                nameFieId.set(t, DateUtils.stringToDate(value.toString(), DateUtils.DATE_TIME_PATTERN));
            }
        }
        //关闭权限
        nameFieId.setAccessible(false);

    }

    /**
     * 获取字段（子方法）
     *
     * @param classIs
     * @param fieldKey
     * @return
     */
    private static Field getField(Class<?> classIs, String fieldKey) {
        Field fieId = null;
        // 先找当前类
        try {
            fieId = classIs.getDeclaredField(fieldKey);
        } catch (NoSuchFieldException e) {
            //e.printStackTrace();
        }

        // 当前类没有找父类
        if (fieId == null) {
            try {
                fieId = classIs.getSuperclass().getDeclaredField(fieldKey);
            } catch (NoSuchFieldException e) {
                // e.printStackTrace();
            }
        }
        return fieId;
    }


    /**
     * 获取bo中属性值
     *
     * @param cla
     * @return
     */
    private static <T> List<Map.Entry<String, InExcelProperty>> findInExcelPropertyByClass(Class<T> cla) {
        Field[] fields = cla.getDeclaredFields();
        Map<String, InExcelProperty> map = new LinkedHashMap<>();//按顺序
        for (Field field : fields) {
            if (field.isAnnotationPresent(InExcelProperty.class)) {
                InExcelProperty dataDict = field.getAnnotation(InExcelProperty.class);
                String fieldName = field.getName();
                map.put(fieldName, dataDict);
            }
        }
        List<Map.Entry<String, InExcelProperty>> list = new ArrayList<Map.Entry<String, InExcelProperty>>(map.entrySet());
        Collections.sort(list, (a, b) -> {
            return a.getValue().index() - b.getValue().index();
        });


        return list;
    }

}

3.使用场景

     

   3.1、自定义实体类BaseNumcomparExcel 并使用该注解

import lombok.Data;

/**
 * @description: 编码对照
 * @create: 2023-05-23 17:14
 **/
@Data
public class BaseNumcomparExcel {


   
    @InExcelProperty(name = "系统编码", index = 0)
    private String sysNum;
   
    @InExcelProperty(name = "现场编码", index = 1)
    private String siteNum;

    @InExcelProperty(name = "编码说明", index = 2)
    private String illustrate;


}

        3.2  调用

    @PostMapping("/import")
    @ApiOperation("导入")
    @LogOperation("导入")
    @ApiImplicitParam(name = "file", value = "文件", paramType = "query", dataType = "file")
    public Result importUnit(@RequestParam("file") MultipartFile file) throws Exception {
        //解析文件
        List<BaseNumcomparEntity> list = InexcelUtils.excelAnalysis(file,   BaseNumcomparExcel.class, BaseNumcomparEntity.class);
        String code = “op”;
       baseNumcomparService.importUnit(list, code);

        return new Result().ok("成功导入数据" + list.size() + "条");


    }