excel导入到html和从html导出文件到excel

可参见@跃焱邵隼,里面很详细 还有后端方法。很方便

一:前端js导出(ie兼容不友好)

<!--
  var jsonList=[                //jsonList
           {
               "name":"小人",
               "id":"123",
               "爱好":"跑步"
           },
           {
               "name":"小人",
               "id":"5",
               "爱好":"跑步"
           },
           {
               "name":"小敏",
               "id":"49",
               "爱好":"篮球"
           },
           {
               "name":"小李",
               "id":"39",
               "爱好":"杀人"
           }
       ];

       /*********  excel ***********/
       var str1="<tr><td>name</td><td>id</td><td>爱好</td></tr>"; //头部模板
       $(".btn-1").click(function () {
           ys.toTableExcel(jsonList,str1);
       });

       /*********  csv ***********/
       var str2 = "name,id,爱好\n";
       $(".btn-2").click(function () {
           ys.toCSV(jsonList,str2);
       });

       /*********  LargerCsv(可以自增长序号) ***********/
       var str3='行号,名字,身份证,爱好\n';
       for(var i=0;i<jsonList.length;i++){
           str3 += i+','+jsonList[i]["name"]+','+jsonList[i]["id"]+','+jsonList[i]["爱好"]+'\n';
       }
       console.log(str3);
       $(".btn-3").click(function () {
           ys.toLargerCSV(str3);
       });
 

需要引入:js

ysExprots.js
xlsx.full.min.js

 

二:前端js导出(只支持ie包括ie8)

 <!--

<button type="button" onclick="method2('tableExcel')">导出Excel方法二</button>
<table id="tableExcel" width="100%" border="1" cellspacing="0" cellpadding="0">
    <tr>
        <td colspan="5" align="center">html 表格导出道Excel</td>
    </tr>
    <tr>
        <td>列标题1</td>
        <td>列标题2</td>
        <td>类标题3</td>
        <td>列标题4</td>
        <td>列标题5</td>
    </tr>
    <tr>
        <td>aaa</td>
        <td>bbb</td>
        <td>ccc</td>
        <td>ddd</td>
        <td>eee</td>
    </tr>
    <tr>
        <td>AAA</td>
        <td>BBB</td>
        <td>CCC</td>
        <td>DDD</td>
        <td>EEE</td>
    </tr>
    <tr>
        <td>FFF</td>
        <td>GGG</td>
        <td>HHH</td>
        <td>III</td>
        <td>JJJ</td>
    </tr>
</table>

<script>
    function method2(tableid)
    {

        var curTbl = document.getElementById(tableid);
        var oXL = new ActiveXObject("Excel.Application");
        var oWB = oXL.Workbooks.Add();
        var oSheet = oWB.ActiveSheet;
        var Lenr = curTbl.rows.length;
        for (i = 0; i < Lenr; i++)
        {        var Lenc = curTbl.rows(i).cells.length;
            for (j = 0; j < Lenc; j++)
            {
                oSheet.Cells(i + 1, j + 1).value = curTbl.rows(i).cells(j).innerText;

            }

        }
        oXL.Visible = true;
    }
</script>
 

三:前端js导入(ie兼容不友好)

<!--
html:
<input type="file" onchange="importExcel(this,false,function(data){getJsonData(data)})" class="upload-input" />

js:
//监听上传文件;
function importExcel(obj,rABS,callback) {
        var wb;
        if(!obj.files) {
            return;
        }

        const IMPORTFILE_MAXSIZE = 2*1024;//这里可以自定义控制导入文件大小
        var suffix = obj.files[0].name.split(".")[1];

        if(suffix != 'xls' && suffix !='xlsx'){
            alert('导入的文件格式不正确!');
            return
        }
        if(obj.files[0].size/1024 > IMPORTFILE_MAXSIZE){
            alert('导入的表格文件不能大于2M');
            return
        }

        var f = obj.files[0];
        var reader = new FileReader();
        reader.onload = function(e) {
            var data = e.target.result;
            if(rABS) {
                wb = XLSX.read(btoa(fixdata(data)), {//手动转化
                    type: 'base64'
                });
            } else {
                wb = XLSX.read(data, {
                    type: 'binary'
                });
            }
            var jsonData=JSON.stringify( XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]) );

            callback(jsonData);

        };// reader.onload闭合
        if(rABS) {
            reader.readAsArrayBuffer(f);
        } else {
            reader.readAsBinaryString(f);
        }
}

function getJsonData(jsonData) {
      console.log(jsonData);  //这里通过回调函数获取到data;
}

 -->

需要引入:js
ysExprots.js
xlsx.full.min.js

-->