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

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

mmcnair80mmcnair80 Posts: 83Questions: 21Answers: 7

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.

This question has an accepted answers - jump to answer

«1

Answers

  • mmcnair80mmcnair80 Posts: 83Questions: 21Answers: 7

    @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.

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    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

  • mmcnair80mmcnair80 Posts: 83Questions: 21Answers: 7

    @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?

  • tfriedlichtfriedlich Posts: 1Questions: 0Answers: 0

    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).

  • mmcnair80mmcnair80 Posts: 83Questions: 21Answers: 7

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

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    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

  • mmcnair80mmcnair80 Posts: 83Questions: 21Answers: 7

    @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.

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

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

    Allan

  • mmcnair80mmcnair80 Posts: 83Questions: 21Answers: 7
    edited January 2017

    @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.

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

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

    Allan

  • mmcnair80mmcnair80 Posts: 83Questions: 21Answers: 7

    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);
            })
        }
    }
    
  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    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

  • mmcnair80mmcnair80 Posts: 83Questions: 21Answers: 7

    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.

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    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

  • mmcnair80mmcnair80 Posts: 83Questions: 21Answers: 7
    edited January 2017

    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.

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    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

  • mmcnair80mmcnair80 Posts: 83Questions: 21Answers: 7

    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.

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

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

    Thanks,
    Allan

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    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

  • mmcnair80mmcnair80 Posts: 83Questions: 21Answers: 7

    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));
    }
    
  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394

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

  • mmcnair80mmcnair80 Posts: 83Questions: 21Answers: 7

    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?

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    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

  • mmcnair80mmcnair80 Posts: 83Questions: 21Answers: 7

    @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.

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    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

  • mmcnair80mmcnair80 Posts: 83Questions: 21Answers: 7

    @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.

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin
    Answer ✓

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

    Allan

  • mmcnair80mmcnair80 Posts: 83Questions: 21Answers: 7

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

  • mmcnair80mmcnair80 Posts: 83Questions: 21Answers: 7
    edited January 2017

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

This discussion has been closed.