easyexcel导出树状结构

主类

package cn.piesat.nj.farm.agricultural;

import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.BufferedReader;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;

/**
 * @author linjunwei
 * @version 2024/5/30 14:36
 */
public class test {

    public static void main(String[] args) {
        String filePath = "C:\\Users\\HTHT\\Desktop\\全国行政区中心点坐标.txt";
        String txtContent = readTxtFile(filePath);
        Dto dto = JSON.parseObject(txtContent, Dto.class);
        int i = dto.getChildren().get(0).getChildren().get(0).countAllChildren();
        System.out.println(i);
        int i2 = dto.getChildren().get(0).getChildren().get(1).countAllChildren();
        System.out.println(i2);
        System.out.println(dto.getChildren().get(1).countAllChildren());

        try (Workbook workbook = new XSSFWorkbook();
             FileOutputStream fileOut = new FileOutputStream("C:\\Users\\HTHT\\Desktop\\全国行政区中心点坐标3.xlsx")) {
            Sheet sheet = workbook.createSheet("Tree Data");

            // 递归遍历树结构数据，并写入Excel
            int rowIndex = 0;
            exportTreeDataToExcel(dto, sheet, rowIndex, 0);
            workbook.write(fileOut);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }



    private static void exportTreeDataToExcel(Dto treeData, Sheet sheet, int rowIndex, int level) {
        Row row = sheet.getRow(rowIndex);
        if (row == null){
            row = sheet.createRow(rowIndex);
        }
        Cell cell = row.createCell(level);
        cell.setCellValue(treeData.getAddvnm());

        if (treeData.getChildren() != null && !treeData.getChildren().isEmpty()) {
            int startRow = rowIndex;
            for (Dto child : treeData.getChildren()) {
                exportTreeDataToExcel(child, sheet, rowIndex, level + 1);
                rowIndex += child.countAllChildren();
            }

            int endRow = rowIndex-1;

            try{
                sheet.addMergedRegion(new CellRangeAddress(startRow, endRow, level, level));
            }catch (Exception e){

            }


        } else {
            Cell cell2 = row.createCell(++level);
            cell2.setCellValue("主人");
        }
    }


    public static String readTxtFile(String filePath) {
        StringBuilder content = new StringBuilder();
        try (BufferedReader br = new BufferedReader(new FileReader(filePath))) {
            String line;
            while ((line = br.readLine()) != null) {
                content.append(line);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return content.toString();
    }
}
dto对象

package cn.piesat.nj.farm.agricultural;

import lombok.Data;

import java.util.List;

/**
 * @author linjunwei
 * @version 2024/5/30 14:07
 */
@Data
public class Dto {

    private String addvnm;

    private String padcd;

    private String level;

    private String lgtd;

    private String lttd;

    private List<Dto> children;

    public int countAllChildren() {
        int count = 0; // Include the current node itself
        if (this.children != null) {
            for (Dto child : this.children) {
                count += child.countAllChildren();
            }
        }else {
            count = 1;
        }
        return count;
    }

}

数据，由于全部数据放不下，这里截取一部分数据

{"addvcd": "",
"addvnm": "全国",
"lgtd": null,
"lttd": null,
"padcd": "ROOT",
"init": null,
"comments": null,
"children": [
{
"addvcd": "110000",
"addvnm": "北京",
"lgtd": 116.411616,
"lttd": 40.183962,
"padcd": "000000",
"init": null,
"comments": "北京",
"children": [
{
"addvcd": "110100",
"addvnm": "市辖区",
"lgtd": 116.348169,
"lttd": 40.064724,
"padcd": "110000",
"init": null,
"comments": "北京-市辖区",
"children": [
{
"addvcd": "110101",
"addvnm": "东城区",
"lgtd": 116.410521,
"lttd": 39.911497,
"padcd": "110100",
"init": null,
"comments": "北京-市辖区-东城区",
"children": null,
"level": 3
},
{
"addvcd": "110102",
"addvnm": "西城区",
"lgtd": 116.359646,
"lttd": 39.910748,
"padcd": "110100",
"init": null,
"comments": "北京-市辖区-西城区",
"children": null,
"level": 3
},
{
"addvcd": "110105",
"addvnm": "朝阳区",
"lgtd": 116.509024,
"lttd": 39.94905,
"padcd": "110100",
"init": null,
"comments": "北京-市辖区-朝阳区",
"children": null,
"level": 3
},
{
"addvcd": "110106",
"addvnm": "丰台区",
"lgtd": 116.243918,
"lttd": 39.834659,
"padcd": "110100",
"init": null,
"comments": "北京-市辖区-丰台区",
"children": null,
"level": 3
},
{
"addvcd": "110107",
"addvnm": "石景山区",
"lgtd": 116.170175,
"lttd": 39.931996,
"padcd": "110100",
"init": null,
"comments": "北京-市辖区-石景山区",
"children": null,
"level": 3
},
{
"addvcd": "110108",
"addvnm": "海淀区",
"lgtd": 116.227851,
"lttd": 40.025564,
"padcd": "110100",
"init": null,
"comments": "北京-市辖区-海淀区",
"children": null,
"level": 3
},
{
"addvcd": "110109",
"addvnm": "门头沟区",
"lgtd": 115.78368,
"lttd": 39.994165,
"padcd": "110100",
"init": null,
"comments": "北京-市辖区-门头沟区",
"children": null,
"level": 3
},
{
"addvcd": "110111",
"addvnm": "房山区",
"lgtd": 115.849268,
"lttd": 39.719017,
"padcd": "110100",
"init": null,
"comments": "北京-市辖区-房山区",
"children": null,
"level": 3
},
{
"addvcd": "110112",
"addvnm": "通州区",
"lgtd": 116.728488,
"lttd": 39.80108,
"padcd": "110100",
"init": null,
"comments": "北京-市辖区-通州区",
"children": null,
"level": 3
},
{
"addvcd": "110113",
"addvnm": "顺义区",
"lgtd": 116.71669,
"lttd": 40.148274,
"padcd": "110100",
"init": null,
"comments": "北京-市辖区-顺义区",
"children": null,
"level": 3
},
{
"addvcd": "110114",
"addvnm": "昌平区",
"lgtd": 116.204687,
"lttd": 40.214541,
"padcd": "110100",
"init": null,
"comments": "北京-市辖区-昌平区",
"children": null,
"level": 3
},
{
"addvcd": "110115",
"addvnm": "大兴区",
"lgtd": 116.411932,
"lttd": 39.646566,
"padcd": "110100",
"init": null,
"comments": "北京-市辖区-大兴区",
"children": null,
"level": 3
},
{
"addvcd": "110116",
"addvnm": "怀柔区",
"lgtd": 116.580706,
"lttd": 40.628786,
"padcd": "110100",
"init": null,
"comments": "北京-市辖区-怀柔区",
"children": null,
"level": 3
},
{
"addvcd": "110117",
"addvnm": "平谷区",
"lgtd": 117.137882,
"lttd": 40.2084,
"padcd": "110100",
"init": null,
"comments": "北京-市辖区-平谷区",
"children": null,
"level": 3
}
],
"level": 2
},
{
"addvcd": "110200",
"addvnm": "郊区县",
"lgtd": 116.597258,
"lttd": 40.531034,
"padcd": "110000",
"init": null,
"comments": "北京-郊区县",
"children": [
{
"addvcd": "110228",
"addvnm": "密云县",
"lgtd": 116.988102,
"lttd": 40.524896,
"padcd": "110200",
"init": null,
"comments": "北京-郊区县-密云县",
"children": null,
"level": 3
},
{
"addvcd": "110229",
"addvnm": "延庆县",
"lgtd": 116.158684,
"lttd": 40.537922,
"padcd": "110200",
"init": null,
"comments": "北京-郊区县-延庆县",
"children": null,
"level": 3
}
],
"level": 2
}
],
"level": 1
}}