Export from html table to excel

Asked

Viewed 5,503 times

6

I am trying to export an html table to an Excel file. This jquery plugin works almost perfectly for me.

My problem is with accentuation, which comes to excel with strange characters. Some tip on how to solve?

inserir a descrição da imagem aqui

javascript jquery excel
  • 1

    Check https://github.com/jmaister/excellentexport/blob/master/excellentexport.js on line 19 and you will know the reason why.

  • 1

    I saw that there are some characters, but I wouldn’t know where to put the accents. I tried at first but it wasn’t, I’m going to test the @Filipe.Fonseca response

  • @2madera Some hint of how to put a list of characters that works in the plugin?

  • 1

    Try changing the html page encoding to iso-8859-1 and adding the characters in the javascript as per the answer below. Office uses ANSI text by default.

  • It didn’t solve. I tried to put a meta tag charset with iso-8859-1 on line 87, on the template variable, but it also didn’t work.

2 answers

3


In the archive Excellentexport.js,in this line:

var template = {excel: '<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>'}

Add <meta charset="UTF-8"> within the <head></head>

Thus remaining:

var template = {excel: '<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><meta charset="UTF-8"><!--[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>'}

If your hmtl is UTF-8, clear-cut

  • It worked! I had put another charset and forgot to test the utf-8. Thanks!

1

Simple function I did for this purpose.

//adicionar a classe 'remove' em elementos que não devem aparecer no excel
//(inclusive em inputs type='hidden')
function exportaExcel(id,nome){ //id da <table> e nome do arquivo a ser gerado
    var hoje = new Date();
    var data = ('0'+hoje.getDate()).slice(-2)+"-"+('0'+(hoje.getMonth()+1)).slice(-2)+"-"+hoje.getFullYear();

    var nome_arquivo = nome+"_"+data+".xls"; //nome final do arquivo
    var meta = '<meta http-equiv="content-type" content="text/html; charset=UTF-8" />';
    var html = $("#"+id).clone();

    html.find('.remove').remove();
    html.find('a').each(function() {
        var txt = $(this).text();
        $(this).after(txt).remove();
    });
    html.find('input, textarea').each(function() {
        var txt = $(this).val().replace(/\r\n|\r|\n/g,"<br>");
        $(this).after(txt).remove();
    });
    html.find('select').each(function() {
        var txt = $(this).find('option:selected').text();
        $(this).after(txt).remove();
    });
    html.find('br').attr('style', "mso-data-placement:same-cell");
    html = "<table>"+html.html()+"</table>";

    var uri = 'data:application/vnd.ms-excel,'+encodeURIComponent(meta+html);
    var a = $("<a>", {href: uri, download: nome_arquivo});
    $(a)[0].click();
}

You can call the function by clicking a button for example:

$("#botao_exportar").click(function(e){
    exportaExcel("id_tabela", "nome_arquivo");
});

Some remarks:

  • Requires jQuery (although with few modifications it is possible to use without)
  • As far as testing works only in Chrome, I believe that with certain adaptations it should be possible to use in other browsers.
  • Add the "remove" class in table elements that should not appear in Excel.
  • Modify the variable "filename" as needed (in my case I added today’s date for reporting purposes).
  • The function replaces inputs, textareas and selects for their respective texts.

Browser other questions tagged javascript jquery excel

You are not signed in. Login or sign up in order to post.