Export html table data to Excel using JavaScript /

2019-01-01 12:27发布

站内文章 / JavaScript
33 0 0

问题:

I have a HTML table in velocity template. I want to export the html table data to excel using either java script or jquery, comatibale with all browser. I am using below script 

<script type=\"text/javascript\">
function ExportToExcel(mytblId){
       var htmltable= document.getElementById(\'my-table-id\');
       var html = htmltable.outerHTML;
       window.open(\'data:application/vnd.ms-excel,\' + encodeURIComponent(html));
    }
</script>

This script works fine in Mozilla Firefox,it pops-up with a dialog box of excel and ask for open or save options. But when I tested same script in Chrome browser it is not working as expected,when clicked on button there is no pop-up for excel. Data gets downloaded in a file with \"file type : file\" , no extension like .xls There are no errors in chrome console.

Jsfiddle example :

http://jsfiddle.net/insin/cmewv/

This works fine in mozilla but not in chrome.

Chrome browser Test Case :

First Image:I click on Export to excel button

\"First

and result :

\"Result\"

回答1:

Excel export script works on IE7+, Firefox and Chrome.

function fnExcelReport()
{
    var tab_text=\"<table border=\'2px\'><tr bgcolor=\'#87AFC6\'>\";
    var textRange; var j=0;
    tab = document.getElementById(\'headerTable\'); // id of table

    for(j = 0 ; j < tab.rows.length ; j++) 
    {     
        tab_text=tab_text+tab.rows[j].innerHTML+\"</tr>\";
        //tab_text=tab_text+\"</tr>\";
    }

    tab_text=tab_text+\"</table>\";
    tab_text= tab_text.replace(/<A[^>]*>|<\\/A>/g, \"\");//remove if u want links in your table
    tab_text= tab_text.replace(/<img[^>]*>/gi,\"\"); // remove if u want images in your table
    tab_text= tab_text.replace(/<input[^>]*>|<\\/input>/gi, \"\"); // reomves input params

    var ua = window.navigator.userAgent;
    var msie = ua.indexOf(\"MSIE \"); 

    if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\\:11\\./))      // If Internet Explorer
    {
        txtArea1.document.open(\"txt/html\",\"replace\");
        txtArea1.document.write(tab_text);
        txtArea1.document.close();
        txtArea1.focus(); 
        sa=txtArea1.document.execCommand(\"SaveAs\",true,\"Say Thanks to Sumit.xls\");
    }  
    else                 //other browser not tested on IE 11
        sa = window.open(\'data:application/vnd.ms-excel,\' + encodeURIComponent(tab_text));  

    return (sa);
}

Just create a blank iframe:

<iframe id=\"txtArea1\" style=\"display:none\"></iframe>

Call this function on:

<button id=\"btnExport\" onclick=\"fnExcelReport();\"> EXPORT </button>


回答2:

Datatable plugin solves the purpose best and allows us to export the HTML table data into Excel , PDF , TEXT. easily configurable.

Please find the complete example in below datatable reference link :

https://datatables.net/extensions/buttons/examples/html5/simple.html

(screenshot from datatable reference site) \"enter



回答3:

This could help

function exportToExcel(){
var htmls = \"\";
            var uri = \'data:application/vnd.ms-excel;base64,\';
            var 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>\'; 
            var base64 = function(s) {
                return window.btoa(unescape(encodeURIComponent(s)))
            };

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

            htmls = \"YOUR HTML AS TABLE\"

            var ctx = {
                worksheet : \'Worksheet\',
                table : htmls
            }


            var link = document.createElement(\"a\");
            link.download = \"export.xls\";
            link.href = uri + base64(format(template, ctx));
            link.click();
}


回答4:

http://wsnippets.com/export-html-table-data-excel-sheet-using-jquery/ try this link it might solve your problem

\"enter



回答5:

You can use a library like ShieldUI to do that.

It supports exporting to both XML and XLSX widely-used Excel formats.

More details here: http://demos.shieldui.com/web/grid-general/export-to-excel



回答6:

Regarding to sampopes answer from Jun 6 \'14 at 11:59:

I have insert a css style with font-size of 20px to display the excel data greater. In sampopes code the leading <tr> tags are missing, so i first output the headline and than the other tables lines within a loop.

function fnExcelReport()
{
    var tab_text = \'<table border=\"1px\" style=\"font-size:20px\" \">\';
    var textRange; 
    var j = 0;
    var tab = document.getElementById(\'DataTableId\'); // id of table
    var lines = tab.rows.length;

    // the first headline of the table
    if (lines > 0) {
        tab_text = tab_text + \'<tr bgcolor=\"#DFDFDF\">\' + tab.rows[0].innerHTML + \'</tr>\';
    }

    // table data lines, loop starting from 1
    for (j = 1 ; j < lines; j++) {     
        tab_text = tab_text + \"<tr>\" + tab.rows[j].innerHTML + \"</tr>\";
    }

    tab_text = tab_text + \"</table>\";
    tab_text = tab_text.replace(/<A[^>]*>|<\\/A>/g, \"\");             //remove if u want links in your table
    tab_text = tab_text.replace(/<img[^>]*>/gi,\"\");                 // remove if u want images in your table
    tab_text = tab_text.replace(/<input[^>]*>|<\\/input>/gi, \"\");    // reomves input params
    // console.log(tab_text); // aktivate so see the result (press F12 in browser)

    var ua = window.navigator.userAgent;
    var msie = ua.indexOf(\"MSIE \"); 

     // if Internet Explorer
    if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\\:11\\./)) {
        txtArea1.document.open(\"txt/html\",\"replace\");
        txtArea1.document.write(tab_text);
        txtArea1.document.close();
        txtArea1.focus(); 
        sa = txtArea1.document.execCommand(\"SaveAs\", true, \"DataTableExport.xls\");
    }  
    else // other browser not tested on IE 11
        sa = window.open(\'data:application/vnd.ms-excel,\' + encodeURIComponent(tab_text));  

    return (sa);
}


回答7:

\"TableExport\"

TableExport - The simple, easy-to-implement library to export HTML tables to xlsx, xls, csv, and txt files.

To use this library, simple call the TableExport constructor:

new TableExport(document.getElementsByTagName(\"table\"));

// OR simply

TableExport(document.getElementsByTagName(\"table\"));

// OR using jQuery

$(\"table\").tableExport();

Additional properties can be passed-in to customize the look and feel of your tables, buttons, and exported data. See here more info



回答8:

 function exportToExcel() {
        var tab_text = \"<tr bgcolor=\'#87AFC6\'>\";
        var textRange; var j = 0, rows = \'\';
        tab = document.getElementById(\'student-detail\');
        tab_text = tab_text + tab.rows[0].innerHTML + \"</tr>\";
        var tableData = $(\'#student-detail\').DataTable().rows().data();
        for (var i = 0; i < tableData.length; i++) {
            rows += \'<tr>\'
                + \'<td>\' + tableData[i].value1 + \'</td>\'
                + \'<td>\' + tableData[i].value2 + \'</td>\'
                + \'<td>\' + tableData[i].value3 + \'</td>\'
                + \'<td>\' + tableData[i].value4 + \'</td>\'
                + \'<td>\' + tableData[i].value5 + \'</td>\'
                + \'<td>\' + tableData[i].value6 + \'</td>\'
                + \'<td>\' + tableData[i].value7 + \'</td>\'
                + \'<td>\' +  tableData[i].value8 + \'</td>\'
                + \'<td>\' + tableData[i].value9 + \'</td>\'
                + \'<td>\' + tableData[i].value10 + \'</td>\'
                + \'</tr>\';
        }
        tab_text += rows;
        var data_type = \'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=\"2px\">{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];
                })
            }

        var ctx = {
            worksheet: \"Sheet 1\" || \'Worksheet\',
            table: tab_text
        }
        document.getElementById(\"dlink\").href = data_type + base64(format(template, ctx));
        document.getElementById(\"dlink\").download = \"StudentDetails.xls\";
        document.getElementById(\"dlink\").traget = \"_blank\";
        document.getElementById(\"dlink\").click();
    }

Here Value 1 to 10 are column names that you are getting



回答9:

My version of @sampopes answer

function exportToExcel(that, id, hasHeader, removeLinks, removeImages, removeInputParams) {
if (that == null || typeof that === \'undefined\') {
    console.log(\'Sender is required\');
    return false;
}

if (!(that instanceof HTMLAnchorElement)) {
    console.log(\'Sender must be an anchor element\');
    return false;
}

if (id == null || typeof id === \'undefined\') {
    console.log(\'Table id is required\');
    return false;
}
if (hasHeader == null || typeof hasHeader === \'undefined\') {
    hasHeader = true;
}
if (removeLinks == null || typeof removeLinks === \'undefined\') {
    removeLinks = true;
}
if (removeImages == null || typeof removeImages === \'undefined\') {
    removeImages = false;
}
if (removeInputParams == null || typeof removeInputParams === \'undefined\') {
    removeInputParams = true;
}

var tab_text = \"<table border=\'2px\'>\";
var textRange;

tab = $(id).get(0);

if (tab == null || typeof tab === \'undefined\') {
    console.log(\'Table not found\');
    return;
}

var j = 0;

if (hasHeader && tab.rows.length > 0) {
    var row = tab.rows[0];
    tab_text += \"<tr bgcolor=\'#87AFC6\'>\";
    for (var l = 0; l < row.cells.length; l++) {
        if ($(tab.rows[0].cells[l]).is(\':visible\')) {//export visible cols only
            tab_text += \"<td>\" + row.cells[l].innerHTML + \"</td>\";
        }
    }
    tab_text += \"</tr>\";
    j++;
}

for (; j < tab.rows.length; j++) {
    var row = tab.rows[j];
    tab_text += \"<tr>\";
    for (var l = 0; l < row.cells.length; l++) {
        if ($(tab.rows[j].cells[l]).is(\':visible\')) {//export visible cols only
            tab_text += \"<td>\" + row.cells[l].innerHTML + \"</td>\";
        }
    }
    tab_text += \"</tr>\";
}

tab_text = tab_text + \"</table>\";
if (removeLinks)
    tab_text = tab_text.replace(/<A[^>]*>|<\\/A>/g, \"\");
if (removeImages)
    tab_text = tab_text.replace(/<img[^>]*>/gi, \"\"); 
if (removeInputParams)
    tab_text = tab_text.replace(/<input[^>]*>|<\\/input>/gi, \"\");

var ua = window.navigator.userAgent;
var msie = ua.indexOf(\"MSIE \");

if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\\:11\\./))      // If Internet Explorer
{
    myIframe.document.open(\"txt/html\", \"replace\");
    myIframe.document.write(tab_text);
    myIframe.document.close();
    myIframe.focus();
    sa = myIframe.document.execCommand(\"SaveAs\", true, document.title + \".xls\");
    return true;
}
else {
    //other browser tested on IE 11
    var result = \"data:application/vnd.ms-excel,\" + encodeURIComponent(tab_text);
    that.href = result;
    that.download = document.title + \".xls\";
    return true;
}
}

Requires an iframe

<iframe id=\"myIframe\" style=\"opacity: 0; width: 100%; height: 0px;\" seamless=\"seamless\"></iframe>

Usage

$(\"#btnExportToExcel\").click(function () {
    exportToExcel(this, \'#mytable\');
});


标签： javascript jquery html excel google-chrome
举报

收藏的人(0)

Ta的文章 更多文章
登录 后发表评论
0条评论
还没有人评论过~