shixixiyue-ExcelHelp
/
index.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>前端导入Excel处理</title>
    <link rel="stylesheet" href="css/bootstrap.min.css">
    <link rel="stylesheet" href="css/lumen.css">
    <link rel="stylesheet" href="css/bootstrapEx.css">
    <link href="js/json-viewer/css/jquery.json-viewer.css" type="text/css" rel="stylesheet" />


    <script src="js/jquery-1.9.1.min.js"></script>
    <script src="js/jquery.json-viewer.js"></script>
    <script type="text/javascript" src="js/global.js"></script>
    <script type="text/javascript" src="js/from/jquery.smart-form.js"></script>
    <script type="text/javascript" src="js/bootstrap.min.js"></script>
    <script type="text/javascript" src="js/bootstrapEx2.0.js"></script>
    <script type="text/javascript" src="js/excel/xlsx.core.min.js"></script>
    <script type="text/javascript" src="js/excelHelp.js"></script>
    <script type="text/javascript" src="js/index.js"></script>
    <script>
        //注册按钮
        var interfaceData = [{
            id:"btnDemo",text:"DemoExcel下载",onclick:function(){
                var link = document.createElement("a");
                link.href = 'http://gitee.com/shixixiyue/ExcelHelp/raw/master/TestData.xlsx';
                link.style = "visibility:hidden";
                document.body.appendChild(link);
                link.click();
                document.body.removeChild(link);
            }
        },{
            id:"btnSave",text:"DemoSQL导出"
        },{
            id:"btn2",text:"对比处理Demo(name匹配ID)"
        }];

         //按钮事件
         function btn2_onclick(){
            //读取Excel 回调返回数据 按sheet为数组,参数2 返回sheet名称的数组
            $._ExcelHelp.read(function(excelData,sheets){
                //第一页数据
                var sheet1 = excelData[0];
                //第二页数据
                var sheet2 = excelData[1];
                var newdata = [];
                //循环开始
                sheet2.forEach(function(item2){
                    //查询name相等行 找一个是find 找数组是filter
                   var rows = sheet1.find(function(item1){ return item2.name==item1.name });
                   if(rows && rows.code){
                        newdata.push(rows);
                   }
                })
                console.log(newdata);
                //导出结果
                $._ExcelHelp.out("结果",newdata,[])
            })
        }

        //新增 修改 SQL语句生成
        function btnSave_onclick(){
             //读取Excel 回调返回数据 按sheet为数组,参数2 返回sheet名称的数组
             $._ExcelHelp.read(function(excelData,sheets){
                var newdata = [];
                //循环页签 页签名称为表明
                sheets.forEach(function(sheetname,index){
                    //当前页签的数据
                    var data = excelData[index];
                    //循环行数据
                    data.forEach(function(_row){
                        var newrow = {};//新增行
                        var insertsql = " insert into  " + sheetname + " ( ";//准备新增语句
                        var updatesql = " update " + sheetname + " set ";//准备更新语句

                        var vals = [],cols=[],set=[],where="";//准备临时参数
                        //循环列
                        for(var ColumnName in _row){
                            //当前行列值
                            var _v = _row[ColumnName];
                            //拼写SQL
                            cols.push(ColumnName);
                            vals.push(_v);
                            set.push(ColumnName + "='"+ _v+"'");
                            if(where==""){
                                where = " where " + ColumnName + "='" + _v + "';";
                            }
                        }
                        //拼写SQL
                        insertsql += cols.join(",") +") values('";
                        insertsql += vals.join("','")+"')";
                        updatesql += set.join(",");
                        updatesql += where;

                        //本行拼写完成
                        newrow.insertsql = insertsql;
                        newrow.updatesql = updatesql;
                        newdata.push(newrow);
                    });
                })
                console.log(newdata);
                //导出结果
                $._ExcelHelp.out("SQL语句生成结果",newdata,[])
            })
        }


    </script>
</head>
<body>
        &nbsp;<span style='font-size:20px;font-weight:bold;'><a href="https://gitee.com/shixixiyue/ExcelHelp">Excel导入处理</a> By @北京-没想好</span>
         <p/>
        <div class="container-fluid">
            <p/>
            <div class="form-inline">
                <input type="text" id="fileurl"  class="form-control" style="width:500px">
                <button id="btnfile" class="btn btn-primary">选择Excel</button>
                <button id="btn_tool" class="btn btn-primary">工具</button>
            </div>
            <div id="btns">
            </div>
            <p>
        </div>
</body>

</html>