欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

js 实现下载当前页table数据(Excel)

程序员文章站 2024-03-20 16:26:58
...

下载方法1需要引入js,方法2不需要

js

/*
 *  jQuery table2excel - v1.1.1
 *  jQuery plugin to export an .xls file in browser from an HTML table
 *  https://github.com/rainabba/jquery-table2excel
 *
 *  Made by rainabba
 *  Under MIT License
 */
!function(a,b,c,d){function e(b,c){this.element=b,this.settings=a.extend({},k,c),this._defaults=k,this._name=j,this.init()}function f(a){return a.filename?a.filename:"table2excel"}function g(a){var b=/(\s+alt\s*=\s*"([^"]*)"|\s+alt\s*=\s*'([^']*)')/i;return a.replace(/<img[^>]*>/gi,function(a){var c=b.exec(a);return null!==c&&c.length>=2?c[2]:""})}function h(a){return a.replace(/<a[^>]*>|<\/a>/gi,"")}function i(a){var b=/(\s+value\s*=\s*"([^"]*)"|\s+value\s*=\s*'([^']*)')/i;return a.replace(/<input[^>]*>|<\/input>/gi,function(a){var c=b.exec(a);return null!==c&&c.length>=2?c[2]:""})}var j="table2excel",k={exclude:".noExl",name:"Table2Excel",filename:"table2excel",fileext:".xls",exclude_img:!0,exclude_links:!0,exclude_inputs:!0};e.prototype={init:function(){var b=this;b.template={head:'<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><meta http-equiv="content-type" content="application/vnd.ms-excel; charset=UTF-8"><head>\x3c!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets>',sheet:{head:"<x:ExcelWorksheet><x:Name>",tail:"</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet>"},mid:"</x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--\x3e</head><body>",table:{head:"<table border='1'>",tail:"</table>"},foot:"</body></html>"},b.tableRows=[],a(b.element).each(function(c,d){var e="";a(d).find("tr").not(b.settings.exclude).each(function(c,d){e+="<tr>",a(d).find("td,th").not(b.settings.exclude).each(function(c,d){var f={rows:a(this).attr("rowspan"),cols:a(this).attr("colspan"),flag:a(d).find(b.settings.exclude)};f.flag.length>0?e+="<td> </td>":f.rows&f.cols?e+="<td>"+a(d).html()+"</td>":(e+="<td",f.rows>0&&(e+=" rowspan='"+f.rows+"' "),f.cols>0&&(e+=" colspan='"+f.cols+"' "),e+="/>"+a(d).html()+"</td>")}),e+="</tr>",console.log(e)}),b.settings.exclude_img&&(e=g(e)),b.settings.exclude_links&&(e=h(e)),b.settings.exclude_inputs&&(e=i(e)),b.tableRows.push(e)}),b.tableToExcel(b.tableRows,b.settings.name,b.settings.sheetName)},tableToExcel:function(d,e,g){var h,i,j,k=this,l="";if(k.format=function(a,b){return a.replace(/{(\w+)}/g,function(a,c){return b[c]})},g=void 0===g?"Sheet":g,k.ctx={worksheet:e||"Worksheet",table:d,sheetName:g},l=k.template.head,a.isArray(d))for(h in d)l+=k.template.sheet.head+g+h+k.template.sheet.tail;if(l+=k.template.mid,a.isArray(d))for(h in d)l+=k.template.table.head+"{table"+h+"}"+k.template.table.tail;l+=k.template.foot;for(h in d)k.ctx["table"+h]=d[h];if(delete k.ctx.table,!c.documentMode){var m=new Blob([k.format(l,k.ctx)],{type:"application/vnd.ms-excel"});b.URL=b.URL||b.webkitURL,i=b.URL.createObjectURL(m),j=c.createElement("a"),j.download=f(k.settings),j.href=i,c.body.appendChild(j),j.click(),c.body.removeChild(j)}else if("undefined"!=typeof Blob){l=k.format(l,k.ctx),l=[l];var n=new Blob(l,{type:"text/html"});b.navigator.msSaveBlob(n,f(k.settings))}else txtArea1.document.open("text/html","replace"),txtArea1.document.write(k.format(l,k.ctx)),txtArea1.document.close(),txtArea1.focus(),sa=txtArea1.document.execCommand("SaveAs",!0,f(k.settings));return!0}},a.fn[j]=function(b){var c=this;return c.each(function(){a.data(c,"plugin_"+j)||a.data(c,"plugin_"+j,new e(this,b))}),c}}(jQuery,window,document);

html文件

<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>excel下载</title>
    <script src="../js/jquery.min.js"></script>
    <script src="../js/excel.min.js"></script>
</head>
<body>
<input type="button" id="upload" value="下载(方法1)"/>
<a class="statistics-download" onclick="upload('upload')">下载(方法2</a>
<table id="tableId"  class="table table-striped">
    <tr>
        <th style="vertical-align: inherit; border: 0px;" class="th">课程名称</th>
        <th style="vertical-align: inherit; border: 0px;" class="th">上课时间</th>
        <th style="vertical-align: inherit; border: 0px;" class="th">上课地点</th>
        <th style="vertical-align: inherit; border: 0px;" class="th">课程类型</th>
        <th style="vertical-align: inherit; border: 0px;" class="th">主讲人</th>
        <th style="vertical-align: inherit; border: 0px;" class="th">预约</th>
        <th style="vertical-align: inherit; border: 0px;" class="th">预约实到</th>
        <th style="vertical-align: inherit; border: 0px;" class="th">未预约听课</th>
        <th style="vertical-align: inherit; border: 0px;" class="th">总参与</th>
    </tr>
    <tr>
        <td>1</td>
        <td>1</td>
        <td>1</td>
        <td >1</td>
        <td>1</td>
        <td>1</td>
        <td>1</td>
        <td>1</td>
        <td>1</td>
    </tr>
    <tr>
        <td>2</td>
        <td>2</td>
        <td>2</td>
        <td>2</td>
        <td>2</td>
        <td>2<td>
        <td>2</td>
        <td>2</td>
        <td>2</td>
    </tr>
</table>
</body>
</html>
<script >
    $("#upload").click(function () {
        alert("下载数据(方法1)");
        $("#tableId").table2excel({
            exclude: ".excludeThisClass",
            name: "课程统计",
            filename: "课程统计.xls" //do not include extension
        });
    })


    var idTmr;

    function getExplorer() {
        var explorer = window.navigator.userAgent;
        //ie
        if (explorer.indexOf("MSIE") >= 0) {
            return 'ie';
        }
        //firefox
        else if (explorer.indexOf("Firefox") >= 0) {
            return 'Firefox';
        }
        //Chrome
        else if (explorer.indexOf("Chrome") >= 0) {
            return 'Chrome';
        }
        //Opera
        else if (explorer.indexOf("Opera") >= 0) {
            return 'Opera';
        }
        //Safari
        else if (explorer.indexOf("Safari") >= 0) {
            return 'Safari';
        }
    }

    function upload(tableid,name="1.xlsx") { //整个表格拷贝到EXCEL中
        alert("下载数据(方法2)");
        if (getExplorer() == 'ie') {
            var curTbl = document.getElementById(tableid);
            var oXL = new ActiveXObject("Excel.Application");

            //创建AX对象excel
            var oWB = oXL.Workbooks.Add();
            //获取workbook对象
            var xlsheet = oWB.Worksheets(1);
            //**当前sheet
            var sel = document.body.createTextRange();
            sel.moveToElementText(curTbl);
            //把表格中的内容移到TextRange中
            sel.select;
            //全选TextRange中内容
            sel.execCommand("Copy");
            //复制TextRange中内容
            xlsheet.Paste();
            //粘贴到活动的EXCEL中
            oXL.Visible = true;
            //设置excel可见属性

            try {
                var fname = oXL.Application.GetSaveAsFilename("Excel.xls", "Excel Spreadsheets (*.xls), *.xls");
            } catch (e) {
                print("Nested catch caught " + e);
            } finally {
                oWB.SaveAs(fname);

                oWB.Close(savechanges = false);
                //xls.visible = false;
                oXL.Quit();
                oXL = null;
                //结束excel进程,退出完成
                //window.setInterval("Cleanup();",1);
                idTmr = window.setInterval("Cleanup();", 1);

            }

        } else {
            tableToExcel(tableid)
        }
    }

    function Cleanup() {
        window.clearInterval(idTmr);
        CollectGarbage();
    }
    var tableToExcel = (function () {
        var uri = 'data:application/vnd.ms-excel;base64,',
            template =
                '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table border="1">{table}</table></body></html>',
            base64 = function (s) {
                return window.btoa(unescape(encodeURIComponent(s)))
            },
            format = function (s, c) {
                return s.replace(/{(\w+)}/g,
                    function (m, p) {
                        return c[p];
                    })
            }
        return function (table) {
            if (!table.nodeType) table = document.getElementById("tableId");
            var ctx = {
                worksheet: name || 'Worksheet',
                table: table.innerHTML
            }
            $('.statistics-download').attr('href',uri + base64(format(template, ctx)));
            $('.statistics-download').attr('download',"孕妇听课统计数据统计.xls");
        }
    })();
</script>