html table to excel using jquery

Question

I have this jquery function that works, except I need to add something. I have a filter setup on the table to hide columns. When I hide columns and click export it uses all fields regardless. How would I only export the table columns that are not hidden?

var write_to_excel = (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))
        }
})()

I think I need something like.

if($(table).display.style!="none")

Thanks in advance.

You'd have to loop through the rows of the table and export the HTML per-cell, depending on whether the cell's column is visible or not. — Tim Williams, Jul 02 '12 at 22:02

score 2 · Accepted Answer · answered Jul 02 '12 at 22:35

The easiest way is to just remove the columns from the table before you pass it in to the format function. Since you're throwing away the page anyway to navigate to a data URI, it's not important to preserve the hidden columns.

The way you remove the hidden columns depends on how the columns are made hidden in the first place. If it's just by applying display:none to the hidden TDs, you can do it by making this change to the code.

    return function(table, name) {
        if (!table.nodeType)
            table = document.getElementById(table);

        $(table).find(':not(:visible)').remove();

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

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

score 1 · Answer 2 · answered Mar 01 '13 at 16:47

Or you could use simple jQuery to export the content in a table to Excel format, using window.open() method. This will also solve the display:none issue in a simpler manner :)

window.open(MIMEtype,replace);

MIMEtype: Optional. The type of document you are writing to. Default value is "text/html".

replace: Optional. If set, the history entry for the new document inherits the history entry from the document which opened this document.

Below jQuery code will export the table data to Excel.

$("#btnExport").click(function(e) {
    window.open('data:application/vnd.ms-excel,' + $('#dvData').html());
    e.preventDefault();
});

If your table HTML contain special characters, then before sending the html to excel it must be encoded.

$("#btnExport").click(function(e) {
    window.open('data:application/vnd.ms-excel,' + encodeURIComponent($('#dvData').html()));
    e.preventDefault();
});

jsFiddle Example - http://jsfiddle.net/jquerybyexample/xhYcD/

All the spaces in result magically disappear in my case. Do you have similar problem? — Vlado, Jun 09 '16 at 11:18

score 0 · Answer 3 · answered May 10 '13 at 07:20

0

$("#btnExport").click(function(e) {

  window.open('data:application/vnd.ms-excel,' + encodeURIComponent($('#export').html()));

});

answered May 10 '13 at 07:20

Avinash Saini

1,203
11
10

html table to excel using jquery

3 Answers3

Linked