What option do I need to set to get an Export button to export more than the 10 records on screen?

kwein1

When I click the Excel (export) button, it only exports the 10 records currently showing on the screen ("Showing 1 to 10 of 59 entries"). If I tell it to display 25 at a time, the Excel button will export 25. I'd like it to export all 59 records in this case. (I know I could select 100. That doesn't help me when I have 100s or 1000s of records).
Here's my messy code. The only button I'm trying to fix at the moment is the excelHtml5 one. You can see a couple of options I've tried to set, to no avail. The text of the button does change to "My Excel2".

Is it because of how I'm pulling it from the server?

$(document).ready(function() {
  var oTable = $('#tblMetadata').DataTable( {
        dom: 'lBfrtip',
        paging: true,
        ordering: true,
        info: true,
        stateSave: true,
        stateDuration: 0,
        scrollX: true,
        scrollCollapse: true,
        fixedColumns: true,
        buttons: [
            'copyHtml5',
            { 
                extend: 'excelHtml5',
                text: 'My Excel2',
                //bShowAll: true,
                exportOptions: {
                    columns: ':visible',
                    rows: { selected: true }
                    //rows: { selected: false }
                }
            },
            'csvHtml5',
            'pdfHtml5'
        ],
        processing: true,
        serverSide: true,
        ajax:{
                        // This is the site variable set in consoleMatrix
                        //url :"EID-grid-data.php?site=" + jsSite, // json datasource
                        url : jsGridQuery, // json datasource
                        type: "post",  // method  , by default get
                        error: function(){  // error handling
                            $(".tblMetadata-error").html("");
                            $("#tblMetadata").append('<tbody class="tblMetadata-error"><tr><th colspan="3">No data found in the server</th></tr></tbody>');
                            $("#tblMetadata_processing").css("display","none");
                        }
                    }
    } );
} );

function adjustTable() {
    oTable.fnAdjustColumnSizing();
};
function jsfixColumns() {
  var oTable = $('#tblMetadata').dataTable();
    oTable.fnAdjustColumnSizing();
}

aot2002

Why not just setup the export to call another external php page which exports to csv file using php?

kwein1

You mean roll my own button? I don't know how to export it to Excel.

kwein1

Just discovered "lengthMenu" or "aLengthMenu", which says it will let me use a -1 to select all records. If that worked, it would be the simplest answer to what I need. But I just get "No data found in the server" at the bottom of the set of records I was previously seeing.

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

but [10, 25, 50, 1000, 10000] works OK. I wonder why -1 doesn't?

jLinux

Since you're using the serverSide setting, DataTables has only rendered whats on the current page, and you can only export what DataTables has.

And per a post by the infamous @Allan in this thread..

serverSide: true

This is the issue. The file export is client-side, but if you are using server-side processing then the data available at the client-side is only that which is currently displayed (that is the whole point of server-side processing - only a limited sub-set is available at the client-side).

If you want to export the full data set, you would need to either:

1. Disable server-side processing, or
2. Have the server create the files for you.

And my reply to you is the same to the OP in that thread..

(In my opinion), if you're using the serverSide option, then overall, you're playing around with more rows than you would want to export.. I would think it would be best to play around with the lengthMenu, giving the viewers limits of how much they can export, then allow them to filter for the relevant data they want to export via search... EG: If they want to export all rows containing "Some Data", then set the page length to the highest you would let it go (100, 200?), then filter for "Some Data", and export that.

kwein1

"No data found in the server" is from the ajax:{ error: function(){
error handling. I looked, and I had a LIMIT statement that was using the -1 instead of a very high #. I just changed the code to use a very high # if there was a -1, and it works!

jLinux

Sure it will work.. Its retrieving all those rows, then exporting what it sees

jLinux

And im assuming that -1 doesnt work, because DataTables doesnt know how many rows to call on to render, since it doesnt have the data saved (like it does in all other data source types).. but thats just my guess.

kwein1

My code uses a LIMIT statement in the DB pull to decide which records to use. It gets the limits from the pagination - 1 to 10, then 11 to 20, etc. But it's set to -1 in the case of "Get all records", and I wasn't translating that into what SQL wanted (a very high number, or dropping the LIMIT statement altogether).

jLinux

Like I stated above, you should just provide some high limits in the page limits array, and let them export only those amounts. If you want to let them export everything, theres really no point in using serverSide at all.. If they even have the ability to export everything without freezing the browser, then you dont have enough records to justify using serverSide, just use ajax