Export to CSV using jQuery and html

Question

I have a tabular data which I need to export to csv without using any external plugin or api. I used the window.open method passing the mime types but faced issues like below:

How to determine whether Microsoft Excel or Open Office is installed on the system using jquery

The code should be independent of the fact that what is being installed on the system i.e., openoffice or ms excel. I believe CSV is the format which can be expected to show in both the editors.

CODE

    <html>

<head>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>  

<script type="text/JavaScript">
$(document).ready(function(){
    $("#btnExport").click(function(e) {
        var msg = GetMimeTypes();
        //OpenOffice
        window.open('data:application/vnd.oasis.opendocument.spreadsheet,' + $('#dvData').html());
        //MS-Excel
        window.open('data:application/vnd.ms-excel,' + $('#dvData').html());
        //CSV
        window.open('data:application/csv,charset=utf-8,' + $('#dvData').html());
        e.preventDefault();
    });
});

function GetMimeTypes () {
    var message = "";
        // Internet Explorer supports the mimeTypes collection, but it is always empty
    if (navigator.mimeTypes && navigator.mimeTypes.length > 0) {
        var mimes = navigator.mimeTypes;
        for (var i=0; i < mimes.length; i++) {
            message += "<b>" + mimes[i].type + "</b> : " + mimes[i].description + "<br />";
        }
    }
    else {
        message = "Your browser does not support this ";
       //sorry!
    }

    return ( message);
}
</script>

</head>
<body>
<div id="dvData">
<table>
    <tr>
        <th>Column One </th>
        <th>Column Two</th>
        <th>Column Three</th>
    </tr>
    <tr>
        <td>row1 Col1</td>
        <td>row1 Col2</td>
        <td>row1 Col3</td>
   </tr>
   <tr>
        <td>row2 Col1</td>
        <td>row2 Col2</td>
        <td>row2 Col3</td>
   </tr>
      <tr>
        <td>row3 Col1</td>
        <td>row3 Col2</td>
        <td>row3 Col3</td>  
   </tr>
</table>
</div>
<br/>
<input type="button" id="btnExport" value=" Export Table data into Excel " />

</body>

Errors:

CSV: Unrecognised over the browsers

ODS & Excel: is working but I am not able to find which one to generate when system is having an excel installed or openoffice installed?

IE version 8 : it is totally not working, opens in a new window and as below screenshot.

enter image description here

i am not able to export it to csv using jquery and html... i dont want to use any plugin for it.. i used mime type but it doesnt seem to work — GOK, Apr 18 '13 at 09:08
Then show some code and describe what exactly doesn't work how? What error messages are you getting? Etc. — Pekka, Apr 18 '13 at 09:10
@Pekka웃: See the EDIT, if now also you dont understand be specific to ask queries thanks — GOK, Apr 18 '13 at 09:24
Regarding CSV: You're telling the browser you're serving `application/csv`, but in fact you're throwing html at it. If you want to use the data URL approach, I suggest you try to convert the data to CSV format first. — jmosbech, Apr 21 '13 at 19:50
@jmosbech: The same we do in application/vnd.ms-excel, it works! leaving aside IE as it doesnt work for all formats. — GOK, Apr 22 '13 at 04:53
You said you couldn't use any plugin. IF someday you change your mind, you can always apply DataTables.js to the table and use its [TableTools plugin](http://datatables.net/extras/tabletools/) which exports in csv, pdf, prints, etc. — RaphaelDDL, Apr 23 '13 at 15:01
My guess is that Excel is able to cope with the fact that you're sending HTML labeled as native Excel data, but CSV is a different game. If you want to be able to serve CSV, you should convert the data first. Everything else is just asking for trouble. — jmosbech, Apr 23 '13 at 19:42
i have posted question that me be your expected answer.hope this help you. http://stackoverflow.com/questions/19539274/export-html-table-to-excel-in-java-script — Prabakaran G, Nov 08 '13 at 12:22
possible duplicate of [Export to csv in jQuery](http://stackoverflow.com/questions/4639372/export-to-csv-in-jquery) — Italo Borssatto, Nov 14 '13 at 19:26

score 129 · Accepted Answer · edited Apr 21 '18 at 01:18

Demo

See below for an explanation.

$(document).ready(function() {

  function exportTableToCSV($table, filename) {

    var $rows = $table.find('tr:has(td)'),

      // Temporary delimiter characters unlikely to be typed by keyboard
      // This is to avoid accidentally splitting the actual contents
      tmpColDelim = String.fromCharCode(11), // vertical tab character
      tmpRowDelim = String.fromCharCode(0), // null character

      // actual delimiter characters for CSV format
      colDelim = '","',
      rowDelim = '"\r\n"',

      // Grab text from table into CSV formatted string
      csv = '"' + $rows.map(function(i, row) {
        var $row = $(row),
          $cols = $row.find('td');

        return $cols.map(function(j, col) {
          var $col = $(col),
            text = $col.text();

          return text.replace(/"/g, '""'); // escape double quotes

        }).get().join(tmpColDelim);

      }).get().join(tmpRowDelim)
      .split(tmpRowDelim).join(rowDelim)
      .split(tmpColDelim).join(colDelim) + '"';

    // Deliberate 'false', see comment below
    if (false && window.navigator.msSaveBlob) {

      var blob = new Blob([decodeURIComponent(csv)], {
        type: 'text/csv;charset=utf8'
      });

      // Crashes in IE 10, IE 11 and Microsoft Edge
      // See MS Edge Issue #10396033
      // Hence, the deliberate 'false'
      // This is here just for completeness
      // Remove the 'false' at your own risk
      window.navigator.msSaveBlob(blob, filename);

    } else if (window.Blob && window.URL) {
      // HTML5 Blob        
      var blob = new Blob([csv], {
        type: 'text/csv;charset=utf-8'
      });
      var csvUrl = URL.createObjectURL(blob);

      $(this)
        .attr({
          'download': filename,
          'href': csvUrl
        });
    } else {
      // Data URI
      var csvData = 'data:application/csv;charset=utf-8,' + encodeURIComponent(csv);

      $(this)
        .attr({
          'download': filename,
          'href': csvData,
          'target': '_blank'
        });
    }
  }

  // This must be a hyperlink
  $(".export").on('click', function(event) {
    // CSV
    var args = [$('#dvData>table'), 'export.csv'];

    exportTableToCSV.apply(this, args);

    // If CSV, don't do event.preventDefault() or return false
    // We actually need this to be a typical hyperlink
  });
});

a.export,
a.export:visited {
  display: inline-block;
  text-decoration: none;
  color: #000;
  background-color: #ddd;
  border: 1px solid #ccc;
  padding: 8px;
}

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<a href="#" class="export">Export Table data into Excel</a>
<div id="dvData">
  <table>
    <tr>
      <th>Column One</th>
      <th>Column Two</th>
      <th>Column Three</th>
    </tr>
    <tr>
      <td>row1 Col1</td>
      <td>row1 Col2</td>
      <td>row1 Col3</td>
    </tr>
    <tr>
      <td>row2 Col1</td>
      <td>row2 Col2</td>
      <td>row2 Col3</td>
    </tr>
    <tr>
      <td>row3 Col1</td>
      <td>row3 Col2</td>
      <td>row3 Col3</td>
    </tr>
    <tr>
      <td>row4 'Col1'</td>
      <td>row4 'Col2'</td>
      <td>row4 'Col3'</td>
    </tr>
    <tr>
      <td>row5 &quot;Col1&quot;</td>
      <td>row5 &quot;Col2&quot;</td>
      <td>row5 &quot;Col3&quot;</td>
    </tr>
    <tr>
      <td>row6 "Col1"</td>
      <td>row6 "Col2"</td>
      <td>row6 "Col3"</td>
    </tr>
  </table>
</div>

As of 2017

Now uses HTML5 Blob and URL as the preferred method with Data URI as a fallback.

On Internet Explorer

Other answers suggest window.navigator.msSaveBlob; however, it is known to crash IE10/Window 7 and IE11/Windows 10. Whether it works using Microsoft Edge is dubious (see Microsoft Edge issue ticket #10396033).

Merely calling this in Microsoft's own Developer Tools / Console causes the browser to crash:

navigator.msSaveBlob(new Blob(["hello"], {type: "text/plain"}), "test.txt");

 Four years after my first answer, new IE versions include IE10, IE11, and Edge. They all crash on a function that Microsoft invented (slow clap).

Add navigator.msSaveBlob support at your own risk.

As of 2013

Typically this would be performed using a server-side solution, but this is my attempt at a client-side solution. Simply dumping HTML as a Data URI will not work, but is a helpful step. So:

Convert the table contents into a valid CSV formatted string. (This is the easy part.)
Force the browser to download it. The window.open approach would not work in Firefox, so I used <a href="{Data URI here}">.
Assign a default file name using the <a> tag's download attribute, which only works in Firefox and Google Chrome. Since it is just an attribute, it degrades gracefully.

Notes

You can style your link to look like a button. I'll leave this effort to you
IE has Data URI restrictions. See: Data URI scheme and Internet Explorer 9 Errors
About the "download" attribute, see these:

Compatibility

Browsers testing includes:

Firefox 20+, Win/Mac (works)
Google Chrome 26+, Win/Mac (works)
Safari 6, Mac (works, but filename is ignored)
IE 9+ (fails)

Content Encoding

The CSV is exported correctly, but when imported into Excel, the character ü is printed out as Ã¤. Excel interprets the value incorrectly.

Introduce var csv = '\ufeff'; and then Excel 2013+ interprets the values correctly.

If you need compatibility with Excel 2007, add UTF-8 prefixes at each data value. See also:

It works quite well for my requirement; just for doesn't takes up the or header for the table in consideration.. is that intentional... What if we have two headers with colspan's; can we handle that using this?? — GOK, May 23 '13 at 08:37
Oops.. but is dere any way to fix it?? I am checking it on IE8 — GOK, May 27 '13 at 15:30
Just a quick note: to include as well, replace the 'tr:has(td)' with 'tr:has(td),tr:has(th)' and the 'td' a bit further down by 'td,th'. — Wouter, Apr 15 '14 at 00:18
P.S. If anyone has any issues getting the filename to work, this related SO question has a solution: http://stackoverflow.com/questions/23816005/anchor-tag-download-attribute-not-working-bug-in-chrome-35-0-1916-114 — clifgriffin, Jul 16 '14 at 16:39
It works perfectly with a large data set, but I faced on with a cyrillic characters issue (example in jsfiddle doesn't work properly too). Tested in FF and Chrome. Can somebody help me? — dimonser, Feb 15 '15 at 16:50
Include table header(Implementing @Wouter suggestions) as below, 1) var $rows = $table.find('tr:has(td),tr:has(th)'), 2) $cols = $row.find('td,th'); — Bala, Jun 04 '15 at 06:32
regardin ´text = $col.text();´ how do you get the text generated with css:before content? I get empty values. Please see: http://stackoverflow.com/questions/35774973/get-css-generated-text-inside-a-td-with-jquery — Mario, Mar 11 '16 at 16:53
Thank you for your answer, How can we add additional rows (static, which are not present in html) into csv file using javascript? — sree, Jul 30 '18 at 12:50
would you please send some way to do same for json string which is return in ajax response....Thanks — Sachin Sarola, Dec 21 '18 at 07:38
Thanks this is really helpful, however, is there any update for formatting colspan and rowspan? — Paddy Hallihan, Jan 17 '19 at 09:40
For me, I also had to trim the column texts: $col.text().trim(). Otherwise, I ended up with lots of spaces in every field. — MastaBaba, Feb 02 '19 at 13:45

score 7 · Answer 2 · edited Nov 24 '15 at 13:31

I am not sure if the above CSV generation code is so great as it appears to skip th cells and also did not appear to allow for commas in the value. So here is my CSV generation code that might be useful.

It does assume you have the $table variable which is a jQuery object (eg. var $table = $('#yourtable');)

$rows = $table.find('tr');

var csvData = "";

for(var i=0;i<$rows.length;i++){
                var $cells = $($rows[i]).children('th,td'); //header or content cells

                for(var y=0;y<$cells.length;y++){
                    if(y>0){
                      csvData += ",";
                    }
                    var txt = ($($cells[y]).text()).toString().trim();
                    if(txt.indexOf(',')>=0 || txt.indexOf('\"')>=0 || txt.indexOf('\n')>=0){
                        txt = "\"" + txt.replace(/\"/g, "\"\"") + "\"";
                    }
                    csvData += txt;
                }
                csvData += '\n';
 }

score 2 · Answer 3 · edited Oct 13 '15 at 17:14

 <a id="export" role='button'>
        Click Here To Download Below Report
    </a>
    <table id="testbed_results" style="table-layout:fixed">
        <thead>
            <tr width="100%" style="color:white" bgcolor="#3195A9" id="tblHeader">
                <th>Name</th>
                <th>Date</th>
                <th>Speed</th>
                <th>Column2</th>
                <th>Interface</th>
                <th>Interface2</th>
                <th>Sub</th>
                <th>COmpany result</th>
                <th>company2</th>
                <th>Gen</th>
            </tr>
        </thead>
        <tbody>
            <tr id="samplerow">
                <td>hello</td>
                <td>100</td>
                <td>200</td>
                <td>300</td>
                <td>html2svc</td>
                <td>ajax</td>
                <td>200</td>
                <td>7</td>
                <td>8</td>
                <td>9</td>
            </tr>
            <tr>
                <td>hello</td>
                <td>100</td>
                <td>200</td>
                <td>300</td>
                <td>html2svc</td>
                <td>ajax</td>
                <td>200</td>
                <td>7</td>
                <td>8</td>
                <td>9</td>
            </tr>
        </tbody>
    </table>

    $(document).ready(function () {
        Html2CSV('testbed_results', 'myfilename','export');
    });



    function Html2CSV(tableId, filename,alinkButtonId) {
        var array = [];
        var headers = [];
        var arrayItem = [];
        var csvData = new Array();
        $('#' + tableId + ' th').each(function (index, item) {
            headers[index] = '"' + $(item).html() + '"';
        });
        csvData.push(headers);
        $('#' + tableId + ' tr').has('td').each(function () {

            $('td', $(this)).each(function (index, item) {
                arrayItem[index] = '"' + $(item).html() + '"';
            });
            array.push(arrayItem);
            csvData.push(arrayItem);
        });




        var fileName = filename + '.csv';
        var buffer = csvData.join("\n");
        var blob = new Blob([buffer], {
            "type": "text/csv;charset=utf8;"
        });
        var link = document.getElementById(alinkButton);

        if (link.download !== undefined) { // feature detection
            // Browsers that support HTML5 download attribute
            link.setAttribute("href", window.URL.createObjectURL(blob));
            link.setAttribute("download", fileName);
        }
        else if (navigator.msSaveBlob) { // IE 10+
            link.setAttribute("href", "#");
            link.addEventListener("click", function (event) {
                navigator.msSaveBlob(blob, fileName);
            }, false);
        }
        else {
            // it needs to implement server side export
            link.setAttribute("href", "http://www.example.com/export");
        }
    }

</script>

You should add some text why and how your code solves the issue — Reeno, Oct 13 '15 at 17:15

score 2 · Answer 4 · edited Jun 07 '16 at 16:50

2

A tiny update for @Terry Young answer, i.e. add IE 10+ support

if (window.navigator.msSaveOrOpenBlob) {
  // IE 10+
  var blob = new Blob([decodeURIComponent(encodeURI(csvString))], {
    type: 'text/csv;charset=' + document.characterSet
  });
  window.navigator.msSaveBlob(blob, filename);
} else {
  // actual real browsers
  //Data URI
  csvData = 'data:application/csv;charset=utf-8,' + encodeURIComponent(csvData);

    $(this).attr({
      'download': filename,
      'href': csvData,
      'target': '_blank'
    });
}

edited Jun 07 '16 at 16:50

armstb01

573
6
11

answered Nov 24 '15 at 11:41

Dušan Maďar

7,399
4
30
51

Will this work for IE9 to export html table to excel like `type: 'data:application/vnd.ms-excel;'` ? Thanks – Disera Apr 20 '16 at 10:02

score 1 · Answer 5 · answered Apr 23 '13 at 14:14

What if you have your data in CSV format and convert it to HTML for display on the web page? You may use the http://code.google.com/p/js-tables/ plugin. Check this example http://code.google.com/p/js-tables/wiki/Table As you are already using jQuery library I have assumed you are able to add other javascript toolkit libraries.

If the data is in CSV format, you should be able to use the generic 'application/octetstream' mime type. All the 3 mime types you have tried are dependent on the software installed on the clients computer.

score 1 · Answer 6 · edited May 23 '17 at 12:03

From what I understand, you have your data on a table and you want to create the CSV from that data. However, you have problem creating the CSV.

My thoughts would be to iterate and parse the contents of the table and generate a string from the parsed data. You can check How to convert JSON to CSV format and store in a variable for an example. You are using jQuery in your example so that would not count as an external plugin. Then, you just have to serve the resulting string using window.open and use application/octetstream as suggested.