Javascript to export html table to Excel

Question

I need to export the html table in my page to an Excel when user clicks 'Export' button. Now, I found a solution here on stack overflow that works in Firefox.

Export dynamic html table to excel in javascript in firefox browser

Now, it doesn't handle special characters like ö,ü,ö which are common in languages we use here so I wanted to ask if anyone knows how I can export them successfully as well without problems?

Here is my code:

 function tabletoExcel(table, name) {
    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>{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]; }); };
        if (!table.nodeType) table = document.getElementById(table);
        var ctx = { worksheet: name || 'Worksheet', table: table.innerHTML };
        window.location.href = uri + base64(format(template, ctx));

}

With this method you can only export whatever is visible in the page. Are you okay with that approach? — Candide, Jun 17 '13 at 08:56
Yes. That is what I am trying to do. Sometimes, there are names with special characters and they do not get exported correctly. So, I want to know what encoding I should use? — Tulips, Jun 17 '13 at 09:21
Check This http://stackoverflow.com/questions/22317951/export-html-table-data-to-excel-using-javascript-jquery-is-not-working-properl/24081343#24081343 — sampopes, Nov 27 '14 at 18:05
How can I extend this js, in order to export multiples tables html in excel? — Ignacio Chiazzo, Nov 01 '15 at 14:06

score 68 · Accepted Answer · answered Jun 17 '13 at 10:59

68

If you add:

<meta http-equiv="content-type" content="text/plain; charset=UTF-8"/>

in the head of the document it will start working as expected:

<script type="text/javascript">
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, name) {
    if (!table.nodeType) table = document.getElementById(table)
    var ctx = {worksheet: name || 'Worksheet', table: table.innerHTML}
    window.location.href = uri + base64(format(template, ctx))
  }
})()
</script>

Updated Fiddle Here.

answered Jun 17 '13 at 10:59

Candide

30,469
8
53
60

@seroth You get this error because you are using IE < 10. http://caniuse.com/#search=btoa – Bakudan Mar 12 '15 at 09:46
This is not working for me in IE 11. When you click on the Export button it just shows that the page is waiting . – Jason Ebersey Apr 29 '15 at 12:14
11

"The file format and extension of download.xls don't match" this is a problem as end users do not want to see there and click on Yes .... – Tom Stickel Oct 30 '15 at 20:53
How can I export multiples tables with this js? – Ignacio Chiazzo Nov 01 '15 at 14:05
Thank you so much. You made my day :) – Tarun Nagpal Jan 28 '16 at 10:00
@Candide I know the original code is not yours, but do you have any idea of how to get this code to export to xlsx instead of xls? – Tot Zam Oct 27 '16 at 19:06
@TotZam xlsx is a mix of zip+files, I think it is quite difficult to do it with javascript – Candide Oct 27 '16 at 19:16
why the file downloaded doesn't come with an extension? – Progs Mar 17 '17 at 18:23
1

File is downloaded but when opening the file in MS Excel it shows warning for unmatched header and it's extension. – codemirror Dec 29 '17 at 07:21
hello, how did you came to this solution?how you solve it? @Candide – Alex Hunter Oct 08 '18 at 11:48
@Candide, thanks for your answer. When I download the file on windows computer, '2.8.5' is shown as date format. Do you know how can I fix this problem? – aylin Nov 10 '21 at 14:30
@Candide , If there is too much data, I get 'about:blank#blocked' warning in the browser. How can I prevent this? – aylin Dec 30 '21 at 12:09

score 6 · Answer 2 · answered Feb 05 '15 at 15:48

For UTF 8 Conversion and Currency Symbol Export Use this:

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 version="1.0" encoding="UTF-8" standalone="yes"?><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>{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 }
    window.location.href = uri + base64(format(template, ctx))
  }
})()

score 1 · Answer 3 · answered Nov 19 '15 at 19:47

1

ShieldUI's export to excel functionality should already support all special chars.

answered Nov 19 '15 at 19:47

Vladimir Georgiev

1,949
23
26

Javascript to export html table to Excel

3 Answers3

Linked