HTML table export to excel

Navigation
6

I'm trying to export a table in html to file in Excel. This jquery plugin works almost perfectly for me.

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

    
asked by anonymous 11.03.2014 / 14:32

2 answers

3

In the Excellentexport.js file, 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 <head></head>

Looking like this: 

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 , of course

    
11.03.2014 / 16:12
1

Simple function I made 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 comments:

  • Requires jQuery (although with few modifications you can use it without)
  • As far as I tested it only works in Chrome, I believe that with certain adaptations it should be possible to use it in other browsers.
  • Add the "remove" class to table elements that should not appear in Excel.
  • Modify the variable "filename" according to your needs (in my case I added the date of today for reporting purposes).
  • The function replaces inputs, textareas and selects with their respective texts.
24.05.2017 / 23:24
How to upload Dynamic Images in JSF? What are database model migrations?