Geek Answers Handbook

Excel spreadsheet (xls) using javascript / jQuery

I am trying to export an html table to an Excel document via javascript / jquery export. I still found 2 solutions, but did not meet my requirements:

HTML table export

  • Does not export inline style (style is a prerequisite)

Table2excel

  • Does not work in all versions of IE (requires all browser compatibility)

I am looking for a solution that will be as small as possible (both of these solutions were very “neat”, but according to my comments above, they both had a drop that did not meet my requirements.

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

  • Need to work in all browsers
  • Need to export inline style
  • IF POSSIBLE, it would be nice to name the file

Would thank for any help here by pulling my hair out as I can't be the first person to require this feature ...

Thank!

+4
source share
1 answer

I assume that PHP / Flash is not an option. (If not, check out PHPExcel and DataTables ' export function.)

In addition, file naming is virtually impossible without programming on the server side of any type. I am sure this is a security issue for most browsers.

Before moving on to the code, some limitations:

  • You will probably need to search for MS Office XML Formats for XLS and configure
  • ( .xls) , , , : " XLS, , ?"
  • , , " " - excel, HTML excel.
  • Chrome/Firefox/Internet Explorer, .
  • /. XML / vars tableToExcel().
  • - . , script.
  • , ( ), javascript. 50x300, .

HTML ( <table>). HTML- ( , CSS).

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

. !

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;


}
Hide result
+1

More articles:


All Articles