SOURCE

console 命令行工具 X clear 

                    

                        
>

                        
                    

                

            

            
console

            
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Excel批量处理</title>
    <script src="https://cdn.bootcss.com/jquery/3.2.1/jquery.js"></script>
    <script src="https://cdn.bootcss.com/xlsx/0.11.5/xlsx.core.min.js"></script>
</head>
<body>
<input type="file" id="excel-file">
<textarea id="area"></textarea>
<table id="tabShow" border="1" cellspacing="0" style="font-size:9px;">
    <tr>
        <td>跟团号</td>
        <td>下单人</td>
        <td>团员备注</td>
        <td>商品</td>
        <td>订单金额</td>
        <td>退款金额</td>
        <td>订单状态</td>
        <td>自提点</td>
        <td>收货人</td>
        <td>联系电话</td>
        <td>详细地址</td>
        <td>格式化地址</td>
    </tr>
</table>
<script>
$(document).ready(function(){

    //中文数字转阿拉伯数字
    var numCtoA = function(str){
            var addStr = str;
            var nums = [
                {"cnum":"三十", "anum":30},
                {"cnum":"二十九", "anum":29},
                {"cnum":"二十八", "anum":28},
                {"cnum":"二十七", "anum":27},
                {"cnum":"二十六", "anum":26},
                {"cnum":"二十五", "anum":25},
                {"cnum":"二十四", "anum":24},
                {"cnum":"二十三", "anum":23},
                {"cnum":"二十二", "anum":22},
                {"cnum":"二十一", "anum":21},
                {"cnum":"二十", "anum":20},
                {"cnum":"十九", "anum":19},
                {"cnum":"十八", "anum":18},
                {"cnum":"十七", "anum":17},
                {"cnum":"十六", "anum":16},
                {"cnum":"十五", "anum":15},
                {"cnum":"十四", "anum":14},
                {"cnum":"十三", "anum":13},
                {"cnum":"十二", "anum":12},
                {"cnum":"十一", "anum":11},
                {"cnum":"十", "anum":10},
                {"cnum":"九", "anum":9},
                {"cnum":"八", "anum":8},
                {"cnum":"七", "anum":7},
                {"cnum":"六", "anum":6},
                {"cnum":"五", "anum":5},
                {"cnum":"四", "anum":4},
                {"cnum":"三", "anum":3},
                {"cnum":"二", "anum":2},
                {"cnum":"一", "anum":1}
            ];

            for(var i = 0; i < nums.length; i++){
                if(addStr.includes(nums[i]["cnum"])){
                    addStr = addStr.replace(nums[i]["cnum"], nums[i]["anum"]);
                    i--;
                }
            }

            return addStr;
    }

    //楼号单元号格式化
    var addFormat = function(str){
            var formatStr = str;
            var formatList = [
                {"hz":"号楼", "fh":"-"},
                {"hz":"楼", "fh":"-"},
                {"hz":"栋", "fh":"-"},
                {"hz":"洞", "fh":"-"},
                {"hz":"幢", "fh":"-"},
                {"hz":"号", "fh":"-"},
                {"hz":"单元", "fh":"-"},
                {"hz":" ", "fh":""},
                {"hz":" ", "fh":""}
            ];

            for(var i = 0; i < formatList.length; i++){
                if(formatStr.includes(formatList[i]["hz"])){
                    formatStr = formatStr.replace(formatList[i]["hz"], formatList[i]["fh"]);
                    i--;
                }
            }

            return formatStr;
    }


//给input标签绑定change事件，一上传选中的.xls文件就会触发该函数
    $('#excel-file').change(function(e) {

        var files = e.target.files;
        var fileReader = new FileReader();	
        var persons = []; // 存储获取到的数据

        fileReader.onload = function(ev) {
            try {
                var data = ev.target.result
                var workbook = XLSX.read(data, {
                    type: 'binary'
                }) // 以二进制流方式读取得到整份excel表格对象
            } catch (e) {
                console.log('文件类型不正确');
                return;
            }

            // 表格的表格范围，可用于判断表头是否数量是否正确
            var fromTo = '';
            // 遍历每张表读取
            for (var sheet in workbook.Sheets) {
                if (workbook.Sheets.hasOwnProperty(sheet)) {
                    fromTo = workbook.Sheets[sheet]['!ref'];
                    console.log(fromTo);
                    persons = persons.concat(XLSX.utils.sheet_to_json(workbook.Sheets[sheet]));
                    break; // 如果只取第一张表，就取消注释这行
                }
            }
            //在控制台打印出来表格中的数据
            //console.log(persons);
            $("#area").val(JSON.stringify(persons));

            for(var i=0; i<persons.length; i++){
                var addFromStr = persons[i]["详细地址"];
                addFromStr = numCtoA(addFromStr);
                addFromStr = addFormat(addFromStr);

                if(addFromStr.indexOf("期") > -1){
                    addFromStr = addFromStr.substr(addFromStr.indexOf("期") + 1);
                }else if(addFromStr.indexOf("元") > -1){
                    addFromStr = addFromStr.substr(addFromStr.indexOf("元") + 1);
                }else{
                    addFromStr = "";
                }

                persons[i]["格式化地址"] = addFromStr;

                var newTr = "<tr>"
                +"<td>"+persons[i]["跟团号"]+"</td>"
                +"<td>"+persons[i]["下单人"]+"</td>"
                +"<td>"+persons[i]["团员备注"]+"</td>"
                +"<td>"+persons[i]["商品"]+"</td>"
                +"<td>"+persons[i]["订单金额"]+"</td>"
                +"<td>"+persons[i]["退款金额"]+"</td>"
                +"<td>"+persons[i]["订单状态"]+"</td>"
                +"<td>"+persons[i]["自提点"]+"</td>"
                +"<td>"+persons[i]["收货人"]+"</td>"
                +"<td>"+persons[i]["联系电话"]+"</td>"
                +"<td>"+persons[i]["详细地址"]+"</td>"
                +"<td>"+persons[i]["格式化地址"]+"</td>"
                +"</tr>"

                $("#tabShow").append(newTr);
            }           
        };

        // 以二进制方式打开文件
        fileReader.readAsBinaryString(files[0]);
    });
});
</script>
</body>
</html>