java POI 一对多多对多导入excel工具类，2023不好用你打我

柔光的暖阳◎ 2023-10-14 14:13 16阅读 0赞

> 业务场景：当用户需要导入一张一对多对多的excel表格时，寻找了网上的很多方案很多都不够通用，自己根据apache POI 进行编写了一个工具类来应对这种场景。

#### 目录 ####

*  一、实现效果：
 *   *  1.1.excel格式
     *  2.解析后的格式
 *  二、设计思路：
 *  三、代码展示：
 *   *  3.1.业务实体类
     *   *  3.1.1 school 学校实体类
         *  3.1.2 teacherMy 教师实体类
         *  3.1.3 classMy 班级实体类
         *  3.1.4 studetMy 学生实体类
     *  3.2 自定义注解
     *   *  3.2.1 实体类与单元格映射关系注解
         *  3.2.2 实体类与实体类关系映射注解
     *  3.3 dto 中间操作类
     *   *  3.3.1 需要导入的类 ImportClass
         *  3.3.2 需要导入的字段 ImportField
     *  3.4 导入工具类（核心）
     *   *  3.4.1 工具类 ImportUtils
     *  3.5 使用用例展示 ImportUtilsTestMain
 *  总结

话不多说我们先看效果，效果不符合你的预期直接再见！

## 一、实现效果： ##

简单介绍下，一共四张MySQL表：school、teacherMy、classMy、studentMy  
直接关联关系：

1.  一个《学校》可能有多个老师
2.  一个《老师》可能有多个班级
3.  一个《班级》可能有多个学生

### 1.1.excel格式 ###

注意：本文解析一对多个的格式是根据《合并单元格的行高》来计算，也就是说《合并了的单元格》是“一”，后面单元格行高《小于》前面单元格行高的是“多”  
![在这里插入图片描述][0c85606d6c0242f58cf454eaa7aaa05d.png_pic_center]

### 2.解析后的格式 ###

解析后，debug断点截图  
![在这里插入图片描述][72a9ac3f1b8d4c9a8eccc612b532ddde.png_pic_center]  
解析后，控制台打印结果截图：  
从下图可以很清晰的看出来，完美的将excel的《合并了的单元格》关系映射为了我们的java 实体类层级关系！  
![在这里插入图片描述][78b8c19c6ee24d7abebc09a52f27323b.png_pic_center]

## 二、设计思路： ##

1.遍历用户编写的当前excel的当前sheet页对应的所有的 《行》  
2.得出当前《最外层元素》也就是《学校》对应的单元列的合并单元格的最大行高作为这一整条业务数据的行高，每次都是针对于一个完整的数据来进行解析  
3.通过《自定义注解和递归的方式》来解析单元格与我们的业务实体类的对应关系，此处的对应关系包括：《实体类之间》一对多的关系映射，《单元格与实体类字段》的映射  
4.在这解析的期间还包含了必填单元格的校验等（后续优化多项校验，如文本长度，时间格式等）

本文是利用了 apache POI 来进行实现，maven依赖如下

备注：因为博主用的是较高版本apache poi的依赖，可能有些方法不兼容低版本。
    可以自行百度替换对应的版本写法即可，很简单的！

<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>5.2.2</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>5.2.2</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-scratchpad</artifactId>
                <version>3.17</version>
            </dependency>

## 三、代码展示： ##

### 3.1.业务实体类 ###

#### 3.1.1 school 学校实体类 ####

package com.lzq.learn.test.POI一对多数据结构导入.复用.entity;
    
    import com.lzq.learn.test.POI一对多数据结构导入.复用.anno.MyExcel;
    import com.lzq.learn.test.POI一对多数据结构导入.复用.anno.MyExcelCollection;
    import lombok.Data;
    
    import java.time.LocalDate;
    import java.util.List;
    
    /**
     * 学校 实体类
     * @author LiuZhiQiang
     */
    @Data
    public class School {
        
    
        @MyExcel(name = "学校名称")
        private String schoolName;
    
        @MyExcel(name = "建校日期")
        private LocalDate schoolCreateDate;
    
        @MyExcel(name = "学校总人数")
        private Integer schoolPeopleTotal;
    
        @MyExcelCollection
        private List<TeacherMy> teacherMyList;
    
    
        public String printInfo() {
        
            String info =  "学校信息 ：{" +
                    "schoolName='" + schoolName + '\'' +
                    ", schoolCreateDate=" + schoolCreateDate +
                    ", schoolPeopleTotal=" + schoolPeopleTotal +
                    '}';
            System.out.println(info);
            return info;
        }
    }

#### 3.1.2 teacherMy 教师实体类 ####

package com.lzq.learn.test.POI一对多数据结构导入.复用.entity;
    
    import com.lzq.learn.test.POI一对多数据结构导入.复用.anno.MyExcel;
    import com.lzq.learn.test.POI一对多数据结构导入.复用.anno.MyExcelCollection;
    import lombok.Data;
    
    import java.util.List;
    
    /**
     * 老师 实体类
     * @author LiuZhiQiang
     */
    @Data
    public class TeacherMy {
        
    
        @MyExcel(name = "老师姓名")
        private String teacherName;
    
        @MyExcel(name = "老师编号")
        private String teacherCode;
    
        @MyExcel(name = "老师年龄")
        private Integer teacherAge;
    
        @MyExcel(name = "老师性别")
        private String teacherSex;
    
        @MyExcelCollection
        private List<ClassMy> classMyList;
    
    
        public String printInfo() {
        
            String info = "     老师信息 {" +
                    "teacherName='" + teacherName + '\'' +
                    ", teacherCode='" + teacherCode + '\'' +
                    ", teacherAge=" + teacherAge +
                    ", teacherSex='" + teacherSex + '\'' +
                    '}';
            System.out.println(info);
            return info;
        }
    
    }

#### 3.1.3 classMy 班级实体类 ####

package com.lzq.learn.test.POI一对多数据结构导入.复用.entity;
    
    import cn.afterturn.easypoi.excel.annotation.Excel;
    import com.lzq.learn.test.POI一对多数据结构导入.复用.anno.MyExcel;
    import com.lzq.learn.test.POI一对多数据结构导入.复用.anno.MyExcelCollection;
    import lombok.Data;
    
    import java.util.List;
    
    /**
     * 班级 实体类
     * @author LiuZhiQiang
     */
    @Data
    public class ClassMy {
        
    
        @MyExcel(name = "班级名称")
        private String className;
    
        @MyExcel(name = "班级编号")
        private String classCode;
    
        @MyExcelCollection
        private List<StudentMy> studentMyList;
    
    
        public String printInfo() {
        
            String info =  "        班级信息 {" +
                    "className='" + className + '\'' +
                    ", classCode='" + classCode + '\'' +
                    '}';
            System.out.println(info);
            return info;
        }
    }

#### 3.1.4 studetMy 学生实体类 ####

package com.lzq.learn.test.POI一对多数据结构导入.复用.entity;
    
    import com.lzq.learn.test.POI一对多数据结构导入.复用.anno.MyExcel;
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    
    import java.math.BigDecimal;
    
    /**
     * 学生实体类
     * @author LiuZhiQiang
     */
    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public class StudentMy {
        
    
        @MyExcel(name = "学生姓名")
        private String studentName;
    
        @MyExcel(name = "身份证号码")
        private String idCard;
    
        @MyExcel(name = "入学成绩", required = true)
        private BigDecimal admissionScore;
    
    
    
        public String printInfo() {
        
            String info =  "            学生信息 {" +
                    "studentName='" + studentName + '\'' +
                    ", idCard='" + idCard + '\'' +
                    ", admissionScore=" + admissionScore +
                    '}';
            System.out.println(info);
            return info;
        }
    }

### 3.2 自定义注解 ###

#### 3.2.1 实体类与单元格映射关系注解 ####

package com.lzq.learn.test.POI一对多数据结构导入.复用.anno;
    
    import java.lang.annotation.*;
    
    /**
     * @author x
     */
    @Documented
    @Target(value = ElementType.FIELD)
    @Retention(value = RetentionPolicy.RUNTIME)
    public @interface MyExcel {
        
    
    
        /**
         * Excel表格字段名称
         */
        String name() default "";
    
        /**
         * Excel表格字段是否必填
         */
        boolean required() default false;
    
        /**
         * Excel表格字段宽度
         */
        short width() default 20;
    
        /**
         * Excel表格字段排序
         */
        int sort() default 0;
    }

#### 3.2.2 实体类与实体类关系映射注解 ####

package com.lzq.learn.test.POI一对多数据结构导入.复用.anno;
    
    import java.lang.annotation.*;
    
    /**
     * @author LiuZhiQiang
     */
    @Documented
    @Target(value = ElementType.FIELD)
    @Retention(value = RetentionPolicy.RUNTIME)
    public @interface MyExcelCollection {
        
    }

### 3.3 dto 中间操作类 ###

#### 3.3.1 需要导入的类 ImportClass ####

package com.lzq.learn.test.POI一对多数据结构导入.复用.dto;
    
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    
    import java.util.List;
    
    /**
     * 表头对应的业务实体类结构
     * @author x
     */
    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public class ImportClass {
        
    
        /**             主类              **/
        private Class mainClass;
    
        /**             作为其他类的从类列表字段名称          **/
        private String fieldName;
    
        private List<ImportField> MainClassImportFieldList;
    
        /**             对应的从类           **/
        private List<ImportClass> slaveClassList;
    
    }

#### 3.3.2 需要导入的字段 ImportField ####

package com.lzq.learn.test.POI一对多数据结构导入.复用.dto;
    
    import com.lzq.learn.test.POI一对多数据结构导入.复用.entity.School;
    import com.lzq.learn.test.POI一对多数据结构导入.复用.anno.MyExcel;
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    
    import java.lang.reflect.Field;
    import java.lang.reflect.ParameterizedType;
    import java.lang.reflect.Type;
    import java.math.BigDecimal;
    import java.util.ArrayList;
    
    /**
     * @author x
     */
    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public class ImportField {
        
    
        private Integer columnIndex;
        private Boolean required;
        private String fieldName;
        //单元格表头名称
        private String cellHeadName;
        private Class<?> fieldType;
        private Field field;
    
    
        public static ImportField buildByField(Field field, int index) {
        
            ImportField importField = new ImportField();
            importField.setColumnIndex(index);
            importField.setRequired(field.getAnnotation(MyExcel.class).required());
            importField.setCellHeadName(field.getAnnotation(MyExcel.class).name());
            importField.setFieldName(field.getName());
            importField.setFieldType(field.getType());
            importField.setField(field);
            return importField;
        }
    
    }

### 3.4 导入工具类（核心） ###

#### 3.4.1 工具类 ImportUtils ####

package com.lzq.learn.test.POI一对多数据结构导入.复用.util;
    
    import cn.hutool.core.collection.CollUtil;
    import cn.hutool.core.util.StrUtil;
    import com.lzq.learn.test.POI一对多数据结构导入.复用.anno.MyExcel;
    import com.lzq.learn.test.POI一对多数据结构导入.复用.anno.MyExcelCollection;
    import com.lzq.learn.test.POI一对多数据结构导入.复用.dto.ImportClass;
    import com.lzq.learn.test.POI一对多数据结构导入.复用.dto.ImportField;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.ss.util.CellRangeAddress;
    
    import java.lang.reflect.*;
    import java.math.BigDecimal;
    import java.text.DecimalFormat;
    import java.time.LocalDate;
    import java.time.LocalDateTime;
    import java.time.ZoneId;
    import java.util.*;
    import java.util.stream.Collectors;
    
    /**
     * @author LiuZhiQiang
     */
    public class ImportUtils {
        
    
        public static void main(String[] args) throws Exception {
        
    //        parse(0, 1, School.class);
        }
    
        private static List<String> importHeader(Sheet sheet, int headerRowIndex) {
        
            Row row = sheet.getRow(headerRowIndex);
            List<String> headNameList = new ArrayList<>();
            for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
        
                Cell cell = row.getCell(i);
                String headCellValue = cell.getStringCellValue();
                headNameList.add(headCellValue);
    
            }
            return headNameList;
        }
    
    
        // 反射根据 class 对象获取对应的字段
        private static List<Field> getExportFields(Class clazz) {
        
            List<Field> collect = Arrays.stream(clazz.getDeclaredFields()).filter(
                    field -> field.isAnnotationPresent(MyExcel.class)
            ).sorted(
                    Comparator.comparing(field -> field.getAnnotation(MyExcel.class).sort())
            ).collect(Collectors.toList());
    
            return collect;
        }
    
        // 获取 ImportClass 对应的《一对多,一对多 多层》的 数据结构
        public static ImportClass getImportClass(Class clazz, ImportClass importClass, List<String> headNameList) {
        
            if (importClass == null) {
        
                importClass = new ImportClass();
            }
            importClass.setMainClass(clazz);
            ArrayList<ImportField> mainClassImportFieldList = new ArrayList<>();
            for (Field declaredField : clazz.getDeclaredFields()) {
        
                declaredField.setAccessible(true);
                if (declaredField.isAnnotationPresent(MyExcel.class)) {
        
    //                mainClassImportFieldList.add(ImportField.buildByField(declaredField, declaredField.getAnnotation(MyExcel.class).sort()));
                    String MyExcelAttributeName = declaredField.getAnnotation(MyExcel.class).name();
                    if (headNameList.contains(MyExcelAttributeName)) {
        
                        mainClassImportFieldList.add(ImportField.buildByField(declaredField, headNameList.indexOf(MyExcelAttributeName)));
                    }
                }
                boolean annotationPresent = declaredField.isAnnotationPresent(MyExcelCollection.class);
                if (annotationPresent) {
        
                    Class<?> classByListField = ImportUtils.getClassByListField(declaredField);
                    if (classByListField == null) {
        
                        continue;
                    }
                    List<ImportClass> slaveClassListOld = importClass.getSlaveClassList();
                    if (CollUtil.isEmpty(slaveClassListOld)) {
        
                        slaveClassListOld = new ArrayList<>();
                    }
                    ImportClass slaveClass = new ImportClass();
                    slaveClass.setFieldName(declaredField.getName());
                    ImportClass classByListFieldImportClass = ImportUtils.getImportClass(classByListField, slaveClass, headNameList);
                    slaveClassListOld.add(classByListFieldImportClass);
                    importClass.setSlaveClassList(slaveClassListOld);
                }
            }
            importClass.setMainClassImportFieldList(mainClassImportFieldList);
            return importClass;
        }
    
        // 获取 List<T>字段 对应的泛型类型
        public static Class<?> getClassByListField(Field field) {
        
            field.setAccessible(true);
            if (field.getType() == java.util.List.class) {
        
                // 如果是List类型，得到其Generic的类型
                Type genericType = field.getGenericType();
                if (genericType == null) {
        
                    return null;
                }
                // 如果是泛型参数的类型
                if (genericType instanceof ParameterizedType) {
        
                    ParameterizedType pt = (ParameterizedType) genericType;
                    //得到泛型里的class类型对象
                    return (Class<?>) pt.getActualTypeArguments()[0];
                }
            }
            return null;
        }
    
    
        public static <T> List<T> buildDataList(Sheet sheet, Class<T> tClass, int headRowIndex, int dataRowIndex) {
        
            // 获取 tClass 对应的所有字段， 然后寻找是否有字段带有注解@MyExcelCollection
            ArrayList<T> dataList = new ArrayList<>();
            // 1.获取表头信息对应的单元格列名
    //        List<Map<String, Object>> headerFieldList = ImportUtils.importHeader(tClass, sheet, headRowIndex);
            List result = new ArrayList();
            for (int i = dataRowIndex; i < sheet.getPhysicalNumberOfRows(); ) {
        
                Row row = sheet.getRow(i);
                //当前这行全部为空或者空格就忽视当前这行
                if (isAllRowEmpty(row, sheet.getRow(headRowIndex))) {
        
                    continue;
                }
                T tInstance = ImportUtils.parseRowData(tClass, sheet, i);
                int rowHeight = ImportUtils.getRowSpan(sheet, i, 0);
                i = i + rowHeight;
                dataList.add(tInstance);
            }
            return dataList;
        }
    
    
        /**
         * 解析一条完整的数据
         **/
        public static <T> T parseRowData(Class<T> tClass, Sheet sheet, int rowIndex) {
        
            try {
        
                Row row = sheet.getRow(rowIndex);
                // 假设当前是
                T instance = null;
                // TODO: 2023/8/4  先写死表头index=0
                List<String> headNameList = ImportUtils.importHeader(sheet, 0);
                ImportClass importClass = ImportUtils.getImportClass(tClass, null, headNameList);
    
                instance = tClass.newInstance();
                Set<Integer> mainClassRowHeightSet = new LinkedHashSet<>();
                for (ImportField importField : importClass.getMainClassImportFieldList()) {
        
                    int slaveRowIndex = rowIndex;
                    Integer columnIndex = importField.getColumnIndex();
                    int mainClassRowHeight = ImportUtils.getRowSpan(sheet, rowIndex, columnIndex);
                    Cell cell = row.getCell(columnIndex);
                    mainClassRowHeightSet.add(mainClassRowHeight);
                    Method method = null;
    
                    method = tClass.getMethod(getMethodName("set", importField.getFieldName()), importField.getFieldType());
    
                    if (method == null) {
        
                        // 说明当前对象没有这个字段
                    }
                    if (method != null) {
        
                        if (importField.getRequired() && cell == null) {
        
                            StringBuilder builder = new StringBuilder()
                                    .append("第" )
                                    .append(rowIndex + 1)
                                    .append("行，第" )
                                    .append(importField.getColumnIndex() + 1)
                                    .append("列，未填写" );
                            throw new RuntimeException(builder.toString());
                        }
                        if (Objects.nonNull(method) && Objects.nonNull(cell)) {
        
                            Object cellValue = getCellValue(cell, importField.getFieldType());
                            if (Objects.nonNull(cellValue)) {
        
                                method.invoke(instance, cellValue);
                            }
                        }
                    }
    
                }
                // 合并单元格后的当前行拥有的的行高
                List<Integer> heightList = mainClassRowHeightSet.stream().sorted().collect(Collectors.toList());
                Integer maxHeight = heightList.get(0);
                // 遍历每个子元素, 进行单元格内的数据填充
                for (ImportClass slaveImportClass : importClass.getSlaveClassList()) {
        
                    // 每个子元素都从 父元素的《slaveRowIndex》开始
                    // 遍历父元素拥有的行内，对应的有多少个子元素
                    List parseRowItemList = ImportUtils.parseRowItemList(sheet, rowIndex, maxHeight, slaveImportClass);
                    // 给list字段赋值: 写法一暂时行不通
    //                Method slaveListMethod = null;
    //                slaveListMethod = slaveImportClass.getMainClass().getMethod(getMethodName("set", slaveImportClass.getFieldName()), slaveImportClass.getMainClass());
    //                if (Objects.nonNull(slaveListMethod)) {
        
    //                    slaveListMethod.invoke(instance, parseRowItemList);
    //                }
                    // 给list字段赋值：写法二
                    Field declaredListField = instance.getClass().getDeclaredField(slaveImportClass.getFieldName());
                    declaredListField.setAccessible(true);
                    declaredListField.set(instance, parseRowItemList);
                }
                return instance;
            } catch (Exception e) {
        
                e.printStackTrace();
            }
            return null;
        }
    
        /**
         * 获取父元素行内有多少个子元素，返回值是一个List
         **/
        public static <T> List<T> parseRowItemList(Sheet sheet, int parentRowIndex, int parentMaxHeight, ImportClass slaveImportClass) throws Exception {
        
            Class<T> slaveClass = slaveImportClass.getMainClass();
            List<ImportField> slaveImportFieldList = slaveImportClass.getMainClassImportFieldList();
            ArrayList<T> slaveDataList = new ArrayList<>();
            // 每个子元素都从 父元素的《第一行》开始
            // 遍历父元素拥有的行内，对应的有多少个子元素
            for (int slaveRowIndex = parentRowIndex; slaveRowIndex < parentRowIndex + parentMaxHeight; ) {
        
                T slaveInstance = slaveClass.newInstance();
                int slaveClassRowHeight = 1;
                for (ImportField slaveImportField : slaveImportFieldList) {
        
                    Row slaveRow = sheet.getRow(slaveRowIndex);
                    Integer slaveColumnIndex = slaveImportField.getColumnIndex();
                    // 当前单元格 对应的行高
                    slaveClassRowHeight = ImportUtils.getRowSpan(sheet, slaveRowIndex, slaveColumnIndex);
                    Cell slaveRowCell = slaveRow.getCell(slaveColumnIndex);
    //                        slaveClassRowHeightSet.add(slaveClassRowHeight);
                    Method slaveMethod = null;
                    slaveMethod = slaveClass.getMethod(getMethodName("set", slaveImportField.getFieldName()), slaveImportField.getFieldType());
                    if (slaveMethod == null) {
        
                        // 说明当前对象没有这个字段
                        System.out.println("说明当前对象没有这个字段" );
                    }
                    if (slaveMethod != null) {
        
                        if (slaveImportField.getRequired() && slaveRowCell == null) {
        
                            StringBuilder builder = new StringBuilder()
                                    .append("第" )
                                    .append(slaveRowIndex + 1)
                                    .append("行，第" )
                                    .append(slaveImportField.getColumnIndex() + 1)
                                    .append("列，未填写" );
                            throw new RuntimeException(builder.toString());
                        }
                        if (Objects.nonNull(slaveMethod) && Objects.nonNull(slaveRowCell)) {
        
                            Object cellValue = getCellValue(slaveRowCell, slaveImportField.getFieldType());
                            if (Objects.nonNull(cellValue)) {
        
                                slaveMethod.invoke(slaveInstance, cellValue);
                            }
                        }
                    }
                }
                // 子元素的子元素进行填充值, 此处使用了递归
                if (CollUtil.isNotEmpty(slaveImportClass.getSlaveClassList())) {
        
                    for (ImportClass slaveClassSlaveClass : slaveImportClass.getSlaveClassList()) {
        
                        List<Object> slaveListSlaveList = ImportUtils.parseRowItemList(sheet, slaveRowIndex, slaveClassRowHeight, slaveClassSlaveClass);
                        Method slaveListSlaveListMethod = null;
                        slaveListSlaveListMethod = slaveImportClass.getMainClass().getMethod(getMethodName("set", slaveClassSlaveClass.getFieldName()), java.util.List.class);
                        if (Objects.nonNull(slaveListSlaveListMethod)) {
        
                            slaveListSlaveListMethod.invoke(slaveInstance, slaveListSlaveList);
                        } else {
        
                            System.out.println("说明当前对象没有这个List字段" );
                        }
                    }
                }
                slaveRowIndex = slaveRowIndex + slaveClassRowHeight;
                slaveDataList.add(slaveInstance);
            }
            return slaveDataList;
        }
    
    
        private static Object getCellValue(Cell cell, Class fieldType) {
        
            Object result = null;
            CellType cellType = cell.getCellType();
            switch (cellType) {
        
                case _NONE:
                    break;
                case BLANK:
                    break;
                case STRING:
                    result = cell.getStringCellValue().trim();
                    break;
                case NUMERIC:
                    double numericCellValue = cell.getNumericCellValue();
                    if (fieldType.equals(LocalDate.class)) {
        
                        result = cell.getDateCellValue().toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
                    } else if (fieldType.equals(LocalDateTime.class)) {
        
                        result = cell.getDateCellValue().toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
                        //TODO 查看此处为何会报错，是版本的原因吗？
                        result = cell.getLocalDateTimeCellValue();
                    } else if (fieldType.equals(String.class)) {
        
                        DecimalFormat decimalFormat = new DecimalFormat("#" );
                        result = decimalFormat.format(cell.getNumericCellValue());
                    } else if (fieldType == Short.class) {
        
                        result = (short) numericCellValue;
                    } else if (fieldType == Integer.class) {
        
                        result = (int) cell.getNumericCellValue();
                    } else if (fieldType == Long.class) {
        
                        result = (long) numericCellValue;
                    } else if (fieldType == Float.class) {
        
                        result = (float) numericCellValue;
                    } else if (fieldType == Double.class) {
        
                        result = numericCellValue;
                    } else if (fieldType == BigDecimal.class) {
        
                        result = BigDecimal.valueOf(cell.getNumericCellValue());
                    } else {
        
                        result = cell.getNumericCellValue();
                    }
                    break;
                case FORMULA:
                    break;
                case BOOLEAN:
                    break;
                case ERROR:
                    break;
            }
            return result;
        }
    
        private static String getMethodName(String prefix, String fieldName) {
        
            StringBuilder builder = new StringBuilder()
                    .append(prefix)
                    .append(fieldName.substring(0, 1).toUpperCase())
                    .append(fieldName.substring(1));
            return builder.toString();
        }
    
        /**
         * 验证excel是否全部为空
         *
         * @param row      当前行
         * @param firstRow 第一行标题行
         * @return
         */
        public static boolean isAllRowEmpty(Row row, Row firstRow) {
        
            if (row == null) {
        
                return true;
            }
            int count = 0;
            //单元格数量
            int rowCount = firstRow.getLastCellNum() - firstRow.getFirstCellNum();
            //判断多少个单元格为空
            for (int c = 0; c < rowCount; c++) {
        
                Cell cell = row.getCell(c);
                if (cell == null || cell.getCellType() == CellType.BLANK || StrUtil.isEmpty((cell + "" ).trim())) {
        
                    count += 1;
                }
            }
            if (count == rowCount) {
        
                return true;
            }
            return false;
        }
    
        // 查看某个单元格是否在 合并区域内, 返回值是 该合并区域内的高度差
        private static int getRowSpan(Sheet sheet, int rowIndex, int columnIndex) {
        
            List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
            for (CellRangeAddress mergedRegion : mergedRegions) {
        
                if (mergedRegion.isInRange(rowIndex, columnIndex)) {
        
                    int count = mergedRegion.getLastRow() - mergedRegion.getFirstRow() + 1;
                    if (count < 1) {
        
                        throw new RuntimeException("计算单元格高度异常，程序终止" );
                    }
                    return count;
                }
            }
            return 1;
        }
    
    
    }

### 3.5 使用用例展示 ImportUtilsTestMain ###

package com.lzq.learn.test.POI一对多数据结构导入.复用;
    
    import com.lzq.learn.test.POI一对多数据结构导入.复用.entity.ClassMy;
    import com.lzq.learn.test.POI一对多数据结构导入.复用.entity.School;
    import com.lzq.learn.test.POI一对多数据结构导入.复用.entity.StudentMy;
    import com.lzq.learn.test.POI一对多数据结构导入.复用.entity.TeacherMy;
    import com.lzq.learn.test.POI一对多数据结构导入.复用.util.ImportUtils;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.ss.usermodel.WorkbookFactory;
    
    import java.io.FileInputStream;
    import java.util.List;
    
    /**
     * @author x
     */
    public class ImportUtilsTestMain {
        
    
    
        public static void main(String[] args) throws Exception{
        
    //        testSheet0();
            testSheet1();
        }
    
        /**                         测试sheet0                            **/
        public static void testSheet0() throws Exception{
        
            // 打开 Excel 文件
            String workingDir = System.getProperty("user.dir" );
            String filePath = workingDir + "//src//main//resources//importExcel//fuyong//ontToMoreFuYong.xlsx";
            FileInputStream fis = new FileInputStream(filePath);
            Workbook wb = WorkbookFactory.create(fis);
            int sheetIndex = 0;
            int headRowSpan = 0;
            int dataRowStartIndex = 1;
            // 解析表格数据
            Sheet sheet = wb.getSheetAt(sheetIndex);
            // todo 递归结构待优化
            List<School> schoolList = ImportUtils.buildDataList(sheet, School.class, headRowSpan, dataRowStartIndex);
            schoolList.forEach(System.out::println);
        }
    
        /**                             测试 sheet1                       **/
        public static void testSheet1() throws Exception{
        
            // 打开 Excel 文件
            String workingDir = System.getProperty("user.dir" );
            String filePath = workingDir + "//src//main//resources//importExcel//fuyong//ontToMoreFuYong.xlsx";
            FileInputStream fis = new FileInputStream(filePath);
            Workbook wb = WorkbookFactory.create(fis);
            int sheetIndex = 1;
            int headRowSpan = 0;
            int dataRowStartIndex = 1;
            // 解析表格数据
            Sheet sheet = wb.getSheetAt(sheetIndex);
            // todo 递归结构待优化
            List<School> schoolList = ImportUtils.buildDataList(sheet, School.class, headRowSpan, dataRowStartIndex);
    //        schoolList.forEach(System.out::println);
            System.out.println();
            System.out.println("解析结果如下：");
            for (School school : schoolList) {
        
                school.printInfo();
                for (TeacherMy teacherMy : school.getTeacherMyList()) {
        
                    teacherMy.printInfo();
                    for (ClassMy classMy : teacherMy.getClassMyList()) {
        
                        classMy.printInfo();
                        for (StudentMy studentMy : classMy.getStudentMyList()) {
        
                            studentMy.printInfo();
                        }
                    }
                }
                System.out.println();
            }
        }
    }

## 总结 ##

> 总体来说实现思想就是： 反射 + 自定义注解 + 递归。  
> 一般来说只要按照《合并单元格代表对应实体类关系》的规律来创建excel，都可以将excel中合并单元格的关系转化为java 实体类的层级关系。

文中不足的地方，或者哪里有错，有疑问的地方可以在下方评论区评论，感谢留言。觉得好用的话，记得收藏 + 点赞。

[0c85606d6c0242f58cf454eaa7aaa05d.png_pic_center]: https://img-blog.csdnimg.cn/0c85606d6c0242f58cf454eaa7aaa05d.png#pic_center
[72a9ac3f1b8d4c9a8eccc612b532ddde.png_pic_center]: https://img-blog.csdnimg.cn/72a9ac3f1b8d4c9a8eccc612b532ddde.png#pic_center
[78b8c19c6ee24d7abebc09a52f27323b.png_pic_center]: https://img-blog.csdnimg.cn/78b8c19c6ee24d7abebc09a52f27323b.png#pic_center