前端解析Excel文件js-xlsx与bootstrapTable

1.引入xlsx.js

<script src="https://cdn.bootcss.com/xlsx/0.11.5/xlsx.core.min.js"></script>

2.上传Excel按钮

<input type="button" value="上传Excel" onclick="$('#fileOne').click()" class="btn btn-primary" style="margin-right: -3px;">
<input type="text" id="showFileNameByExcel" disabled="disabled" readonly style="border: none; background-color: #F5F5F5;">
<input type="file" id="fileOne" name="fileOne" onchange="getFileNameByExcel(this.files)" class="form-input" style="display: none;margin-left: 15px"accept=".csv, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel"/>
<button class="pull-left" type="button" class="btn btn-info" style="display: inline-block;margin-left: -35px" onclick="destroy();">重置</button>

上传Excel按钮

3.读取Excel文件

var excel = [];

function getFileNameByExcel(files) {
    excel = [];
    //table销毁，否则会保留上次加载的内容
    $('#exampleTable').bootstrapTable('destroy');
    //处理文件名称，防止文件名过长
    var name = $("#fileOne").val().split("\\");
    name = name[name.length - 1];
    if (strlen(name) > 15) {
        name = name.substring(0,8)+"..."+name.split(".")[1]
        $("#showFileNameByExcel").val(name);
    } else {
        $("#showFileNameByExcel").val(name);
    }
    
    if (files.length) {
        var file = files[0];
        var reader = new FileReader();
        //读取Excel文件
        reader.onload = function (e) {
            var data = e.target.result;
            var workbook = XLSX.read(data, {type: 'binary'});
            var worksheet = workbook.Sheets[workbook.SheetNames[0]];
            jsonData = XLSX.utils.sheet_to_json(worksheet);
            tempNo = jsonData[1].__EMPTY;
            jsonData.splice(0, 3);
            $.each(jsonData, function (name, value) {
                var excelInfo = {};
                excelInfo["realname"] = value.结佣模板;
                excelInfo["idCard"] = value.__EMPTY;
                excelInfo["openBank"] = value.__EMPTY_1;
                excelInfo["bankAccount"] = value.__EMPTY_2;
                excelInfo["phone"] = value.__EMPTY_3;
                excelInfo["settleAmount"] = value.__EMPTY_4;
                excelInfo["packageAmount"] = value.__EMPTY_5;
                excel.push(excelInfo);
            });
            json();
        };
        reader.readAsBinaryString(file);
    }
}

//将读取的ExcelJson显示到表格
function json() {
    $('#exampleTable').bootstrapTable({
        data: excel,
        columns: [{
            field: 'no',
            align: 'center',
            title: '序号',
            formatter: function (value, row, index) {
                return index + 1;
            }
        }, {
            field: 'realname',
            align: 'center',
            title: '收款人姓名',
        }, {
            field: 'idCard',
            align: 'center',
            title: '收款人身份证',
        }, {
            field: 'openBank',
            align: 'center',
            title: '开户行',
        }, {
            field: 'bankAccount',
            align: 'center',
            title: '银行账号',
        }, {
            field: 'settleAmount',
            align: 'center',
            title: '结算金额',
        },{
            field: 'packageAmount',
            align: 'center',
            title: '含服务费金额',
        },]
    });
}

//获取文件名称长度,对汉字与字母数字处理
function strlen(str) {
    var len = 0;
    for (var i = 0; i < str.length; i++) {
        var c = str.charCodeAt(i);
        if ((c >= 0x0001 && c <= 0x007e) || (0xff60 <= c && c <= 0xff9f)) {
            len++;
        } else {
            len += 2;
        }
    }
    return len;
}

/**
 *重置excel
 **/
function uploadExcel() {
    var fileOne = document.getElementById("fileOne");
    fileOne.outerHTML = fileOne.outerHTML;
    $('#showFileNameByExcel').val("");
    $('#exampleTable').bootstrapTable('destroy');
}

4.结果