Remove From My Forums

export html table to xlsx excel

Question
0

Sign in to vote

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><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

0

Sign in to vote

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