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() 

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 
   
     catesgreisListFour = new ArrayList<>(); 
    // //第五级 // List 
   
     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)); } }