Java 读取Excel导入数据库，形成树状结构

最近开发过程中遇到一个Excel的导入的功能，因为导入的数据结构具有层次结构，经过一番研究，最终得以实现，所有写下该文章，记录过程，供以后参考。

下图是导入Excel的数据结构：

使用POI解析Excel，数据封装然后进行入库。下面是核心代码。

@Override
    public KnowledgeBaseDictImportRespVO importKnowledgeBaseDict(MultipartFile file) throws IOException {
        KnowledgeBaseDictImportRespVO kbdir = KnowledgeBaseDictImportRespVO.builder().build(); // 该对象是方法返回的结果对象
        Map res = new ConcurrentHashMap<>();
        List createProblemsList = new ArrayList<>();
        String fileName = file.getOriginalFilename();
        String fileType = fileName.substring(fileName.lastIndexOf(".")).toLowerCase();
        if(fileType.equals(".xls") || fileType.equals(".xlsx")) {
            AdminUserRespDTO adminUserRespDTO = adminUserApi.getUser(SecurityFrameworkUtils.getLoginUserId()).getData();
            //poi解析excel
            InputStream inputStream = file.getInputStream();
            // 读取工作薄 .xls 与 .xlsx 需要创建不通的工作簿 采用工作簿工厂类创建对应的工作簿类
            Workbook workbook = WorkbookFactory.create(inputStream);
            Sheet sheet = workbook.getSheetAt(0);
            //获取系统名称
            Row row0 = sheet.getRow(0); // 第一行的表头
            Cell cell0 = row0.getCell(0); // 第一列
            String belongSystem = cell0.getStringCellValue().trim().replace("系统功能树","");
            if (StringUtils.isEmpty(belongSystem)) {
                res.put(StrUtils.getRandomString(6),"归属系统不能为空!");
                kbdir.setFailureProblems(res);
                return kbdir;
            }
            KnowledgeBaseDictDO kbdictdo = knowledgeBaseDictMapper.getKnowledgeBaseDictByParenetIdAndMenuName(0L,belongSystem);
            //定义一个cellId,此为每一次循环前一列的id
            long cellId = 0;
            String cellName = null; // 记录父级名称
            String firstMenuName = null; // 因为合并单元格只有第一个有值,需要记录该值。
            int count = 0;
            if (ObjectUtils.isEmpty(kbdictdo)) {
                kbdictdo = new KnowledgeBaseDictDO();
                kbdictdo.setSort(0);
                kbdictdo.setParentId(0L);
                kbdictdo.setMenuLevel(0);
                kbdictdo.setMenuName(belongSystem);
                kbdictdo.setBelongingSystem(belongSystem);
                kbdictdo.setCreator(adminUserRespDTO.getNickname());
 
                int rs = knowledgeBaseDictMapper.insert(kbdictdo);
                if (rs > 0) {
                    count += rs;
                    createProblemsList.add(belongSystem);
                    cellId = kbdictdo.getId();
                    cellName = kbdictdo.getMenuName();
                }
            }
            int rows = sheet.getPhysicalNumberOfRows();
            if (rows > 2) {
                //按照行进行循环，读取当前行的列
                for (int i = 0,j = 2; j < rows; j++,i++) {
                    // 读取行
                    Row row = sheet.getRow(j);
                    //查询当前行有多少列
                    int physical = sheet.getRow(j).getPhysicalNumberOfCells();
                    if (row != null) {
                        //获取第一列的单元格
                        Cell cell = row.getCell(0);
                        //判断单元格是否为空
                        if (!(cell == null || "".equals(cell.toString().trim()))) {
                            firstMenuName = cell.getStringCellValue().trim();
                            //根据单元格的数据查询数据库是否存在记录
                            KnowledgeBaseDictDO kbddoFirst = knowledgeBaseDictMapper.getKnowledgeBaseDictByMenuNameAndMenuLevelAndbls(firstMenuName,1,belongSystem);
                            //数据库不存在，添加记录
                            if (ObjectUtils.isEmpty(kbddoFirst)) {
                                kbddoFirst = new KnowledgeBaseDictDO();
                                kbddoFirst.setSort(i);
                                kbddoFirst.setParentId(cellId);
                                kbddoFirst.setBelongingSystem(belongSystem);
                                kbddoFirst.setParentMenuName(cellName);
                                kbddoFirst.setMenuName(cell.getStringCellValue().trim());
                                kbddoFirst.setMenuLevel(1);
                                kbddoFirst.setCreator(adminUserRespDTO.getNickname());
 
                                int rs = knowledgeBaseDictMapper.insert(kbddoFirst);
                                if (rs > 0) {
                                    count += rs;
                                    createProblemsList.add(cell.getStringCellValue().trim());
                                    // 记录父级id 与 名称
                                    cellId = kbddoFirst.getId();
                                    cellName = kbddoFirst.getMenuName();
                                }
                            } else {
                                //数据库存在记录，将这条记录的id作为父id
                                cellId = kbddoFirst.getId();
                                cellName = kbddoFirst.getMenuName();
                            }
                        } else { // 表示循环到合并单元格的地方,查询数据库记录父级信息
                            if (StringUtils.isNotEmpty(firstMenuName)) {
                                KnowledgeBaseDictDO kbddoFirst = knowledgeBaseDictMapper.getKnowledgeBaseDictByMenuNameAndMenuLevelAndbls(firstMenuName,1,belongSystem);
                                cellId = kbddoFirst.getId();
                                cellName = kbddoFirst.getMenuName();
                            }
                        }
                    }
                    // 从第二列单元格开始
                    for (int k = 1,l = 0; k < physical; k++,l++) {
                        //取单元格
                        Cell cell = row.getCell(k);
                        //判断单元格是否为空
                        if (!(cell == null || "".equals(cell.toString().trim()))) {
                            //查询数据库有无此记录
                            KnowledgeBaseDictDO kbddoSecond = knowledgeBaseDictMapper.getKnowledgeBaseDictByMenuNameAndMenuLevelAndbls(cell.getStringCellValue().trim(),k + 1,belongSystem);
                            if (ObjectUtils.isEmpty(kbddoSecond)) {
                                kbddoSecond = new KnowledgeBaseDictDO();
                                kbddoSecond.setSort(l);
                                kbddoSecond.setParentId(cellId);
                                kbddoSecond.setBelongingSystem(belongSystem);
                                kbddoSecond.setParentMenuName(cellName);
                                kbddoSecond.setMenuName(cell.getStringCellValue().trim());
                                kbddoSecond.setMenuLevel(k+1);
                                kbddoSecond.setCreator(adminUserRespDTO.getNickname());
 
                                int rs = knowledgeBaseDictMapper.insert(kbddoSecond);
                                if (rs > 0) {
                                    count += rs;
                                    createProblemsList.add(cell.getStringCellValue().trim());
                                    // 记录父级id 与 名称
                                    cellId = kbddoSecond.getId();
                                    cellName = kbddoSecond.getMenuName();
                                }
                            } else {
                                cellId = kbddoSecond.getId();
                                cellName = kbddoSecond.getMenuName();
                            }
                            if (k == physical - 1) {
                                // 内层循环结束,表示一行的一级菜单结束,需要指定一级菜单的父级
                                cellId = kbdictdo.getId();
                                cellName = kbdictdo.getMenuName();
                            }
                        } else { // 如果为空,需要记录父级id与名称
                            cellId = kbdictdo.getId();
                            cellName = kbdictdo.getMenuName();
                        }
                    }
                }
            } else {
                res.put(StrUtils.getRandomString(6),"导入的数据不能为空!");
                kbdir.setFailureProblems(res);
                return kbdir;
            }
            kbdir.setCreateProblems(createProblemsList);
            kbdir.setFailureProblems(res);
        }
        return kbdir;
    }

 该方法也适用于下面的Excel数据结构导入（未进行列合并单元格的数据结构）：

树形结构数据如何高效封装，通过接口返回前端展示，请看我的另一篇文章。

Java 树形结构数据如何高效返回给前端进行展示？-CSDN博客