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

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

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

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

@Overridepublic KnowledgeBaseDictImportRespVO importKnowledgeBaseDict(MultipartFile file) throws IOException {KnowledgeBaseDictImportRespVO kbdir = KnowledgeBaseDictImportRespVO.builder().build(); // 该对象是方法返回的结果对象Map<String,String> res = new ConcurrentHashMap<>();List<String> 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解析excelInputStream 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,此为每一次循环前一列的idlong 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作为父idcellId = 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数据结构导入（未进行列合并单元格的数据结构）：