将xlsx解析的excel表头json数据转成树结构

项目需求需要将用户上传的excel解析成前端可以使用的树数据并且绘制表格到页面上,这里我将用户上传的excel使用xlsx解析后,拿到json数据对他进行解析生成树,上传的excel为:

将xlsx解析的excel表头json数据转成树结构

 

 ,解析方法如下:

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>
</head>

<body>

</body>

</html>
<script>
    let ref = "A1:L3"//xlsx解析后获取到的单元格所占行列数
    let marge = [
        {
            "s": { "c": 6, "r": 0 },
            "e": { "c": 11, "r": 0 }
        },
        {
            "s": { "c": 9, "r": 1 },
            "e": { "c": 11, "r": 1 }
        },
        {
            "s": { "c": 7, "r": 1 },
            "e": { "c": 8, "r": 1 }
        },
        {
            "s": { "c": 6, "r": 1 },
            "e": { "c": 6, "r": 2 }
        },
        {
            "s": { "c": 0, "r": 0 },
            "e": { "c": 0, "r": 2 }
        },
        {
            "s": { "c": 1, "r": 0 },
            "e": { "c": 1, "r": 2 }
        },
        {
            "s": { "c": 2, "r": 0 },
            "e": { "c": 2, "r": 2 }
        },
        {
            "s": { "c": 3, "r": 0 },
            "e": { "c": 4, "r": 1 }
        },
        {
            "s": { "c": 5, "r": 0 },
            "e": { "c": 5, "r": 1 }
        }
    ];//xlsx解析后获取到的单元格合并数据
    let arr = [
        { "key_": "A1", "data": "点点滴滴" },
        { "key_": "B1", "data": "数量" },
        { "key_": "C1", "data": "双数" },
        { "key_": "D1", "data": "单数" },
        { "key_": "F1", "data": "单双数" },
        { "key_": "G1", "data": "2019年" },
        { "key_": "G2", "data": "计算2018" },
        { "key_": "H2", "data": "*大的" },
        { "key_": "J2", "data": "阿达" },
        { "key_": "D3", "data": "法法" },
        { "key_": "E3", "data": "大师傅" },
        { "key_": "F3", "data": "阿发" },
        { "key_": "H3", "data": "阿法狗" },
        { "key_": "I3", "data": "算法22" },
        { "key_": "J3", "data": "阿嘎" },
        { "key_": "K3", "data": "阿法阿发" },
        { "key_": "L3", "data": "谁改的根" }
    ];//xlsx解析后获取到的单元格数据,我将它转化为了数组格式
    let tree = [];//存储目标树形结构
    let margezh = [];//存储合并单元格数据
    marge.forEach(item => {
        let t = Number(item.s.r) + 1;
        margezh.push({
            "s": item.s,
            "e": item.e,
            "s_": numToString(item.s.c) + "" + t
        })
    })

    console.log(margezh, "margezh");

    function numToString(number) {//解析单元格合并
        let char = "";
        let array = [];
        let numToStringAction = function (nnum) {
            let num = nnum;
            let a = parseInt(num / 26);
            let b = num % 26;
            array.push(b);
            if (a > 0) {
                numToStringAction(a);
            }
        }
        numToStringAction(number);
        array = array.reverse();
        for (let i = 0; i < array.length; i++) {
            char += String.fromCharCode(64 + parseInt(array[i] + 1));
        }
        return char;
    }

    arr.forEach((item, idx) => {//存储字母数字
        let separat = separation(item.key_);
        item.letter = separat.letter[0];
        item.digital = Number(separat.digital[0]);
        let a = stringTonum(separat.letter[0]);
        item.letter_digital = a;
    })

    function separation(num) {//拆分字母数字
        let letter = num.match(/^[a-z|A-Z]+/gi);
        let digital = num.match(/\d+$/gi);
        console.log(letter + "," + digital);
        return { "letter": letter, "digital": digital }
    }

    for (let j = 0; j < arr.length; j++) {
        if (arr[j].digital == 1) {
            let t = matchMerger(arr[j].key_);
            if (t && t.e) {
                tree.push(
                    {
                        name: arr[j].data,
                        startrow: t.s.r,
                        startcol: t.s.c,
                        endrow: t.e.r,
                        endcol: t.e.c,
                        children: [],
                        flagkey: arr[j].key_,
                        flag_r: t.e.r+1, //自身所在行
                        flag_c: arr[j].letter_digital//自身所在列
                    }
                )
            } else {
                tree.push(
                    {
                        name: arr[j].data,
                        children: [],
                        flagkey: arr[j].key_,
                        flag_r: arr[j].digital, //自身所在行
                        flag_c: arr[j].letter_digital//自身所在列
                    }
                )
            }
        } else {
            recursive(tree, arr[j].letter_digital, arr[j].digital, arr[j].key_, arr[j].data);
        }
    }

    console.log(tree,"tree");

    function stringTonum(a) {//excel字母转数字
        var str = a.toLowerCase().split("");
        var num = 0;
        var al = str.length;
        var getCharNumber = function (charx) {
            return charx.charCodeAt() - 96;
        };
        var numout = 0;
        var charnum = 0;
        for (var i = 0; i < al; i++) {
            charnum = getCharNumber(str[i]);
            numout += charnum * Math.pow(26, al - i - 1);
        };
        return numout;
    }

    function matchMerger(data) {//获取所合并行数据
        for (let i = 0; i < margezh.length; i++) {
            if (margezh[i].s_ == data) {
                return margezh[i];
            }
        }
    }

    function recursive(data, xcol, xrow, key, name) {//递归树
        let flag = 0;
        for (let i = 0; i < data.length; i++) {
            if (data[i].children) {
                if (data[i].flag_c <= xcol && data[i].flag_r == xrow - 1&&data[i].endcol+1 >= xcol ) {
                    if (flag == 0) {
                        let t = matchMerger(key);
                        if (t && t.s) {
                            data[i].children.push({
                                name: name,
                                startrow: t.s.r,
                                startcol: t.s.c,
                                endrow: t.e.r,
                                endcol: t.e.c,
                                children: [],
                                flagkey: key,
                                flag_r: t.e.r+1, //自身所在行
                                flag_c: xcol//自身所在列
                            })

                        } else {
                            data[i].children.push({
                                name: name,
                                children: [],
                                flagkey: key,
                                flag_r: xrow, //自身所在行
                                flag_c: xcol//自身所在列
                            })
                        }
                    }

                    falag = 1;
                } else {
                    recursive(data[i].children, xcol, xrow, key, name);
                }
            } else {
            }
        }
    }
    console.log(tree)//打印最后生成树
</script>

最后打印的结果为

将xlsx解析的excel表头json数据转成树结构

 

 其中有的参数或许你们不需要,可以在生成树后递归去掉,别在代码里去掉,这会出现错误。

下面贴出xlsx解析excel需要取得数据

将xlsx解析的excel表头json数据转成树结构

 

 这些就是具体核心方法,如果不知道如何解析excel,请参考前面发的文章,若有问题请留言,大家一起学习,谢谢。

将xlsx解析的excel表头json数据转成树结构

上一篇:Servlet在Tomcat动态web项目上的简单应用


下一篇:JSP