Server Side Processing with export to CSV and PDF

David_Dean

I have seen several post about this issue, but I have not been able to execute the suggestion made in the Forums. It seems like an AJAX call is required, but I can't seem to make it work (or more probably I jsut don't understand the process).

I am using Server Side processing of DataTables. The project I am working on has multiple size tables. I have some situations where the tables will be small, and I am going to do everything on the client side. This is the scenario where the tables will contain less than 100 rows. But, then I have the situation where I could be dealing with 1,000's of records and I want to allow the user to be able to specify filter's in the DataTables, but then export all of those filtered records out to a PDF, CSV, Excel, etc. Honestly, I am not sure what this will require, but at the minimum I need the PDF and CSV exports. And I guess I should say at the VERY minimum the CSV export. The situation I am trying to create is that the user experience is the same on all of the tables, if a user is working with a small table, thus completely client side processing behind the scenes, or a larger table, which requires server side processing behind the scenes, they have the same filtering / export options.

At this point I am working with the examples from your Website, the Rendering engine, Browser, Platform, etc. I figure once we get this completely worked out, I can apply this knowledge to the tables I will use in my project.

I would like to start with the CSV export, so that I can get a feel for how the AJAX call is supposed to work. On the server side I plan on using JSP, Java Script, or Java. When I implemented the server side processing of DataTables I used a JSP to return the data, it seems like I should be able to modify this JSP but I am not sure if that is a good assumption or not?

allan

Hi David,

CSV is a good starting point I would say! So what you want to do here is to have TableTools make a request to the server which will result in a file being downloaded with the content you want in it. The easiest way of having a file downloaded is to have a hidden iframe which will point to your server-side file generator and set the MIME type for the file. The HTTP headers you want are something like:

[quote]Content-type: text/csv
Content-Disposition: attachment; filename="downloaded.csv"
[/quote]

This will force a file download with a file name "downloaded.csv".

To have TableTools create this iframe we can do something like this:

[code]
$(document).ready( function () {
$('#example').dataTable( {
"sDom": 'T<"clear">lfrtip',
"oTableTools": {
"aButtons": [ {
"sExtends": "ajax",
"fnClick": function () {
var iframe = document.createElement('iframe');
iframe.style.height = "0px";
iframe.style.width = "0px";
iframe.src = "/generate_csv.php";
document.body.appendChild( iframe );
}
} ]
}
} );
} );
[/code]

In this example I've overridden fnClick for the 'ajax' button with a little function that simply creates an iframe and adds it to the document. By default the 'ajax' button will make an XHR request to the server, but it's a lot harder to force that to download a file than by just quickly building an iframe (a good option for a future button option I think or a plug-in!), hence the iframe.

When the iframe is appended to the body the browser will attempt to load the data for it - and with the MIME information supplied it will cause the download. This is the code that I've just does a quick example with (which is PHP and I realise you aren't using PHP, but just a simple example which can be readily ported to any language):

[code]
<?php
header('Content-type: text/csv');
header('Content-Disposition: attachment; filename="downloaded.csv"');

echo 'Header1,Header2'."\n";
echo 'Body1,Body2'."\n";
?>
[/code]

So from here the remaining challenge is to get the data which needs to be exported. What I would suggest for this is to set a GET parameter which identifies what table it is you want (i.e. generate_csv.jsp?table=games or similar). Then you can use this CSV generator for all tables. From there your server-side process will need to simply read the DB table you need and print the information needed for the CSV output using a 'for' loop or there might even be an extension or library function available for that in JSP.

How does that all sound?

Regards,
Allan

David_Dean

Hi Allan,
Thanks for the suggstions, I definitely feel like I am making progress. I managed to implement your example with a JSP program in place of the PHP example. So, I have taken the first step.

I can theoretically see how the JSP program could be updated to dump a table in to a CSV. My next question is can I reference the filtering fields from the DataTable? I have allowed the global search (search box in the upper right hand corner of the DataTable) and colum filtering. I would like the users to be able to specify filtering, and then when they hit the CSV export (AJAX call) I am able to provide them the rows from the table that match the filtering criteria.

Thanks, David

allan

Hi David,

So with the filter what you need is to get the filtering string which is currently applied to the table and then pass this through to the CSV generating script as a GET parameter (you could POST it by building up a form if you wanted, but GET should be good enough for now). One way of doing this is by querying the DOM, but what you could do is pull it out of the DataTables setting function.

To do this you need to know a couple of things - firstly that the TableTools fnClick function is executed in the TableTools instance scope - so 'this' in fnClick refers to the TableTools instance. Then we can use "this.s.dt" (the variables used to say: this . settings . datatable) to get the DataTables settings object. From there "oPreviousSearch.sSearch" is the parameter we want. A bit complicated but this is what it looks like:

[code]
$(document).ready( function () {
$('#example').dataTable( {
"sDom": 'T<"clear">lfrtip',
"oTableTools": {
"aButtons": [ {
"sExtends": "ajax",
"fnClick": function () {
var search = this.s.dt.oPreviousSearch.sSearch;
var iframe = document.createElement('iframe');
iframe.style.height = "0px";
iframe.style.width = "0px";
iframe.src = "/generate_csv.php?filter="+encodeURIComponent(search);
document.body.appendChild( iframe );
}
} ]
}
} );
} );
[/code]

With that information you can apply a WHERE clause to the SQL statement you use of the server-side - very similar to what is done in the server-side processing script.

Regards,
Allan

David_Dean

Allen,

Thanks! I am one step closer, thanks for working with me an incremental fashion like this, it really helps me to see what is going on.

I was able to filter based on the sSearch in the SQL in my JSP. Now, I need to pass in the filters on the individual columns. I am guessing it will look similar, to the code above accept that .sSearch will be replaced wiht another field name(s).

Thanks, David

allan

Hi David,

Great to hear that this is working well for you. I'm rather enjoying it myself :-). So you are absolutely correct that the column specific filtering can be done in the same way. In this case it's the "aoPreSearchCols" property we want from DataTables which is an array of the column filters:

[code]
$(document).ready( function () {
$('#example').dataTable( {
"sDom": 'T<"clear">lfrtip',
"oTableTools": {
"aButtons": [ {
"sExtends": "ajax",
"fnClick": function () {
var search = this.s.dt.oPreviousSearch.sSearch;
var columns = this.s.dt.aoPreSearchCols;
var columnsearch = "";

for ( i=0 ; i

allan

Been thinking about this a bit and I think the right way to approach this is for me to put together an 'iframe' based plug-in button for TableTools which will do basically the same as above, but as a library function. In addition to this a small change to DataTables would be suitable here I think, to be able to get the information that matches server-side processing requests (it's just a case of moving some code into a function).

Does that sound reasonable to you?

Allan

David_Dean

Thanks Allan, the idea of working this in to the TableTools sounds good to me.

allan

Hi David,

And to me too :-). I've implemented a plug-in to do what you need, which you can download from here: http://datatables.net/extras/tabletools/plug-ins#download . To use simply include that code somewhere after you've loaded DataTables and TableTools, but before you initialise the table.

You'll also need the DataTables 1.8.2 development version from this page: http://datatables.net/download/ (the "nightly").

With this, the TableTools initialisation is a lot simpler:

[code]
$(document).ready( function () {
$('#example').dataTable( {
"sDom": 'T<"clear">lfrtip',
"oTableTools": {
"aButtons": [ {
"sExtends": "download",
"sButtonText": "Download CSV",
"sUrl": "/generate_csv.php"
} ]
}
} );
} );
[/code]

You can customise the text with the sButtonText parameter and the URL for loading data with sUrl. In this way it will make it much easier to have multiple buttons with different parameters.

If you'd like an explanation of how any of what I've done works, please feel free to ask :-)

Regards,
Allan

David_Dean

I managed tp get the CSV working, and the PDF started, but gave up on the PDF because I decided I didn't really need it for this project.

Below is the code JS code I generated based on everything Allan gave me. If anyone needs more information than what is posted below, I would be happy to share. I have a JSP for creating the CSV that I could post, if it is helpful.

This forum, and Allan, have been of great help to me. So, I want to pass it on to others who are just starting to implement this great plugin!

Cheers!

[code]
/* The following downloadn Button Code was added by Allan to allow for the creation of a CSV and PDF button for server side processing */
TableTools.BUTTONS.download = {
"sAction": "text",
"sFieldBoundary": "",
"sFieldSeperator": "\t",
"sNewLine": "
",
"sToolTip": "",
"sButtonClass": "DTTT_button_text",
"sButtonClassHover": "DTTT_button_text_hover",
"sButtonText": "Download",
"mColumns": "all",
"bHeader": true,
"bFooter": true,
"sDiv": "",
"fnMouseover": null,
"fnMouseout": null,
"fnClick": function( nButton, oConfig ) {
var oParams = this.s.dt.oApi._fnAjaxParameters( this.s.dt );
var iframe = document.createElement('iframe');
iframe.style.height = "0px";
iframe.style.width = "0px";
iframe.src = oConfig.sUrl+"?"+$.param(oParams);
document.body.appendChild( iframe );
},
"fnSelect": null,
"fnComplete": null,
"fnInit": null
};

/*
* The following code controls the options of the DataTable, like the pagination features, the server side load,
* the column selection, the CSV button, forcing it to remember the selection criteria and pages, etc.
*/
var asInitVals = new Array();
$(document).ready(function() {
var oTable = $('#example').dataTable( {
"sDom": 'T<"clear">lfrtip',
"oTableTools": {
"sSwfPath": "swf/copy_cvs_xls_pdf.swf",
"aButtons": [
{
"sExtends": "download",
"sButtonText": "CSV",
"sButtonClass": "DTTT_button_csv",
"sButtonClassHover": "DTTT_button_csv_hover",
"sUrl": "jsp/testCSV.jsp"
}]
},
"oLanguage": {
"sSearch": "Search all columns:"
},
"sPaginationType": "full_numbers",
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "jsp/test-data-tables4.jsp",
"bStateSave": true,
"fnInitComplete": function() {
var oSettings = $('#example').dataTable().fnSettings();
for ( var i=0 ; i0){
$("tfoot input")[i].value = oSettings.aoPreSearchCols[i].sSearch;
$("tfoot input")[i].className = "";
}
}
}
} );

$("tfoot input").keyup( function () {
/* Filter on the column (the index) of this element */
/* The "keyup" function above forces the typing in a field to activate the */
/* the query/sql locic with out having to press enter to start the search */
oTable.fnFilter( this.value, $("tfoot input").index(this) );
} );

/*
* Support functions to provide a little bit of 'user friendlyness' to the textboxes in
* the footer
*/
$("tfoot input").each( function (i) {
asInitVals[i] = this.value;
} );

$("tfoot input").focus( function () {
if ( this.className == "search_init" )
{
this.className = "";
this.value = "";
}
} );

$("tfoot input").blur( function (i) {
if ( this.value == "" )
{
this.className = "search_init";
this.value = asInitVals[$("tfoot input").index(this)];
}
} );
} );

[/code]

allan

Hi David,

Good to hear you got it going! And thanks very much for posting your code - I'm sure this will prove to be very useful!

Regards,
Allan

pash

Hm, where do you got [code]this.s.dt.oApi._fnAjaxParameters()[/code] from, because it is not a function in [code]dt.oApi[/code] anymore???

Ooops, sorry will be available in datatables 1.8.2. Sorry.