Is there a way to Export All while using server side processing?

mmcnair80

I am using server side processing for my tables. I have tables that have 60000+ rows with 65+ columns, so using client side is not an option for me. My end users want to pull up the tables and then use the filtering and sorting to narrow down what they are seeing. Then they want to Export All, usually still 30k - 40k rows with the 65+ columns and with the sorting in place too. I have tried a few things that I've found elsewhere and am hoping that you can help. Here's what I've tried so far:

{ 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 does not do what I would like. It exports only the current page (which is why I made the text for it 'Export Current Page')

I've also 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 simply sends the whole table to the screen instead of sending it to an excel file. Though it does pull the whole data set.

I'm sure that through the two of these I'm missing something that would allow me to Export the whole data set without sending it to the screen first.

tangerine

https://datatables.net//forums/discussion/36942

mmcnair80

@tangerine , I've seen that. If I add that option, the "Show All", then the table takes upwards of 5 minutes to render. That is too long a time.

By having the data go straight to an Excel file I am hoping that it will be much faster than that.

allan

Fundamentally if you want to use client-side export for a data set, then the data set needs to be at the client-side. That means you are going to have the 5 minute wait one way or another. Actually, it would probably be a lot longer since the Excel export isn't exactly fast due to the number of nodes it needs to create.

See the last FAQ in the server-side processing section.

Basically, your best option is to create the file on the server-side.

Allan

mmcnair80

@allan If I create the Excel file on the server then how would the end user get it? They don't have access to the server only to the website.

I'm not even sure how to create the file on the server, is there an example that you could show me?

tfriedlich

What technology are you using for your sever side processing of data? If you are using dotnet, you could use http://epplus.codeplex.com/ and stream the response back. I just implemented that myself for the exact same use case and it was pretty easy (and well documented).

mmcnair80

@tfriedlich I am using php for the whole of my site, client side, and server side.

allan

If I create the Excel file on the server then how would the end user get it?

Download it to the client. Basically, you would have your PHP page create the XLSX file. There are a number of PHP XLSX writers available such as this one.

I don't have an example of that on the server-side I'm afraid.

Allan

mmcnair80

@allan So, with my second example in my question, I'm able to capture the data that I want. How do I send that to this new php page instead of back to the one that is already being used?

I'm looking into PHPExcel, and it looks very promising. I just don't know how I'm supposed to send the data to the php file that will create the Excel file.

allan

You could use jQuery's $.ajax() to send the data you have got to the server.

Allan

mmcnair80

@allan Are there any examples of this? Or could you provide an example?
I'm already getting the data that I want from my second attempt above, but I don't know how to then send it through the ajax?

I don't know jquery well enough to figure this out.

allan

There are loads of jQuery Ajax tutorials available on the web, including jQuery's own learning center.

Allan

mmcnair80

Here's what I'm trying now, but it doesn't work. I know I've got something missing.

{
    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);
        })
    }
}

allan

I don't actually see an Ajax call there?

That also appears to be using the client-side export. I really would encourage you not to do that, but rather to create the file server-side. If you use client-side export you are loosing any benefit you might gain from using server-side processing in the first place. Indeed, all you are doing is adding network latency into your application.

Allan

mmcnair80

That's what I was afraid of, that's it's not actually doing what I want. I do have a file that I'm testing from PHPExcel. It will create the file that I need, but I've not yet figured out how to get the data to it. I'm reading up on that $.ajax to try and figure out how to get that to work for me.

allan

Is the data you want not already at the server-side? The only thing you'd need to tell the server is the current sorting and filtering applied to the data.

Allan

mmcnair80

Yes, the data is already server side. I don't know how to tell it what sorting and filtering have been applied. That's the problem. I understand php and can use it, but jquery and ajax I don't understand. I've not had much experience with it and don't know what to do to get the filtering and sorting that I need and send the data to the PHPExcel script that I have on my server to create the excel file.

So, I really need a couple of things.

1. I need to capture the sorting and filtering currently on the table (which will be in the last draw sent to the server)
2. I need to capture all the data that would be contained in that draw, but with a row limit of -1, and send it to the PHPExcel script that I have prepared.

Then the script that I have will create the file that the user can download.

I just don't know how to do those first two things.

allan

1) search() and order() will give you that information.

2) Make the query against the database with the WHERE and ORDER BY based on the above (which you could send as Ajax parameters or even just a simple GET request / redirect).

Allan

mmcnair80

I must be doing this wrong, I'm not getting anything back from those two API's. I've tried this:

    $.fn.dataTable.ext.buttons.export =
    {
        className: 'buttons-alert',
        "text": "Export All Test",
        action: function (e, dt, node, config)
        {
            var SearchData = table.search();
            var OrderData = table.order();
            alert("Test Data for Searching: " + SearchData);
            alert("Test Data for Ordering: " + OrderData);
        }
    };

But only when I comment out the var sections does anything come up.

allan

I'd need a link to a page showing the issue to be able to offer any help but debugging it.

Thanks,
Allan

allan

One thing - probably worth using dt rather than table in the above code, since that is the DataTable instance, whereas table will be specific to a given table.

Allan

mmcnair80

I've tried using this to show what I've got going. But it doesn't work the way I have it working in my environment. I can't get the buttons to show, though I did add that library and several other things. I don't know how to set it up correctly in there to truly show what is happening. I did copy and paste my javascript into it so that you can see that at least. I have several custom buttons, all of them are attempts to get this export to work server side. Here's my ServerSide.php file too:

include 'Helper/PageName.php';              //Pulls the page name and Table name and returns the $SQLTableName, $TableName, $Title and $HeadingDesc
include 'DBConn.php'; //echo "<br>Testing"; //DB connection info

$headings = array();                        //Create the empty array for use later and so that it won't throw an error if not assinged later
$hsql = "select Headings from TableHeadings where TableName = '$TableName' order by Id";    //Get all the column headers from the TableHeadings table in SQL

$getHeadings = $conn->query($hsql);
$rHeadings = $getHeadings->fetchALL(PDO::FETCH_ASSOC);
$CountHeadings = count($rHeadings);         //Count how many columns that there will be
$tsqlHeadings = '';
$ColumnHeader = array();
for ($row = 0; $row < $CountHeadings; $row++)
{
        $headings[$row] = $rHeadings[$row]["Headings"];     //fill the array of column headings for use in creating the DataTable
}
$Edit = 0;
if(in_array("Edit",$headings))
{
    $Edit = 1;
}
else
{
    $Edit = 0;
}

foreach($headings as $index => $columnName)
{
    $ColumnHeader[] = array('db'=>$columnName,'dt'=>$index);    //creates the array for creating the DataTable column headers when sent to the FilterSort.class
}

//DB table to use
$table = $SQLTableName;

//Table's primary key
$primaryKey = 'id';

// Get data to display
$request = array();
$_POST['PageName'] = $Page;
if($Edit == 1)
{
    $_POST['columns']['0']['name'] = "Edit";
}
if (isset($_GET['length']))
{
    $_POST['length'] = $_GET['length'];
}
$request = $_POST;
//http_response_code(606);
require('FilterSort.class.php');

if (isset($_POST['ExportToExcel']) && $_POST['ExportToExcel'] == 'Yes')
{
    require 'ExportAllToExcel.php';
}
else
{
    echo json_encode(FilterSort::complex($request,$sqlConnect,$table,$primaryKey,$ColumnHeader));
}

tangerine

Sort your js loading sequence out. jQuery first, then datatables, then datatables buttons.

mmcnair80

Thanks @tangerine that helped. Now the buttons show up. Though a lot of the formatting is still wrong. I also note that even though I have added the "Select" library, I cannot select anything, this doesn't matter as much to me right now, though.

What does matter is that only my custom buttons show up, not the built in ones from the "Buttons" library?

allan

I also note that even though I have added the "Select" library, I cannot select anything

Have you enabled it with the select option?

What does matter is that only my custom buttons show up, not the built in ones from the "Buttons" library?

That suggests you haven't loaded either the pre-built button files (such as buttons.html5.js) or their dependencies. The download builder will resolve that for you.

Allan

mmcnair80

@allan I have them all working fine in my work environment. It's in the DataTables Live environment that they're not working. I already used the download builder to get them for my working files.

allan

Ah I see. Its because the buttons.html5.js file hasn't been included (or any of the other dependencies for those buttons such as JSZip). The download builder is the way to resolve that there as well.

Allan

mmcnair80

@allan Is there a way to set the href attribute of a button? I've tried:

$('.dt-button.buttons-alert').attr('href', './AjaxHandler.php');

document.querySelector('.buttons-alert').setAttribute('href', './AjaxHandler.php');

But neither of these work. I've also tried adding a href: './AjaxHandler.php' to the initialization of the button, but that didn't work either.

allan

No - just using window.location.href in the action handler for the button should do it.

Allan

mmcnair80

Perfect that worked. I think I almost have a solution to the whole problem. I'll post it here once it's complete.

mmcnair80

I got the DataTables Live to work, I think
live.datatables.net/luxeheye/1