js实现把网页table导成Excel

时间：2022-02-16 09:15:51

方案一：支持IE

 //导出excel

function exportExcel(DivID,strTitle){

    if(DivID==null)

    {

    return false;

    }

    var jXls, myWorkbook, myWorksheet;

    try {

        jXls = new ActiveXObject('Excel.Application');

    }

    catch (e) {

        alert("无法启动Excel!\n\n如果您确信您的电脑中已经安装了Excel，"+"那么请调整IE的安全级别。\n\n具体操作：\n\n"+"工具 → Internet选项 → 安全 → 自定义级别 → 对

没有标记为安全的ActiveX进行初始化和脚本运行 → 启用");

        return false;

    }

    jXls.DisplayAlerts = false;

    myWorkbook = jXls.Workbooks.Add();

    var curTb = document.getElementById(DivID);

    myWorksheet = myWorkbook.ActiveSheet;

    myWorksheet.name=strTitle;

    var sel = document.body.createTextRange();

    sel.moveToElementText(curTb);

    sel.select();

    window.clipboardData.setData('text','');

    sel.execCommand("Copy");

    myWorksheet.Paste();

    jXls.Visible = true;

     try{

        var fname = jXls.Application.GetSaveAsFilename("OA数据"+strTitle+".xls", "Excel Spreadsheets (*.xls), *.xls");

        }catch(e){

        print("Nested catch caught " + e);

    }

    finally{

        if(fname!=false)

        {

             myWorkbook .SaveAs(fname);

            alert("数据成功保存在:"+fname);

        }

     }

     //   myWorkbook .Close(savechanges=false);

     // jXls.Quit();

    window.clipboardData.setData('text','');

    jXls = null;

    myWorkbook = null;

    myWorksheet = null;

}
<table class="oa-el-grid-list" id="tb" style="width:99%" cellspacing="0" cellpadding="0"

            border="0">

            <thead>

                <%=tab_html%>

            </thead>

            <%=TableHtml %>

        </table>

  <input id="btnSavExcel" onclick="exportExcel('tb','……表')" type="button" value="导出"  />

方案2

<input type="button" onclick="tableToExcel('tablename', 'name')" value="Export to Excel">
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>{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, name) {

if (!table.nodeType) table = document.getElementById(table)

var ctx = {worksheet: name || 'Worksheet', table: table.innerHTML}

window.location.href = uri + base64(format(template, ctx))

}

})()

方案3

<a id="dlink"  style="display:none;"></a>

<input type="button" onclick="tableToExcel('tablename', 'name', 'myfile.xls')" value="Export to Excel">
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>{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, name, filename) {

            if (!table.nodeType) table = document.getElementById(table)

            var ctx = { worksheet: name || 'Worksheet', table: table.innerHTML }

            document.getElementById("dlink").href = uri + base64(format(template, ctx));

            document.getElementById("dlink").download = filename;

            document.getElementById("dlink").click();

        }

    })()

支持：Chrome、Firefox、Opera、Safari

根据Json导出Excel

; !function () {

    layui.use(['layer', 'element'], function () {

    var tableTitle = [{ "value": "行政区", "type": "ROW_HEADER_HEADER", "datatype": "string" }, { "value": "留守儿童(人)", "type": "ROW_HEADER_HEADER", "datatype": "string" }, { "value": "困境儿童(人)", "type": "ROW_HEADER_HEADER", "datatype": "string" },{"value": "合计(人)", "type": "ROW_HEADER_HEADER", "datatype": "string" }]

    $(".querynav a:eq(2)").click(function (param) {

        var tableData=[];

        layer.confirm("确认导出当前页数据？",function(params) {

                $.each($(".tableBody tr"),function (i,lay) {

                    var data=[];

                     layer.closeAll();

                    for(i=0;i<$(this).children().length;i++){

                        var a="{value:'"+$(this).children().eq(i).text()+"',type:'ROW_HEADER'}";

                        data.push( eval("(" + a + ")"));

                    }

                    tableData.push(data);

                    console.log(data.toString());

                });

                console.log(tableData.toString());

                var cityName="";

                if ($("#Town").val() != "") {

                    civilregionalismcode += 'civilregionalismcode=' + $("#Town").val();

                }

                else if ($("#Country").val() != "") {

                    civilregionalismcode += 'civilregionalismcode=' + $("#Country").val();

                }

                else if ($("#city").val() != "") {

                    civilregionalismcode += 'civilregionalismcode=' + $("#city").val();

                }

                else if ($("#province").val() != "") {

                    civilregionalismcode += 'civilregionalismcode=' + $("#province").val();

                }

                // return;

                JSONToExcelConvertor(tableData, "测试数据", tableTitle)

        })

    });

     function JSONToExcelConvertor(JSONData, FileName, ShowLabel) {

            //先转化json

            var arrData = typeof JSONData != 'object' ? JSON.parse(JSONData) : JSONData;  

            var excel = '<table>';      

            //设置表头

            var row = "<tr>";

            for (var i = 0, l = ShowLabel.length; i < l; i++) {

                row += "<td>" + ShowLabel[i].value + '</td>';

            }  

            //换行

            excel += row + "</tr>";

            console.log(arrData);

        //   return;

            //设置数据

            for (var i = 0; i < arrData.length; i++) {

                var row = "<tr>";  

                for (var index in arrData[i]) {

                    var value = arrData[i][index].value === "." ? "" : arrData[i][index].value;

                    row += '<td>' + value + '</td>';

                }  

                excel += row + "</tr>";

            }  

            excel += "</table>";  

            var excelFile = "<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'>";

            excelFile += '<meta http-equiv="content-type" content="application/vnd.ms-excel; charset=UTF-8">';

            excelFile += '<meta http-equiv="content-type" content="application/vnd.ms-excel';

            excelFile += '; charset=UTF-8">';

            excelFile += "<head>";

            excelFile += "<!--[if gte mso 9]>";

            excelFile += "<xml>";

            excelFile += "<x:ExcelWorkbook>";

            excelFile += "<x:ExcelWorksheets>";

            excelFile += "<x:ExcelWorksheet>";

            excelFile += "<x:Name>";

            excelFile += "{worksheet}";

            excelFile += "</x:Name>";

            excelFile += "<x:WorksheetOptions>";

            excelFile += "<x:DisplayGridlines/>";

            excelFile += "</x:WorksheetOptions>";

            excelFile += "</x:ExcelWorksheet>";

            excelFile += "</x:ExcelWorksheets>";

            excelFile += "</x:ExcelWorkbook>";

            excelFile += "</xml>";

            excelFile += "<![endif]-->";

            excelFile += "</head>";

            excelFile += "<body>";

            excelFile += excel;

            excelFile += "</body>";

            excelFile += "</html>";  

            var uri = 'data:application/vnd.ms-excel;charset=utf-8,' + encodeURIComponent(excelFile);  

            var link = document.createElement("a");

            link.href = uri;  

            link.style = "visibility:hidden";

            link.download = FileName + ".xls";  

            document.body.appendChild(link);

            link.click();

            document.body.removeChild(link);

        }  

    })

} ()

方案4：

/*将JqGrid导出Excel（中文）*/

<script src="/UILib/tableExport.jquery.plugin-master/libs/FileSaver/FileSaver.min.js"></script>

<script src="/UILib/tableExport.jquery.plugin-master/tableExport.min.js"></script>

<script type="text/javascript">

//导出数据

function ExportData() {

    var tbId = "datagrid";

    //var $tb = $("#" + tbId).clone();

    var $tbst = $("table[aria-labelledby='gbox_" + tbId + "']");

    $tbst.find("td:hidden").remove();

    var $tbs = $tbst.clone();

    $tbs.find("span").remove();

    var $tb;

    if ($tbs.length > 1) {

        var $tbody = $($tbs[1]).children("tbody");

        $tbody.children("tr[class='jqgfirstrow']").remove();

        $($tbs[0]).append($tbody);

        $tb = $($tbs[0]);

        //$tb.children("tbody tr[class='jqgfirstrow']").remove();

        $tb.children("thead").children("tr[class='jqg-first-row-header']");

        $tb.attr("id", "tb_Temp_Export");

        //$tb.hide();//隐藏之后出现导出为空

        var $div = $("<div style='width:0px;height:0px;overflow:hidden;z-index:-1;'></div>").append($tb);

        $(document.body).append($div);

        var tbRepName = "数据报表";

        var exName = tbRepName;//+ "(" + $('#txtSTime').val() + ")";

        $tb.tableExport({

            fileName: exName, type: 'excel', worksheetName: [tbRepName]

           , mso: {

               styles: ['background-color', 'background', 'color', 'font-family', 'font-size', 'font-weight', 'text-align', 'height', 'width']

           }

        });

        setTimeout(function () {

            $div.remove();

        }, 2000);

    } else {

        alert("操作失败，请刷新后重试！");

    }

}

</script>

注：加入styles样式时，可能会导出报错

多个Table导出多个Sheet

<html lang="en">

<head>

    <meta charset="UTF-8">

    <title>ExportTablesToExcel</title>

</head>

<style>

    #tabDiv1,#tabDiv2,#tabDiv3{border:1px solid pink;margin:10px auto;width:100%; }

    button{width:100%;}

</style>

<body>

<div id="tablesDiv">

    <table id="tabDiv1">

        <tr>

            <td>ID</td>

            <td>姓名</td>

            <td>年龄</td>

        </tr>

        <tr>

            <td>0001</td>

            <td>张三</td>

            <td>24</td>

        </tr>

    </table>

    <table id="tabDiv2">

        <tr>

            <td>ID</td>

            <td>姓名</td>

            <td>年龄</td>

        </tr>

        <tr>

            <td>0002</td>

            <td>李四</td>

            <td>24</td>

        </tr>

    </table>

    <table id="tabDiv3">

        <tr>

            <td>ID</td>

            <td>姓名</td>

            <td>年龄</td>

        </tr>

        <tr>

            <td>0003</td>

            <td>王五</td>

            <td>24</td>

        </tr>

    </table>

    <button οnclick="exp();">export to excel...</button>

</div>

</body>

<script>

    function exp(){

        tablesToExcel(['tabDiv1','tabDiv2','tabDiv3'], ['sheet1','sheet2','sheet3'], "testExport.xls", "Excel");

    }

    //导出excel包含多个sheet

    //tables:tableId的数组;wsbames:sheet的名字数组;wbname:工作簿名字;appname:Excel

function tablesToExcel(tables, wsnames, wbname, appname){

        var uri = 'data:application/vnd.ms-excel;base64,'

, tmplWorkbookXML = '<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?><Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">'

+ '<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"><Author>Axel Richter</Author><Created>{created}</Created></DocumentProperties>'

+ '<Styles>'

+ '<Style ss:ID="Currency"><NumberFormat ss:Format="Currency"></NumberFormat></Style>'

+ '<Style ss:ID="Date"><NumberFormat ss:Format="Medium Date"></NumberFormat></Style>'

+ '</Styles>'

+ '{worksheets}</Workbook>'

, tmplWorksheetXML = '<Worksheet ss:Name="{nameWS}"><Table>{rows}</Table></Worksheet>'

, tmplCellXML = '<Cell{attributeStyleID}{attributeFormula}><Data ss:Type="{nameType}">{data}</Data></Cell>'

, base64 = function(s) { return window.btoa(unescape(encodeURIComponent(s))) }

            , format = function(s, c) { return s.replace(/{(\w+)}/g, function(m, p) { return c[p]; }) }

        var ctx = "";

        var workbookXML = "";

        var worksheetsXML = "";

        var rowsXML = "";

        for (var i = 0; i < tables.length; i++) {

            if (!tables[i].nodeType) tables[i] = document.getElementById(tables[i]);

//           控制要导出的行数

for (var j = 0; j < tables[i].rows.length; j++) {

                rowsXML += '<Row>';

                for (var k = 0; k < tables[i].rows[j].cells.length; k++) {

                    var dataType = tables[i].rows[j].cells[k].getAttribute("data-type");

                    var dataStyle = tables[i].rows[j].cells[k].getAttribute("data-style");

                    var dataValue = tables[i].rows[j].cells[k].getAttribute("data-value");

                    dataValue = (dataValue)?dataValue:tables[i].rows[j].cells[k].innerHTML;

                    var dataFormula = tables[i].rows[j].cells[k].getAttribute("data-formula");

                    dataFormula = (dataFormula)?dataFormula:(appname=='Calc' && dataType=='DateTime')?dataValue:null;

                    ctx = {  attributeStyleID: (dataStyle=='Currency' || dataStyle=='Date')?' ss:StyleID="'+dataStyle+'"':''

, nameType: (dataType=='Number' || dataType=='DateTime' || dataType=='Boolean' || dataType=='Error')?dataType:'String'

, data: (dataFormula)?'':dataValue

, attributeFormula: (dataFormula)?' ss:Formula="'+dataFormula+'"':''

};

                    rowsXML += format(tmplCellXML, ctx);

                }

                rowsXML += '</Row>'

}

            ctx = {rows: rowsXML, nameWS: wsnames[i] || 'Sheet' + i};

            worksheetsXML += format(tmplWorksheetXML, ctx);

            rowsXML = "";

        }

        ctx = {created: (new Date()).getTime(), worksheets: worksheetsXML};

        workbookXML = format(tmplWorkbookXML, ctx);

//       查看后台的打印输出

        //console.log(workbookXML);

var link = document.createElement("A");

        link.href = uri + base64(workbookXML);

        link.download = wbname || 'Workbook.xls';

        link.target = '_blank';

        document.body.appendChild(link);

        link.click();

        document.body.removeChild(link);

    }

</script>

</html>

输出内容：

<?xml version="1.0" ?>

<?mso-application progid="Excel.Sheet" ?>

    <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">

        <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">

            <Author>

                hy

            </Author>

            <Created>

                hy31337

            </Created>

        </DocumentProperties>

        <Styles>

            <Style ss:ID="Currency">

                <NumberFormat ss:Format="Currency">

                </NumberFormat>

            </Style>

            <Style ss:ID="Date">

                <NumberFormat ss:Format="Medium Date">

                </NumberFormat>

            </Style>

        </Styles>

        <Worksheet ss:Name="sheet1">

            <Table>

                <Row>

                    <Cell>

                        <Data ss:Type="String">

                            ID

                        </Data>

                    </Cell>

                    <Cell>

                        <Data ss:Type="String">

                            姓名

                        </Data>

                    </Cell>

                    <Cell>

                        <Data ss:Type="String">

                            年龄

                        </Data>

                    </Cell>

                </Row>

                <Row>

                    <Cell>

                        <Data ss:Type="String">

                            0001

                        </Data>

                    </Cell>

                    <Cell>

                        <Data ss:Type="String">

                            张三

                        </Data>

                    </Cell>

                    <Cell>

                        <Data ss:Type="String">

                            24

                        </Data>

                    </Cell>

                </Row>

            </Table>

        </Worksheet>

        <Worksheet ss:Name="sheet2">

            <Table>

                <Row>

                    <Cell>

                        <Data ss:Type="String">

                            ID

                        </Data>

                    </Cell>

                    <Cell>

                        <Data ss:Type="String">

                            姓名

                        </Data>

                    </Cell>

                    <Cell>

                        <Data ss:Type="String">

                            年龄

                        </Data>

                    </Cell>

                </Row>

                <Row>

                    <Cell>

                        <Data ss:Type="String">

                            0002

                        </Data>

                    </Cell>

                    <Cell>

                        <Data ss:Type="String">

                            李四

                        </Data>

                    </Cell>

                    <Cell>

                        <Data ss:Type="String">

                            24

                        </Data>

                    </Cell>

                </Row>

            </Table>

        </Worksheet>

        <Worksheet ss:Name="sheet3">

            <Table>

                <Row>

                    <Cell>

                        <Data ss:Type="String">

                            ID

                        </Data>

                    </Cell>

                    <Cell>

                        <Data ss:Type="String">

                            姓名

                        </Data>

                    </Cell>

                    <Cell>

                        <Data ss:Type="String">

                            年龄

                        </Data>

                    </Cell>

                </Row>

                <Row>

                    <Cell>

                        <Data ss:Type="String">

                            0003

                        </Data>

                    </Cell>

                    <Cell>

                        <Data ss:Type="String">

                            王五

                        </Data>

                    </Cell>

                    <Cell>

                        <Data ss:Type="String">

                            24

                        </Data>

                    </Cell>

                </Row>

            </Table>

        </Worksheet>

    </Workbook>

其它案例：

HTML用JS导出Excel的五种方法

细说JavaScript 导出 上万条Excel数据

git_demo

https://github.com/kayalshri/tableExport.jquery.plugin/blob/master/tableExport.js

https://github.com/hhurz/tableExport.jquery.plugin

https://github.com/wenzhixin/bootstrap-table/blob/master/src/extensions/export/bootstrap-table-export.js

js实现把网页table导成Excel的更多相关文章

  1. js实现把网页table导成Excel（bootstrap、JqGrid、Json）

    方案一:支持IE //导出excel function exportExcel(DivID,strTitle){ if(DivID==null) { return false; } var jXls, ...

  2. 将html table 转成 excel

    package com.sun.office.excel; /** * 跨行元素元数据 * */ public class CrossRangeCellMeta { public CrossRange ...

  3. 使用js代码将HTML Table导出为Excel

    使用js代码将HTML Table导出为Excel的方法: 直接上源码 <html> <head> <meta http-equiv="Content-Type ...

  4. sqlserver将数据库的数据导成excel文档方法

    sqlserver将数据库的数据导成excel文档方法 最近公司需要下载uniport的数据跟之前的数据进行对比,所以避免不了需要将数据库的数据导出来,把SQLServer表中的数据导出为Excel文 ...

  5. js中的table导出成Excel表格

    首先判断手否是IE,原因在于IE导出我用的是ActiveXObject,判断的方式很简单,只需要拿到window.navigator.userAgent即可进行判断,代码如下 function get ...

  6. MVC 将视图页table导出成excel

    前台代码: <table class="tablelist" id="myTable">    <thead>        <t ...

  7. 前端JS脚本将网页表格导出为Excel

    话不多说,上代码! <!DOCTYPE> <html> <head> <title>Excel Test</title> </head ...

  8. EPPlus&lpar;SQL导成Excel&rpar;

    使用Epplus方法把sql数据库中表的数据导出到excel中去: 需要使用EPPlus.dll引用. using System.IO; using OfficeOpenXml; public sta ...

  9. js将HTML中table导出到EXCEL word &lpar;只支持IE&rpar; 另用php 配合AJAX可以支持所有浏览器

    转载请注明来源:https://www.cnblogs.com/hookjc/ <HTML>     <HEAD>       <title>WEB页面导出为EXC ...

随机推荐

  1. 设置与获取Cookie

    自己编写的一个Cookie设置与获取函数,大家有什么感觉需要改进的地方,请告知与我,我一定虚心接受. JavaScript - Code: function setCookie(name,value, ...

  2. paip&period;enhes efis 自动获取文件的中文编码

    paip.enhes efis 自动获取文件的中文编码 ##为什么需要自动获取文件的中文编码 提高开发效率,自动获取文件的中文编码  .不需要手动设置编码...轻松的.. ##cpdetector 可 ...

  3. FreeMarker 一二事 - 静态模板的使用与生成

    如今前后端分离,动静分离 使用freemarker实现动静分离,nginx处理静态资源文件,提高效率 加载jar包 <!-- freemarker --> <dependency&g ...

  4. 《精通javascript》几个简单的函数

    转载http://www.cnblogs.com/jikey/archive/2011/07/25/2116696.html /** * 隐藏元素 * @param {String} elem */f ...

  5. 步步入佳境---UI入门&lpar;4&rpar; --简单练习

    一,创建SingleViewApplication 1,UILabel的简单使用 UILabel *label=[[UILabel alloc]initWithFrame:CGRectMake(0, ...

  6. 关于响应式、媒体查询和media的关系 、流媒体布局flex 和em rem像素的使用 我有一些废话要讲&period;&period;&period;&period;&period;

    一.什么是响应式 随着移动端越来遇火 网站的布局成为一个热议的话题 有的人喜欢用手机浏览网站.有的人喜欢用paid浏览网站.有人喜欢用电脑浏览网站 那么问题来了 我们怎么样才能使用一套css样式 完成 ...

  7. 提高duilib的richedit控制的一些特征

    转载请注明原始出处.谢谢~~:http://blog.csdn.net/zhuhongshu/article/details/41208207 假设要使用透明异形窗口功能,首先要改进duilib库让他 ...

  8. Android 点击桌面快捷方式和Notifycation跳转到Task栈顶Activity

    我们一般下载的应用在第一次启动应用的时候都会给我创建一个桌面快捷方式,然后我在网上找了些资料整理下了,写了一个快捷方式的工具类,这样我们以后要创建快捷方式的时候直接拷贝这个类,里面提供了一些静态方法, ...

  9. maven 运行tomcatrun -Dmaven&period;multiModuleProjectDirectory system property is not set&period; Check &dollar;M2&lowbar;HOME environment variable and mvn script match&period;

      解决-Dmaven.multiModuleProjectDirectory system property is not set. Check $M2_HOME environment varia ...

  10. hdu1255 覆盖的面积 线段树&plus;里离散化求矩形面积的交

    题目链接:http://acm.hdu.edu.cn/showproblem.php?pid=1255 求矩形面积的交的线段树题目,刚做了求并的题目,再做这个刚觉良好啊,只要再加一个表示覆盖次数大于1 ...

相关文章