先写一个文件提交

1
<input type="file" id="excel-file">

导入xlsx.core.min.js

1
<script type="text/javascript" src="xlsx.core.min.js"></script>

解析excel

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
<script>
    $('#excel-file').change(function (e) {
        var files = e.target.files;
        var fileReader = new FileReader();
        fileReader.onload = function (ev) {
            try {
                var data = ev.target.result,
                    workbook = XLSX.read(data, {
                        type: 'binary'
                    }), // 以二进制流方式读取得到整份excel表格对象
                    persons = []; // 存储获取到的数据
            } catch (e) {
                console.log('文件类型不正确');
                return;
            }
            // 遍历每张表读取
            for (var sheet in workbook.Sheets) {
                if (workbook.Sheets.hasOwnProperty(sheet)) {
                    var fromTo = workbook.Sheets[sheet]['!ref'];
                    console.log(fromTo);
                    var datas = workbook.Sheets[sheet];
                    
                    // 如果有不规范数据可以在这里进行处理datas
                    
                    persons = persons.concat(XLSX.utils.sheet_to_json(datas));
                    break; // 只读了第一张表
                }
            }
           console.log(persons);
        };
        // 以二进制方式打开文件
        fileReader.readAsBinaryString(files[0]);
    });
</script>

全部代码

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
<!DOCTYPE html>
<html>
<head>
    <title></title>
</head>
<body>
<input type="file" id="excel-file">
</body>
<script type="text/javascript" src="xlsx.core.min.js"></script>
<script src="https://code.jquery.com/jquery-3.1.1.min.js"></script>
<script>
    $('#excel-file').change(function (e) {
        var files = e.target.files;
        var fileReader = new FileReader();
        fileReader.onload = function (ev) {
            try {
                var data = ev.target.result,
                    workbook = XLSX.read(data, {
                        type: 'binary'
                    }), // 以二进制流方式读取得到整份excel表格对象
                    persons = []; // 存储获取到的数据
            } catch (e) {
                console.log('文件类型不正确');
                return;
            }

            // 遍历每张表读取
            for (var sheet in workbook.Sheets) {
                if (workbook.Sheets.hasOwnProperty(sheet)) {
                    var fromTo = workbook.Sheets[sheet]['!ref'];
                    console.log(fromTo);
                    var datas = workbook.Sheets[sheet];
                    console.log(datas);
                    // 处理不规范的数据
                    for (var nums in datas) {
                        console.log(nums);
                        var num = nums.replace(/[^0-9]/ig, "");
                        console.log(num);
                        var row_data = new Array();
                        row_data[0] = "A" + String(num);
                        row_data[1] = "B" + String(num);
                        row_data[2] = "C" + String(num);
                        row_data[3] = "D" + String(num);
                        row_data[4] = "E" + String(num);
                        row_data[5] = "F" + String(num);
                        row_data[6] = "G" + String(num);
                        row_data[7] = "H" + String(num);
                        row_data[8] = "I" + String(num);
                        row_data[9] = "J" + String(num);
                        row_data[10] = "K" + String(num);
                        row_data[11] = "L" + String(num);
                        row_data[12] = "M" + String(num);
                        
                        var arry_row = new Array();
                        var count = 0;
                        for (var i of row_data) {
                            if (datas[i]) {
                                arry_row[count] = datas[i].v.replace(/\s*/g,"");
                                count += 1;
                            }
                        }
                        // 找到正确数据的列名
                        if (arry_row.indexOf("单位") < 0 || arry_row.indexOf("数量") < 0) {
                            for (var i = 0; i < row_data.length; i++) {
                                delete datas[row_data[i]];
                            }
                        }
                        else {
                            // 修改读取范围
                            datas["!ref"] = row_data[0] + ":" + datas['!ref'].split(':')[1];
                            break;
                        }

                    }
                    persons = persons.concat(XLSX.utils.sheet_to_json(datas));
                    break; // 只读了第一张表
                }
            }
            console.log(persons);
        };
        // 以二进制方式打开文件
        fileReader.readAsBinaryString(files[0]);
    });
</script>

</html>

对前端不怎么熟悉，代码比较乱，仅供参考