2

On my MVC project I have a HTML table bound with Knockout.

I'm trying to Export the table to Excel.

I tried on client side with JavaScript:

self.exportToExcel = function () {
    javascript: window.open('data:application/vnd.ms-excel,' + $("#tableToprint").innerHTML());
}

OR:

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]--></head><body><table cellspacing="0" rules="rows" border="1" style="color:Black;background-color:White;border-color:#CCCCCC;border-width:1px;border-style:None;width:100%;border-collapse:collapse;font-size:9pt;text-align:center;">{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 }
    if (navigator.msSaveBlob) {
        var blob = new Blob([format(template, ctx)], { type: 'application/vnd.ms-excel', endings: 'native' });
        navigator.msSaveBlob(blob, 'export.xlsx')
    } else {
        window.location.href = uri + base64(format(template, ctx))
    }
}
})()

But both codes work in Chrome but not in IE.

I would like to do it on client side using JavaScript or jQuery but if there is not a solution that is supported by both browsers I can also do it in server side with an AJAX Post Request to my Web API.

How can I export a HTML table to Excel supported by Chrome and IE using JavaScript/jQuery OR AJAX and Web API?

Any advice?

user3378165
  • 6,546
  • 17
  • 62
  • 101
  • If you're using MVC, you'd be better off creating the excel xml server-side with a simple `FileStreamResult` action. Then it's browser-independent and not limited to browser address bar limitations. You can use OpenXml SDK (or any wrapper). – freedomn-m Mar 16 '16 at 16:07
  • Thank you for your reply, I can't do it through MVC server side, because throughout all the project for any server side actions I made AJAX call to my Web API, I don't have code on my MVC Controllers... – user3378165 Mar 16 '16 at 16:23
  • 1
    Why does that stop you adding a new 'ExcelDownloadController' ? – freedomn-m Mar 16 '16 at 16:24
  • I would rather create this Controller on my Web API, but I'm not sure how to do it, would you be able to guide me? – user3378165 Mar 16 '16 at 16:27
  • You can not get the browser to handle a file-save via AJAX (maybe can with HTML5 - http://stackoverflow.com/questions/20830309/download-file-using-an-ajax-request). The reason to use `FileStreamResult` is that it instructs the browser what to do with the file (ie show inline or provide a download prompt depending on browser and end-user options). ie does everything you need. So your WebAPI might generate the excel-xml itself, but can't tell the browser how to handle it. You need `content-disposition` and all that other stuff (which the ActionResult above does for you). – freedomn-m Mar 16 '16 at 16:32
  • It can be done all on the client in the latest browsers but <=IE8 will not work, no matter what you do. If this needs to be backwards compatible then use server side function – QBM5 Mar 16 '16 at 18:18

2 Answers2

7

I use the eligrey.filesaver then just use it as

window.saveAs(blob,filename);

based on jparaya's answer I created a little sample in plunker, to save the file with filesaver It does the same as jparaya's code except for the saving part:

function fnExcelReport(id, name) {
  var tab_text = '<html xmlns:x="urn:schemas-microsoft-com:office:excel">';
  tab_text = tab_text + '<head><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet>';
  tab_text = tab_text + '<x:Name>Test Sheet</x:Name>';
  tab_text = tab_text + '<x:WorksheetOptions><x:Panes></x:Panes></x:WorksheetOptions></x:ExcelWorksheet>';
  tab_text = tab_text + '</x:ExcelWorksheets></x:ExcelWorkbook></xml></head><body>';
  tab_text = tab_text + "<table border='1px'>";
  var exportTable = $('#' + id).clone();
  exportTable.find('input').each(function (index, elem) { $(elem).remove(); });
  tab_text = tab_text + exportTable.html();
  tab_text = tab_text + '</table></body></html>';
  var fileName = name + '_' + parseInt(Math.random() * 10000000000) + '.xls';

  //Save the file
  var blob = new Blob([tab_text], { type: "application/vnd.ms-excel;charset=utf-8" })
  window.saveAs(blob, fileName);
}
CodeHacker
  • 2,127
  • 1
  • 22
  • 35
  • Thank you for your reply, I tried it but couldn't get it to work what are the parameters that I should pass in? the table ID? what is this 'blob' parameter? Thank you!! – user3378165 Mar 20 '16 at 21:51
  • I edited my original answer. Hope this makes it more clear – CodeHacker Mar 21 '16 at 07:06
  • Working!! both in IE and Chrome! But I have 2 questions: 1) is there a way to save it as .xlsx file? If I change it manually the file become corrupted. 2) Is there a way to get rid of the excel warning message(trusted source etc..)? Thank you SO much! – user3378165 Mar 21 '16 at 07:16
  • Hm.. Nope.I've tried some things, but couldn't get that to work.Please share it if you find out how to do it – CodeHacker Mar 21 '16 at 09:26
  • Is there a way to avoid this warning "The file format and extension of 'file.xls' dont match." using this method? – Harsha Vardhini Sep 15 '17 at 09:54
  • Seems like a more clean solution than the one used in tableToExcel –  Jun 26 '19 at 07:53
6

Hmm I'm not really sure what's your problem. Looking at the code, seems everything is fine. Anyway, here I send you a simple implementation that I use, which can help you. Uses the same logic. Perhaps I check first IE version and that's why this works.

This sample has a simple link that executes the fnExcelReport function, passing table name and the excel name. I clone first, because my knockout code switches between view/edit modes with inputs. You can remove the clone and $(elem).remove() if you don't have that problem.

Edit: this version now implements the download for Chrome without the <a> tag in the html 

function fnExcelReport(id, name) {
    var tab_text = '<html xmlns:x="urn:schemas-microsoft-com:office:excel">';
    tab_text = tab_text + '<head><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet>';
    tab_text = tab_text + '<x:Name>Test Sheet</x:Name>';
    tab_text = tab_text + '<x:WorksheetOptions><x:Panes></x:Panes></x:WorksheetOptions></x:ExcelWorksheet>';
    tab_text = tab_text + '</x:ExcelWorksheets></x:ExcelWorkbook></xml></head><body>';
    tab_text = tab_text + "<table border='1px'>";
    var exportTable = $('#' + id).clone();
    exportTable.find('input').each(function (index, elem) { $(elem).remove(); });
    tab_text = tab_text + exportTable.html();
    tab_text = tab_text + '</table></body></html>';
    var data_type = 'data:application/vnd.ms-excel';
    var ua = window.navigator.userAgent;
    var msie = ua.indexOf("MSIE ");

    var fileName = name + '_' + parseInt(Math.random() * 10000000000) + '.xls';
    if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./)) {
        if (window.navigator.msSaveBlob) {
            var blob = new Blob([tab_text], {
                type: "application/csv;charset=utf-8;"
            });
            navigator.msSaveBlob(blob, fileName);
        }
    } else {
        var blob2 =  new Blob([tab_text], {
            type: "application/csv;charset=utf-8;"
        });
        var filename = fileName;
            var elem = window.document.createElement('a');
            elem.href = window.URL.createObjectURL(blob2);
            elem.download = filename;
            document.body.appendChild(elem);
            elem.click();
            document.body.removeChild(elem);
    }
}
jparaya
  • 1,331
  • 11
  • 15