excel转json (树状结构)

excel转json (树状结构)

一、python读取excel 转json

目的：在于将excel的数据转换成json格式。

import xlrd, json


def read_xlsx_file(filename):
    # 打开Excel文件
    data = xlrd.open_workbook(filename)
    # 读取第一个工作表
    table = data.sheets()[0]
    # 统计行数
    rows = table.nrows
    data = []  # 存放数据
    for i in range(1, rows):
        values = table.row_values(i)
        data.append(
            (
                {
    
                    "kbName": str(str(values[0])),
                    "cateOne": values[1],
                    "cateTwo": values[2],
                    "cateThree": values[3],
                    "cateFour": values[4],
                }
            )
        )
    return data


if __name__ == '__main__':
    d1 = read_xlsx_file("新建 XLS 工作表.xls")
    # 字典中的数据都是单引号，但是标准的json需要双引号
    js = json.dumps(d1, sort_keys=True, ensure_ascii=False, indent=4, separators=(',', ':'))
    print(js)
    # 前面的数据只是数组，加上外面的json格式大括号
    js = "{" + js + "}"
    # 可读可写，如果不存在则创建，如果有内容则覆盖
    jsFile = open("./text3.json", "w+", encoding='utf-8')
    jsFile.write(js)
    jsFile.close()

二、java 转成响应的树状结构
虽然读取的是json格式，但是没有读成树状结构，因为python不熟悉，所以用java改成树状结构。

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.junit.Test;

import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.Stream;

public class TansTo {
    

    @Test
    public void toTrans() {
    

        String jsonstring = "[\n" +
                " {\n" +
                " \"cateFour\":\"两全\",\n" +
                " \"cateOne\":\"产品\",\n" +
                " \"cateThree\":\"产品分类\",\n" +
                " \"cateTwo\":\"个险活动方案\",\n" +
                " \"kbName\":\"产品_个险活动\"\n" +
                " },\n" +
                " {\n" +
                " \"cateFour\":\"终身\",\n" +
                " \"cateOne\":\"产品\",\n" +
                " \"cateThree\":\"产品分类\",\n" +
                " \"cateTwo\":\"个险活动方案\",\n" +
                " \"kbName\":\"产品_个险活动\"\n" +
                " },\n" +
                " {\n" +
                " \"cateFour\":\"定期\",\n" +
                " \"cateOne\":\"产品\",\n" +
                " \"cateThree\":\"产品分类\",\n" +
                " \"cateTwo\":\"个险活动方案\",\n" +
                " \"kbName\":\"产品_个险活动\"\n" +
                " },\n" +
                " {\n" +
                " \"cateFour\":\"健康\",\n" +
                " \"cateOne\":\"产品\",\n" +
                " \"cateThree\":\"产品分类\",\n" +
                " \"cateTwo\":\"个险活动方案\",\n" +
                " \"kbName\":\"产品_个险活动\"\n" +
                " },\n" +
                " {\n" +
                " \"cateFour\":\"车金\",\n" +
                " \"cateOne\":\"产品\",\n" +
                " \"cateThree\":\"产品分类\",\n" +
                " \"cateTwo\":\"个险活动方案\",\n" +
                " \"kbName\":\"产品_个险活动\"\n" +
                " },\n" +
                " {\n" +
                " \"cateFour\":\"其他\",\n" +
                " \"cateOne\":\"产品\",\n" +
                " \"cateThree\":\"产品分类\",\n" +
                " \"cateTwo\":\"个险活动方案\",\n" +
                " \"kbName\":\"产品_个险活动\"\n" +
                " }\n" +
                "]";

        JSONArray jsonArray = JSONObject.parseArray(jsonstring);
        List<TransMid> transMids = jsonArray.toJavaList(TransMid.class);
        System.out.println(transMids);

        //转目标格式
        //获取kbName的去重list
        List<String> collect = transMids.stream().map(TransMid::getKbName).distinct().collect(Collectors.toList());
        System.out.println(collect);

        List<KnowledgeBaseInfo> knowledgeBaseInfoList = new ArrayList<>();
        for (TransMid transMid : transMids){
    
            //kbName层
            //查找已添加的kb的Name
            List<String> kbNames = knowledgeBaseInfoList.stream().map(kbinf -> kbinf.getKnowledgeBaseName()).collect(Collectors.toList());

            if (!kbNames.contains(transMid.getKbName())) {
    
                KnowledgeBaseInfo knowledgeBaseInfo = new KnowledgeBaseInfo();
                knowledgeBaseInfo.setKnowledgeBaseName(transMid.getKbName());


                List<KnowledgeBaseInfo.Catesgreis> cateOneList = new ArrayList<>();

                //获取相同kbName的
                List<TransMid> samekbInfoList = transMids.stream().filter(trans -> trans.getKbName().equals(transMid.getKbName())).collect(Collectors.toList());

                //获取一级分类名的去重集合
                List<String> collectClassOne = samekbInfoList.stream().map(TransMid::getCateOne).distinct().collect(Collectors.toList());

                collectClassOne.stream().forEach(classOne ->{
    

                    KnowledgeBaseInfo.Catesgreis catesgreis = knowledgeBaseInfo.new Catesgreis();
                    catesgreis.setCateName(classOne);

                    List<KnowledgeBaseInfo.Catesgreis> catesgreisListOne = new ArrayList<>();
                    //第二级分类
                    List<TransMid> sameClassOneList = samekbInfoList.stream().filter(trans -> trans.getCateOne().equals(classOne)).collect(Collectors.toList());
                    List<String> collectClassTwo = sameClassOneList.stream().map(TransMid::getCateTwo).distinct().collect(Collectors.toList());

                    collectClassTwo.stream().forEach(classTwo -> {
    
                        KnowledgeBaseInfo.Catesgreis catesgreisTwo = knowledgeBaseInfo.new Catesgreis();
                        catesgreisTwo.setCateName(classTwo);

                        List<KnowledgeBaseInfo.Catesgreis> catesgreisListTwo = new ArrayList<>();
                        //第三级分类
                        List<TransMid> sameClassTwoList = sameClassOneList.stream().filter(trans -> trans.getCateTwo().equals(classTwo)).collect(Collectors.toList());
                        List<String> collectClassThree = sameClassTwoList.stream().map(TransMid::getCateThree).distinct().collect(Collectors.toList());

                        collectClassThree.stream().forEach(classThree -> {
    
                            KnowledgeBaseInfo.Catesgreis catesgreisThree = knowledgeBaseInfo.new Catesgreis();
                            catesgreisThree.setCateName(classThree);

                            List<KnowledgeBaseInfo.Catesgreis> catesgreisListThree = new ArrayList<>();
                            //第四级分类
                            List<TransMid> sameClassThreeList = sameClassTwoList.stream().filter(trans -> trans.getCateThree().equals(classThree)).collect(Collectors.toList());
                            List<String> collectClassFour = sameClassThreeList.stream().map(TransMid::getCateFour).distinct().collect(Collectors.toList());

                            collectClassFour.stream().forEach(classFour -> {
    
                                KnowledgeBaseInfo.Catesgreis catesgreisFour = knowledgeBaseInfo.new Catesgreis();
                                catesgreisFour.setCateName(classFour);

// List<KnowledgeBaseInfo.Catesgreis> catesgreisListFour = new ArrayList<>();
// //第五级
// List<TransMid> sameClassFourList = sameClassThreeList.stream().filter(trans -> trans.getCateFour().equals(classFour)).collect(Collectors.toList());
// sameClassFourList.stream().map(TransMid::getCateFour)


                                catesgreisFour.setCateList(new ArrayList<>());
                                catesgreisListThree.add(catesgreisFour);
                            });

                            catesgreisThree.setCateList(catesgreisListThree);
                            catesgreisListTwo.add(catesgreisThree);

                        });

                        catesgreisTwo.setCateList(catesgreisListTwo);
                        catesgreisListOne.add(catesgreisTwo);
                    });
                    catesgreis.setCateList(catesgreisListOne);
                    //添加一级的list
                    cateOneList.add(catesgreis);

                });
                knowledgeBaseInfo.setCateList(cateOneList);
                knowledgeBaseInfoList.add(knowledgeBaseInfo);
            }
        }
        System.out.println(knowledgeBaseInfoList);
        System.out.println(JSONArray.toJSONString(knowledgeBaseInfoList));
    }

}