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

方案一：支持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

本文链接：https://www.cnblogs.com/elves/p/3593177.html

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

js实现把网页table导成Excel（bootstrap、JqGrid、Json）的更多相关文章

随机推荐

热门专题

目录导航