I have tables which use DataTables Server Side processing to show on my website. I want to be able to 'Export All' and have all rows be exported, not just those rows being displayed. There are 60000+ rows and 65+ columns, so it must be done with server side processing.
I have tried a few things, but so far nothing has worked.
I have tried this:
{ extend: 'excel',
text: 'Export Current Page',
exportOptions: {
modifier: {
page: 'current'
}
},
customize: function (xlsx)
{
var sheet = xlsx.xl.worksheets['sheet1.xml'];
$('row:first c', sheet).attr('s', '7');
}
}
Which only exported the rows that were showing on the page.
I've tried this:
{
text: 'Export All to Excel',
action: function (e, dt, button, config)
{
dt.one('preXhr', function (e, s, data)
{
data.length = -1;
}).one('draw', function (e, settings, json, xhr)
{
var excelButtonConfig = $.fn.DataTable.ext.buttons.excelHtml5;
var addOptions = { exportOptions: { 'columns': ':all'} };
$.extend(true, excelButtonConfig, addOptions);
excelButtonConfig.action(e, dt, button, excelButtonConfig);
}).draw();
}
}
This sends the whole table's data to the screen instead of using the pagination and sending the whole data set to an excel file.
I've searched around on Google and here in SO, but have not found a solution that works.
I should also mention that I want to Export All based on the current filters set on the table. So that the end user will get an Export of only those rows that they are searching for. They typically limit it to 30k - 40k rows, still with the 65+ columns. I don't (yet) allow to remove/hide columns.
EDIT/UPDATE
Here's a secondary consideration: If I can't Export All from a response from the server, can I build the Excel file on the server? My servers don't have Excel installed and I will still want my end user to get the file. I'm sure that I'd have to find a way to get Excel onto my servers, but how would I transfer any created files to the end user and would that even be any faster than just sending a response with the whole dataset and creating the Excel file on the user's computer?
EDIT
It was recommended that I try jquery's $.ajax() to get this to work. If someone could give me an idea of how to do that I'll try that for a third button.
I can already pull all the data, with the same filters and sorting that are added by the user, and do that with a button. The second attempt above does that but sends it to the screen. I have PHPExcel and a file that can create an Excel sheet. How would I take what I get in that second button and send it to the other file to create the Excel sheet? I thought that using jquery's $.ajax() might work, I just don't know how to get it to. I do know that I'll have to use $_POST since the data could be too big to use $_GET to send the data to the PHPExcel file.
I can already export to a CSV, but I need to export with some formatting, which CSV does not have. That's why I'm going to the trouble of use PHPExcel.
EDIT III
I am trying this, though it's not yet working:
{
text: 'Export all to Excel II',
action: function (e, dt, button, config)
{
dt.one('preXhr', function (e, s, data)
{
data.length = -1;
}).one('export', function (e, settings, json, xhr)
{
var excelButtonConfig = $.fn.DataTable.ext.buttons.excelHtml5;
var addOptions = { exportOptions: { 'columns': ':all'} };
$.extend(true, excelButtonConfig, addOptions);
excelButtonConfig.action(e, dt, button, excelButtonConfig);
})
}
}
EDIT 4
Hopefully the last edit.
I know that I have to do three things to make this work:
- Get current Sorting and Filtering
- Get dataset with length set to -1
Send this to PHPExcel file for processing and creation of Excel file I can create a button like this:
{ text: 'Export all Data to Excel', action: }
I just don't know what the action needs to be.
My second attempt above pulls the whole dataset that I need, but sends it to the screen instead of to my PHPExcel file (ExportAllToExcel.php).
I have been trying to figure this out and haven't gotten very far. I've been told that I need to use $.ajax() to do this, I've been told that I don't need to use that. I have tried with and without and have not been able to get anywhere.
I have also tried using this to no effect:
$.fn.dataTable.ext.buttons.export =
{
className: 'buttons-alert',
"text": "Export All Test",
action: function (e, dt, node, config)
{
var SearchData = dt.search();
var OrderData = dt.order();
alert("Test Data for Searching: " + SearchData);
alert("Test Data for Ordering: " + OrderData);
}
};