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

mister-big

已于 2023-12-11 17:17:08 修改

阅读量1.2k

点赞数 8

分类专栏： java 文章标签： java 数据结构 excel 深度优先

于 2023-12-07 11:46:41 首次发布

本文链接：https://blog.csdn.net/qq_38127559/article/details/134848084

版权

java 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

文章目录

前言
一、字典数据示例
二、解析逻辑
- 1.树节点转EXCEL行
- 2.算法选择（DFS）
三、完整示例

前言

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

在这里插入图片描述

mister-big

关注

8
点赞
踩
16

收藏

觉得还不错? 一键收藏
打赏
0
评论
JAVA树型数据结构展开为excel行数据并导出示例

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

扫一扫