Simple CSV Export with jQuery & PHP

Simple CSV Export with jQuery & PHP

ulrikeulrike Posts: 39Questions: 1Answers: 0
edited September 2012 in Feature requests
Hello,

after trying out TableTools (despite hating Flash) which didn't really work for me, I wrote a simple and fast CSV export function in jQuery which I wanted to share here. Hope this may be helpful to some of you. I am sure one can still do this better than I did, please do not hesitate to comment :)

Here ist the function :
[code]
function table2csv(oTable, exportmode, tableElm) {
var csv = '';
var headers = [];
var rows = [];

// Get header names
$(tableElm+' thead').find('th').each(function() {
var $th = $(this);
var text = $th.text();
var header = '"' + text + '"';
// headers.push(header); // original code
if(text != "") headers.push(header); // actually datatables seems to copy my original headers so there ist an amount of TH cells which are empty
});
csv += headers.join(',') + "\n";

// get table data
if (exportmode == "full") { // total data
var total = oTable.fnSettings().fnRecordsTotal()
for(i = 0; i < total; i++) {
var row = oTable.fnGetData(i);
row = strip_tags(row);
rows.push(row);
}
} else { // visible rows only
$(tableElm+' tbody tr:visible').each(function(index) {
var row = oTable.fnGetData(this);
row = strip_tags(row);
rows.push(row);
})
}
csv += rows.join("\n");

// if a csv div is already open, delete it
if($('.csv-data').length) $('.csv-data').remove();
// open a div with a download link
$('body').append(''+csv+'');

}

function strip_tags(html) {
var tmp = document.createElement("div");
tmp.innerHTML = html;
return tmp.textContent||tmp.innerText;
}

[/code]

This creates a textarea with the code to be copy pasted manually and a button to download a csv-file.
My table is a table with the class "display". So here are my two export buttons :

[code]
// export only what is visible right now (filters & paginationapplied)
$('#export_visible').click(function(event) {
event.preventDefault();
table2csv(oTable, 'visible', 'table.display');
})

// export all table data
$('#export_all').click(function(event) {
event.preventDefault();
table2csv(oTable, 'full', 'table.display');
})
[/code]
I call these in the same init file where I also set up the dataTable (otherwise I wouldn't have access to the variable).
This is the way I set up my datatable :
[code]
$(document).ready(function(){
var asInitVals = new Array();
var oTable = $('table.display').dataTable();
});
[/code]

And the PHP file csv.php at the website root to generate a downloadable CSV :
[code]
<?
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="tabledata.csv"');
if (isset($_POST['csv'])) {
$csv = $_POST['csv'];
echo $csv;
}
?>
[/code]

The export works also with FixedColumns.

Thank you Allan for your great work and documentation.

Best regards.

Replies

  • razibulrazibul Posts: 1Questions: 0Answers: 0
    dear Ulrike,
    i copied your code and apply to my project. but it shows error in csv.php file. i put the file in root folder.
    " The HTTP verb POST used to access path '/csv.php' is not allowed. " this is error. what should i do now?
  • ulrikeulrike Posts: 39Questions: 1Answers: 0
    Hello,

    sorry for the late answer. What i understand from your error, this is a permissions problem.
    Check http://stackoverflow.com/questions/2209324/the-http-verb-post-used-to-access-path-test-html-is-not-allowed
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    > despite hating Flash

    Hah! You are not alone! But until there is an API for copy to clipboard and saving local files in Javascript, we are a bit suck with it for a client-side processing solution. For all I love the new APIs such as full screen etc, I am surprised that these two don't have a place in HTML5... ;-(

    Like your solution :-). Server-side file creation isn't something I wanted to do with the default TableTools since every server is different, but it is certainly useful. Also worth noting that there are a couple of plug-ins for TableTools that can help (specifically the two download plug-ins):
    http://datatables.net/extras/tabletools/plug-ins

    Allan
  • csfirecsfire Posts: 2Questions: 0Answers: 0
    HI
    first of all I'm totally new to this javascripts and php, but i was able to integrate datatable into my page.
    now i want to export the whole datatable to csv. I'm currently working on simple sql query as a test page. once this page is working I'm planning to go to much complex queries.

    so its easier for me to learn to export whole datatables data instead of using anyother method.

    This is how i used your script... my datatable is showing as usual that means its connecting with database properly. but there's no button to export csv.

    Please help me with this.

    I'm using jquery datatables 1.8.2

    below is my codes... please help me if I've done something wrong.

    [code]
    function table2csv(oTable, exportmode, tableElm) {
    var csv = '';
    var headers = [];
    var rows = [];

    // Get header names
    $(tableElm+' thead').find('th').each(function() {
    var $th = $(this);
    var text = $th.text();
    var header = '"' + text + '"';
    // headers.push(header); // original code
    if(text != "") headers.push(header); // actually datatables seems to copy my original headers so there ist an amount of TH cells which are empty
    });
    csv += headers.join(',') + "\n";

    // get table data
    if (exportmode == "full") { // total data
    var total = oTable.fnSettings().fnRecordsTotal();
    for(i = 0; i < total; i++) {
    var row = oTable.fnGetData(i);
    row = strip_tags(row);
    rows.push(row);
    }
    } else { // visible rows only
    $(tableElm+' tbody tr:visible').each(function(index) {
    var row = oTable.fnGetData(this);
    row = strip_tags(row);
    rows.push(row);
    });
    }
    csv += rows.join("\n");

    // if a csv div is already open, delete it
    if($('.csv-data').length) $('.csv-data').remove();
    // open a div with a download link
    $('body').append(''+csv+'');

    }

    function strip_tags(html) {
    var tmp = document.createElement("div");
    tmp.innerHTML = html;
    return tmp.textContent||tmp.innerText;
    }

    // export only what is visible right now (filters & paginationapplied)
    $('#export_visible').click(function(event) {
    event.preventDefault();
    table2csv(oTable, 'visible', 'table.display');
    });

    // export all table data
    $('#export_all').click(function(event) {
    event.preventDefault();
    table2csv(oTable, 'full', 'table.display');
    });

    $(document).ready(function(){
    var asInitVals = new Array();
    var oTable = $('table.display').dataTable();
    });








    User ID
    user
    Full Name
    User group
    <!--World % Users-->



    <?php
    while ($row = mysql_fetch_array($result)) {
    ?>

    <?=$row['user_id']?>
    <?=$row['user']?>
    <?=$row['full_name']?>
    <?=$row['user_group']?>
    <!--<?=$row['world_users']?>-->

    <?php
    }
    ?>




    [/code]
  • csfirecsfire Posts: 2Questions: 0Answers: 0
    Hi guys please do need help with this.
  • dasapadasapa Posts: 17Questions: 3Answers: 0
    Hi,

    i can export full data but i need export filtered data (it can be filtered and not visible) and use datatable multi.column filter.
    http://www.datatables.net/release-datatables/examples/api/multi_filter.html

    I dont know how can obtain value each filter. I think you obtain with function_strip_tags but i dont know how works, what is "div"?

    [code] function strip_tags(html) {
    var tmp = document.createElement("div");
    tmp.innerHTML = html;
    return tmp.textContent||tmp.innerText;
    }[/code]
  • latheesanlatheesan Posts: 1Questions: 0Answers: 0
    edited July 2013
    Hi All,

    I was looking for a way to export any given datatable to CSV and came across this post. Thanks ulrike for this. I used your idea and optimised it. Here's what I got so far (working):

    [ index.php ]

    [code]


    CSV Export - DataTable Test
    @import "css/demo_table.css";

    * { font-family: Verdana; font-size: 12px; }





    // When Page Loads
    var oTable;
    $(document).ready(function()
    {
    // Init DataTable
    oTable = $('#myDataTable').dataTable();

    // Handle Btn Clicks
    $('.ExportBtn').click(function() {
    CSVExportDataTable(oTable, $(this).val());
    });
    });

    // CSV Export Function
    function CSVExportDataTable(oTable, exportMode)
    {
    // Init
    var csv = '';
    var headers = [];
    var rows = [];
    var dataSeparator = '|~|';

    // Get table header names
    $(oTable).find('thead th').each(function() {
    var text = $(this).text();
    if(text != "") headers.push(text);
    });
    csv += headers.join(dataSeparator) + "\r\n";

    // Get table body data
    if (exportMode == 'Full') {
    var totalRows = oTable.fnSettings().fnRecordsTotal();
    for (var i = 0; i < totalRows; i++) {
    var row = oTable.fnGetData(i);
    rows.push(row.join(dataSeparator));
    }
    } else {
    $(oTable._('tr:visible', { })).each(function(index, row) {
    rows.push(row.join(dataSeparator));
    });
    }
    csv += rows.join("\r\n");

    // Proceed if csv data was loaded
    if (csv.length > 0)
    {
    // Ajax Post CSV Data
    $.ajax({
    type: "POST",
    url: 'dt_csv_export.php',
    data: {
    action: "generate",
    csv_type: oTable.attr('id'),
    csv_data: csv
    },
    success: function(download_link) {
    location.href = download_link;
    }
    });
    }
    }








    Rendering engine
    Browser
    Platform(s)
    Engine version
    CSS grade



    <!-- Table Rows Snipped -->






    Export :


    [/code]

    [ dt_csv_export.php ]

    [code]<?php

    // Mini-Config
    $data_separator = '|~|';

    // Handle Generate CSV Action
    if (isset($_POST['action']) && $_POST['action'] == 'generate' &&
    isset($_POST['csv_type']) && !empty($_POST['csv_type']) &&
    isset($_POST['csv_data']) && !empty($_POST['csv_data']))
    {
    // Delete Temp CSV Files - Older Than 1 Day
    $temp_csv_files = glob('*.csv');
    if (is_array($temp_csv_files) && sizeof($temp_csv_files)) {
    foreach ($temp_csv_files as $temp_csv_file) {
    if (is_file($temp_csv_file) && time() - filemtime($temp_csv_file) >= 24*60*60) { // 1 Day Old
    unlink($temp_csv_file);
    }
    }
    }

    // Write CSV To Disk
    $csv_data = explode("\r\n", $_POST['csv_data']);
    $csv_file_name = uniqid($_POST['csv_type'] .'_', true) .'.csv';
    $h = @fopen($csv_file_name, 'w');
    if (false !== $h) {
    if (sizeof($csv_data)) {
    foreach ($csv_data as $csv_row) {
    $csv_row = explode($data_separator, $csv_row);
    array_walk_recursive($csv_row, function(&$item) {
    $item = preg_replace('/\s+/', ' ', preg_replace('/[\r\n\t]*/', '', $item));
    $item = trim(strip_tags($item));
    });
    fputcsv($h, $csv_row, ',', '"');
    }
    }
    fclose($h);
    }
    echo file_exists($csv_file_name) ? $csv_file_name : '';
    }

    // End
    exit();

    ?>[/code]

    Hope someone finds this useful.

    Regards,
    Latheesan
This discussion has been closed.