树形结构导出excel表格

树形结构导出excel表格

原本是想做成这样

但是没学会，最后做成这样

直接看代码

public String excelDowmload(DormCheckStatParamDto param)  {
        String resultUrl = null;
        param.setSearchAll(SEARCH_ALL_NO);
        try {
            DormCheckResultDto dormCheckResultDto = CheckRecordTime(param);//这里就是要导出的结构集，我这里的结果集是个树形结构
            resultUrl = exportTreeStructureWithDataToExcel(dormCheckResultDto);
            System.out.println("文件已上传，访问链接: " + resultUrl);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (DataException de) {
            LOG.info("打印日志：{}",JSON.toJSONString(de));
        }
        return resultUrl;
    }

public  String exportTreeStructureWithDataToExcel(DormCheckResultDto root) throws IOException {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("宿舍检查结果树形结构");
        // 写入表头
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("部门名称");
        headerRow.createCell(1).setCellValue("类型");
        headerRow.createCell(2).setCellValue("在寝人数");
        headerRow.createCell(3).setCellValue("晚归人数");
        headerRow.createCell(4).setCellValue("请假外宿人数");
        headerRow.createCell(5).setCellValue("缺寝人数");
        headerRow.createCell(6).setCellValue("未查寝人数");
        headerRow.createCell(7).setCellValue("离寝人数");
        headerRow.createCell(8).setCellValue("滞留人数");
        headerRow.createCell(9).setCellValue("请假在寝人数");
        exportNodeWithData(sheet, root, 1,0); // 开始导出，根节点从第二行开始，第一行为表头
        try {
            String uuid = CoreUtils.getUUID();
            File tmpFile = File.createTempFile(uuid, ".xlsx");

            try (OutputStream outputStream = new FileOutputStream(tmpFile)) {
                workbook.write(outputStream);
                outputStream.flush();
            }
            try (FileInputStream inputStream = new FileInputStream(tmpFile)) {
                long fileSize = tmpFile.length();
                //这里我是需要上传到公司服务器，也可以做成到客户端下载
                String resultUrl = fileClient.put(inputStream, uuid + ".xlsx", fileSize, "dorm", null, null);
                if (CoreUtils.isNull(resultUrl)) {
                    throw new DataException("上传文件有误");
                }
                return resultUrl; // 返回上传后的文件URL
            }
        } finally {
            workbook.close(); // 确保工作簿资源被释放
        }
    }

private static int recursionCount =1;//这里定义一个全局变量是为了定位数据导出到excel的行数，这是最简单的方法，也可以通过在递归中返回rowIndex定位行数
    private  void exportNodeWithData(Sheet sheet, DormCheckResultDto node, int rowIndex,int spacing) {
        recursionCount++;
        Row row = sheet.createRow(rowIndex);
        Cell cell = row.createCell(0);
        cell.setCellValue(node.getDeptName());
        cell.setCellStyle(createCellStyle(sheet.getWorkbook(), spacing)); // 根据需要调整缩进
        //这里根据缩进增加了一个类型
        if (spacing == 0) {
            row.createCell(1).setCellValue("校区");
        }else if (spacing == 1) {
            row.createCell(1).setCellValue("院系");
        }else if (spacing == 2) {
            row.createCell(1).setCellValue("专业");
        }else if (spacing == 3) {
            row.createCell(1).setCellValue("班级");
        }
        //这里记得做判断
        row.createCell(2).setCellValue(node.getDormCheckResultIn());
        row.createCell(3).setCellValue(node.getDormCheckResultLate());
        row.createCell(4).setCellValue(node.getDormCheckResultApplyLeave());
        row.createCell(5).setCellValue(node.getDormCheckResultAbsent());
        row.createCell(6).setCellValue(node.getDormCheckResultInit());
        row.createCell(7).setCellValue(node.getDormCheckResultLeft());
        row.createCell(8).setCellValue(node.getDormCheckResultStay());
        row.createCell(9).setCellValue(node.getDormCheckResultApplyStay());
        // 递归处理子节点
        if (node.getDormCheckResultInDtos() != null) {
            spacing++;
            for (int i = 0; i < node.getDormCheckResultInDtos().size(); i++) {
                exportNodeWithData(sheet, node.getDormCheckResultInDtos().get(i), recursionCount,spacing); // 调整下一行的索引
            }
        }
    }

private  CellStyle createCellStyle(Workbook workbook, int indentLevel) {
        CellStyle style = workbook.createCellStyle();
        style.setIndention((short) (indentLevel * 4)); // 设置缩进，自己选择长度
        return style;
    }