TableTools not exporting all rows with bServerSide false when via ajax

RobbSadler

I am using the following code to create a datatable. I am pulling data via ajax, but only at the start, so all filtering and sorting etc, is on the client side. If I populate the rows in the table directly I can get all of my filtered results, but if I use the ajax call (which allows me to take advantage of the "Processing..." message) it exports only the current page of data.

[code]
var oTable = $('#dataTableAccountList').dataTable({
"sDom": "<'row'<'col-lg-6'l><'col-lg-6'Tf>r>t<'row'<'col-lg-6'i><'col-lg-6'p>>",
"bServerSide": false,
"bDeferRender": true,
"sAjaxSource": '@Url.Action("AccountLoadThroughAjaxCall", "AccountList")',
"bProcessing": true,
"oTableTools": {
"sSwfPath": "/Scripts/TableTools-2.2.0/swf/copy_csv_xls_pdf.swf",
"aButtons": [
{
"sExtends": "copy",
"oSelectorOpts": { filter: 'applied', order: 'current' }
},
{
"sExtends": "xls",
"oSelectorOpts": { filter: 'applied', order: 'current' },
"sFileName": "AccountList_yyyy-MM-dd_hh.mm.ss.xls"
},
{
"sExtends": "pdf",
"oSelectorOpts": { filter: 'applied', order: 'current' },
"sFileName": "AccountList_yyyy-MM-dd_hh.mm.ss.pdf",
"sPdfOrientation": "landscape",
"sPdfMessage": "Account List" + ($('div.dataTables_filter input').length > 0 ? " Filter: " + $('div.dataTables_filter input').val() : "")
},
{
"sExtends": "print",
"oSelectorOpts": { filter: 'applied', order: 'current' },
}
]
}
});
[/code]

Again I am sure I am missing something simple, but can't see what it is.

Thanks in advance!

RobbSadler

Hmm, no response - probably cause there is no posted web page. But this can really be boiled down to a simple API usage question:

If I use sAjaxSource to pull the data, and have bServerSide set to false, shouldn't I be able to get all of the filtered data with tableTools?

RobbSadler

The answer is that you cannot use sAjaxSource, but you can use aaData and pull the data via ajax that way and populate the datatable manually:

[code]
$(document).ready(function () {
$.ajax({
type: "POST",
url: '/DataAPI/GetData',
contentType: "application/json; charset=utf-8",
success: function (result) {
loadDatatable(result['aaData']);
},
error: function (result) {
alert('Some error occurred while retrieving account list. ' + result.responseText);
}
});

});

function loadDatatable(aaData) {
var oTable = $('#dataTableAccountList').dataTable({
"sDom": "<'row'<'col-lg-6'l><'col-lg-6'Tf>r>t<'row'<'col-lg-6'i><'col-lg-6'p>>",
"bServerSide": false,
"bDeferRender": true,
"aaData": aaData,

...
[/code]

Once I found this the answer is pretty easy. Hopefully someone else will benefit from the answer. Using this method all of the rows are included when you export.

allan

> If I use sAjaxSource to pull the data, and have bServerSide set to false, shouldn't I be able to get all of the filtered data with tableTools?

Yes you should be able to. I put together a little test case based on your code above and it seems to work okay: http://live.datatables.net/conefim/1 .

Can you link me to a test page that shows the issue so I can debug it please?

Allan

KaiHogan

Adding in my 2-cents worth on this topic. I have recently added TableTools to my code and all was working will except that when I clicked the "copy" button (same for Excel, CSV, etc.). My table has over 18,000 rows of data, all being loaded into aaData, no server side processing. Because there is a lot of data I turned on the "bDeferRender" to True which helped with performance.

I had my initial view set to only the first 10 rows. When clicking the "copy" button it was reporting that it was only copying 1o row (not the 18,000+ I am expecting). If I were to go to say page 2 and then onto page 3 (each showing a new set of 10 rows) and then click the "Copy" button it would now say it copied 30 rows (page 1 + 2 + 3) to the clipboard.

After searching the web high and low, and finding almost no one reporting this exact issue I started to do a lot of "what if" testing....turning on or off one element at a time until I found the problem.

The good news is that I found what is the exact issue.....the bad news is that I do not like the answer, but if I have to can live with it....maybe.

The issue is with the "bDeferRender". If true then it only draws what is needed, however, the TableTools appears to only work with what has been rendered. When I removed this option and then clicked the "copy" I saw the 18,000+ I was expecting.

I hope that there is a way for TableTools to work with the "bDeferRender" enabled in the future.

Hope this helps others struggling.

KaiHogan

An interesting side note on the above that I posted earlier. If the "bDeferRender" is true and I click the "print" button it behaves as expected with all rows rendered. I am using TableTools version 2.2.2-dev.

allan

I'm afraid there is an error with TableTools and the deferRender option at the moment. Its on my todo list but I've not had time to get to it I'm afraid. I hope to do so later this week.

Allan

allan

The issue with TableTools exporting data with deferRender enabled has now been fixed. I'll be released the updated TableTools soon.

Allan

Rubenvh

So is it now possible to do what RobbSadler said? Ajax call + aaData set in init of datatables()?

I'm trying this, but only the loaded rows of the pages I viewed are exported, not all pages. What should I do?

Rubenvh

I upgraded my TableTools to 2.2.3, and it works now.

For any other people with the same problem: there are multiple ways to achieve loading tables with many rows of data.

1) Just put it in the DOM and wait countless seconds before everything is loaded. (not recomended)

2) Using the built in ajax feature that requests data every time you change a page or filter is much server side work and the tabletools don't understand that there's more than 1 page (for exporting purposes).

3) Load your data with ajax and set it in the aaData parameter of the initialisation. Set bDeferRender to true and bServerSide to false. Your page will load very fast and tabletools will understand there's more than just 1 page (if you have at least a recent version of tabletools).

Thx for the fix @Allan!