JAVA树型数据结构展开为excel行数据并导出示例

前言

一个统计平台建设，需要梳理统计指标。统计指标主要围绕第三平台的表和表字段进行对应，在对应的过程中，涉及到部分字段的值是字典类型，需要获取第三方平台的字典对应的字典项项数据（主要是为字典编码和字典名称）。第三方平台没有提供相关数据和直接查询接口，通过账号登陆第三方平台后，发现有字典列表和字典树按层级查询的接口。于是通过程序，将所有字典的所有层级的字典项都获取到，然后以字典为根，字典项为子节点，构建了字典树的数据结构。在此结构的基础上，将树展开转换成excel表格的行数据并导出。

一、字典数据示例

{"childNodes":[{"childNodes":[{"parentCode":"BD000000","sort":8,"key":"BD010000","value":"LY职权A"},{"parentCode":"BD000000","sort":8,"key":"BD020000","value":"玩忽职守A"},{"parentCode":"BD000000","sort":8,"key":"BD030000","value":"故意泄露GJMMA"},{"parentCode":"BD000000","sort":8,"key":"BD040000","value":"过失泄露GJMMA"},{"parentCode":"BD000000","sort":8,"key":"BD050000","value":"徇私WFA"},{"parentCode":"BD000000","childNodes":[{"parentCode":"ABCD000","sort":8,"key":"ABCD0100","value":"MSWF裁判A"},{"parentCode":"ABCD0000","sort":8,"key":"ABCD0200","value":"行政WF裁判A"}],"sort":1,"key":"ABCD0000","value":"民事、行政WF裁判A"},{"parentCode":"BD000000","childNodes":[{"parentCode":"BD070000","sort":8,"key":"BD070100","value":"执行判决SZA"},{"parentCode":"BD070000","sort":8,"key":"BD070200","value":"执行裁定SZA"}],"sort":1,"key":"BD070000","value":"执行判决、裁定SZA"},{"parentCode":"BD000000","childNodes":[{"parentCode":"BD080000","sort":8,"key":"BD080100","value":"执行判决LY职权A"},{"parentCode":"BD080000","sort":8,"key":"BD080200","value":"执行裁定LY职权A"}],"sort":1,"key":"BD080000","value":"执行判决、裁定LY职权A"},{"parentCode":"BD000000","sort":8,"key":"BDBD0000","value":"WF仲裁A"},{"parentCode":"BD000000","sort":8,"key":"BD100000","value":"私放在押人员A"},{"parentCode":"BD000000","sort":8,"key":"BDEFG000","value":"SZ致使在押人员脱逃A"},{"parentCode":"BD000000","childNodes":[{"parentCode":"BD120000","sort":8,"key":"BD120100","value":"徇私舞弊减刑A"},{"parentCode":"BD120000","sort":8,"key":"BD120200","value":"徇私舞弊假释A"},{"parentCode":"BD120000","sort":8,"key":"BD120300","value":"徇私舞弊暂予监外执行A"}],"sort":1,"key":"BD120000","value":"徇私舞弊减刑、假释、暂予监外执行A"},{"parentCode":"BD000000","sort":8,"key":"BD130000","value":"徇私舞弊不移交刑事A件A"},{"parentCode":"BD000000","childNodes":[{"parentCode":"BD140000","sort":8,"key":"BD140100","value":"LY管理公司职权A"},{"parentCode":"BD140000","sort":8,"key":"BD140200","value":"LY管理ZQ职权A"}],"sort":1,"key":"BD140000","value":"LY管理公司、ZQ职权A"},{"parentCode":"BD000000","childNodes":[{"parentCode":"BD150000","sort":8,"key":"BD150100","value":"徇私舞弊不征税款A"},{"parentCode":"BD150000","sort":8,"key":"BD150200","value":"徇私舞弊少征税款A"}],"sort":1,"key":"BD150000","value":"徇私舞弊不征、少征税款A"},{"parentCode":"BD000000","childNodes":[{"parentCode":"BD160000","sort":8,"key":"BD160100","value":"徇私舞弊发售发票A"},{"parentCode":"BD160000","sort":8,"key":"BD160200","value":"徇私舞弊抵扣税款A"},{"parentCode":"BD160000","sort":8,"key":"BD160300","value":"徇私舞弊出口退税A"}],"sort":1,"key":"BD160000","value":"徇私舞弊发售发票、抵扣税款、出口退税A"},{"parentCode":"BD000000","sort":8,"key":"BD170000","value":"违法提供出口退税凭证A"},{"parentCode":"BD000000","childNodes":[{"parentCode":"BD180000","sort":8,"key":"BD180100","value":"GJ机关工作人员签订合同SZ被骗A"},{"parentCode":"BD180000","sort":8,"key":"BD180200","value":"GJ机关工作人员履行合同SZ被骗A"}],"sort":1,"key":"BD180000","value":"GJ机关工作人员签订、履行合同SZ被骗A"},{"parentCode":"BD000000","sort":8,"key":"BD190000","value":"违法发放林木采伐许可证A"},{"parentCode":"BD000000","sort":8,"key":"BD200000","value":"环境监管SZA"},{"parentCode":"BD000000","sort":8,"key":"BD210000","value":"食品监管渎职A"},{"parentCode":"BD000000","sort":8,"key":"BD220000","value":"传染病防治SZA"},{"parentCode":"BD000000","sort":8,"key":"BD240000","value":"非法低价出让国有土地使用权A"},{"parentCode":"BD000000","sort":8,"key":"BD250000","value":"放纵走私A"},{"parentCode":"BD000000","sort":8,"key":"BD260000","value":"商检徇私舞弊A"},{"parentCode":"BD000000","sort":8,"key":"BD270000","value":"商检SZA"},{"parentCode":"BD000000","sort":8,"key":"BD280000","value":"动植物检疫徇私舞弊A"},{"parentCode":"BD000000","sort":8,"key":"BD290000","value":"动植物检疫SZA"},{"parentCode":"BD000000","sort":8,"key":"BD300000","value":"放纵制售伪劣商品犯罪行为A"},{"parentCode":"BD000000","sort":8,"key":"BD310000","value":"办理偷越国（边）境人员出入境证件A"},{"parentCode":"BD000000","sort":8,"key":"BD320000","value":"放行偷越国（边）境人员A"},{"parentCode":"BD000000","childNodes":[{"parentCode":"BD330000","sort":8,"key":"BD330100","value":"不解救被拐卖妇女A"},{"parentCode":"BD330000","sort":8,"key":"BD330200","value":"不解救被绑架妇女A"},{"parentCode":"BD330000","sort":8,"key":"BD330300","value":"不解救被拐卖儿童A"},{"parentCode":"BD330000","sort":8,"key":"BD330400","value":"不解救被绑架儿童A"}],"sort":1,"key":"BD330000","value":"不解救被拐卖、绑架妇女、儿童A"},{"parentCode":"BD000000","childNodes":[{"parentCode":"BD340000","sort":8,"key":"BD340100","value":"阻碍解救被拐卖妇女A"},{"parentCode":"BD340000","sort":8,"key":"BD340200","value":"阻碍解救被绑架妇女A"},{"parentCode":"BD340000","sort":8,"key":"BD340300","value":"阻碍解救被拐卖儿童A"},{"parentCode":"BD340000","sort":8,"key":"BD340400","value":"阻碍解救被绑架儿童A"}],"sort":1,"key":"BD340000","value":"阻碍解救被拐卖、绑架妇女、儿童A"},{"parentCode":"BD000000","sort":8,"key":"BD350000","value":"帮助犯罪分子逃避处罚A"},{"parentCode":"BD000000","childNodes":[{"parentCode":"BD360000","sort":8,"key":"BD360100","value":"招收公务员徇私舞弊A"},{"parentCode":"BD360000","sort":8,"key":"BD360200","value":"招收学生徇私舞弊A"}],"sort":1,"key":"BD360000","value":"招收公务员、学生徇私舞弊A"},{"parentCode":"BD000000","childNodes":[{"parentCode":"BD370000","sort":8,"key":"BD370100","value":"SZ造成珍贵文物损毁A"},{"parentCode":"BD370000","sort":8,"key":"BD370200","value":"SZ造成珍贵文物流失A"}],"sort":1,"key":"BD370000","value":"SZ造成珍贵文物损毁、流失A"},{"parentCode":"BD000000","sort":8,"key":"BD400000","value":"非法批准征收、征用、占用土地A"},{"parentCode":"BD000000","sort":8,"key":"BD410000","value":"食品、药品监管渎职A"}],"sort":1,"key":"BD000000","value":"渎职A"},{"childNodes":[{"parentCode":"10000000","sort":8,"key":"10010000","value":"战时违抗命令A"},{"parentCode":"10000000","childNodes":[{"parentCode":"10020000","sort":8,"key":"10020100","value":"隐瞒军情A"},{"parentCode":"10020000","sort":8,"key":"10020200","value":"谎报军情A"}],"sort":1,"key":"10020000","value":"隐瞒、谎报军情A"},{"parentCode":"10000000","childNodes":[{"parentCode":"10030000","sort":8,"key":"10030100","value":"拒传军令A"},{"parentCode":"10030000","sort":8,"key":"10030200","value":"假传军令A"}],"sort":1,"key":"10030000","value":"拒传、假传军令A"},{"parentCode":"10000000","sort":8,"key":"10040000","value":"投降A"},{"parentCode":"10000000","sort":8,"key":"10050000","value":"战时临阵脱逃A"},{"parentCode":"10000000","childNodes":[{"parentCode":"10060000","sort":8,"key":"10060100","value":"擅离JS职守A"},{"parentCode":"10060000","sort":8,"key":"10060200","value":"玩忽JS职守A"}],"sort":1,"key":"10060000","value":"擅离、玩忽JS职守A"},{"parentCode":"10000000","sort":8,"key":"10070000","value":"阻碍执行JS职务A"},{"parentCode":"10000000","sort":8,"key":"10080000","value":"指使部属违反职责A"},{"parentCode":"10000000","sort":8,"key":"10BD0000","value":"违令作战消极A"},{"parentCode":"10000000","sort":8,"key":"10100000","value":"拒不救援友邻部队A"},{"parentCode":"10000000","sort":8,"key":"10EFG000","value":"JR叛逃A"},{"parentCode":"10000000","sort":8,"key":"10120000","value":"非法获取JSMMA"},{"parentCode":"10000000","childNodes":[{"parentCode":"10130000","sort":8,"key":"10130100","value":"为境外窃取JSMMA"},{"parentCode":"10130000","sort":8,"key":"10130200","value":"为境外刺探JSMMA"},{"parentCode":"10130000","sort":8,"key":"10130300","value":"为境外收买JSMMA"},{"parentCode":"10130000","sort":8,"key":"10130400","value":"为境外非法提供JSMMA"}],"sort":1,"key":"10130000","value":"为境外窃取、刺探、收买、非法提供JSMMA"},{"parentCode":"10000000","sort":8,"key":"10140000","value":"故意泄露JSMMA"},{"parentCode":"10000000","sort":8,"key":"10150000","value":"过失泄露JSMMA"},{"parentCode":"10000000","sort":8,"key":"10160000","value":"战时造谣惑众A"},{"parentCode":"10000000","sort":8,"key":"10170000","value":"战时自伤A"},{"parentCode":"10000000","sort":8,"key":"10180000","value":"逃离部队A"},{"parentCode":"10000000","sort":8,"key":"10190000","value":"武器装备肇事A"},{"parentCode":"10000000","sort":8,"key":"10200000","value":"擅自改变武器装备编配用途A"},{"parentCode":"10000000","childNodes":[{"parentCode":"10210000","sort":8,"key":"10210100","value":"盗窃武器装备A"},{"parentCode":"10210000","sort":8,"key":"10210200","value":"盗窃军用物资A"},{"parentCode":"10210000","sort":8,"key":"10210300","value":"抢夺武器装备A"},{"parentCode":"10210000","sort":8,"key":"10210400","value":"抢夺军用物资A"}],"sort":1,"key":"10210000","value":"盗窃、抢夺武器装备、军用物资A"},{"parentCode":"10000000","childNodes":[{"parentCode":"10220000","sort":8,"key":"10220100","value":"非法出卖武器装备A"},{"parentCode":"10220000","sort":8,"key":"10220200","value":"非法转让武器装备A"}],"sort":1,"key":"10220000","value":"非法出卖、转让武器装备A"},{"parentCode":"10000000","sort":8,"key":"10230000","value":"遗弃武器装备A"},{"parentCode":"10000000","sort":8,"key":"10240000","value":"遗失武器装备A"},{"parentCode":"10000000","childNodes":[{"parentCode":"10250000","sort":8,"key":"10250100","value":"擅自出卖房地产A"},{"parentCode":"10250000","sort":8,"key":"10250200","value":"擅自转让军队房地产A"}],"sort":1,"key":"10250000","value":"擅自出卖、转让军队房地产A"},{"parentCode":"10000000","sort":8,"key":"10260000","value":"虐待部属A"},{"parentCode":"10000000","sort":8,"key":"10270000","value":"遗弃伤病JRA"},{"parentCode":"10000000","sort":8,"key":"10280000","value":"战时拒不救治伤病JRA"},{"parentCode":"10000000","childNodes":[{"parentCode":"10290000","sort":8,"key":"10290100","value":"战时残害居民A"},{"parentCode":"10290000","sort":8,"key":"10290200","value":"战时掠夺居民财物A"}],"sort":1,"key":"10290000","value":"战时残害居民、掠夺居民财物A"},{"parentCode":"10000000","sort":8,"key":"10300000","value":"私放俘虏A"},{"parentCode":"10000000","sort":8,"key":"10310000","value":"虐待俘虏A"}],"sort":1,"key":"10000000","value":"JR违反ZZA"},{"sort":8,"key":"EFG00000","value":"XSA件"}],"sort":1,"value":"%25E5%2588%2591%25E4%25B8%2593%25E6%25A1%2588%25E4%25BB%25B6%25E7%25B1%25BB%25E5%2588%25AB"
}

二、解析逻辑

1.树节点转EXCEL行

参照字典JSON结构，以每个数据对象为一个节点，构建一棵字典树。字典树转excel行的转换逻辑为：每个节点单独成行；子节点的excel数据行需要包含对应的上级节点信息。

2.算法选择（DFS）

节点转行逻辑大体上符合树的深度优先算法逻辑（主要思路是从图中未访问的顶点 V 一开始，沿着一条路走到底，然后从这条路尽头的节点回到上一个节点，然后从另一条路走到底…，重复这个过程，直到所有的顶点都完成，它的特点是不撞南墙不回头，先走一条路，再换一条路继续走）。从系统目前可见的字典深度来看，最多只有6级，不用考虑栈溢出的问题，采用递归实现相对简单。

三、完整示例

1.辅助工具类

树形节点数据类：

package com.jzdata.demo.model;import lombok.Data;
import lombok.EqualsAndHashCode;import java.util.List;/*** 父子关系节点数据** @Description* @Author misterbig* @Date 2023/11/30*/
@Data
@EqualsAndHashCode(callSuper = true)
public class NodeModel extends CellModel {public NodeModel() {}public NodeModel(String parentCode) {this.parentCode = parentCode;}public NodeModel(String key, String value, String parentCode) {super(key, value);this.parentCode = parentCode;}public NodeModel(Integer sort, String key, String value, String parentCode) {super(sort, key, value);this.parentCode = parentCode;}/*** 父节代码* <strong>当前字典项所在节点对应的父节点代码</strong>*/private String parentCode;/*** 子节点*/private List<NodeModel> childNodes;}

EXCEL单元格数据类：

package com.jzdata.demo.model;import lombok.Data;/*** @Description* @Author misterbig* @Date 2023/11/24*/
@Data
public class CellModel {public CellModel() {}public CellModel(String value) {this.value = value;}public CellModel(String key, String value) {this.key = key;this.value = value;}public CellModel(Integer sort, String key, String value) {this.sort = sort;this.key = key;this.value = value;}/*** 排序号*/private Integer sort;/*** 标识=attrname*/private String key;/*** 描述=xsm*/private String value;}

EXCEL表格数据类：

package com.jzdata.demo.model;import lombok.Data;import java.util.List;/*** @Description* @Author misterbig* @Date 2023/11/24*/
@Data
public class ExcelModel {public ExcelModel() {}public ExcelModel(String sheetName) {this.sheetName = sheetName;}public ExcelModel(String sheetName, Integer column) {this.sheetName = sheetName;this.column = column;}public ExcelModel(List<CellModel> heads, List<List<CellModel>> rows) {this.heads = heads;this.rows = rows;}public ExcelModel(String sheetName, Integer column, List<List<CellModel>> rows) {this.sheetName = sheetName;this.column = column;this.rows = rows;}public ExcelModel(String sheetName, List<CellModel> heads, List<List<CellModel>> rows) {this.sheetName = sheetName;this.heads = heads;this.rows = rows;}public ExcelModel(String sheetName, Integer column, List<CellModel> heads, List<List<CellModel>> rows) {this.sheetName = sheetName;this.column = column;this.heads = heads;this.rows = rows;}/*** 工作表名称*/private String sheetName;/*** excel列的宽度*/private Integer column;/*** excel表头数据，规则：* <ul>*     <li>k=attrname</li>*     <li>v=xsm</li>* </ul>*/private List<CellModel> heads;/*** excel行数据，规则：* <ul>*     <li>k=attrname</li>*     <li>v=列对应的值</li>* </ul>*/private List<List<CellModel>> rows;
}

EXCEL导出辅助类：

package com.jzdata.demo.service;import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.jzdata.demo.model.CellModel;
import com.jzdata.demo.model.ExcelModel;
import org.apache.commons.compress.utils.Lists;
import org.springframework.stereotype.Component;import java.io.File;
import java.io.IOException;
import java.util.List;/*** @Description* @Author misterbig* @Date 2023/11/23*/
@Component
public class ExcelHandler {/*** 创建excel文件，动态写入文档解析后的内容** @param models* @param excelFileName* @throws IOException*/public static void dynamicWrite(List<ExcelModel> models, String excelFileName) throws IOException {File file = createExcel(excelFileName);// 这里 指定文件try (ExcelWriter excelWriter = EasyExcel.write(file.getAbsolutePath()).build()) {// 去调用写入,这里我调用了五次，实际使用时根据数据库分页的总的页数来。这里最终会写到5个sheet里面for (int i = 0, size = models.size(); i < size; i++) {//获取sheet数据ExcelModel sheet = models.get(i);List<List<String>> heads = Lists.newArrayList();for (CellModel headModel : sheet.getHeads()) {List<String> head = Lists.newArrayList();head.add(headModel.getValue());heads.add(head);}List<List<String>> rows = Lists.newArrayList();for (List<CellModel> originalRows : sheet.getRows()) {List<String> row = Lists.newArrayList();for (CellModel originalRow : originalRows) {row.add(originalRow.getValue());}rows.add(row);}// 每次都要创建writeSheet 这里注意必须指定sheetNo 而且sheetName必须不一样。这里注意DemoData.class 可以每次都变，我这里为了方便 所以用的同一个class// 实际上可以一直变WriteSheet writeSheet = EasyExcel.writerSheet(i, sheet.getSheetName()).head(heads).build();// 分页去数据库查询数据 这里可以去数据库查询每一页的数据excelWriter.write(rows, writeSheet);}}}/*** 不写表头信息** @param models* @param excelFileName* @throws IOException*/public static void dynamicWriteNoHead(List<ExcelModel> models, String excelFileName) throws IOException {File file = createExcel(excelFileName);// 这里 指定文件try (ExcelWriter excelWriter = EasyExcel.write(file.getAbsolutePath()).build()) {// 去调用写入,这里我调用了五次，实际使用时根据数据库分页的总的页数来。这里最终会写到5个sheet里面for (int i = 0, size = models.size(); i < size; i++) {//获取sheet数据ExcelModel sheet = models.get(i);List<List<String>> rows = Lists.newArrayList();for (List<CellModel> originalRows : sheet.getRows()) {List<String> row = Lists.newArrayList();for (CellModel originalRow : originalRows) {row.add(originalRow.getValue());}rows.add(row);}// 每次都要创建writeSheet 这里注意必须指定sheetNo 而且sheetName必须不一样。这里注意DemoData.class 可以每次都变，我这里为了方便 所以用的同一个class// 实际上可以一直变WriteSheet writeSheet = EasyExcel.writerSheet(i, sheet.getSheetName()).build();// 分页去数据库查询数据 这里可以去数据库查询每一页的数据excelWriter.write(rows, writeSheet);}}}/*** 创建excel文件** @param fileName* @return* @throws IOException*/private static File createExcel(String fileName) throws IOException {File file = new File(fileName + System.currentTimeMillis() + ".xlsx");if (file.exists()) {file.delete();}file.createNewFile();return file;}}

2.测试工具类

package com.jzdata.demo.test;import cn.hutool.json.JSONUtil;
import com.google.common.collect.Lists;
import com.jzdata.demo.model.CellModel;
import com.jzdata.demo.model.ExcelModel;
import com.jzdata.demo.model.NodeModel;
import com.jzdata.demo.service.ExcelHandler;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;import java.io.IOException;
import java.util.ArrayList;
import java.util.List;/*** @Description* @Author misterbig* @Date 2023/12/5*/
@Slf4j
public class TextDictItemTree {static String json_str = "{\"childNodes\":[{\"childNodes\":[{\"parentCode\":\"BD000000\",\"sort\":8,\"key\":\"BD010000\",\"value\":\"LY职权A\"},{\"parentCode\":\"BD000000\",\"sort\":8,\"key\":\"BD020000\",\"value\":\"玩忽职守A\"},{\"parentCode\":\"BD000000\",\"sort\":8,\"key\":\"BD030000\",\"value\":\"故意泄露GJMMA\"},{\"parentCode\":\"BD000000\",\"sort\":8,\"key\":\"BD040000\",\"value\":\"过失泄露GJMMA\"},{\"parentCode\":\"BD000000\",\"sort\":8,\"key\":\"BD050000\",\"value\":\"徇私WFA\"},{\"parentCode\":\"BD000000\",\"childNodes\":[{\"parentCode\":\"ABCD000\",\"sort\":8,\"key\":\"ABCD0100\",\"value\":\"MSWF裁判A\"},{\"parentCode\":\"ABCD0000\",\"sort\":8,\"key\":\"ABCD0200\",\"value\":\"行政WF裁判A\"}],\"sort\":1,\"key\":\"ABCD0000\",\"value\":\"民事、行政WF裁判A\"},{\"parentCode\":\"BD000000\",\"childNodes\":[{\"parentCode\":\"BD070000\",\"sort\":8,\"key\":\"BD070100\",\"value\":\"执行判决SZA\"},{\"parentCode\":\"BD070000\",\"sort\":8,\"key\":\"BD070200\",\"value\":\"执行裁定SZA\"}],\"sort\":1,\"key\":\"BD070000\",\"value\":\"执行判决、裁定SZA\"},{\"parentCode\":\"BD000000\",\"childNodes\":[{\"parentCode\":\"BD080000\",\"sort\":8,\"key\":\"BD080100\",\"value\":\"执行判决LY职权A\"},{\"parentCode\":\"BD080000\",\"sort\":8,\"key\":\"BD080200\",\"value\":\"执行裁定LY职权A\"}],\"sort\":1,\"key\":\"BD080000\",\"value\":\"执行判决、裁定LY职权A\"},{\"parentCode\":\"BD000000\",\"sort\":8,\"key\":\"BDBD0000\",\"value\":\"WF仲裁A\"},{\"parentCode\":\"BD000000\",\"sort\":8,\"key\":\"BD100000\",\"value\":\"私放在押人员A\"},{\"parentCode\":\"BD000000\",\"sort\":8,\"key\":\"BDEFG000\",\"value\":\"SZ致使在押人员脱逃A\"},{\"parentCode\":\"BD000000\",\"childNodes\":[{\"parentCode\":\"BD120000\",\"sort\":8,\"key\":\"BD120100\",\"value\":\"徇私舞弊减刑A\"},{\"parentCode\":\"BD120000\",\"sort\":8,\"key\":\"BD120200\",\"value\":\"徇私舞弊假释A\"},{\"parentCode\":\"BD120000\",\"sort\":8,\"key\":\"BD120300\",\"value\":\"徇私舞弊暂予监外执行A\"}],\"sort\":1,\"key\":\"BD120000\",\"value\":\"徇私舞弊减刑、假释、暂予监外执行A\"},{\"parentCode\":\"BD000000\",\"sort\":8,\"key\":\"BD130000\",\"value\":\"徇私舞弊不移交刑事A件A\"},{\"parentCode\":\"BD000000\",\"childNodes\":[{\"parentCode\":\"BD140000\",\"sort\":8,\"key\":\"BD140100\",\"value\":\"LY管理公司职权A\"},{\"parentCode\":\"BD140000\",\"sort\":8,\"key\":\"BD140200\",\"value\":\"LY管理ZQ职权A\"}],\"sort\":1,\"key\":\"BD140000\",\"value\":\"LY管理公司、ZQ职权A\"},{\"parentCode\":\"BD000000\",\"childNodes\":[{\"parentCode\":\"BD150000\",\"sort\":8,\"key\":\"BD150100\",\"value\":\"徇私舞弊不征税款A\"},{\"parentCode\":\"BD150000\",\"sort\":8,\"key\":\"BD150200\",\"value\":\"徇私舞弊少征税款A\"}],\"sort\":1,\"key\":\"BD150000\",\"value\":\"徇私舞弊不征、少征税款A\"},{\"parentCode\":\"BD000000\",\"childNodes\":[{\"parentCode\":\"BD160000\",\"sort\":8,\"key\":\"BD160100\",\"value\":\"徇私舞弊发售发票A\"},{\"parentCode\":\"BD160000\",\"sort\":8,\"key\":\"BD160200\",\"value\":\"徇私舞弊抵扣税款A\"},{\"parentCode\":\"BD160000\",\"sort\":8,\"key\":\"BD160300\",\"value\":\"徇私舞弊出口退税A\"}],\"sort\":1,\"key\":\"BD160000\",\"value\":\"徇私舞弊发售发票、抵扣税款、出口退税A\"},{\"parentCode\":\"BD000000\",\"sort\":8,\"key\":\"BD170000\",\"value\":\"违法提供出口退税凭证A\"},{\"parentCode\":\"BD000000\",\"childNodes\":[{\"parentCode\":\"BD180000\",\"sort\":8,\"key\":\"BD180100\",\"value\":\"GJ机关工作人员签订合同SZ被骗A\"},{\"parentCode\":\"BD180000\",\"sort\":8,\"key\":\"BD180200\",\"value\":\"GJ机关工作人员履行合同SZ被骗A\"}],\"sort\":1,\"key\":\"BD180000\",\"value\":\"GJ机关工作人员签订、履行合同SZ被骗A\"},{\"parentCode\":\"BD000000\",\"sort\":8,\"key\":\"BD190000\",\"value\":\"违法发放林木采伐许可证A\"},{\"parentCode\":\"BD000000\",\"sort\":8,\"key\":\"BD200000\",\"value\":\"环境监管SZA\"},{\"parentCode\":\"BD000000\",\"sort\":8,\"key\":\"BD210000\",\"value\":\"食品监管渎职A\"},{\"parentCode\":\"BD000000\",\"sort\":8,\"key\":\"BD220000\",\"value\":\"传染病防治SZA\"},{\"parentCode\":\"BD000000\",\"sort\":8,\"key\":\"BD240000\",\"value\":\"非法低价出让国有土地使用权A\"},{\"parentCode\":\"BD000000\",\"sort\":8,\"key\":\"BD250000\",\"value\":\"放纵走私A\"},{\"parentCode\":\"BD000000\",\"sort\":8,\"key\":\"BD260000\",\"value\":\"商检徇私舞弊A\"},{\"parentCode\":\"BD000000\",\"sort\":8,\"key\":\"BD270000\",\"value\":\"商检SZA\"},{\"parentCode\":\"BD000000\",\"sort\":8,\"key\":\"BD280000\",\"value\":\"动植物检疫徇私舞弊A\"},{\"parentCode\":\"BD000000\",\"sort\":8,\"key\":\"BD290000\",\"value\":\"动植物检疫SZA\"},{\"parentCode\":\"BD000000\",\"sort\":8,\"key\":\"BD300000\",\"value\":\"放纵制售伪劣商品犯罪行为A\"},{\"parentCode\":\"BD000000\",\"sort\":8,\"key\":\"BD310000\",\"value\":\"办理偷越国（边）境人员出入境证件A\"},{\"parentCode\":\"BD000000\",\"sort\":8,\"key\":\"BD320000\",\"value\":\"放行偷越国（边）境人员A\"},{\"parentCode\":\"BD000000\",\"childNodes\":[{\"parentCode\":\"BD330000\",\"sort\":8,\"key\":\"BD330100\",\"value\":\"不解救被拐卖妇女A\"},{\"parentCode\":\"BD330000\",\"sort\":8,\"key\":\"BD330200\",\"value\":\"不解救被绑架妇女A\"},{\"parentCode\":\"BD330000\",\"sort\":8,\"key\":\"BD330300\",\"value\":\"不解救被拐卖儿童A\"},{\"parentCode\":\"BD330000\",\"sort\":8,\"key\":\"BD330400\",\"value\":\"不解救被绑架儿童A\"}],\"sort\":1,\"key\":\"BD330000\",\"value\":\"不解救被拐卖、绑架妇女、儿童A\"},{\"parentCode\":\"BD000000\",\"childNodes\":[{\"parentCode\":\"BD340000\",\"sort\":8,\"key\":\"BD340100\",\"value\":\"阻碍解救被拐卖妇女A\"},{\"parentCode\":\"BD340000\",\"sort\":8,\"key\":\"BD340200\",\"value\":\"阻碍解救被绑架妇女A\"},{\"parentCode\":\"BD340000\",\"sort\":8,\"key\":\"BD340300\",\"value\":\"阻碍解救被拐卖儿童A\"},{\"parentCode\":\"BD340000\",\"sort\":8,\"key\":\"BD340400\",\"value\":\"阻碍解救被绑架儿童A\"}],\"sort\":1,\"key\":\"BD340000\",\"value\":\"阻碍解救被拐卖、绑架妇女、儿童A\"},{\"parentCode\":\"BD000000\",\"sort\":8,\"key\":\"BD350000\",\"value\":\"帮助犯罪分子逃避处罚A\"},{\"parentCode\":\"BD000000\",\"childNodes\":[{\"parentCode\":\"BD360000\",\"sort\":8,\"key\":\"BD360100\",\"value\":\"招收公务员徇私舞弊A\"},{\"parentCode\":\"BD360000\",\"sort\":8,\"key\":\"BD360200\",\"value\":\"招收学生徇私舞弊A\"}],\"sort\":1,\"key\":\"BD360000\",\"value\":\"招收公务员、学生徇私舞弊A\"},{\"parentCode\":\"BD000000\",\"childNodes\":[{\"parentCode\":\"BD370000\",\"sort\":8,\"key\":\"BD370100\",\"value\":\"SZ造成珍贵文物损毁A\"},{\"parentCode\":\"BD370000\",\"sort\":8,\"key\":\"BD370200\",\"value\":\"SZ造成珍贵文物流失A\"}],\"sort\":1,\"key\":\"BD370000\",\"value\":\"SZ造成珍贵文物损毁、流失A\"},{\"parentCode\":\"BD000000\",\"sort\":8,\"key\":\"BD400000\",\"value\":\"非法批准征收、征用、占用土地A\"},{\"parentCode\":\"BD000000\",\"sort\":8,\"key\":\"BD410000\",\"value\":\"食品、药品监管渎职A\"}],\"sort\":1,\"key\":\"BD000000\",\"value\":\"渎职A\"},{\"childNodes\":[{\"parentCode\":\"10000000\",\"sort\":8,\"key\":\"10010000\",\"value\":\"战时违抗命令A\"},{\"parentCode\":\"10000000\",\"childNodes\":[{\"parentCode\":\"10020000\",\"sort\":8,\"key\":\"10020100\",\"value\":\"隐瞒军情A\"},{\"parentCode\":\"10020000\",\"sort\":8,\"key\":\"10020200\",\"value\":\"谎报军情A\"}],\"sort\":1,\"key\":\"10020000\",\"value\":\"隐瞒、谎报军情A\"},{\"parentCode\":\"10000000\",\"childNodes\":[{\"parentCode\":\"10030000\",\"sort\":8,\"key\":\"10030100\",\"value\":\"拒传军令A\"},{\"parentCode\":\"10030000\",\"sort\":8,\"key\":\"10030200\",\"value\":\"假传军令A\"}],\"sort\":1,\"key\":\"10030000\",\"value\":\"拒传、假传军令A\"},{\"parentCode\":\"10000000\",\"sort\":8,\"key\":\"10040000\",\"value\":\"投降A\"},{\"parentCode\":\"10000000\",\"sort\":8,\"key\":\"10050000\",\"value\":\"战时临阵脱逃A\"},{\"parentCode\":\"10000000\",\"childNodes\":[{\"parentCode\":\"10060000\",\"sort\":8,\"key\":\"10060100\",\"value\":\"擅离JS职守A\"},{\"parentCode\":\"10060000\",\"sort\":8,\"key\":\"10060200\",\"value\":\"玩忽JS职守A\"}],\"sort\":1,\"key\":\"10060000\",\"value\":\"擅离、玩忽JS职守A\"},{\"parentCode\":\"10000000\",\"sort\":8,\"key\":\"10070000\",\"value\":\"阻碍执行JS职务A\"},{\"parentCode\":\"10000000\",\"sort\":8,\"key\":\"10080000\",\"value\":\"指使部属违反职责A\"},{\"parentCode\":\"10000000\",\"sort\":8,\"key\":\"10BD0000\",\"value\":\"违令作战消极A\"},{\"parentCode\":\"10000000\",\"sort\":8,\"key\":\"10100000\",\"value\":\"拒不救援友邻部队A\"},{\"parentCode\":\"10000000\",\"sort\":8,\"key\":\"10EFG000\",\"value\":\"JR叛逃A\"},{\"parentCode\":\"10000000\",\"sort\":8,\"key\":\"10120000\",\"value\":\"非法获取JSMMA\"},{\"parentCode\":\"10000000\",\"childNodes\":[{\"parentCode\":\"10130000\",\"sort\":8,\"key\":\"10130100\",\"value\":\"为境外窃取JSMMA\"},{\"parentCode\":\"10130000\",\"sort\":8,\"key\":\"10130200\",\"value\":\"为境外刺探JSMMA\"},{\"parentCode\":\"10130000\",\"sort\":8,\"key\":\"10130300\",\"value\":\"为境外收买JSMMA\"},{\"parentCode\":\"10130000\",\"sort\":8,\"key\":\"10130400\",\"value\":\"为境外非法提供JSMMA\"}],\"sort\":1,\"key\":\"10130000\",\"value\":\"为境外窃取、刺探、收买、非法提供JSMMA\"},{\"parentCode\":\"10000000\",\"sort\":8,\"key\":\"10140000\",\"value\":\"故意泄露JSMMA\"},{\"parentCode\":\"10000000\",\"sort\":8,\"key\":\"10150000\",\"value\":\"过失泄露JSMMA\"},{\"parentCode\":\"10000000\",\"sort\":8,\"key\":\"10160000\",\"value\":\"战时造谣惑众A\"},{\"parentCode\":\"10000000\",\"sort\":8,\"key\":\"10170000\",\"value\":\"战时自伤A\"},{\"parentCode\":\"10000000\",\"sort\":8,\"key\":\"10180000\",\"value\":\"逃离部队A\"},{\"parentCode\":\"10000000\",\"sort\":8,\"key\":\"10190000\",\"value\":\"武器装备肇事A\"},{\"parentCode\":\"10000000\",\"sort\":8,\"key\":\"10200000\",\"value\":\"擅自改变武器装备编配用途A\"},{\"parentCode\":\"10000000\",\"childNodes\":[{\"parentCode\":\"10210000\",\"sort\":8,\"key\":\"10210100\",\"value\":\"盗窃武器装备A\"},{\"parentCode\":\"10210000\",\"sort\":8,\"key\":\"10210200\",\"value\":\"盗窃军用物资A\"},{\"parentCode\":\"10210000\",\"sort\":8,\"key\":\"10210300\",\"value\":\"抢夺武器装备A\"},{\"parentCode\":\"10210000\",\"sort\":8,\"key\":\"10210400\",\"value\":\"抢夺军用物资A\"}],\"sort\":1,\"key\":\"10210000\",\"value\":\"盗窃、抢夺武器装备、军用物资A\"},{\"parentCode\":\"10000000\",\"childNodes\":[{\"parentCode\":\"10220000\",\"sort\":8,\"key\":\"10220100\",\"value\":\"非法出卖武器装备A\"},{\"parentCode\":\"10220000\",\"sort\":8,\"key\":\"10220200\",\"value\":\"非法转让武器装备A\"}],\"sort\":1,\"key\":\"10220000\",\"value\":\"非法出卖、转让武器装备A\"},{\"parentCode\":\"10000000\",\"sort\":8,\"key\":\"10230000\",\"value\":\"遗弃武器装备A\"},{\"parentCode\":\"10000000\",\"sort\":8,\"key\":\"10240000\",\"value\":\"遗失武器装备A\"},{\"parentCode\":\"10000000\",\"childNodes\":[{\"parentCode\":\"10250000\",\"sort\":8,\"key\":\"10250100\",\"value\":\"擅自出卖房地产A\"},{\"parentCode\":\"10250000\",\"sort\":8,\"key\":\"10250200\",\"value\":\"擅自转让军队房地产A\"}],\"sort\":1,\"key\":\"10250000\",\"value\":\"擅自出卖、转让军队房地产A\"},{\"parentCode\":\"10000000\",\"sort\":8,\"key\":\"10260000\",\"value\":\"虐待部属A\"},{\"parentCode\":\"10000000\",\"sort\":8,\"key\":\"10270000\",\"value\":\"遗弃伤病JRA\"},{\"parentCode\":\"10000000\",\"sort\":8,\"key\":\"10280000\",\"value\":\"战时拒不救治伤病JRA\"},{\"parentCode\":\"10000000\",\"childNodes\":[{\"parentCode\":\"10290000\",\"sort\":8,\"key\":\"10290100\",\"value\":\"战时残害居民A\"},{\"parentCode\":\"10290000\",\"sort\":8,\"key\":\"10290200\",\"value\":\"战时掠夺居民财物A\"}],\"sort\":1,\"key\":\"10290000\",\"value\":\"战时残害居民、掠夺居民财物A\"},{\"parentCode\":\"10000000\",\"sort\":8,\"key\":\"10300000\",\"value\":\"私放俘虏A\"},{\"parentCode\":\"10000000\",\"sort\":8,\"key\":\"10310000\",\"value\":\"虐待俘虏A\"}],\"sort\":1,\"key\":\"10000000\",\"value\":\"JR违反ZZA\"},{\"sort\":8,\"key\":\"EFG00000\",\"value\":\"XSA件\"}],\"sort\":1,\"value\":\"%25E5%2588%2591%25E4%25B8%2593%25E6%25A1%2588%25E4%25BB%25B6%25E7%25B1%25BB%25E5%2588%25AB\"}";static NodeModel rootTree;static {rootTree = JSONUtil.toBean(json_str, NodeModel.class);}public static void main(String[] args) throws IOException {List<List<CellModel>> dictRow = Lists.newArrayList();tree2ExcelRow(rootTree, dictRow, Lists.newArrayList());ExcelHandler.dynamicWriteNoHead(Lists.newArrayList(new ExcelModel(null, dictRow)), "字典项数据调试");}/*** 数据构建树* <Strong>* 树的深度优先遍历（DFS）* </Strong>** @param nodeModel* @param rows* @param frontNodes*/public static void tree2ExcelRow(NodeModel nodeModel, List<List<CellModel>> rows, List<NodeModel> frontNodes) {//step1.设置数据行List<CellModel> row = new ArrayList<>();for (NodeModel frontNode : frontNodes) {row.addAll(node2ExcelCell(frontNode));}rows.add(row);if (CollectionUtils.isNotEmpty(nodeModel.getChildNodes())) {//step1.1.如果还存在子节点，继续读取子节点for (NodeModel childNode : nodeModel.getChildNodes()) {frontNodes.add(childNode);tree2ExcelRow(childNode, rows, frontNodes);log.info("深度变化值：{}", frontNodes.size());frontNodes.remove(childNode);}}}public static List<CellModel> node2ExcelCell(NodeModel nodeModel) {List<CellModel> cells = Lists.newArrayList();String dictItemCode = nodeModel.getKey();cells.add(new CellModel(dictItemCode));String dictItemName = nodeModel.getValue();cells.add(new CellModel(dictItemName));return cells;}}

3.字典树开展为EXCEL表格行结果展示