Richard Golebiowski: jQuery - Output DataTables as CSV

I switched to using jQuery DataTables in my project and need a way to output the table data as CSV. Here is the JavaScript that I came up with:


function rgDataTable2CSV(mTable, mOffset) {
   var csvData = [];
   var tmpRow = [];
   var cL = 0;
   //get the header
   $('#lstTabHead'+ mTable + ' th').each (function(index, domEle) {
       if ($(this).closest('.dataTables_scrollHeadInner').length > 0){
          tmpRow[tmpRow.length] = formatData($.trim($(this).html()));
       };
   });
    row2CSV(tmpRow);
    //get the data
   $('#lstTab' + mTable).find('tr').each (function() {
      tmpRow = [];
      $(this).find('td').each(function(index, domEle) {
          tmpRow[tmpRow.length] = formatData($.trim($(this).html()));
        });
       row2CSV(tmpRow);
   });
    tmpRow = [];
    //this gets the left side (the fixed column)
   $('#lstTabFoot' + mTable).find('tr').each (function() {
      $(this).find('td').each(function(index, domEle) {
          tmpRow[tmpRow.length] = formatData($.trim($(this).html()));
        });
   });
   cl = 0;
   //this gets the right side
   $('#lstTabFoot'+ mTable + ' td').filter(':visible').each (function(index, domEle) {
       if ($(this).closest('.dataTables_scrollFootInner').length > 0){
          cL = cL + 1
          if(cL > mOffset){
          tmpRow[tmpRow.length] = formatData($.trim($(this).html()));
          };
       };
   });
   row2CSV(tmpRow);    
    var mydata = csvData.join('\n');
    return popup(mydata);
          
    function row2CSV(tmpRow) {
        var tmp = tmpRow.join('') // to remove any blank rows
        if (tmpRow.length > 0 && tmp != '') {
            var mystr = tmpRow.join(',');
            csvData[csvData.length] = mystr;
        }
    }
       
    function formatData(input) {
        // replace " with “
        var regexp = new RegExp(/["]/g);
        var output = input.replace(regexp, "“");
        //HTML
        var regexp = new RegExp(/\<[^\<]+\>/g);
        var output = output.replace(regexp, "");
        if (output == "") return '';
        return '"' + output + '"';
    }
       
    function popup(data) {
        var generator = window.open('', 'csv', 'height=400,width=600,menubar=1,resizable=1');
        generator.document.write('<html><head><title>CSV</title>');
        generator.document.write('</head><body >');
        generator.document.write('<textArea cols=70 rows=15 wrap="off" >');
        generator.document.write(data);
        generator.document.write('</textArea>');
        generator.document.write('</body></html>');
        generator.document.close();
        generator.focus();
    }
       
}

This script is based off of the Table2CSV script. The HTML table structure for the of the DataTables in my application have a thead id = lstTabHead + tablename, tbody id = lstTab + tablename, tfoot id = lstTabFoot + tablename. This naming convention is required by this script. Call the script using "rgDataTable2CSV(tablename, 1)". The second parameter is an offset that is used for the first column being omitted.

2 comments:

hemalAugust 10, 2016 at 12:34 PM
I had an issue with leading zeros getting deleted for CSV and Excel buttons. So I added exportOptions : { orthogonal : 'sort'} for csvHTML5 and excelHTML5. This resolved issue with Excel but created a new problem for CSV, every datarow which I am forcing to keep zeros is getting â€Œ value in front. I am using Datatable1.10.9 and buttons.html5.js seems to be doing this. I would really appreciate if you can you please help me with this.

Thank you for commenting!

Richard Golebiowski

Translate

Monday, July 16, 2012

jQuery - Output DataTables as CSV

2 comments: