HTML Table to Excel (xls) using javascript/jQuery

Question

HTML Table to Excel (xls) using javascript/jQuery

961 Views Asked by David Dawson At 19 October 2024 at 16:48

I am trying to export a html table to an Excel document via a javascript/jquery export. I so far have found 2 solutions but neither meet my requirements:

HTML Table Export

Does not export inline styling (styling is a must-have requirement)

Table2Excel

Does not work in all IE versions (need all browser compatibility)

I am looking for a solution that is as minimal as possible (both of these solutions were very "tidy" but as per my comments above they both had a downfall that fell short of my requirements.

Does anyone know a better solution? My requirements for this export are:

Needs to work on all browsers
Needs to export the inline styling
IF POSSIBLE it would be nice to be able to name the file

Would appreciate any assistance here, pulling my hair out as I can't be the first person that has required this feature...

Thanks!

Original Q&A

There are 1 best solutions below

**Michael Tallino** · Answer 1

I'm assuming that PHP / Flash is a no go. (If not, checkout PHPExcel and DataTables' export feature.)

Also, naming the file is nigh impossible without server-side programming of some type. I'm pretty sure this is a security problem for most browsers.

Before we get to the code, some limitations:

You'll probably need to lookup MS Office XML formats for XLS and customize
Once the file downloads (it will be .xls) you will likely get an error like "This file says it's in XLS format but might not be, do you want to open anyway?"
You will likely need to "Save As" some valid excel format to force it to convert from HTML in the excel document body.
I've used this fairly extensively in Chrome / Firefox / Internet Explorer but you should test yourself.
Currently set to freeze rows / columns. Change the XML and / or freeze vars at the top of the tableToExcel() function.
Resource-expensive on the client side. Probably best to use a server-side script of some kind.
There is a limit to the size of the table which is (I'm guessing) based on the size of the javascript variable. I used it with some tables like 50x300 but I wouldn't recommend on much larger tables.

It accepts a string of HTML table text (include <table>). You can format that HTML with inline styles (won't work with a CSS stylesheet unfortunately).

tableToExcel($('table').html(),'Worksheet Name');

Here's the functions. Enjoy!

function b64toBlob(b64Data, contentType, sliceSize) {
    contentType = contentType || '';
    sliceSize = sliceSize || 512;

    var byteCharacters = atob(b64Data);
    var byteArrays = [];

    for (var offset = 0; offset < byteCharacters.length; offset += sliceSize) {
        var slice = byteCharacters.slice(offset, offset + sliceSize);

        var byteNumbers = new Array(slice.length);
        for (var i = 0; i < slice.length; i++) {
            byteNumbers[i] = slice.charCodeAt(i);
        }

        var byteArray = new Uint8Array(byteNumbers);

        byteArrays.push(byteArray);
    }

    var blob = new Blob(byteArrays, {type: contentType});
    return blob;
}

function tableToExcel(table,name) {
 var freezeTopRowNumber = '4';
 var freezeColNumber = '6';
 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">';
 template += '<head><!--[if gte mso 9]>';
 template += '<xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name>';
 template += '<x:WorksheetOptions><x:Selected/><x:FreezePanes/><x:FrozenNoSplit/><x:SplitHorizontal>'+freezeTopRowNumber+'</x:SplitHorizontal><x:TopRowBottomPane>'+freezeTopRowNumber+'</x:TopRowBottomPane>';
 template += '<x:SplitVertical>'+freezeColNumber+'</x:SplitVertical><x:LeftColumnRightPane>'+freezeColNumber+'</x:LeftColumnRightPane>';
 template += '<x:ActivePane>2</x:ActivePane><x:Panes><x:Pane><x:Number>3</x:Number></x:Pane><x:Pane><x:Number>2</x:Number></x:Pane></x:Panes>';
 template += '<x:ProtectContents>False</x:ProtectContents><x:ProtectObjects>False</x:ProtectObjects><x:ProtectScenarios>False</x:ProtectScenarios>';
 template += '<x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head>';
 template += '<body>{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]; }) };
 var ctx = {worksheet: name || 'Worksheet', table: table};
 var b = base64(format(template,ctx));

 var blob = b64toBlob(b,'application/vnd.ms-excel');
 var blobURL = URL.createObjectURL(blob);
 window.location.href = blobURL;


}

HTML Table to Excel (xls) using javascript/jQuery

There are 1 best solutions below

Related Questions in JAVASCRIPT

Related Questions in JQUERY

Related Questions in HTML

Related Questions in EXCEL

Related Questions in HTML-TABLE

Trending Questions

Popular # Hahtags

Popular Questions