Export html table data to Excel using JavaScript / JQuery is not working properly in chrome browser

Question

I have a HTML table in velocity template. I want to export the html table data to excel using either java script or jquery, comatibale with all browser. I am using below script

<script type="text/javascript">
function ExportToExcel(mytblId){
       var htmltable= document.getElementById('my-table-id');
       var html = htmltable.outerHTML;
       window.open('data:application/vnd.ms-excel,' + encodeURIComponent(html));
    }
</script>

This script works fine in Mozilla Firefox,it pops-up with a dialog box of excel and ask for open or save options. But when I tested same script in Chrome browser it is not working as expected,when clicked on button there is no pop-up for excel. Data gets downloaded in a file with "file type : file" , no extension like .xls There are no errors in chrome console.

Jsfiddle example :

http://jsfiddle.net/insin/cmewv/

This works fine in mozilla but not in chrome.

Chrome browser Test Case :

First Image:I click on Export to excel button

and result :

Result

Above test case fails if you are using open source product LibreOffice and Chrome browser. But if you have MS Office installed then code wil work properly. — Sukane, Mar 19 '14 at 17:39

score 206 · Accepted Answer · edited Jan 06 '15 at 18:26

206

Excel export script works on IE7+, Firefox and Chrome.

function fnExcelReport()
{
    var tab_text="<table border='2px'><tr bgcolor='#87AFC6'>";
    var textRange; var j=0;
    tab = document.getElementById('headerTable'); // id of table

    for(j = 0 ; j < tab.rows.length ; j++) 
    {     
        tab_text=tab_text+tab.rows[j].innerHTML+"</tr>";
        //tab_text=tab_text+"</tr>";
    }

    tab_text=tab_text+"</table>";
    tab_text= tab_text.replace(/<A[^>]*>|<\/A>/g, "");//remove if u want links in your table
    tab_text= tab_text.replace(/<img[^>]*>/gi,""); // remove if u want images in your table
    tab_text= tab_text.replace(/<input[^>]*>|<\/input>/gi, ""); // reomves input params

    var ua = window.navigator.userAgent;
    var msie = ua.indexOf("MSIE "); 

    if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./))      // If Internet Explorer
    {
        txtArea1.document.open("txt/html","replace");
        txtArea1.document.write(tab_text);
        txtArea1.document.close();
        txtArea1.focus(); 
        sa=txtArea1.document.execCommand("SaveAs",true,"Say Thanks to Sumit.xls");
    }  
    else                 //other browser not tested on IE 11
        sa = window.open('data:application/vnd.ms-excel,' + encodeURIComponent(tab_text));  

    return (sa);
}

Just create a blank iframe:

<iframe id="txtArea1" style="display:none"></iframe>

Call this function on:

<button id="btnExport" onclick="fnExcelReport();"> EXPORT </button>

edited Jan 06 '15 at 18:26

JasonMArcher

14,195
22
56
52

answered Jun 06 '14 at 11:59

sampopes

2,646
1
22
34

variable txt is not defined – mel3kings Jun 19 '14 at 06:50
1

if not then for what this line is?? var txt = document.getElementById('txtArea1').contentWindow; – sampopes Jun 20 '14 at 06:15
Nothing seems to happen with this solution. Is it still working for you? I know Chrome made an update that may have disabled some of these file download methods. – Kyle Bachan Jul 07 '14 at 19:01
@kyle: What issue you are facing with crome?? – sampopes Jul 08 '14 at 07:15
4

In Chrome, when you click the download button nothing happens at all when using this method. No console errors or anything. – Kyle Bachan Jul 08 '14 at 14:13
@Kyle: you cannot simply download in Chrome because now we need to use download API of Chrome.. or else it might be due to http://stackoverflow.com/questions/23816005/anchor-tag-download-attribute-not-working-bug-in-chrome-35-0-1916-114 . So developers will have to edit it for Chrome(only Save As functionality). – sampopes Aug 19 '14 at 10:28
1

Perfect +1 !!! Its working in IE 11, Chrome 35, Firefox 29, Opera 17 But not working in safari 5.1.7 !!! :( – Suganth G Nov 27 '14 at 12:16
@Jake: Actually I never tested on safari. Can you tell me what error you are getting? – sampopes Nov 27 '14 at 13:06
Its opening a new tab in browser and showing save or open file , If i click save or open nothing is happening :( – Suganth G Nov 28 '14 at 03:42
Is there any way to encode the HTML tags? In my column I have `This is Bold` and instead of that the column showing This is Bold, it should display `This is Bold` in bold text? – SearchForKnowledge Jan 06 '15 at 17:50
@SearchForKnowledge : You can do that by appending the
tab_text or use inner html on its parent node. – sampopes Jan 07 '15 at 11:02
@Jake745 : I never worked for safari but if you need it I'll try. – sampopes Jan 07 '15 at 11:07
@sampopes I want to take the last column of every row and convert the raw HTML into formatter display. – SearchForKnowledge Jan 07 '15 at 20:58
I tried this: `alert(tab.rows[j].column[0].innerHTML);` but that gave an error. – SearchForKnowledge Jan 07 '15 at 21:05
14

how can I change Randome name . I wnat set specefic name . – osman Rahimi Feb 02 '15 at 08:06
@osman: it depends on browser in some cases.. In IE it works but chrome n ff there is a problem.. – sampopes Feb 07 '15 at 04:31
Guys, the Chrome browser crashes when I have 18K records to export. Whereas it works fine in IE11. Any suggestions how can I fix this? – shawnnyglum Aug 28 '15 at 12:01
@sidrocks: This depends on browsers internal memory allocation and thus you will have variations based on number of records and length of a record. – sampopes Aug 31 '15 at 06:28
ohk! so seems like completely uncertain... any way I can ascertain before export that if the export is going to cross the browser's internal memory? In this case I can alert the users to filter the data before exporting. Thanks! – shawnnyglum Aug 31 '15 at 10:34
Cool, the only solution for jquery export of my table that works in Chrome and IE 10 Nice job! – Tom Stickel Oct 22 '15 at 20:58
26

"The file format and extension of download.xls don't match" This is not making the manager happy :/ Any ideas? – Tom Stickel Oct 30 '15 at 21:02
Can anyone please tell me a way to remove a specific column of cells only from being exported to excel, using something like this -> `tab.rows[j].cells[13]` ` for(j = 0 ; j < tab.rows.length ; j++) { tab_text=tab_text+tab.rows[j].innerHTML+""; }` ? Any help is reallyappreciated. – Disera Apr 13 '16 at 09:39
1

@Disera: You just need to traverse the array one more level down. Use a loop to traverse all cells of rows. Add all cells except the column number of you don't want. – sampopes Apr 13 '16 at 13:38
Works nice, however, images are not inserted properly. Are there any possible solutions? – Aleksandr Ianevski Sep 05 '16 at 23:19
Above code works for me on Chrome but not on IE11. Can any one please help? – Madhura Mar 29 '17 at 13:24
@sampopes I am trying to export HTML Table in xls format. But on Safari Browser, its creating unknown file. – Madhura Apr 03 '17 at 09:09
@sampopes Thanks for your useful solution. I have a question based on your answer and I would like to seek your advice please. I have a table, the pagesize is 10 because I have to show 10 rows/records for each page. The table contains 18 rows/records, so the table will have 2 pages, in the button if I click the button to export to excel, it can show rows/records in page 1. However in page 2, if I click the button to export excel, it shows rows/records in page 1. – Learner Oct 10 '17 at 01:38
@sampopes (cont) At this moment, I guess maybe the browser (Internet Explorer) remembers the content in page 1, so I refresh the browser, click the page 2 of the table and click the button to export the excel, howerver it (the excel) still shows rows/records in page 1. – Learner Oct 10 '17 at 01:38
@sampopes (cont) What I have tried: I add another iframe `txtArea2` and add code for `txtArea2` inside the function. I run the program, the result is the same. Do you have idea about how to export table data based on table pages? Thank you very much. – Learner Oct 10 '17 at 01:39
@Learner: At the time of exporting data your only one page will be loaded if you are using ajax calls/ websockets to get the data. So you may have to use something like cookies or local db to store data. – sampopes Oct 11 '17 at 11:26
Confirm @sidrocks words about size limitation. Chrome exports only 3000 rows instead of 10000 in table, whilst IE exports fine. – Suncatcher Nov 01 '17 at 10:47
@sampopes - I cannot change the name of file. In Chrome the file is downloaded as `download.xls` and in Firefox it is downloaded as `some_random_generated_name.xls`. Surprisingly, it works fine in IE10+ – Aakash Goplani Nov 07 '17 at 12:24
@AakashGoplani : Its a chrome & firefox policy for local file store. It was not a problem for me and other users so never tried to patch up something to fix this. – sampopes Nov 09 '17 at 13:40
Is it possible to export to multisheet book via this approach? – Suncatcher Nov 10 '17 at 11:09
@Suncatcher No. You can try other heavy built JS or Java libraries for that. – sampopes Nov 10 '17 at 12:01
What about Edge? – Erick Lanford Xenes Dec 19 '17 at 20:27
works well, supports unicode languages (arabic), perfect, no iframe needed – SoliQuiD Jan 16 '18 at 09:30
Please be aware that using `txtArea1.contentDocument` or `txtArea1.contentWindow.document` instead of `txtArea1.document` might save you some troubles – JanS Mar 13 '18 at 10:49
Any idea how to convert multiple html tables to multiple sheets in same excel? – Himalaya Garg Aug 30 '18 at 06:55
@HimalayaGarg: Multiple sheets would need xlsx format for which you will have to something which is bigger, may be a library or service. Try https://datatables.net/extensions/buttons/examples/html5/simple.html – sampopes Aug 31 '18 at 07:10
2

is it possible ti change the file name from "download.xls" to something else ? – Youssef Boudaya Jan 07 '19 at 08:56
Its something which is browserDependant as our js is run by browser. In case of xhr request the file name is retained but otherwise not. – sampopes Jan 08 '19 at 07:25
i know this is old.. but for so many upvotes, is it just me or does that generate invalid Html (only one .. ever.. but potentially so many ? – Michael Sefranek Mar 21 '19 at 19:01
I'm not able to do EXCEL operations with this sheet. eg: Autosum. Do we have any other way to do this..? – Nanda Kishore Allu Jun 25 '19 at 09:03
2

export name is always `download.xls` – J M Jul 02 '19 at 05:06
1

I have implemented this function however when using Chrome Version 79.0.3945.130 I keep getting a new tab opening with about:blank#blocked. I have adjusted the privacy settings to allow notifications, flash, automatic downloads, and pop-ups and redirects however no success. Any suggessions on rectifying this will be appreciated. – devdar Jan 17 '20 at 20:27
code works like magic!! but I have an issue. When I use datatable, it only displays first 10 data entries as default. How can I get all datatable entries? please help – danielpr Feb 24 '20 at 13:48
How can I deploy this with multiple sheets within the same excel file? – Ikechukwu Nov 08 '20 at 08:13
excellent, but if we can customize the name of the file in firefox and chrome it will be great – Mohamed Mehanny Feb 09 '21 at 13:00
What can we do in case of pagination? – ECS Roman Jul 07 '21 at 07:21
Great..! I uswe this function with Query Button click event and it works! – Gajen Dissanayake Mar 30 '22 at 21:46
I want to write bootstrap class inside tab_text for custom header with data. How can I write? – Md. Tazimul Islam Raj Aug 04 '22 at 08:45

score 46 · Answer 2 · answered Aug 04 '16 at 07:35

46

Datatable plugin solves the purpose best and allows us to export the HTML table data into Excel , PDF , TEXT. easily configurable.

Please find the complete example in below datatable reference link :

https://datatables.net/extensions/buttons/examples/html5/simple.html

(screenshot from datatable reference site)

answered Aug 04 '16 at 07:35

Aditya

2,385
18
25

6

OMG! This is an awesome plugin!... and has several plugins embedded!! Export, Sort, Reorder, Print Preview, etc... Solved all my data showing needs in just 1 shot! – El Gucs Oct 16 '16 at 10:21
Can I apply styles to my excel using this plugin? – Shruthi Sathyanarayana Jun 14 '17 at 06:43
@Addy this is a DataTables only solution though, right? – Karl Aug 28 '17 at 10:53
1

The export button will remove each page row count drop down, if you want to keep both things ? please check https://datatables.net/extensions/buttons/examples/initialisation/pageLength.html to resolve it. – Kaushik Das Sep 07 '17 at 11:41
The plugin is great. Is there a way to just extract the relevant code to export the data and highlight here? That'd be a great help. Thanks in advance – Murtuza Husain Mar 04 '19 at 08:53
this is good! but i have a problem with this plugin is not support multiple in is there any solution, please let me know. thanks! – Mizanur Rahman Aug 01 '21 at 04:29

score 43 · Answer 3 · answered Jan 08 '15 at 15:14

This could help

function exportToExcel(){
var htmls = "";
            var uri = 'data:application/vnd.ms-excel;base64,';
            var 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>'; 
            var base64 = function(s) {
                return window.btoa(unescape(encodeURIComponent(s)))
            };

            var format = function(s, c) {
                return s.replace(/{(\w+)}/g, function(m, p) {
                    return c[p];
                })
            };

            htmls = "YOUR HTML AS TABLE"

            var ctx = {
                worksheet : 'Worksheet',
                table : htmls
            }


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

what if I only want to export columns which are visible in the html table?? — Nouman Bhatti, Nov 03 '16 at 00:47
Hi there, i'm not able to do any EXCEL operations with the sheet, eg: Autosum. Is that because of data format of this EXCEL sheet or any other issues.. I need help to do some operations. — Nanda Kishore Allu, Jun 25 '19 at 09:00

score 26 · Answer 4 · answered Mar 03 '20 at 16:10

You can use tableToExcel.js to export table in excel file.

This works in a following way :

1). Include this CDN in your project/file

<script src="https://cdn.jsdelivr.net/gh/linways/table-to-excel@v1.0.4/dist/tableToExcel.js"></script>

2). Either Using JavaScript:

<button id="btnExport" onclick="exportReportToExcel(this)">EXPORT REPORT</button>

function exportReportToExcel() {
  let table = document.getElementsByTagName("table"); // you can use document.getElementById('tableId') as well by providing id to the table tag
  TableToExcel.convert(table[0], { // html code may contain multiple tables so here we are refering to 1st table tag
    name: `export.xlsx`, // fileName you could use any name
    sheet: {
      name: 'Sheet 1' // sheetName
    }
  });
}

3). Or by Using Jquery

<button id="btnExport">EXPORT REPORT</button>

$(document).ready(function(){
    $("#btnExport").click(function() {
        let table = document.getElementsByTagName("table");
        TableToExcel.convert(table[0], { // html code may contain multiple tables so here we are refering to 1st table tag
           name: `export.xlsx`, // fileName you could use any name
           sheet: {
              name: 'Sheet 1' // sheetName
           }
        });
    });
});

You may refer to this github link for any other information

https://github.com/linways/table-to-excel/tree/master

or for referring the live example visit the following link

https://codepen.io/rohithb/pen/YdjVbb

Hope this will help someone :-)

is there any way that we can ignore some of the columns before exporting ? — Jegan Baskaran, Oct 21 '22 at 07:50
@JeganBaskaran yes, this library comes with built-in attribute "". I know it is late but it might help someone :) — Ammar F-A, Feb 15 '23 at 12:25

score 15 · Answer 5 · answered Mar 06 '19 at 12:50

Instead of using window.open you can use a link with the onclick event.
And you can put the html table into the uri and set the file name to be downloaded.

Live demo :

function exportF(elem) {
  var table = document.getElementById("table");
  var html = table.outerHTML;
  var url = 'data:application/vnd.ms-excel,' + escape(html); // Set your html table into url 
  elem.setAttribute("href", url);
  elem.setAttribute("download", "export.xls"); // Choose the file name
  return false;
}

<table id="table" border="1">
  <tr>
    <td>
      Foo
    </td>
    <td>
      Bar
    </td>
  </tr>
</table>

<a id="downloadLink" onclick="exportF(this)">Export to excel</a>

how can i setting the table border , td width etc in function ? — Zeffry Reynando, Apr 27 '20 at 14:06

insign · Answer 6 · 2021-11-23T14:22:57.373

14

TableExport - The simple, easy-to-implement library to export HTML tables to xlsx, xls, csv, and txt files.

To use this library, simple call the TableExport constructor:

new TableExport(document.getElementsByTagName("table"));

// OR simply

TableExport(document.getElementsByTagName("table"));

// OR using jQuery

$("table").tableExport();

Additional properties can be passed-in to customize the look and feel of your tables, buttons, and exported data. See here more info

edited Nov 23 '21 at 14:22

answered Jul 28 '17 at 19:06

insign

5,353
1
38
35

Does it support PhantomJs? How to report bugs? – golimar Feb 07 '20 at 16:22

score 9 · Answer 7 · edited Jul 28 '15 at 08:59

9

http://wsnippets.com/export-html-table-data-excel-sheet-using-jquery/ try this link it might solve your problem

enter image description here

edited Jul 28 '15 at 08:59

Mohammad Faisal

5,783
15
70
117

answered Mar 18 '14 at 07:02

Uchit Shrestha

549
2
5

Uchit,thank you for your answer.But I have already tried this example.As I have mentioned I question it it work fine in Mozilla but not in Chorme.Can please try this example in Chrome browser ? – Sukane Mar 18 '14 at 10:58
5

I have tried Your code on jsfiddle and i m getting positive result. It's working fine even in chorme. You can see in the above image that download was sucessfull – Uchit Shrestha Mar 19 '14 at 06:23
1

Uchit,thank you.Yes the code is working. Problem is I was using Libre Office,so in Chrome export to excel was not working fine (ref screen shot in m question). But in MS office it working,as you have mentioned. Thank you for providing a jQuery code for this. – Sukane Mar 19 '14 at 17:35
This function is not exporting the values of fields having drop downs..and timestamps – SNT93 Oct 19 '15 at 07:14
it doesn't work with multiples tables html, only with one table. How can I do this with multiples tables?????? – Ignacio Chiazzo Nov 01 '15 at 14:07
I wasn't able to open mentioned link? – Isma Feb 24 '20 at 17:59

score 7 · Answer 8 · answered Sep 19 '19 at 12:41

My merge of these examples:

https://www.codexworld.com/export-html-table-data-to-excel-using-javascript https://bl.ocks.org/Flyer53/1de5a78de9c89850999c

function exportTableToExcel(tableId, filename) {
    let dataType = 'application/vnd.ms-excel';
    let extension = '.xls';

    let base64 = function(s) {
        return window.btoa(unescape(encodeURIComponent(s)))
    };

    let 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>';
    let render = function(template, content) {
        return template.replace(/{(\w+)}/g, function(m, p) { return content[p]; });
    };

    let tableElement = document.getElementById(tableId);

    let tableExcel = render(template, {
        worksheet: filename,
        table: tableElement.innerHTML
    });

    filename = filename + extension;

    if (navigator.msSaveOrOpenBlob)
    {
        let blob = new Blob(
            [ '\ufeff', tableExcel ],
            { type: dataType }
        );

        navigator.msSaveOrOpenBlob(blob, filename);
    } else {
        let downloadLink = document.createElement("a");

        document.body.appendChild(downloadLink);

        downloadLink.href = 'data:' + dataType + ';base64,' + base64(tableExcel);

        downloadLink.download = filename;

        downloadLink.click();
    }
}

Uncaught ReferenceError: base64 is not defined – Pedro Joaquín Feb 12 '20 at 15:01 — Pedro Joaquín, Feb 12 '20 at 15:01

Dilip Kumar Choudhary · Answer 9 · 2023-05-09T05:17:19.640

Simplest way using Jquery

Just add this after head Tag:

<script src="https://cdn.jsdelivr.net/gh/linways/table-to-excel@v1.0.4/dist/tableToExcel.js"></script>

Then add Jquery script inside Body Tag:

<script type="text/javascript">
  $(document).ready(function () {
      $("#exportBtn1").click(function(){
        TableToExcel.convert(document.getElementById("tab1"), {
            name: "Traceability.xlsx",
            sheet: {
            name: "Sheet1"
            }
          });
        });
  });
</script>

Then add HTML button:

<button id="exportBtn1">Export To Excel</button><br><br>

Note: "exportBtn1" will be button ID and "tab1" will be table ID

score 4 · Answer 10 · answered Nov 19 '15 at 19:46

4

You can use a library like ShieldUI to do that.

It supports exporting to both XML and XLSX widely-used Excel formats.

More details here: http://demos.shieldui.com/web/grid-general/export-to-excel

answered Nov 19 '15 at 19:46

Vladimir Georgiev

1,949
23
26

score 4 · Answer 11 · answered Mar 16 '16 at 12:54

Regarding to sampopes answer from Jun 6 '14 at 11:59:

I have insert a css style with font-size of 20px to display the excel data greater. In sampopes code the leading <tr> tags are missing, so i first output the headline and than the other tables lines within a loop.

function fnExcelReport()
{
    var tab_text = '<table border="1px" style="font-size:20px" ">';
    var textRange; 
    var j = 0;
    var tab = document.getElementById('DataTableId'); // id of table
    var lines = tab.rows.length;

    // the first headline of the table
    if (lines > 0) {
        tab_text = tab_text + '<tr bgcolor="#DFDFDF">' + tab.rows[0].innerHTML + '</tr>';
    }

    // table data lines, loop starting from 1
    for (j = 1 ; j < lines; j++) {     
        tab_text = tab_text + "<tr>" + tab.rows[j].innerHTML + "</tr>";
    }

    tab_text = tab_text + "</table>";
    tab_text = tab_text.replace(/<A[^>]*>|<\/A>/g, "");             //remove if u want links in your table
    tab_text = tab_text.replace(/<img[^>]*>/gi,"");                 // remove if u want images in your table
    tab_text = tab_text.replace(/<input[^>]*>|<\/input>/gi, "");    // reomves input params
    // console.log(tab_text); // aktivate so see the result (press F12 in browser)

    var ua = window.navigator.userAgent;
    var msie = ua.indexOf("MSIE "); 

     // if Internet Explorer
    if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./)) {
        txtArea1.document.open("txt/html","replace");
        txtArea1.document.write(tab_text);
        txtArea1.document.close();
        txtArea1.focus(); 
        sa = txtArea1.document.execCommand("SaveAs", true, "DataTableExport.xls");
    }  
    else // other browser not tested on IE 11
        sa = window.open('data:application/vnd.ms-excel,' + encodeURIComponent(tab_text));  

    return (sa);
}

Can you please tell me how to remove specific column from being exported to excel. For example, i want to avoid `tab.rows[j].cells[13] `, your help is really appreciated — Disera, Apr 13 '16 at 09:21
Very nice answer, for a very simple table I would just keep the last line: ```window.open('data:application/vnd.ms-excel,' + encodeURIComponent(tab_text));``` the enconding is very important. — pearpages, May 19 '16 at 14:10

score 4 · Answer 12 · edited Jun 19 '18 at 20:48

 function exportToExcel() {
        var tab_text = "<tr bgcolor='#87AFC6'>";
        var textRange; var j = 0, rows = '';
        tab = document.getElementById('student-detail');
        tab_text = tab_text + tab.rows[0].innerHTML + "</tr>";
        var tableData = $('#student-detail').DataTable().rows().data();
        for (var i = 0; i < tableData.length; i++) {
            rows += '<tr>'
                + '<td>' + tableData[i].value1 + '</td>'
                + '<td>' + tableData[i].value2 + '</td>'
                + '<td>' + tableData[i].value3 + '</td>'
                + '<td>' + tableData[i].value4 + '</td>'
                + '<td>' + tableData[i].value5 + '</td>'
                + '<td>' + tableData[i].value6 + '</td>'
                + '<td>' + tableData[i].value7 + '</td>'
                + '<td>' +  tableData[i].value8 + '</td>'
                + '<td>' + tableData[i].value9 + '</td>'
                + '<td>' + tableData[i].value10 + '</td>'
                + '</tr>';
        }
        tab_text += rows;
        var data_type = '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 border="2px">{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];
                })
            }

        var ctx = {
            worksheet: "Sheet 1" || 'Worksheet',
            table: tab_text
        }
        document.getElementById("dlink").href = data_type + base64(format(template, ctx));
        document.getElementById("dlink").download = "StudentDetails.xls";
        document.getElementById("dlink").traget = "_blank";
        document.getElementById("dlink").click();
    }

Here Value 1 to 10 are column names that you are getting

score 2 · Answer 13 · answered Aug 02 '22 at 05:09

Below code will work on latest Chrome , Edge , Firefox , not require 3rd party library.

HTML

<button onclick="download_table_as_csv('MyTableID_Value');">Export as CSV</button>

Jscript

function download_table_as_csv(table_id, separator = ',') {
// Select rows from table_id
var rows = document.querySelectorAll('table#' + table_id + ' tr');
// Construct csv
var csv = [];
for (var i = 0; i < rows.length; i++) {
    var row = [], cols = rows[i].querySelectorAll('td, th');
    for (var j = 0; j < cols.length; j++) {
        // Clean innertext to remove multiple spaces and jumpline (break csv)
        var data = cols[j].innerText.replace(/(\r\n|\n|\r)/gm, '').replace(/(\s\s)/gm, ' ')
        // Escape double-quote with double-double-quote (see https://stackoverflow.com/questions/17808511/properly-escape-a-double-quote-in-csv)
        data = data.replace(/"/g, '""');
        // Push escaped string
        row.push('"' + data + '"');
    }
    csv.push(row.join(separator));
}
var csv_string = csv.join('\n');
// Download it
var filename = 'export_' + table_id + '_' + new Date().toLocaleDateString() + '.csv';
var link = document.createElement('a');
link.style.display = 'none';
link.setAttribute('target', '_blank');
link.setAttribute('href', 'data:text/csv;charset=utf-8,' + encodeURIComponent(csv_string));
link.setAttribute('download', filename);
document.body.appendChild(link);
link.click();
document.body.removeChild(link);}

score 1 · Answer 14 · answered Oct 26 '18 at 10:34

My version of @sampopes answer

function exportToExcel(that, id, hasHeader, removeLinks, removeImages, removeInputParams) {
if (that == null || typeof that === 'undefined') {
    console.log('Sender is required');
    return false;
}

if (!(that instanceof HTMLAnchorElement)) {
    console.log('Sender must be an anchor element');
    return false;
}

if (id == null || typeof id === 'undefined') {
    console.log('Table id is required');
    return false;
}
if (hasHeader == null || typeof hasHeader === 'undefined') {
    hasHeader = true;
}
if (removeLinks == null || typeof removeLinks === 'undefined') {
    removeLinks = true;
}
if (removeImages == null || typeof removeImages === 'undefined') {
    removeImages = false;
}
if (removeInputParams == null || typeof removeInputParams === 'undefined') {
    removeInputParams = true;
}

var tab_text = "<table border='2px'>";
var textRange;

tab = $(id).get(0);

if (tab == null || typeof tab === 'undefined') {
    console.log('Table not found');
    return;
}

var j = 0;

if (hasHeader && tab.rows.length > 0) {
    var row = tab.rows[0];
    tab_text += "<tr bgcolor='#87AFC6'>";
    for (var l = 0; l < row.cells.length; l++) {
        if ($(tab.rows[0].cells[l]).is(':visible')) {//export visible cols only
            tab_text += "<td>" + row.cells[l].innerHTML + "</td>";
        }
    }
    tab_text += "</tr>";
    j++;
}

for (; j < tab.rows.length; j++) {
    var row = tab.rows[j];
    tab_text += "<tr>";
    for (var l = 0; l < row.cells.length; l++) {
        if ($(tab.rows[j].cells[l]).is(':visible')) {//export visible cols only
            tab_text += "<td>" + row.cells[l].innerHTML + "</td>";
        }
    }
    tab_text += "</tr>";
}

tab_text = tab_text + "</table>";
if (removeLinks)
    tab_text = tab_text.replace(/<A[^>]*>|<\/A>/g, "");
if (removeImages)
    tab_text = tab_text.replace(/<img[^>]*>/gi, ""); 
if (removeInputParams)
    tab_text = tab_text.replace(/<input[^>]*>|<\/input>/gi, "");

var ua = window.navigator.userAgent;
var msie = ua.indexOf("MSIE ");

if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./))      // If Internet Explorer
{
    myIframe.document.open("txt/html", "replace");
    myIframe.document.write(tab_text);
    myIframe.document.close();
    myIframe.focus();
    sa = myIframe.document.execCommand("SaveAs", true, document.title + ".xls");
    return true;
}
else {
    //other browser tested on IE 11
    var result = "data:application/vnd.ms-excel," + encodeURIComponent(tab_text);
    that.href = result;
    that.download = document.title + ".xls";
    return true;
}
}

Requires an iframe

<iframe id="myIframe" style="opacity: 0; width: 100%; height: 0px;" seamless="seamless"></iframe>

Usage

$("#btnExportToExcel").click(function () {
    exportToExcel(this, '#mytable');
});

Bulbul Sarker · Answer 15 · 2020-11-24T07:38:35.560

Very Easy Code
Follow this instruction
Create excel.php file in your localhost root directory and copy and past this code.
Like this
http://localhost/excel.php?fileName=excelfile&link=1
<!-- http://localhost/excel.php?fileName=excelfile&link=2 -->

<!DOCTYPE html>
<html>
<head>
    <title>Export excel file from html table</title>
</head>
<body style="display:
<?php 
if( $_REQUEST['link'] == 1 ){
    echo 'none';
}
?>;
">

<!-- --------Optional-------- -->
Excel <input type="radio" value="1" name="exportFile">
Others <input type="radio" value="2" name="exportFile">
<button onclick="myFunction()">Download</button>
<br>
<br>
<!-- --------/Optional-------- -->

<table width="100%" id="tblData">
    <tbody>
        <tr>
            <th>Student Name</th>
            <th>Group</th>
            <th>Roll No.</th>
            <th>Class</th>
            <th>Contact No</th>
        </tr>
        <tr>
            <td>Bulbul Sarker</td>
            <td>Science</td>
            <td>1</td>
            <td>Nine</td>
            <td>01724....</td>
        </tr>
        <tr>
            <td>Karim</td>
            <td>Science</td>
            <td>3</td>
            <td>Nine</td>
            <td>0172444...</td>
        </tr>
    </tbody>
</table>

</body>
</html>

<style>
    table#tblData{
        border-collapse: collapse;
    }
    #tblData th,
    #tblData td{
        border:1px solid #CCC;
        text-align: center;
    }
</style>

<script type="text/javascript">

    /*--------Optional--------*/
    function myFunction() {
        let val = document.querySelector('input[name="exportFile"]:checked').value;     
        if(val == 1)
        {
            this.exportTableToExcel();
        }
    }
    /*--------/Optional--------*/

    function exportTableToExcel(){
        let filename2 = "<?php echo $_REQUEST['fileName']; ?>";
        let tableId = 'tblData';

        var downloadLink;
        var dataType = 'application/vnd.ms-excel';
        var tableSelect = document.getElementById(tableId);
        var tableHTML = tableSelect.outerHTML.replace(/ /g, '%20');

            // Specify file name
            let filename = filename2?filename2+'.xls':'excel_data.xls';

            // Create download link element
            downloadLink = document.createElement("a");

            document.body.appendChild(downloadLink);

            if(navigator.msSaveOrOpenBlob){
                var blob = new Blob(['\ufeff', tableHTML], {
                    type: dataType
                });
                navigator.msSaveOrOpenBlob( blob, filename);
            }else{
            // Create a link to the file
            downloadLink.href = 'data:' + dataType + ', ' + tableHTML;

            // Setting the file name
            downloadLink.download = filename;

            //triggering the function
            downloadLink.click();
        }       
    }
</script>

<?php
if( $_REQUEST['link'] == 1 ){       
    echo '<script type="text/javascript">
    exportTableToExcel();
    </script>'; 
}
?>

Murosadatul · Answer 16 · 2021-10-27T07:12:09.610

Html

<a onclick="download_to_excel()">Download</a> 

    
 <table id="tableId">
          <thead>
              <tr>
              <th>No</th>
              <th>Name</th>
              <th>Address</th>
            </tr>
          </thead>
          <tbody>
          <tr>
            <td colspan="3">Data Not Found</td>
          </tr>
          </tbody>
        </table>

JavaScript

function download_to_excel()
{ 

var tab_text="<table><tr>";
var textRange = ''; 
var j=0;
var tab = document.getElementById('tableId'); // id of table

for(j = 0 ; j < tab.rows.length ; j++) 
{     
    tab_text += tab.rows[j].innerHTML+"</tr>";
}

tab_text +="</table>";

var a = document.createElement('a');
var data_type = 'data:application/vnd.ms-excel';
a.href = data_type + ', ' + encodeURIComponent(tab_text);
//setting the file name
a.download = 'file_name.xls';
//triggering the function
a.click();
//just in case, prevent default behaviour
e.preventDefault();

}

Export html table data to Excel using JavaScript / JQuery is not working properly in chrome browser

16 Answers16

Linked

Related