TableTools csv/excel to get whole table (with ServerSide)

fbas

I believe I've seen a thread on this before, but could not find it or info in the docs about it, but how can I have my table tools CSV button get the entire table (with bServerSide) rather than just what's on the screen?

allan

Because the client doesn't have the full data set, you have basically two options:

1. You can have the client make a request to the server, get the data and then pass it to Flash for the export.

2. You can make a request to the server for it to generate the file and then offer it as a download to the client.

Personally I would say option 2 is better, since if you are using server-side processing you are likely to be dealing with a reasonably large number of rows - and Flash really isn't that fast when it comes to processing the output.

To do option 2, there is a 'download' button plug-in for TableTools that will do all the client-side stuff for you: http://datatables.net/extras/tabletools/plug-ins#download . That will create an iFrame with the required request, and all the server-side parameters you would normally get for server-side processing (so the same script can be reused). Thus all that is needed is on the server-side, a little script to generate the file :-)

Regards,
Allan

fbas

Thanks. This is a perfect solution.

I had already modified my server_processing.php code to accept a parameter marking output to csv which changes the header("Content-type: text/csv"); and outputs csv rather than json.

near the top of the file (though could be incorporated at the bottom with the output code as well):
[code]
// parse new querystring variable "o" to represent output_mode
$output_mode = (isset($_REQUEST["o"])) ? $_REQUEST["o"] : "json";
[/code]

at the bottom of the file:
[code]
if ($output_mode == "csv") {
header("Content-type: text/csv");
header("Cache-Control: no-store, no-cache");
header('Content-Disposition: attachment; filename="filename.csv"'); // you can change the default file name here

echo csv_encode( $output["aaData"], $aColumns ); // see function below
}
else {
// default is to output json
echo json_encode( $output );
}


/*
* csv_encode - encode data as csv, wrapping values in quotes
*/
function csv_encode($aaData, $aHeaders = NULL) {
// output headers
$output = "";
if ($aHeaders) $output .= '"' . implode('","', $aHeaders ) . '"' . "\r\n";

foreach ($aaData as $aRow) {
$output .= '"' . implode('","', $aRow) . '"' . "\r\n";
}

return $output;
}
[/code]

allan

Nice one - that's a very nice solution to reusing the server-side script. Thanks for sharing your solution.

Ultimately I think I should build it into a much more flexible PHP class that would allow easy querying from DataTables or another source pretending to be DataTables... One for the to-do list.

Regards,
Allan

fbas

My code is choking on this line (from http://datatables.net/extras/tabletools/plug-ins#download ):

[code]var oParams = this.s.dt.oApi._fnAjaxParameters( this.s.dt );[/code]

what is "this" in context?

allan

The TableTools instance. The callback function is executed with the scope of the TableTools instance that is used to generate that button (similar to DataTables callback functions using the DataTables instance for 'this').

Are you using DataTables 1.8.2? I think _fnAjaxParameters was new in that release.

Allan

fbas

It was 1.8.1. I'll upgrade it. Thanks.

fbas

Ok, that helped, but here's my next issue:

is there a way to add my parameter "o=csv" to the querystring that is tacked onto the sAjaxUrl?

I originally tried sAjaxUrl: "server_processing.php?o=csv"

but the system adds another "?" and the calculated query string like "server_processing.php?o=csv?sEcho=2&iColumns=16&sColumns=..."

is there an equivalent to fnServerParams here?




I guess as a workaround I can add an ampersand after o=csv to make sure that param is seen properly