前端解析Excel文件-xlsx.core.min.js使用

需要的JS:

https://code.jquery.com/jquery-3.1.1.min.js

https://cdn.bootcss.com/xlsx/0.12.7/xlsx.core.min.js

读取Excel代码示例:

<!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>