Export All from DataTables with Server Side processing?

Export All from DataTables with Server Side processing?

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

Export All from DataTables with Server Side Processing

Exporting a full dataset from a DataTable initialized with server-side processing is a common requirement, especially when dealing with large tables containing thousands of rows and numerous columns. The core challenge lies in decoupling the pagination logic from the export logic while preserving the current state of filters and sorting.

Standard DataTables buttons like excelHtml5 default to exporting only the visible rows. To export the entire dataset filtered by the user's current search, you must intercept the request before the server responds. This involves modifying the data payload sent to your backend to indicate that no pagination should be applied.

Intercepting the Export Request

The most robust way to handle this in DataTables is by extending the button configuration to manipulate the preXhr event. This event fires just before DataTables makes an HTTP request to your server. By setting the length parameter to -1, you instruct the server-side script (often using a library like Yajra's Laravel Datatables or similar) to ignore pagination and return every row that matches the current search and sort criteria.

Here is how you configure the button in DataTables:

{
    extend: 'excelHtml5',
    text: 'Export All',
    exportOptions: {
        modifier: { page: 'all' } // This ensures the view reflects all rows temporarily, but logic handles data
    },
    action: function (e, dt, button, config) {
        // Intercept the request to fetch all data
        var originalSettings = $.extend(true, {}, config);

        $(dt.table().node()).one('preXhr', function (e, settings, data) {
            // Override pagination length to fetch all rows
            data.length = -1;
        }).one('draw', function () {
            // Optional: Reset or handle the full draw event if needed
            // However, for Excel export, we usually want to bypass standard drawing logic 
            // and trigger the export action manually after getting the data.
        });

        // Trigger a new draw with the modified settings
        dt.draw();
    }
}

Alternatively, if your backend expects specific parameters, you can manipulate the exportOptions directly within the customize function of the button or by constructing a custom AJAX request that mirrors the DataTables logic but omits pagination limits.

Server-Side Processing and PHPExcel

When working with large datasets (e.g., 60,000+ rows), generating the Excel file client-side using JavaScript libraries like SheetJS can be resource-intensive and may cause browser hangs. A more performant approach is to generate the file on the server using a library like PHPEXL or PHPExcel.

To achieve this, you cannot simply redirect the user to a new page with a link; you must intercept the export action and trigger a direct download from your server. This typically involves creating a separate endpoint (e.g., ExportAllToExcel.php) that accepts POST parameters containing the search term, sort order, and column filters.

Your custom button's action function should handle this redirection or AJAX call:

{
    text: 'Export All to Excel',
    action: function (e, dt, button, config) {
        // Get current search and sort parameters
        var search = dt.search();
        var order = dt.order();
        
        // Construct the URL for your server-side export script
        var url = '/export-all-excel'; 
        
        // Pass the necessary state via POST or GET depending on size limits
        // For very large datasets, POST is safer to avoid URL length limits
        $.ajax({
            url: url,
            type: 'POST',
            data: {
                search: search,
                order: JSON.stringify(order),
                columns: dt.columns().visible().map(function(i) { return i; }) // Visible columns only if needed
            },
            success: function (response) {
                // The server should trigger a download header here
                // This example assumes the server sends a file response
                window.location.href = '/export-all-excel?download=true'; 
                // Or handle the Blob directly if the backend returns JSON with file data
            }
        });
    }
}

Trade-offs and Considerations

Generating Excel files on the server introduces specific trade-offs. First, you must ensure your server can handle the memory load of processing 60,000+ rows with 65+ columns simultaneously. PHPExcel is known to be memory-intensive; for such large datasets, libraries like PhpSpreadsheet (the modern successor) are often preferred as they offer better memory management and streaming capabilities.

Secondly, transferring a file via POST parameters in AJAX can hit size limits depending on your web server configuration (php_post_max_size). If the dataset is massive, it is often more efficient to return the Excel file directly from the endpoint rather than passing data back and forth. The browser will automatically prompt the user to download the file if the response headers are set correctly (e.g., Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet).

Finally, formatting is key to why you might choose Excel over CSV. While CSV preserves data structure, it lacks styling, merged cells, and specific fonts. If your business logic requires these visual elements, the server-side generation path is the only viable option for preserving them across thousands of rows.

Our network