Encoding UTF-8 when exporting HTML table to Excel

Question

I am trying to export an HTML table to Excel using javascript. This is the javascript code

<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]--></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>

This is my header

<meta http-equiv="content-type" content="application/vnd.ms-excel;" charset="UTF-8">
<meta charset="UTF-8">

This is my table

<table id="tblExport">
   <tr>
      <td>José</td>
      <td>María</td>
   </tr>
</table>

And this is the button which triggers the export

<input type="button" onclick="tableToExcel('tblExport', 'W3C Example Table')" value="Export to Excel">

I can't export the UTF-8 characters correctly, like é or í. I try this Importing HTML table into OO Calc as UTF8 without converting to entities but not works. I have MS-Excel 2010 and Win7 64 bits.

How can I do to export UTF-8 chars correctly?

Thanks!

score 52 · Answer 1 · edited Dec 19 '17 at 10:06

52

First: Your header is malformed. It should be:

<meta http-equiv="content-type" content="application/vnd.ms-excel; charset=UTF-8">

And second: It should be into your template, because it contains charset information for Excel.

<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"><meta http-equiv="content-type" content="application/vnd.ms-excel; charset=UTF-8"><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]; }) }
          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>

edited Dec 19 '17 at 10:06

GhostCat

137,827
25
176
248

answered Sep 08 '14 at 18:19

Axel Richter

56,077
6
60
87

9

Can you share the simple solution that you found? Tks! – Icaro Bombonato Dec 12 '14 at 12:20
1

Thanks for sharing the simple solution :D – Sal Jun 19 '15 at 07:26
3

so what was the simple solution? – Espen Schulstad Jun 25 '15 at 13:05
1

Any news about the "solution more simple" ?? ^_^ – Pierre Nov 03 '15 at 10:26
your answer is good , and thé function you wrote was helpful for me +1 – Muath Dec 20 '15 at 13:16
MS IE does not support `data` protocol for all use cases. ["For security reasons, data URIs are restricted to downloaded resources. Data URIs cannot be used for navigation, for scripting, or to populate frame or iframe elements."](https://msdn.microsoft.com/de-de/library/cc848897%28v=vs.85%29.aspx). So no, no solution for IE with this approach. – Axel Richter Dec 20 '16 at 08:09
This solution didn't properly work for me. To properly encode the UTF-8 string I used [b64EncodeUnicode()](https://developer.mozilla.org/en-US/docs/Web/API/WindowBase64/Base64_encoding_and_decoding#The_Unicode_Problem) instead of base64() defined in the snippet. – Franck Valentin Mar 07 '18 at 14:50
@Franck Valentin: Maybe, maybe not. This "solution" is outdated. There are multiple JavaScript libraries which are able creating **really** Microsoft Excel files. No need for foisting Excel that crude mixture of HTML and XML. – Axel Richter Mar 07 '18 at 15:24

score 4 · Answer 2 · answered Jan 19 '22 at 15:16

You could prepend the content string with the UTF-8 BOM definition:

function ExportToExcel() {
    var BOM = "\uFEFF";
    var htmltabel = document.getElementById("tabella_finale");
    var html = htmltabel.outerHTML;
    window.open('data:application/vnd.ms-excel,'+ encodeURI(BOM + html));
}

score 1 · Answer 3 · edited Mar 10 '19 at 16:53

1

function exportData(report_id){
    var blob = new Blob([document.getElementById(report_id).innerHTML], {
        type: "text/plain;charset=utf-8;"
    });
    saveAs(blob, "Report.xls");
}

Takes table data as plain text and save as Excel without encoding problem

edited Mar 10 '19 at 16:53

SherylHohman

16,580
17
88
94

answered Feb 28 '17 at 07:33

C.T

151
2
9

I struggled with making something like this work, there's also an "encoding" property on Blob. But, every excel program is going to interpret "html saved as .xls file" differently, so who knows. – HoldOffHunger Jul 19 '18 at 17:23

score 1 · Answer 4 · answered Feb 12 '19 at 08:00

if you want to change default file name according to @Axel Richter's answer, try this:

var link = document.createElement('a');
link.download = 'filename.xls';
...
link.href = uri + base64(format(template, ctx));
link.click();
...

replace window.location.href with link.href

score 0 · Answer 5 · answered Jul 28 '17 at 20:28

I will cite again a respect that was indicated above. You need to include the meta tag code inside the head tag:

<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta charset="utf-8" />
<!--This is what you should include-->
<meta http-equiv="content-type" content="application/vnd.ms-excel; charset=UTF-8">
<!---->
<title>Ver Listado Pago</title>
<link href="/Images/Decretos.ico" rel="shortcut icon" type="image/x-icon" />
<meta name="viewport" content="width=device-width" />
<link href="/Content/site.css" rel="stylesheet" />
<link href="~/Content/booostrap/bootstrap.min.css" rel="stylesheet" />
<link href="~/Content/booostrap/bootstrap-theme.css" rel="stylesheet" />
<link href="~/Content/booostrap/bootstrap-theme.min.css" rel="stylesheet" />

This has worked for me. But IE and WIN10 have some conflicts for download because of the xls extension. However, the problem of special characters is corrected

score 0 · Answer 6 · answered Mar 21 '22 at 05:47

after googling I found a solution edit file /tableExport/tableExport.js line 280

var base64 = function(s) { return window.btoa(unescape(encodeURIComponent(s))) } // New base64

// var base64data = "base64," + $.base64.encode(excelFile); OLD LINE
var base64data = "base64," + base64(excelFile); // New line with new base64

that is all

score -1 · Answer 7 · answered Mar 08 '17 at 01:46

-1

Use the code below in the var uri:

var uri = 'data:application/vnd.ms-excel;charset=UTF-8;base64,'

Output

answered Mar 08 '17 at 01:46

Maurivan

99
2

Encoding UTF-8 when exporting HTML table to Excel

7 Answers7

Linked

Related