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>

上一篇：基于el-table的树形表格及js-xlsx实现下载excel功能（二）

下一篇：简单了解HttpServer的使用

js 实现下载当前页table数据（Excel）

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

js

html文件

基于el-table的树形表格及js-xlsx实现下载excel功能（二）

js 实现下载当前页table数据（Excel）

OA (ssh) 基本实现(poi 生成 Excel , struts2动态下载 mysql数据

js局部刷新当前页面（js实现局部刷新数据）

js局部刷新当前页面（js实现局部刷新数据）

js与jQuery实现获取table中的数据并拼成json字符串操作示例

JSP实现从数据库导出数据到Excel下载的方法

JS实现点击Radio动态更新table数据

PHP实现实时生成并下载超大数据量的EXCEL文件详解

结合bootstrap fileinput插件和Bootstrap-table表格插件，实现文件上传、预览、提交的导入Excel数据操作流程