将树形的Excel数据导入到数据库中，包含层级一，二，三，1,1.1，1.2等分层数据导入

将此类数据导入到数据库中并存为树结构

思路：

（1）先将Excel数据读取成每条，并用map存储，并将其对象存入到数据库中

String filePath = "C:/Users/Administrator/Desktop/概算导入模版.xlsx";
        XSSFWorkbook wookbook = new XSSFWorkbook(new FileInputStream(filePath));
        XSSFSheet sheet = wookbook.getSheet("Sheet1");
        //获取到Excel文件中的所有行数
        int rows = sheet.getPhysicalNumberOfRows();
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        for (int i = 3; i < rows; i++) {//i=3代表从第三行开始读取数据
            // 读取左上端单元格
            XSSFRow row = sheet.getRow(i);
            // 行不为空
            if (row != null) {
                Map<String, Object> map = new HashMap<String, Object>();
                //获取到Excel文件中的所有的列
                int cells = row.getPhysicalNumberOfCells();
                //排序编号
                XSSFCell a = row.getCell(0);//第一列数据
                //概算名称
                XSSFCell b = row.getCell(1);//第二列数据
                //概算金额
                XSSFCell c = row.getCell(2);//第三列数据
                map.put("a", a);
                map.put("b", b);
                map.put("c", c);
                list.add(map);
            }
        }
//并将每条数据遍历赋值给对象，批量存入到数据库中
List<ProjectLeader> strings = new ArrayList<>();
        list.forEach(k -> {
            ProjectLeader leader = new ProjectLeader();
            leader.setCollectMoney(k.get("a").toString());
            leader.setEarlierStage(k.get("b").toString());
            leader.setEngineer(k.get("c").toString());
            leader.setInviteTenders("0");
            strings.add(leader);
        });
        projectLeaderService.saveBatch(strings);

（2）将存入的数据读取出来，并对列表的排序编号进行层级区分，设置父类id和子类的层级关系，并更新到当前数据库中。

List<ProjectLeader> leaderList = new ArrayList<>();
        projectLeaderService.list().forEach(s -> {
            if (s.getCollectMoney().contains("总概算")) {//根据排序编号来区分，第一个默认为父节点，parentId设为0
                leaderList.add(s);
            }
            if (s.getCollectMoney().matches("[\u4E00-\u9FA5]+")) {//判断是否为中文，如果是中文为子节点，将parentId设为父对象的id
                Object[] objects = leaderList.stream().filter(k -> k.getCollectMoney().contains("总概算")).map(ProjectLeader::getId).toArray();
                s.setInviteTenders(String.valueOf(objects[0]));
                leaderList.add(s);
                //如果为正整数，则为孙子节点，将parentId设为存入新的list中倒序找到的第一个中文的id
            } else if (s.getCollectMoney().matches("^[0-9]*[1-9][0-9]*$")) {
                Object[] objects = leaderList.stream().sorted(Comparator.comparing(ProjectLeader::getCollectMoney).reversed()).filter(t ->
                        t.getCollectMoney().matches("[\u4E00-\u9FA5]+")).limit(1).map(ProjectLeader::getId).toArray();
                s.setInviteTenders(String.valueOf(objects[0]));
                leaderList.add(s);
                //如果为小数，例如1.1,1.2...则为最下级数据，取字符为“.”之前的数字，并在数据中找到这个id，存入到当前的parentId中
            } else if (s.getCollectMoney().matches("-?[0-9]+.?[0-9]+")) {
                String substring = s.getCollectMoney().substring(0, s.getCollectMoney().indexOf("."));
                Object[] array = leaderList.stream().filter(a -> a.getCollectMoney().equals(substring))
                        .map(ProjectLeader::getId).toArray();
                s.setInviteTenders(String.valueOf(array[0]));
                leaderList.add(s);
            }
        });
projectLeaderService.saveOrUpdateBatch(leaderList);

 （3）存入后的数据，如下图所示

"data": [
    {
      "id": "1509052386180829186",
      "createBy": null,
      "createDept": null,
      "createTime": "2022-03-30 14:18:20",
      "updateBy": null,
      "updateTime": "2022-03-30 14:18:20",
      "status": 1,
      "isDeleted": 0,
      "earlierStage": "建设项目总投资(一)+(二)+(三)+(四)",
      "inviteTenders": "0",
      "levy": null,
      "engineer": "0.0",
      "collectMoney": "总概算 ",
      "packageCaseLed": null,
      "manageId": null,
      "childList": [
        {
          "id": "1509052386180829187",
          "createBy": null,
          "createDept": null,
          "createTime": "2022-03-30 14:18:20",
          "updateBy": null,
          "updateTime": "2022-03-30 14:18:20",
          "status": 1,
          "isDeleted": 0,
          "earlierStage": "工程费用",
          "inviteTenders": "1509052386180829186",
          "levy": null,
          "engineer": "43553.0",
          "collectMoney": "一",
          "packageCaseLed": null,
          "manageId": null,
          "childList": []
        },
        {
          "id": "1509052386180829188",
          "createBy": null,
          "createDept": null,
          "createTime": "2022-03-30 14:18:20",
          "updateBy": null,
          "updateTime": "2022-03-30 14:18:20",
          "status": 1,
          "isDeleted": 0,
          "earlierStage": "工程建设其他费用",
          "inviteTenders": "1509052386180829186",
          "levy": null,
          "engineer": "0.0",
          "collectMoney": "二",
          "packageCaseLed": null,
          "manageId": null,
          "childList": [
            {
              "id": "1509052386180829189",
              "createBy": null,
              "createDept": null,
              "createTime": "2022-03-30 14:18:20",
              "updateBy": null,
              "updateTime": "2022-03-30 14:18:20",
              "status": 1,
              "isDeleted": 0,
              "earlierStage": "建设用地费",
              "inviteTenders": "1509052386180829188",
              "levy": null,
              "engineer": "0.0",
              "collectMoney": "1",
              "packageCaseLed": null,
              "manageId": null,
              "childList": [
                {
                  "id": "1509052386180829190",
                  "createBy": null,
                  "createDept": null,
                  "createTime": "2022-03-30 14:18:20",
                  "updateBy": null,
                  "updateTime": "2022-03-30 14:18:20",
                  "status": 1,
                  "isDeleted": 0,
                  "earlierStage": "征收补偿费用",
                  "inviteTenders": "1509052386180829189",
                  "levy": null,
                  "engineer": "0.0",
                  "collectMoney": "1.1",
                  "packageCaseLed": null,
                  "manageId": null,
                  "childList": []
                },