How to export Datatables table to Excel which works in IE as well

Question

I am using

  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();
        }
    })();

for exporting datatables to excel and it works fine in both chrome and firefox. however it gives

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

error in all IE versions. Please give me some workaround as how to solve this problem or suggest any new method that works in all browsers. I am googling about the same since yesterday but all in vain... Any help would be apperciated .. thanks

:Edit i have tried downloadify.js, filesaver but didn't worked for me .

Duplicate of [tabletoexcel-jquery-throws-strange-error-in-ie](http://stackoverflow.com/questions/18619902/tabletoexcel-jquery-throws-strange-error-in-ie) — bastos.sergio, Nov 20 '14 at 12:35
Yes,I have seen that question but couldn't get any result. That's why i posted here. — Alok, Nov 20 '14 at 12:38
Like the link states, the error is due a limitation in how IE stores URIs (ie. old versions have a limited data capacity). If you need to support old IE versions, then you'll have to do this the old way and create the Excel server side. — bastos.sergio, Nov 21 '14 at 11:44
Yes, now i am using downloadify for server side downloading. also The code above is not supported in any IE versions ..I am using IE 11... — Alok, Nov 21 '14 at 15:15

Alok · Accepted Answer · 2015-02-09T10:14:39.277

I used jspdf to get the desired results.Following points i achieved:-

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

.

Libraries 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 have to write the logic to get the html of table you want to export .

How to export Datatables table to Excel which works in IE as well

1 Answers1

Linked