locked
export html table to xlsx excel RRS feed

  • Question

  • User-1634604574 posted

    i want to export html table to excel with xlsx extension how can i do it?

    i have this code but this is not create xlsx excel

    <table id='testExportId'>
    <tr>
    <th bgcolor='red'>Firstname</th>
    <th bgcolor='red'>Lastname</th>
    <th bgcolor='red'>Savings</th>
    </tr>
    <tr>
    <td>Peter</td>
    <td>Griffin</td>
    <td>$100</td>
    </tr>
    <tr>
    <td>Lois</td>
    <td>Griffin</td>
    <td>$150</td>
    </tr>
    <tr>
    <td>Joe</td>
    <td>Swanson</td>
    <td>$300</td>
    </tr>
    <tr>
    <td>Cleveland</td>
    <td>Brown</td>
    <td>$250</td>
    </tr>
    </table>


    <br>
    <button id='btnExport'>
    Export To Excel
    </button>

    $("#btnExport").click(
    function () {
    tableToExcel('testExportId', 'test', 'TestExport');

    });

    function getIEVersion()
    {
    var rv = -1;
    if (navigator.appName == 'Microsoft Internet Explorer') {
    var ua = navigator.userAgent;
    var re = new RegExp("MSIE ([0-9]{1,}[\.0-9]{0,})");
    if (re.exec(ua) != null)
    rv = parseFloat(RegExp.$1);
    }
    return rv;
    }

    function tableToExcel(table, sheetName, fileName) {
    var ua = window.navigator.userAgent;
    var msie = ua.indexOf("MSIE ");
    if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./)) // If Internet Explorer
    {
    return fnExcelReport(table, fileName);
    }

    var uri = 'data:application/vnd.ms-excel;base64,',
    templateData = '<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]--><meta http-equiv="content-type" content="text/plain; charset=UTF-8"/></head><body><table>{table}</table></body></html>',
    base64Conversion = function (s) { return window.btoa(unescape(encodeURIComponent(s))) },
    formatExcelData = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }) }

    $("tbody > tr[data-level='0']").show();

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

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

    var element = document.createElement('a');
    element.setAttribute('href', 'data:application/vnd.ms-excel;base64,' + base64Conversion(formatExcelData(templateData, ctx)));
    element.setAttribute('download', fileName);
    element.style.display = 'none';
    document.body.appendChild(element);
    element.click();
    document.body.removeChild(element);

    $("tbody > tr[data-level='0']").hide();
    }


    function fnExcelReport(table, fileName) {

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

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

    $("tbody > tr[data-level='0']").show();
    tab_text = tab_text + table.innerHTML;

    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

    txtArea1.document.open("txt/html", "replace");
    txtArea1.document.write(tab_text);
    txtArea1.document.close();
    txtArea1.focus();
    sa = txtArea1.document.execCommand("SaveAs", false, fileName + ".xlsx");
    $("tbody > tr[data-level='0']").hide();
    return (sa);
    }

    Friday, February 1, 2019 8:07 PM

Answers

  • User839733648 posted

    Hi zhyanadil.it@gmail.com,

    I've tried your code and it works well on my side.

    The only difference is that I add the jquery reference. Have you added?

    Here is my full testing code, please pay attention to the highlight.

    <!DOCTYPE html>
    <html>
    <head>
        <meta charset="utf-8" />
        <title></title>
        <script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
    </head>
    <body>
        <table id='testExportId'>
            <tr>
                <th bgcolor='red'>Firstname</th>
                <th bgcolor='red'>Lastname</th>
                <th bgcolor='red'>Savings</th>
            </tr>
            <tr>
                <td>Peter</td>
                <td>Griffin</td>
                <td>$100</td>
            </tr>
            <tr>
                <td>Lois</td>
                <td>Griffin</td>
                <td>$150</td>
            </tr>
            <tr>
                <td>Joe</td>
                <td>Swanson</td>
                <td>$300</td>
            </tr>
            <tr>
                <td>Cleveland</td>
                <td>Brown</td>
                <td>$250</td>
            </tr>
        </table>
        <br>
        <button id='btnExport'>
            Export To Excel
        </button>
        <script>
            $("#btnExport").click(
                function () {
                    tableToExcel('testExportId', 'test', 'TestExport');
    
                });
    
            function getIEVersion() {
                var rv = -1;
                if (navigator.appName == 'Microsoft Internet Explorer') {
                    var ua = navigator.userAgent;
                    var re = new RegExp("MSIE ([0-9]{1,}[\.0-9]{0,})");
                    if (re.exec(ua) != null)
                        rv = parseFloat(RegExp.$1);
                }
                return rv;
            }
    
            function tableToExcel(table, sheetName, fileName) {
                var ua = window.navigator.userAgent;
                var msie = ua.indexOf("MSIE ");
                if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./)) // If Internet Explorer
                {
                    return fnExcelReport(table, fileName);
                }
    
                var uri = 'data:application/vnd.ms-excel;base64,',
                    templateData = '<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]--><meta http-equiv="content-type" content="text/plain; charset=UTF-8"/></head><body><table>{table}</table></body></html>',
                    base64Conversion = function (s) { return window.btoa(unescape(encodeURIComponent(s))) },
                    formatExcelData = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }) }
    
                $("tbody > tr[data-level='0']").show();
    
                if (!table.nodeType)
                    table = document.getElementById(table)
    
                var ctx = { worksheet: sheetName || 'Worksheet', table: table.innerHTML }
    
                var element = document.createElement('a');
                element.setAttribute('href', 'data:application/vnd.ms-excel;base64,' + base64Conversion(formatExcelData(templateData, ctx)));
                element.setAttribute('download', fileName);
                element.style.display = 'none';
                document.body.appendChild(element);
                element.click();
                document.body.removeChild(element);
    
                $("tbody > tr[data-level='0']").hide();
            }
    
    
            function fnExcelReport(table, fileName) {
    
                var tab_text = "<table border='2px'>";
                var textRange;
    
                if (!table.nodeType)
                    table = document.getElementById(table)
    
                $("tbody > tr[data-level='0']").show();
                tab_text = tab_text + table.innerHTML;
    
                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
    
                txtArea1.document.open("txt/html", "replace");
                txtArea1.document.write(tab_text);
                txtArea1.document.close();
                txtArea1.focus();
                sa = txtArea1.document.execCommand("SaveAs", false, fileName + ".xlsx");
                $("tbody > tr[data-level='0']").hide();
                return (sa);
            }
        </script>
    </body>
    </html>

    result:

    Best Regards,

    Jenifer

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 4, 2019 7:54 AM

All replies

  • User-2054057000 posted

    Have a look to this - How to Export Database Records into Excel File in ASP.NET MVC

    Saturday, February 2, 2019 1:06 PM
  • User839733648 posted

    Hi zhyanadil.it@gmail.com,

    I've tried your code and it works well on my side.

    The only difference is that I add the jquery reference. Have you added?

    Here is my full testing code, please pay attention to the highlight.

    <!DOCTYPE html>
    <html>
    <head>
        <meta charset="utf-8" />
        <title></title>
        <script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
    </head>
    <body>
        <table id='testExportId'>
            <tr>
                <th bgcolor='red'>Firstname</th>
                <th bgcolor='red'>Lastname</th>
                <th bgcolor='red'>Savings</th>
            </tr>
            <tr>
                <td>Peter</td>
                <td>Griffin</td>
                <td>$100</td>
            </tr>
            <tr>
                <td>Lois</td>
                <td>Griffin</td>
                <td>$150</td>
            </tr>
            <tr>
                <td>Joe</td>
                <td>Swanson</td>
                <td>$300</td>
            </tr>
            <tr>
                <td>Cleveland</td>
                <td>Brown</td>
                <td>$250</td>
            </tr>
        </table>
        <br>
        <button id='btnExport'>
            Export To Excel
        </button>
        <script>
            $("#btnExport").click(
                function () {
                    tableToExcel('testExportId', 'test', 'TestExport');
    
                });
    
            function getIEVersion() {
                var rv = -1;
                if (navigator.appName == 'Microsoft Internet Explorer') {
                    var ua = navigator.userAgent;
                    var re = new RegExp("MSIE ([0-9]{1,}[\.0-9]{0,})");
                    if (re.exec(ua) != null)
                        rv = parseFloat(RegExp.$1);
                }
                return rv;
            }
    
            function tableToExcel(table, sheetName, fileName) {
                var ua = window.navigator.userAgent;
                var msie = ua.indexOf("MSIE ");
                if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./)) // If Internet Explorer
                {
                    return fnExcelReport(table, fileName);
                }
    
                var uri = 'data:application/vnd.ms-excel;base64,',
                    templateData = '<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]--><meta http-equiv="content-type" content="text/plain; charset=UTF-8"/></head><body><table>{table}</table></body></html>',
                    base64Conversion = function (s) { return window.btoa(unescape(encodeURIComponent(s))) },
                    formatExcelData = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }) }
    
                $("tbody > tr[data-level='0']").show();
    
                if (!table.nodeType)
                    table = document.getElementById(table)
    
                var ctx = { worksheet: sheetName || 'Worksheet', table: table.innerHTML }
    
                var element = document.createElement('a');
                element.setAttribute('href', 'data:application/vnd.ms-excel;base64,' + base64Conversion(formatExcelData(templateData, ctx)));
                element.setAttribute('download', fileName);
                element.style.display = 'none';
                document.body.appendChild(element);
                element.click();
                document.body.removeChild(element);
    
                $("tbody > tr[data-level='0']").hide();
            }
    
    
            function fnExcelReport(table, fileName) {
    
                var tab_text = "<table border='2px'>";
                var textRange;
    
                if (!table.nodeType)
                    table = document.getElementById(table)
    
                $("tbody > tr[data-level='0']").show();
                tab_text = tab_text + table.innerHTML;
    
                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
    
                txtArea1.document.open("txt/html", "replace");
                txtArea1.document.write(tab_text);
                txtArea1.document.close();
                txtArea1.focus();
                sa = txtArea1.document.execCommand("SaveAs", false, fileName + ".xlsx");
                $("tbody > tr[data-level='0']").hide();
                return (sa);
            }
        </script>
    </body>
    </html>

    result:

    Best Regards,

    Jenifer

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 4, 2019 7:54 AM