【JAVA】读取excel导入数据库，形成树状结构

最近需要导入一个excel表格，存到数据库并以树状结构读取出来
下面两张图片是需要导入的excel

    @Transactional(rollbackFor = Exception.class)
    public String import(MultipartFile file, Integer projectId) throws Exception {
        //poi解析excel
        InputStream inputStream = file.getInputStream();
        // 读取工作薄
        Workbook workbook = new XSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0);
        //定义一个cellId,此为每一次循环前一列的id
        long cellId = 0;
        //根据项目id查询项目名称
        ProjectManager one = projectManagerService.lambdaQuery().select(ProjectManager::getProjectName).eq(ProjectManager::getId, projectId).one();
        int rows = sheet.getPhysicalNumberOfRows();
        if (rows > 1) {
            //按照行进行循环，读取当前行的列
            for (int k = 1; k < rows; k++) {
                // 读取行
                Row row = sheet.getRow(k);
                //查询当前行有多少列
                int physical = sheet.getRow(k).getPhysicalNumberOfCells();
                
                if (row != null && physical > 3) {
                    //获取第一列的单元格
                    Cell cell1 = row.getCell(0);
                    //判断单元格是否为空
                    if (!(cell1 == null || "".equals(cell1.toString().trim()))) {
                        //根据单元格的数据查询数据库是否存在记录
                        IndexInfo one1 = indexInfoService.lambdaQuery().select(IndexInfo::getIndexPname, IndexInfo::getId)
                                .eq(IndexInfo::getIndexPname, cell1.getStringCellValue()).eq(IndexInfo::getProjectId, projectId).one();
                        //数据库不存在，添加记录
                        if (one1 == null) {
                            IndexInfo indexInfo = new IndexInfo();
                            indexInfo.setIndexPid(0L);
                            indexInfo.setIndexPname(cell1.getStringCellValue());
                            indexInfo.setProjectName(one.getProjectName());
                            indexInfo.setProjectId(projectId);
                            indexInfo.setIndexClass(2);
                            boolean save = indexInfoService.save(indexInfo);
                            if (save) {
                                //添加成功，将添加的id作为父id
                                cellId = indexInfo.getId();
                            }
                            //数据库存在记录，将这条记录的id作为父id
                        } else {
                            cellId = one1.getId();
                        }
                    }
                }
                for (int j = 1; j < physical - 2; j++) {
                    //取单元格
                    Cell cell = row.getCell(j);
                    //判断单元格是否为空
                    if (!(cell == null || "".equals(cell.toString().trim()))) {
                        //查询数据库有无此记录
                        IndexInfo one1 = indexInfoService.lambdaQuery().select(IndexInfo::getIndexPname, IndexInfo::getId)
                                .eq(IndexInfo::getIndexPname, cell.getStringCellValue()).eq(IndexInfo::getProjectId, projectId).one();
                        if (one1 == null) {
                        //这里保存自己需要的信息
                            IndexInfo indexInfo = new IndexInfo();
                            indexInfo.setIndexPid(cellId);
                            indexInfo.setIndexPname(cell.getStringCellValue());
                            indexInfo.setProjectName(one.getProjectName());
                            indexInfo.setProjectId(projectId);
                            indexInfo.setIndexClass(2);
                            //最后两列为描述和分数
                            if (j == physical - 3) {
                                int score = 0;
                                String description = null;
                                //循环最后两列单元格
                                for (int i = 0; i < 2; i++) {
                                    //取单元格的值
                                    Cell cell1 = row.getCell(j + i + 1);
                                    //判断单元格是否为空
                                    if (!(cell == null || "".equals(cell.toString().trim()))) {
                                        try {
                                            //因为分数是int类型，如果报错就是取了分数单元格的值
                                            description = cell1.getStringCellValue();
                                        } catch (Exception e) {
                                            score = (int) cell1.getNumericCellValue();
                                        }
                                        indexInfo.setIndexScore(score);
                                        indexInfo.setIndexDescription(description);
                                    }
                                }
                            }
                            boolean save = indexInfoService.save(indexInfo);
                            if (save) {
                                cellId = indexInfo.getId();
                            }
                        } else {
                            cellId = one1.getId();
                        }
                    }
                }
            }
            return "文件导入成功";
        } else {
            return "导入失败";
        }
    }

还有一个这种的表格，多读一列就可以了

@Transactional(rollbackFor = Exception.class)
    public String import(MultipartFile file, Integer projectId) throws Exception {
        //poi解析excel
        InputStream inputStream = file.getInputStream();
        // 读取工作薄
        Workbook workbook = new XSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0);
        //定义一个cellId,此为每一次循环前一列的id
        long cellId = 0;
        //根据项目id查询项目名称
        ProjectManager one = projectManagerService.lambdaQuery().select(ProjectManager::getProjectName).eq(ProjectManager::getId, projectId).one();
        int rows = sheet.getPhysicalNumberOfRows();
        if (rows > 1) {
            //按照行进行循环，读取当前行的列
            for (int k = 1; k < rows; k++) {
                // 读取行
                Row row = sheet.getRow(k);
                //查询当前行有多少列
                int physical = sheet.getRow(k).getPhysicalNumberOfCells();
             
                if (row != null && physical > 3) {
                    //获取第一列的单元格
                    Cell cell = row.getCell(0);
                    //判断单元格是否为空
                    if (!(cell == null || "".equals(cell.toString().trim()))) {
                        //根据单元格的数据查询数据库是否存在记录
                        IndexInfo one1 = indexInfoService.lambdaQuery().select(IndexInfo::getIndexPname, IndexInfo::getId)
                                .eq(IndexInfo::getIndexPname, cell.getStringCellValue()).eq(IndexInfo::getProjectId, projectId).one();
                        //数据库不存在，添加记录
                        if (one1 == null) {
                            IndexInfo indexInfo = new IndexInfo();
                            indexInfo.setIndexPid(0L);
                            indexInfo.setIndexPname(cell.getStringCellValue());
                            indexInfo.setProjectName(one.getProjectName());
                            indexInfo.setProjectId(projectId);
                            indexInfo.setIndexClass(2);
                            indexInfo.setIndexWeight(row.getCell(1).getNumericCellValue());
                            boolean save = indexInfoService.save(indexInfo);
                            if (save) {
                                //添加成功，将添加的id作为父id
                                cellId = indexInfo.getId();
                            }
                            //数据库存在记录，将这条记录的id作为父id
                        } else {
                            cellId = one1.getId();
                        }
                    }
                }
                for (int i = 2; i < physical-1; i += 2) {
                    //取单元格
                    Cell cell = row.getCell(i);
                    //判断单元格是否为空
                    if (!(cell == null || "".equals(cell.toString().trim()))) {
                        //查询数据库有无此记录
                        IndexInfo one1 = indexInfoService.lambdaQuery().select(IndexInfo::getIndexPname, IndexInfo::getId)
                                .eq(IndexInfo::getIndexPname, cell.getStringCellValue()).eq(IndexInfo::getProjectId, projectId).one();
                        if (one1 == null) {
                            IndexInfo indexInfo = new IndexInfo();
                            indexInfo.setIndexPid(cellId);
                            indexInfo.setProjectName(one.getProjectName());
                            indexInfo.setProjectId(projectId);
                            indexInfo.setIndexPname(cell.getStringCellValue());
                            indexInfo.setIndexClass(2);
                            indexInfo.setIndexWeight(row.getCell(i +1).getNumericCellValue());
                            //最后两列为描述和分数
                            if (i == physical - 3) {
                                //循环最后两列单元格
                                indexInfo.setIndexDescription(row.getCell(i +2).getStringCellValue());
                            }
                            boolean save = indexInfoService.save(indexInfo);
                            if (save) {
                                cellId = indexInfo.getId();
                            }
                        } else {
                            cellId = one1.getId();
                        }
                    }
                }
            }
            return "导入成功";
        } else
            return "导入失败";

    }

读取完成保存到数据库后，形成树状结构具体步骤可以看我上一篇帖子