Export html table to Excel javascript function special characters changed

Question

I have the following function that exports an html to excel:

function generateexcel(tableid) {
  var table= document.getElementById(tableid);
  var html = table.outerHTML;
  window.open('data:application/vnd.ms-excel,' + encodeURIComponent(html));
}

One problem is that the especial characters in the data are transformed to other symbols:

1º = 1Âº
é = Ã©

How would you fix this? Is there any character replace to the html to prevent it? Any encoding option?

Have you tried adding an explicit encoding declaration to the media type? `data:application/vnd.ms-excel;charset=UTF-8` — Pointy, Jun 18 '12 at 14:07
Yep it was one of the solutions i found and tried before asking here, it didnt work with the different charsets i tried... Details here: http://www.weblogism.com/item/270/why-does-e-become-a and http://www.weblogism.com/item/271/why-does-e-become-a-ii — VSP, Jun 18 '12 at 15:17

score 9 · Accepted Answer · edited Mar 15 '17 at 08:28

9

Replacing chars is a poor solution.

I replaced encodeURIComponent for escape and works fine, but escape is deprecated since ECMAScript v3.

This issue occurs because encodeURIComponent works with UTF-8 and Excel does not.

Better way for me.

Encode data to base64 and export like this. I used jquery-base64 plugin from https://github.com/carlo/jquery-base64/blob/master/jquery.base64.min.js

And change code to:

window.open('data:application/vnd.ms-excel;base64,' + $.base64.encode(html));

If you don't want to use jquery, you can use this base64_encode function http://phpjs.org/functions/base64_encode

"Base64 encoding/decoding is already a native function in modern(tm) browsers: btoa(str) and atob(str) are the functions that should can be used without any external reimplementation." - chipairon

edited Mar 15 '17 at 08:28

agriboz

4,724
4
35
49

answered Apr 02 '13 at 15:41

Pedro Muniz

568
12
19

Its a good idea, if you could add a direct EncodetoBase64() function to your response for the people that dont want to use extra plugins like jquery i will grant the response to you – VSP Apr 03 '13 at 09:16
4

Base64 encoding/decoding is already a native function in modern(tm) browsers: `btoa(str)` and `atob(str)` are the functions that should can be used without any external reimplementation. Take a look at https://developer.mozilla.org/en-US/docs/Web/JavaScript/Base64_encoding_and_decoding – chipairon Jan 23 '14 at 15:58
I set your answer as the correct one as promised, but if you could add the native function option contributed by @chipairon to the answer for other people to see it would be great ^^ – VSP Apr 07 '14 at 13:37
And how do you name your file? – Pedro Joaquín Oct 25 '19 at 15:41

score 4 · Answer 2 · answered Jun 18 '12 at 15:16

4

Solved adding a replace for the problematic symbols:

function generateexcel(tableid) {
  var table= document.getElementById(tableid);
  var html = table.outerHTML;

  //add more symbols if needed...
  while (html.indexOf('á') != -1) html = html.replace('á', '&aacute;');
  while (html.indexOf('é') != -1) html = html.replace('é', '&eacute;');
  while (html.indexOf('í') != -1) html = html.replace('í', '&iacute;');
  while (html.indexOf('ó') != -1) html = html.replace('ó', '&oacute;');
  while (html.indexOf('ú') != -1) html = html.replace('ú', '&uacute;');
  while (html.indexOf('º') != -1) html = html.replace('º', '&ordm;');

  window.open('data:application/vnd.ms-excel,' + encodeURIComponent(html));
}

answered Jun 18 '12 at 15:16

VSP

2,367
8
38
59

1

Simpler (prob. faster too): `html = html.replace(/á/g, '$aacute;');` etc. - the "g" after the regex makes it perform the substitution throughout the string, so you don't need the `while` loop. – Pointy Jun 18 '12 at 15:31
1

@Pointy It works but the replace would be html = html.replace(/á/g, 'á'); not $, if you put it as an answer i will accept it – VSP Jun 19 '12 at 07:13
1

I think it is a poor solution, we have so much symbols to replace all. I replaced encodeURIComponent for escape and works fine. – Pedro Muniz Apr 02 '13 at 15:26

score 3 · Answer 3 · answered Mar 27 '13 at 16:44

3

I have the same issue, just replace encodeURIComponent for escape.

function generateexcel(tableid) {
 var table= document.getElementById(tableid);
 var html = table.outerHTML;
 window.open('data:application/vnd.ms-excel,' + escape(html));
}

It works for me...

answered Mar 27 '13 at 16:44

Deyvid Martinez

430
3
8

1

As Pedro Muniz said, `escape` is being deprecated. It is preferable to use base64 encoding. – chipairon Jan 23 '14 at 15:56

score 2 · Answer 4 · edited Apr 28 '15 at 09:38

2

Just replace encodeURIComponent with escape.

edited Apr 28 '15 at 09:38

Krzysztof

15,900
2
46
76

answered Jun 21 '13 at 12:33

Ramon Lucas

167
1
4

score 1 · Answer 5 · answered Jul 27 '15 at 21:57

In my case I use generateexcel function previously posted, just adding Capital letters of special characters in order to make it work

    function generateexcel(tableid) {
      var table= document.getElementById(tableid);
      var html = table.outerHTML;
      while (html.indexOf('á') != -1) html = html.replace('á', '&aacute;');
      while (html.indexOf('Á') != -1) html = html.replace('Á', '&Aacute;');
      while (html.indexOf('é') != -1) html = html.replace('é', '&eacute;');
      while (html.indexOf('É') != -1) html = html.replace('É', '&Eacute;');
      while (html.indexOf('í') != -1) html = html.replace('í', '&iacute;');
      while (html.indexOf('Í') != -1) html = html.replace('Í', '&Iacute;');
      while (html.indexOf('ó') != -1) html = html.replace('ó', '&oacute;');
      while (html.indexOf('Ó') != -1) html = html.replace('Ó', '&Oacute;');
      while (html.indexOf('ú') != -1) html = html.replace('ú', '&uacute;');
      while (html.indexOf('Ú') != -1) html = html.replace('Ú', '&Uacute;');
      while (html.indexOf('º') != -1) html = html.replace('º', '&ordm;');
      while (html.indexOf('ñ') != -1) html = html.replace('ñ', '&ntilde;'); 
      while (html.indexOf('Ñ') != -1) html = html.replace('Ñ', '&Ntilde;');  

  window.open('data:application/vnd.ms-excel,' + encodeURIComponent(html));
}

Hope it helps...

Export html table to Excel javascript function special characters changed

5 Answers5

Linked