Excel Export all records with ajax and scroller true
Excel Export all records with ajax and scroller true
hpegmslicensemgmt
Posts: 23Questions: 8Answers: 0
in Buttons
Dear all,
I have a pretty big datatable which is using ajax for the data retrieval from the Database and the scroller extension to load the data only when it's needed.
When I export the data to Excel, I only get the records that have been in the "cursor" around 300 ish...
I see in the Chrome Dev Tools, that the ajax is not fired prior to the export - so I assume datatables just uses the data from it's cache....
Is there a simple way that datatables would read all records, prior to export ?
Best regards,
David
var oTable = $('#myTable').DataTable({
scrollX: true,
scrollY: sHeight,
serverSide: true,
ordering: true,
dom: 'B<"#dateselect">fr<t>ip',
stateSave: false,
deferRender: true,
ajax: {
url: "ajax.php",
data: function ( d ) {
d.AjaxAction = 'datatables';
d.SQLWhere = $('#SQLWhere').val();
},
type: 'GET'
},
order: [[ 0, "asc"]],
scroller: {
loadingIndicator: true
},
search: {
return: true
},
columnDefs: [
{
targets: [<?php echo $_SESSION['numtargets'] ?>],
className: 'dt-body-right'
}
],
buttons: [
{
extend: 'copy',
text: '<img src="images/copy.png" border="0">',
titleAttr: 'Copy'
},
{
extend: 'csv',
text: '<img src="images/csv.png" border="0">',
titleAttr: 'CSV',
filename: '<?php echo $_SESSION['title']; ?>'
},
{
extend: 'excel',
text: '<img src="images/excel.gif" border="0">',
titleAttr: 'XLS',
className: 'ExcelExport',
filename: '<?php echo $_SESSION['title']; ?>',
title: 'null',
exportOptions: {
modifier: {
order: 'current',
page: 'all'
}
}
},
{
extend: 'print',
autoPrint: false,
text: '<img src="images/print.png" border="0">',
titleAttr: 'Print'
},
{
extend: 'pdf',
orientation: 'landscape',
pageSize: 'A4',
text: '<img src="images/pdf.png" border="0">',
titleAttr: 'PDF',
title: '<?php echo $_SESSION['title']; ?>'
},
{
text: '<img src=images/filter.gif border=0>',
className: 'FilterButton',
action: function ( e, dt, node, config ) {
$('#builderfilter').dialog('open');
}
}
]
});
This question has an accepted answers - jump to answer
Answers
See this FAQ about using Server Side Processing with the export buttons.
Kevin
Dear Kevin, thank you very much for your answer !
Do you have an idea if there is a way to execute a command prior to the export when clicking on the Excel button ?
In this case I could trigger an ajax refresh and tell the php script to send all records ...
Best regards,
David
You can create a Custom button like this example. The
buttons.buttons.actiondocs show how to call one of the export functions like Excel.Kevin
Dear Kevin,
The following Button works perfectly and reloads the complete dataset prior to export.
A huge THANKS for your help :-)
Just one last small thing, the generated XLSX File has no headers. Any idea how I can tell the export to include the headers ?
Best regards,
David
It should export the headers by default. Is the issue that you have multiple rows in your header? If not, we're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.
Cheers,
Colin
Dear all,
I found a small work around for my excel header issue:
Now I get the perfect Excel export with all records and the headers :-)
Best regards,
David