2

I tried to export my html table to excel using the code given in this Gist. But after exporting, when i opened the file, It displays the html code of the demo page in excel. Can anyone please give the correct sample of javascript used to export the html table to excel (Should be opened in office Calc too).

EDIT: Attached the image screenshot.

Attached the image screenshot.

Pradeep Shankar
  • 109
  • 1
  • 3
  • 10
  • The sample works just fine here. Win7, Google Chrome, Office 2007. I do note, the excel message-box shown when opening the file: "The file you are trying to open 'download.xls', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?" - renaming the file to download.xlsx caused excel to refuse to open it. However, as it stands, the excel file looks just the same as the html table, complete with blue background in cell(2,2) – enhzflep Mar 22 '13 at 06:15
  • possibly duplicate http://stackoverflow.com/questions/13710405/javascript-table-export-to-excel – Trikaldarshiii Mar 22 '13 at 06:41
  • also http://stackoverflow.com/questions/4507666/html-to-excel-export – Trikaldarshiii Mar 22 '13 at 06:42
  • @enhzflep - I tried using Win XP, Google chrome, Open office. Please see the attached image in edit. – Pradeep Shankar Mar 22 '13 at 07:01
  • I also tried the example in [this link](http://jsfiddle.net/jquerybyexample/xhYcD/). But same result. – Pradeep Shankar Mar 22 '13 at 07:13
  • Visit http://stackoverflow.com/questions/3206775/export-html-table-to-excel-using-jquery-or-java for the solution. –  Mar 22 '13 at 07:13
  • @PradeepShankar - Oh, I see. When you said Excel, I thought you meant excel - not 'some generic spreadsheet program'(i.e. OpenOffice Calc). If I save the file as download.html, and open a web-page with *excel*, it opens without a problem. If I try to open it with LibreOffice, it opens in LibreWord each and every time - if the file extension is HTML or XML. If the file (qIO7mWPV.xls) is opened with LibreOffice Calc, the spreadsheet looks just as expected, albeit without the blue background in cell(2,2). Using Linux Mint R13 x64, LibreOffice 3.5.2.2 – enhzflep Mar 24 '13 at 00:49
  • @enhzflep - I think it is possible to open in Open office if the table is exported in csv format. Am i right? – Pradeep Shankar Mar 24 '13 at 04:19
  • I'd expect so Pradeep (but don't know). – enhzflep Mar 24 '13 at 04:53

1 Answers1

0

Here is a function I made.

Add "remove" class on elements you do not want to show in the excel.

function exportExcel(id,name){ //<table> id and filename
    var today = new Date();
    var date = ('0'+today.getDate()).slice(-2)+"-"+('0'+(today.getMonth()+1)).slice(-2)+"-"+today.getFullYear();

    var file_name = name+"_"+date+".xls"; //filename with current date, change if needed
    var meta = '<meta http-equiv="content-type" content="text/html; charset=UTF-8" />';
    var html = $("#"+id).clone();

    html.find('.remove').remove(); //add the 'remove' class on elements you do not want to show in the excel
    html.find('a').each(function() { //remove links, leave text only
        var txt = $(this).text();
        $(this).after(txt).remove();
    });
    html.find('input, textarea').each(function() { //replace inputs for their respectives texts
        var txt = $(this).val().replace(/\r\n|\r|\n/g,"<br>");
        $(this).after(txt).remove();
    });
    html.find('select').each(function() { //replace selects for their selected option text
        var txt = $(this).find('option:selected').text();
        $(this).after(txt).remove();
    });
    html.find('br').attr('style', "mso-data-placement:same-cell"); //make line breaks show in single cell
    html = "<table>"+html.html()+"</table>";

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

Call it on an event, example:

$("#export_button").click(function(e){
    exportExcel("table_id", "filename");
});
Bob
  • 41
  • 8