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       lt html gt    lt head gt   lt script src  http   ajax googleapis com ajax libs jquery 1 9 1 jquery min js  gt  lt  script gt      lt script type  text JavaScript  gt    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  amp  amp  navigator mimeTypes length  gt  0            var mimes   navigator mimeTypes          for  var i 0  i  lt  mimes length  i                  message      lt b gt     mimes i  type     lt  b gt        mimes i  description     lt br   gt                        else           message    Your browser does not support this             sorry             return   message      lt  script gt    lt  head gt   lt body gt   lt div id  dvData  gt   lt table gt       lt tr gt           lt th gt Column One  lt  th gt           lt th gt Column Two lt  th gt           lt th gt Column Three lt  th gt       lt  tr gt       lt tr gt           lt td gt row1 Col1 lt  td gt           lt td gt row1 Col2 lt  td gt           lt td gt row1 Col3 lt  td gt      lt  tr gt      lt tr gt           lt td gt row2 Col1 lt  td gt           lt td gt row2 Col2 lt  td gt           lt td gt row2 Col3 lt  td gt      lt  tr gt         lt tr gt           lt td gt row3 Col1 lt  td gt           lt td gt row3 Col2 lt  td gt           lt td gt row3 Col3 lt  td gt        lt  tr gt   lt  table gt   lt  div gt   lt br  gt   lt input type  button  id  btnExport  value   Export Table data into Excel     gt    lt  body gt    Errors   CSV  Unrecognised over the browsers  ODS  amp  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

User · Answer

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';
 }

User · Answer

Demo  See below for an explanation    x000D   x000D    document  ready function     x000D   x000D    function exportTableToCSV  table  filename    x000D   x000D      var  rows    table find  tr has td     x000D   x000D           Temporary delimiter characters unlikely to be typed by keyboard x000D           This is to avoid accidentally splitting the actual contents x000D        tmpColDelim   String fromCharCode 11      vertical tab character x000D        tmpRowDelim   String fromCharCode 0      null character x000D   x000D           actual delimiter characters for CSV format x000D        colDelim          x000D        rowDelim      r n    x000D   x000D           Grab text from table into CSV formatted string x000D        csv          rows map function i  row    x000D          var  row     row   x000D             cols    row find  td    x000D   x000D          return  cols map function j  col    x000D            var  col     col   x000D              text    col text    x000D   x000D            return text replace    g            escape double quotes x000D   x000D             get   join tmpColDelim   x000D   x000D           get   join tmpRowDelim  x000D         split tmpRowDelim  join rowDelim  x000D         split tmpColDelim  join colDelim         x000D   x000D         Deliberate  false   see comment below x000D      if  false  amp  amp  window navigator msSaveBlob    x000D   x000D        var blob   new Blob  decodeURIComponent csv      x000D          type   text csv charset utf8  x000D            x000D   x000D           Crashes in IE 10  IE 11 and Microsoft Edge x000D           See MS Edge Issue  10396033 x000D           Hence  the deliberate  false  x000D           This is here just for completeness x000D           Remove the  false  at your own risk x000D        window navigator msSaveBlob blob  filename   x000D   x000D        else if  window Blob  amp  amp  window URL    x000D           HTML5 Blob         x000D        var blob   new Blob  csv     x000D          type   text csv charset utf-8  x000D            x000D        var csvUrl   URL createObjectURL blob   x000D   x000D          this  x000D           attr   x000D             download   filename  x000D             href   csvUrl x000D              x000D        else   x000D           Data URI x000D        var csvData    data application csv charset utf-8     encodeURIComponent csv   x000D   x000D          this  x000D           attr   x000D             download   filename  x000D             href   csvData  x000D             target     blank  x000D              x000D        x000D      x000D   x000D       This must be a hyperlink x000D        export   on  click   function event    x000D         CSV x000D      var args        dvData gt table     export csv    x000D   x000D      exportTableToCSV apply this  args   x000D   x000D         If CSV  don t do event preventDefault   or return false x000D         We actually need this to be a typical hyperlink x000D        x000D      x000D  a export  x000D  a export visited   x000D    display  inline-block  x000D    text-decoration  none  x000D    color   000  x000D    background-color   ddd  x000D    border  1px solid  ccc  x000D    padding  8px  x000D    x000D   lt script src  https   ajax googleapis com ajax libs jquery 2 1 1 jquery min js  gt  lt  script gt  x000D   lt a href     class  export  gt Export Table data into Excel lt  a gt  x000D   lt div id  dvData  gt  x000D     lt table gt  x000D       lt tr gt  x000D         lt th gt Column One lt  th gt  x000D         lt th gt Column Two lt  th gt  x000D         lt th gt Column Three lt  th gt  x000D       lt  tr gt  x000D       lt tr gt  x000D         lt td gt row1 Col1 lt  td gt  x000D         lt td gt row1 Col2 lt  td gt  x000D         lt td gt row1 Col3 lt  td gt  x000D       lt  tr gt  x000D       lt tr gt  x000D         lt td gt row2 Col1 lt  td gt  x000D         lt td gt row2 Col2 lt  td gt  x000D         lt td gt row2 Col3 lt  td gt  x000D       lt  tr gt  x000D       lt tr gt  x000D         lt td gt row3 Col1 lt  td gt  x000D         lt td gt row3 Col2 lt  td gt  x000D         lt td gt row3 Col3 lt  td gt  x000D       lt  tr gt  x000D       lt tr gt  x000D         lt td gt row4  Col1  lt  td gt  x000D         lt td gt row4  Col2  lt  td gt  x000D         lt td gt row4  Col3  lt  td gt  x000D       lt  tr gt  x000D       lt tr gt  x000D         lt td gt row5  amp quot Col1 amp quot  lt  td gt  x000D         lt td gt row5  amp quot Col2 amp quot  lt  td gt  x000D         lt td gt row5  amp quot Col3 amp quot  lt  td gt  x000D       lt  tr gt  x000D       lt tr gt  x000D         lt td gt row6  Col1  lt  td gt  x000D         lt td gt row6  Col2  lt  td gt  x000D         lt td gt row6  Col3  lt  td gt  x000D       lt  tr gt  x000D     lt  table gt  x000D   lt  div gt  x000D   x000D   x000D      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  lt a href   Data URI here   gt   Assign a default file name using the  lt a gt  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    Downloading resources in HTML5  a download  Bug 676619 - Implement proposed download attribute How to detect support for the HTML5    download    attribute    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    Is it possible to force Excel recognize UTF-8 CSV files automatically  Microsoft Excel mangles Diacritics in  csv files  Adding UTF-8 BOM to string Blob

User · Answer

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

User · Answer

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

User · Answer

lt a id  export  role  button  gt          Click Here To Download Below Report      lt  a gt       lt table id  testbed results  style  table-layout fixed  gt           lt thead gt               lt tr width  100   style  color white  bgcolor   3195A9  id  tblHeader  gt                   lt th gt Name lt  th gt                   lt th gt Date lt  th gt                   lt th gt Speed lt  th gt                   lt th gt Column2 lt  th gt                   lt th gt Interface lt  th gt                   lt th gt Interface2 lt  th gt                   lt th gt Sub lt  th gt                   lt th gt COmpany result lt  th gt                   lt th gt company2 lt  th gt                   lt th gt Gen lt  th gt               lt  tr gt           lt  thead gt           lt tbody gt               lt tr id  samplerow  gt                   lt td gt hello lt  td gt                   lt td gt 100 lt  td gt                   lt td gt 200 lt  td gt                   lt td gt 300 lt  td gt                   lt td gt html2svc lt  td gt                   lt td gt ajax lt  td gt                   lt td gt 200 lt  td gt                   lt td gt 7 lt  td gt                   lt td gt 8 lt  td gt                   lt td gt 9 lt  td gt               lt  tr gt               lt tr gt                   lt td gt hello lt  td gt                   lt td gt 100 lt  td gt                   lt td gt 200 lt  td gt                   lt td gt 300 lt  td gt                   lt td gt html2svc lt  td gt                   lt td gt ajax lt  td gt                   lt td gt 200 lt  td gt                   lt td gt 7 lt  td gt                   lt td gt 8 lt  td gt                   lt td gt 9 lt  td gt               lt  tr gt           lt  tbody gt       lt  table gt            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                      lt  script gt

User · Answer

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

[jquery] Export to CSV using jQuery and html

Examples related to jquery

Examples related to html

Examples related to csv

Examples related to export

Examples related to export-to-csv