All geek questions in one place

How to export Datatables table to Excel, which also works in IE

I use

var tableToExcel = (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]--><meta http-equiv="content-type" content="text/plain; charset=UTF-8"/></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, fileName) { if (!table.nodeType) table = document.getElementById(table) var ctx = { worksheet: fileName || 'Worksheet', table: table.innerHTML } $("<a id='dlink' style='display:none;'></a>").appendTo("body"); document.getElementById("dlink").href = uri + base64(format(template, ctx)) document.getElementById("dlink").download = fileName; document.getElementById("dlink").click(); } })();

to export data to excel and works fine in both Chrome and firefox. however he gives

The data area passed to the system call is too small.

error in all versions of IE . Please give me some solution on how to solve this problem or suggest any new method that works in all browsers. Since then, I'm going to deal with the same, but all in vain ... Any help would be apperciated .. thanks

: Edit, I tried downloadify.js, the file manager, but it didn’t work for me.

+2
source share
1 answer

I used jspdf to get the desired results. The reached points I have reached: -

 1) This works in all versions of IE. 2) You can give the freeze column and freeze row options also in Excel

.

The libraries that I used

 1)downloadify.js 2)swfobject.js 3)downloadify.swf

Functions i used

 1)getHtmlForExport(); // gives the html of the table to be exported. 2)tableToExcel();// converts html code to Microsoft Excel specific XML code 3)handleExcelExport();// downloads excel specific XML to excel file. var tableToExcel = function (table, horizontalFreezeRowNo, VerticalFreezeRowNo) { var worksheetString = ''; //worksheet freeze pane options worksheetString += '<x:WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"><x:Selected/><x:FreezePanes/><x:FrozenNoSplit/><x:ActivePane>2</x:ActivePane>'; if (horizontalFreezeRowNo !== undefined) worksheetString += '<x:SplitHorizontal>' + horizontalFreezeRowNo + '</x:SplitHorizontal><x:TopRowBottomPane>' + horizontalFreezeRowNo + '</x:TopRowBottomPane>'; if (VerticalFreezeRowNo !== undefined) worksheetString += '<x:SplitVertical>' + VerticalFreezeRowNo + '</x:SplitVertical><x:LeftColumnRightPane>' + VerticalFreezeRowNo + '</x:LeftColumnRightPane>'; worksheetString += '</x:WorksheetOptions>'; 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"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions>' + worksheetString + '</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>' , format = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }) }; if (!table.nodeType) table = document.getElementById(table) var ctx = { table: table.innerHTML } $("#exportTable").remove(); return format(template, ctx) } function handleExcelExport(gridConfig) { $("." + gridConfig.objectID + "Export").downloadify({ filename: function () { var elementClicked = this.el; var headerText = $(elementClicked).parents('.portlet').find('.portlet-title h8').text(); var fileName; if (elementClicked == undefined || headerText == undefined) { fileName = "excel" } else { fileName = headerText.toString().trim() } return fileName + ".xls"; }, data: function () { var elementClicked = this.el; getHtmlForExport(elementClicked, gridConfig); return (tableToExcel('exportTable', 1, 1));// table id, horizontal freeze,vertical freeze }, onComplete: function () { }, onCancel: function () { }, onError: function () { }, swf: 'resources/js/downloadify/downloadify.swf', downloadImage: 'resources/css/images/excelDownload.png', width: 65, height: 20, transparent: true, append: false }); }

and for getHtmlForExport you need to write logic to get the html of the table you want to export.

0
source

Source: https://habr.com/ru/post/1500585/

More articles:


All Articles