Excel to JSON (tree structure)

excel turn json ( Tree structure )

One 、python Read excel turn json

Purpose ： Lies in excel The data is converted into json Format .

import xlrd, json


def read_xlsx_file(filename):
    #  open Excel file 
    data = xlrd.open_workbook(filename)
    #  Read the first worksheet 
    table = data.sheets()[0]
    #  The statistical number of rows 
    rows = table.nrows
    data = []  #  Storing 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(" newly build  XLS  Worksheet .xls")
    #  The data in the dictionary are all single quotation marks , But standard json Double quotation marks are required 
    js = json.dumps(d1, sort_keys=True, ensure_ascii=False, indent=4, separators=(',', ':'))
    print(js)
    #  The previous data is just an array , Plus the outside json Format braces 
    js = "{" + js + "}"
    #  Can read but write , Create... If it doesn't exist , If there is content, overwrite 
    jsFile = open("./text3.json", "w+", encoding='utf-8')
    jsFile.write(js)
    jsFile.close()

Two 、java Turn into a tree structure of response
Although the reading is json Format , But it is not read as a tree structure , because python Not familiar with , So use java Change to tree structure .

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\":\" Two complete \",\n" +
                " \"cateOne\":\" product \",\n" +
                " \"cateThree\":\" Product classification \",\n" +
                " \"cateTwo\":\" Personal insurance activity plan \",\n" +
                " \"kbName\":\" product _ Personal insurance activities \"\n" +
                " },\n" +
                " {\n" +
                " \"cateFour\":\" For life \",\n" +
                " \"cateOne\":\" product \",\n" +
                " \"cateThree\":\" Product classification \",\n" +
                " \"cateTwo\":\" Personal insurance activity plan \",\n" +
                " \"kbName\":\" product _ Personal insurance activities \"\n" +
                " },\n" +
                " {\n" +
                " \"cateFour\":\" regular \",\n" +
                " \"cateOne\":\" product \",\n" +
                " \"cateThree\":\" Product classification \",\n" +
                " \"cateTwo\":\" Personal insurance activity plan \",\n" +
                " \"kbName\":\" product _ Personal insurance activities \"\n" +
                " },\n" +
                " {\n" +
                " \"cateFour\":\" health \",\n" +
                " \"cateOne\":\" product \",\n" +
                " \"cateThree\":\" Product classification \",\n" +
                " \"cateTwo\":\" Personal insurance activity plan \",\n" +
                " \"kbName\":\" product _ Personal insurance activities \"\n" +
                " },\n" +
                " {\n" +
                " \"cateFour\":\" Car gold \",\n" +
                " \"cateOne\":\" product \",\n" +
                " \"cateThree\":\" Product classification \",\n" +
                " \"cateTwo\":\" Personal insurance activity plan \",\n" +
                " \"kbName\":\" product _ Personal insurance activities \"\n" +
                " },\n" +
                " {\n" +
                " \"cateFour\":\" other \",\n" +
                " \"cateOne\":\" product \",\n" +
                " \"cateThree\":\" Product classification \",\n" +
                " \"cateTwo\":\" Personal insurance activity plan \",\n" +
                " \"kbName\":\" product _ Personal insurance activities \"\n" +
                " }\n" +
                "]";

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

        // To target format 
        // obtain kbName De duplication of 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 layer 
            // Find the added kb Of 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<>();

                // Get the same kbName Of 
                List<TransMid> samekbInfoList = transMids.stream().filter(trans -> trans.getKbName().equals(transMid.getKbName())).collect(Collectors.toList());

                // Get the de duplication set of the first level classification name 
                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<>();
                    // Second level classification 
                    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<>();
                        // Third level classification 
                        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<>();
                            // The fourth level of classification 
                            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<>();
// // fifth degree 
// 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);
                    // Add a level list
                    cateOneList.add(catesgreis);

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

}