最近需要导入一个excel表格，存到数据库并以树状结构读取出来
下面两张图片是需要导入的excel
@Transactional(rollbackFor = Exception.class)public String import(MultipartFile file, Integer projectId) throws Exception {//poi解析excelInputStream inputStream = file.getInputStream();// 读取工作薄Workbook workbook = new XSSFWorkbook(inputStream);Sheet sheet = workbook.getSheetAt(0);//定义一个cellId,此为每一次循环前一列的idlong 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作为父idcellId = 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解析excelInputStream inputStream = file.getInputStream();// 读取工作薄Workbook workbook = new XSSFWorkbook(inputStream);Sheet sheet = workbook.getSheetAt(0);//定义一个cellId,此为每一次循环前一列的idlong 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作为父idcellId = 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 "导入成功";} elsereturn "导入失败";}
读取完成保存到数据库后，形成树状结构具体步骤可以看我上一篇帖子
