基于poi封裝 注解式導入excel表格功能

前言

本篇文章針對使用poi封裝Excel導入功能, 可excel轉成對應實體對象。代碼中無複雜邏輯，已标注多處注釋，故不在這多做解釋。

如需要導出請參考另外一篇文章:

https://blog.csdn.net/qq_45813281/article/details/108009102

源代碼

以下為導出全部源代碼

依賴

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
    <scope>compile</scope>
</dependency>
           

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface Excel {

    /**
     * 表格 列名
     *
     * @return
     */
    public String name() default "";

}
           

核心代碼

public class ExcelImportUtil {

    /**
     * 資料轉換實體對象
     */
    Class entityClass;

    /**
     * 要解析的檔案
     */
    private File file;

    /**
     * excel檔案
     */
    private Workbook workbook;

    /**
     * 緩存實體的屬性map
     */
    private static Map<String, Map> cacheEntityFieldMap = new HashMap();

    public <T> List importToList(File file, Class<T> clazz) {
        this.entityClass = clazz;
        this.file = file;
        init();
        T t = null;
        try {
            t = clazz.newInstance();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
        return this.<T>parse(t);
    }

    /**
     * 初始化資源檔案
     */
    private void init() {
        try {
            String fileName = file.getName().toUpperCase();
            if (fileName.endsWith(ExcelTypeEnum.XLS.name())) {
                this.workbook = new HSSFWorkbook(new FileInputStream(this.file));
            } else if (fileName.endsWith(ExcelTypeEnum.XLSX.name())) {
                this.workbook = new XSSFWorkbook(new FileInputStream(this.file));
            } else {
                Assert.notNull(workbook, "檔案名稱非Excel格式!");
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 解析表格資料
     *
     * @return
     */
    private <T> List<T> parse(T t) {
        // 傳回值
        List results = new ArrayList<>();
        // 擷取所有的字段
        Map<String, Field> fieldMap = null;
        // 放置對象屬性
        ArrayList<Field> fieldList = new ArrayList();
        try {
            // 緩存
            if (cacheEntityFieldMap.get(this.entityClass.getName()) != null) {
                fieldMap = cacheEntityFieldMap.get(this.entityClass.getName());
            } else {
                // 通過反射, 将有實體的字段放入map
                fieldMap = new HashMap(10);
                Field[] declaredFields = this.entityClass.getDeclaredFields();
                if (declaredFields != null) {
                    for (Field field : declaredFields) {
                        Excel excel = field.getAnnotation(Excel.class);
                        if (excel == null) {
                            continue;
                        }
                        String name = excel.name();
                        fieldMap.put(name, field);
                    }
                }
                ExcelImportUtil.cacheEntityFieldMap.put(this.entityClass.getName(), fieldMap);
            }
            // 擷取sheet個數
            int sheetTotal = this.workbook.getNumberOfSheets();
            // 循環表格sheet頁
            for (int i = 0; i < sheetTotal; i++) {
                Sheet sheet = this.workbook.getSheetAt(i);
                Row topRow = sheet.getRow(0);
                short lastCellNum = topRow.getLastCellNum();
                for (int j = 0; j < lastCellNum; j++) {
                    Cell cell = topRow.getCell(j);
                    String value = cell.getStringCellValue();
                    Field field = fieldMap.get(value);
                    // 設定字段的私有通路
                    field.setAccessible(true);
                    // 根據單元位置 存放實體屬性
                    fieldList.add(field);
                }
                // 循環所有行
                for (int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) {
                    Row row = sheet.getRow(j);
                    // 建立實體對象
                    Object entityObj = this.entityClass.newInstance();
                    // 循環一行所有列
                    for (int k = 0; k < row.getLastCellNum(); k++) {
                        Field field = fieldList.get(k);
                        // 擷取字段類型名稱
                        String typeName = field.getType().getName();
                        String cellValue = row.getCell(k).getStringCellValue();
                        field.set(entityObj, transitionType(typeName, cellValue));
                    }
                    // 存放轉換後實體
                    results.add((T) entityObj);
                }
            }
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return results;
    }

    /**
     * 轉換資料類型
     *
     * @param typeName  類型name
     * @param cellValue 要轉換的字元串
     * @return
     */
    private Object transitionType(String typeName, String cellValue) {
        Object value;
        switch (typeName) {
            case "java.lang.String":
                value = cellValue;
                break;
            case "int":
                value = Integer.valueOf(cellValue);
                break;
            case "long":
                value = Long.valueOf(cellValue);
                break;
            case "double":
                value = Double.valueOf(cellValue);
                break;
            case "byte":
                value = Byte.valueOf(cellValue);
                break;
            case "short":
                value = Short.valueOf(cellValue);
                break;
            case "float":
                value = Float.valueOf(cellValue);
                break;
            case "boolean":
                value = Boolean.valueOf(cellValue);
                break;
            default:
                value = null;
                break;
        }
        return value;
    }

}

           

實體

public class QydjYykhEntity {

    private String id;
    /**
     * 企業主鍵
     */
    @Excel(name = "企業主鍵")
    private String pripid;
    /**
     * 公司名稱
     */
    @Excel(name = "公司名稱")
    private String entname;
    /**
     * 預約網點
     */
    @Excel(name = "預約網點")
    private String bankno;
    /**
     * 辦理人電話
     */
    @Excel(name = "辦理人電話")
    private String tel;
    /**
     * 辦理人
     */
    @Excel(name = "辦理人")
    private String name;
    
....省略 get set
}
           

測試代碼

@Test
    public void testImportToList() {
        ExcelImportUtil excelImportUtil = new ExcelImportUtil();
        List<QydjYykhEntity> list = excelImportUtil.importToList(new File("E:\\workspace\\test.xlsx"), QydjYykhEntity.class);
        System.out.println(list);
    }
           

導入檔案截圖

sheet

sheet1

結果