Export All from DataTables with Server Side processing?

Question

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:

1. Get current Sorting and Filtering
2. Get dataset with length set to -1

3. 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);
    }
};

Answer 1

First add the follwoing code in DataTable

"dom": 'Blfrtip',
                    "buttons": [
                        {
                            "extend": 'excel',
                            "text": '<button class="btn"><i class="fa fa-file-excel-o" style="color: green;"></i>  Excel</button>',
                            "titleAttr": 'Excel',
                            "action": newexportaction
                        },
                    ],

Then add this function inside $(document).ready() function

function newexportaction(e, dt, button, config) {
         var self = this;
         var oldStart = dt.settings()[0]._iDisplayStart;
         dt.one('preXhr', function (e, s, data) {
             // Just this once, load all data from the server...
             data.start = 0;
             data.length = 2147483647;
             dt.one('preDraw', function (e, settings) {
                 // Call the original action function
                 if (button[0].className.indexOf('buttons-copy') >= 0) {
                     $.fn.dataTable.ext.buttons.copyHtml5.action.call(self, e, dt, button, config);
                 } else if (button[0].className.indexOf('buttons-excel') >= 0) {
                     $.fn.dataTable.ext.buttons.excelHtml5.available(dt, config) ?
                         $.fn.dataTable.ext.buttons.excelHtml5.action.call(self, e, dt, button, config) :
                         $.fn.dataTable.ext.buttons.excelFlash.action.call(self, e, dt, button, config);
                 } else if (button[0].className.indexOf('buttons-csv') >= 0) {
                     $.fn.dataTable.ext.buttons.csvHtml5.available(dt, config) ?
                         $.fn.dataTable.ext.buttons.csvHtml5.action.call(self, e, dt, button, config) :
                         $.fn.dataTable.ext.buttons.csvFlash.action.call(self, e, dt, button, config);
                 } else if (button[0].className.indexOf('buttons-pdf') >= 0) {
                     $.fn.dataTable.ext.buttons.pdfHtml5.available(dt, config) ?
                         $.fn.dataTable.ext.buttons.pdfHtml5.action.call(self, e, dt, button, config) :
                         $.fn.dataTable.ext.buttons.pdfFlash.action.call(self, e, dt, button, config);
                 } else if (button[0].className.indexOf('buttons-print') >= 0) {
                     $.fn.dataTable.ext.buttons.print.action(e, dt, button, config);
                 }
                 dt.one('preXhr', function (e, s, data) {
                     // DataTables thinks the first item displayed is index 0, but we're not drawing that.
                     // Set the property to what it was before exporting.
                     settings._iDisplayStart = oldStart;
                     data.start = oldStart;
                 });
                 // Reload the grid with the original page. Otherwise, API functions like table.cell(this) don't work properly.
                 setTimeout(dt.ajax.reload, 0);
                 // Prevent rendering of the full data to the DOM
                 return false;
             });
         });
         // Requery the server with the new one-time export settings
         dt.ajax.reload();
     }

Answer 2

I have this working, mostly. It is now timing out, but that's a separate issue due to data size not to this working. For small datasets, it works perfectly.

This is how I create the button (it's the export button that I'm using here):

"buttons": [{
                extend: 'collection',
                text: 'Selection',
                buttons: ['selectAll', 'selectNone']
            }, {
                extend: 'collection',
                text: 'Export',
                buttons: ['export', 'excel', 'csv', 'pdf', { 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');
                    }
                }]
            }
            ]

This is the initialization of the button created above:

$.fn.dataTable.ext.buttons.export =
{
    className: 'buttons-alert',
    id: 'ExportButton',
    text: "Export All Test III",
    action: function (e, dt, node, config)
    {
        var SearchData = dt.rows({ filter: 'applied' }).data();
        var SearchData1 = dt.search();
        console.log(SearchData);
        var OrderData = dt.order();
        console.log(SearchData1);
        var NumCol = SearchData[0].length;
        var NumRow = SearchData.length;
        var SearchData2 = [];
        for (j = 0; j < NumRow; j++)
        {
            var NewSearchData = SearchData[j];
            for (i = 0; i < NewSearchData.length; i++)
            {
                NewSearchData[i] = NewSearchData[i].replace("<div class='Scrollable'>", "");
                NewSearchData[i] = NewSearchData[i].replace("</div>", "");
            }
            SearchData2.push([NewSearchData]);
        }

        for (i = 0; i < SearchData2.length; i++)
        {
            for (j = 0; j < SearchData2[i].length; j++)
            {
                SearchData2[i][j] = SearchData2[i][j].join('::');
            }
        }
        SearchData2 = SearchData2.join("%%");
        window.location.href = './ServerSide.php?ExportToExcel=Yes';
    }
};

And here is the part of the ServerSide.php file that gets the data and sends it to the server for processing:

require('FilterSort.class.php');

if (isset($_GET['ExportToExcel']) && $_GET['ExportToExcel'] == 'Yes')
{
    $request = @unserialize($_COOKIE['KeepPost']);
    $DataReturn = json_encode(FilterSort::complex($request,$sqlConnect,$table,$primaryKey,$ColumnHeader));
    require './ExportAllToExcel.php';
}
else
{
    echo json_encode(FilterSort::complex($request,$sqlConnect,$table,$primaryKey,$ColumnHeader));
}

This is how I set the cookie that I use to keep the search and sort criteria:

if(isset($_POST['draw']))
{
    $KeepPost = $_POST;    
    $KeepPost['length'] = -1;
    $PostKept = serialize($KeepPost);
    setcookie("KeepPost",$PostKept,time() + (60*60*24*7));
}

All this combined sends the correct criteria to FilterSort.class.php which should process the criteria and return the dataset to ExportAllToExcell.php which then creates the Excel file. Right now I'm sending it huge reports and it times out, though.

UPDATE

I have slightly changed the way that I do this:

Here is the new set of buttons:

"buttons": [{
    extend: 'collection',
    text: 'Export',
    buttons: ['export', { extend: 'csv',
        text: 'Export All To CSV',              //Export all to CSV file
        action: function (e, dt, node, config)
        {
            window.location.href = './ServerSide.php?ExportToCSV=Yes';
        }
    }, 'csv', 'pdf', { extend: 'excel',
        text: 'Export Current Page',            //Export to Excel only the current page and highlight the first row as headers
        exportOptions: {
            modifier: {
                page: 'current'
            }
        },
        customize: function (xlsx)
        {
            var sheet = xlsx.xl.worksheets['sheet1.xml'];
            $('row:first c', sheet).attr('s', '7');
        }
    }]
}
]

Here is how I create the Export All to Excel button:

$.fn.dataTable.ext.buttons.export =
{
    className: 'buttons-alert',                         //Adds the "Export all to Excel" button
    id: 'ExportButton',
    text: "Export All To Excel",
    action: function (e, dt, node, config)
    {
        window.location.href = './ServerSide.php?ExportToExcel=Yes';
    }
};

These now send the data to the same ServerSide.php file that I was using before:

require('FilterSort.class.php');
if (isset($_GET['ExportToExcel']) && $_GET['ExportToExcel'] == 'Yes')
{
    include 'Helper/LogReport.php';
    $GetSQL = "Select Value from PostKept where UserName = '" .$_COOKIE['UserName']. "'";
    $KeepResult = $conn->query($GetSQL);
    $KeepResults = $KeepResult->fetchALL(PDO::FETCH_ASSOC);

    $request = unserialize($KeepResults[0]['Value']);

    $DataReturn = json_encode(FilterSort::complex($request,$sqlConnect,$table,$primaryKey,$ColumnHeader,1));
    require './ExportAllToExcel.php';

I have also changed the way that I keep the query, I have it now also keeping the Table Name and UserName like this:

include 'DBConn.php';
$KeepPost = $_POST;                                     //POST holds all the data for the search
$KeepPost['length'] = -1;                               //-1 means pulling the whole table
$PostKept = serialize($KeepPost);                       //This takes the array of data and turns it into a string for storage in SQL
$SQLCheck = "select distinct UserName from PostKept";   //Gets all the distinct Usernames of users that have used the Report Dashboard.
$sth = $conn->query($SQLCheck);
$CheckedUser = $sth->fetchALL(PDO::FETCH_ASSOC);
foreach($CheckedUser as $User)
{
    foreach($User as $Index => $Who)
    {
        $FoundUsers[] = $Who;                           //Taking all the found users and placing them into a simpler array for searching later

    }
}

if(isset($_COOKIE['UserName']) && in_array($_COOKIE['UserName'],$FoundUsers))   //If the user already has an entry update it with new information
{
    $TSQL = "UPDATE PostKept set Value = '" .$PostKept. "', TableName = '" .$TableName. "' where UserName = '" .$_COOKIE['UserName']. "'";
}
else
{
    if(isset($_COOKIE['UserName']))     //If this is a new user
    {
        $TSQL = "INSERT into PostKept(Value, TableName, UserName) select '" .$PostKept. "','" .$TableName. "','" .$_COOKIE['UserName']. "'";
    }
    else        //If this is on the Prod site and the User info is not yet kept
    {
        $TSQL = "INSERT into PostKept(Value, TableName) select '" .$PostKept. "','" .$TableName. "'";
    }
}

$sth = $conn->prepare($TSQL);
$sth->execute();

This is now what all combines to send the data to the ExportAllToExcel.php file that I have and then it in turn creates the file.

Answer 3

I just ran into this and came up with an alternate solution.

In the DataTable options, add this:

"lengthMenu": [[10, 25, 50, -1], [10, 25, 50, "All"]]

This will allow user to select all rows and will send -1 to server in 'length' query string parameter. At server side, you need to handle negative number and allow to return all rows when -1 is received.

This would display all rows in table and will export all of them.

I understand that this may not be suitable for 50-60K rows but for a smaller dataset, this can work without implementing any additional code at server and client side both.

Answer 4

in buttons:

action: function (e, dt, node, config) {

var formData = 'yourfilters';
formData.begin = '0';
formData.length = 'yourTotalSize';

$http({
    url: 'yourURL',
    method: 'POST',
    data: JSON.stringify(formData)
}).then(function (ajaxReturnedData) {

    dt.rows.add(ajaxReturnedData.data).draw();
    $.fn.dataTable.ext.buttons.excelHtml5.action.call(this, e, dt, node, config);

});}