【js】excel/csv转json_xls转json数组的js

作者：weixin_40725706 | 2024-05-03 10:31:22

踩

xls转json数组的js

csv转json


	csvJSON(csv) {
      var lines = csv.split("\n");
      var result = [];
      var headers = lines[0].split(",");
      for (var i = 1; i < lines.length; i++) {
        var obj = {};
        var currentline = lines[i].split(",");
        for (var j = 0; j < headers.length; j++) {
          obj[headers[j].trim()] = currentline[j];
        }
        result.push(obj);
      }
      return result;
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

excel转json

核心代码

利用 js-xlsx


        function readExcel(e) {
            const files = e.target.files
            console.log(files)
            if (files.length <= 0) {
                return false
            } else if (!/\.(xls|xlsx)$/.test(files[0].name.toLowerCase())) {
                alert('上传格式不正确，请上传xls或者xlsx格式')
                return false
            }

            const fileReader = new FileReader()
            fileReader.onload = (ev) => {
                try {
                    const data = ev.target.result
                    const workbook = XLSX.read(data, {
                        type: 'binary'
                    })
                    const wsname = workbook.SheetNames[0]
                    const ws = XLSX.utils.sheet_to_json(workbook.Sheets[wsname])
                    let output = document.getElementById("output")
                    output.innerHTML = JSON.stringify(ws)
                } catch (e) {
                    console.log(e)
                }
            }
            fileReader.readAsBinaryString(files[0])
        }
        let dom = document.getElementById("input")
        dom.addEventListener('change', e => {
            readExcel(e)
        })
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

一个小demo

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

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <script src="./xlsx.full.min.js"></script>
    <title>Document</title>

    <style>
        div {
            margin: 10px;
        }

        .tooltip{
            font-size: 12px;
            color: grey;
        }

        #output {
            border: 1px solid #d5d5d5;
        }
    </style>
</head>

<body>
    <div class="tooltip">
        <h5>示例：</h5>
        <table border="1">
            <tr>
                <th>品牌</th>
                <th>型号</th>
                <th>用户数</th>
            </tr>
        
            <tr>
                <td>a</td>
                <td>a1</td>
                <td>1</td>
            </tr>
            <tr>
                <td>a</td>
                <td>a2</td>
                <td>2</td>
            </tr>
        </table>
        <p>1.直接转换,父字段名和子字段名不填写:<br></code></code><code>[{"品牌":"a","型号":"a1","用户数":1},{"品牌":"a","型号":"a2","用户数":2}]</code></p>
        <p>2.包含json格式的子节点，父字段名【品牌】，子字段名【用户数】：<br><code>[{"品牌":"a","value":3,"children":[{"型号":"a1","用户数":1},{"型号":"a2","用户数":2}]}]</code></p>

    </div>
    <div><label for="">excel转json：</label><input type="file" id="input"></div>
    <div><label for="">父字段名: <input type="text" id="col1" placeholder=""></label><label for="">值字段名: <input type="text"
                id="val"></label></div>
    <div id="output"></div>

    <script>
        function readExcel(e) {
            const files = e.target.files
            console.log(files)
            if (files.length <= 0) {
                return false
            } else if (!/\.(xls|xlsx)$/.test(files[0].name.toLowerCase())) {
                alert('上传格式不正确，请上传xls或者xlsx格式')
                return false
            }

            const fileReader = new FileReader()
            fileReader.onload = (ev) => {
                try {
                    const data = ev.target.result
                    const workbook = XLSX.read(data, {
                        type: 'binary'
                    })
                    const wsname = workbook.SheetNames[0]
                    const ws = XLSX.utils.sheet_to_json(workbook.Sheets[wsname])
                    let col1 = document.getElementById("col1").value
                    let val = document.getElementById("val").value
                    let output = document.getElementById("output")
                    if (col1 != "") {
                        // 获取所有父字段值数组
                        let pArr = ws.map(item => {
                            return item[col1]
                        })
                        // console.log(pArr)
                        // 父字段值数组去重
                        let npArr = pArr.reduce((pre, cur) => {
                            if (!pre.includes(cur)) {
                                return pre.concat(cur)
                            } else {
                                return pre
                            }
                        }, [])
                        // console.log(npArr)
                        let nws = []
                        for (let i = 0; i < npArr.length; i++) {
                            let item = npArr[i]
                            let childArr = ws.filter((value, index) => {
                                // console.log(value[col1]==item)
                                return value[col1] == item
                            }, [])
                            console.log(childArr)
                            if (val != "") {
                                let arr = childArr.reduce((pre, cur) => {
                                    let npre = {}
                                    npre = JSON.parse(JSON.stringify(pre))
                                    npre[val] = pre[val] + cur[val]
                                    return npre
                                })
                                console.log(arr)
                                let nchildArr = childArr.map((item) => {
                                    let keys = Object.keys(item)
                                    let res = {}
                                    for (let i = 0; i < keys.length; i++) {
                                        if (keys[i] != col1) {
                                            res[keys[i]] = item[keys[i]]
                                        }
                                    }
                                    return res
                                })
                                nws.push({
                                    [col1]: item,
                                    value: arr[val],
                                    children: nchildArr
                                })
                            } else {
                                let nchildArr = childArr.map((item) => {
                                    let keys = Object.keys(item)
                                    let res = {}
                                    for (let i = 0; i < keys.length; i++) {
                                        if (keys[i] != col1) {
                                            res.keys[i] = item[keys[i]]
                                        }
                                    }
                                    return res
                                })
                                nws.push({
                                    name: item,
                                    children: nchildArr
                                })
                            }
                        }
                        console.log(nws)
                        output.innerHTML = JSON.stringify(nws)
                    } else {
                        console.log(ws)
                        output.innerHTML = JSON.stringify(ws)
                    }
                } catch (e) {
                    console.log(e)
                }
            }
            fileReader.readAsBinaryString(files[0])
        }
        let dom = document.getElementById("input")
        dom.addEventListener('change', e => {
            readExcel(e)
        })
    </script>
</body>

</html>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/weixin_40725706/article/detail/529166