CSV File Export Without Loading All Pages

Dionatan

I would like to know if there is a way to export a CSV file of everything present in a paginated table using the Buttons extension, but without loading all the pages.

allan

I'm going to assume you are using server-side processing? Client-side processing will export all rows of the table, hence my assumption.

Based on that, please see this FAQ:

Q. How can I export the whole table when using serverSide and Buttons.

Allan

Dionatan

The processing is client-side. When the user queries a table with, for example, 40,000 records, pagination limits the display to, say, 50 records per page. However, I want the 'Export to .csv' button to export all 40,000 records at once, not just the 50 displayed on the current page.

kthorngren

Please post your Datatables init code. This example shows that by default the export buttons will export all rows in the table when using client side processing. There is something specific to your page that is limiting this.

Kevin

allan

If you could post a link to a test case as well, that would be great.

Thanks,
Allan

Dionatan

        <script>

        autoloadPainel('datatable');

        $(document).ready(function() {
            var saved = localStorage.getItem('DataTables_fila_cs_table_' + window.location.pathname) != null;
            var dataTable = $('#fila_cs_table').DataTable( {
                processing: true,
                serverSide: true,
                paging: true,
                searching: {{ $pesquisa }},
                responsive: {{ $responsivo }},
                ordering: {{ $ordenacao }},
                aaSorting: [],
                lengthMenu: [
                    [10, 25, 50, 100, 200, -1],
                    [10, 25, 50, 100, 200, "Tudo"]
                ],
                ajax: {
                    url: "{{ route('cs.listagem.dados', [$fila->codigo, $token]) }}",
                    type: "POST",
                    data: {
                        token: "{{ $token }}",
                        saved: saved
                    }
                },
                @if ($exportacao == 'true')
                buttons: [
                    {extend: 'copyHtml5'},
                    {extend: 'csvHtml5', fieldSeparator: ';', extension: '.csv', filename: "Nome da fila", text: "Exportar para .CSV"}
                ],
                @else
                buttons: [],
                @endif
                columns: [
                    @foreach ($cabecalho as $coluna )
                        {
                            name: "{!! $coluna !!}",
                            searchable: false,
                            ordenable: true,
                            data: {
                                _: "{!! $coluna !!}",
                                sort: "{!! isset($ordens[$coluna]) ? $ordens[$coluna] : $coluna !!}"
                            },
                        },
                    @endforeach
                ],
                language: {
                    url: "{{ url('Portuguese-Brasil.json?1') }}",
                    decimal: ",",
                    thousands: "."
                },
                dom: `{!! $layout_dataTables !!}`,
                initComplete: () => {
                    var dataTableAPI = $('#fila_cs_table').dataTable().api();

                    // Ajuste para somente buscar se digitar mais de 3 caracteres
                    $(".dataTables_filter input")
                    .attr('placeholder', 'Mínimo 3 caracteres...')
                    .off()
                    .on("input", function(e) {
                        if (this.value.length >= 3 || e.keyCode == 13) { // Tem 3 caracteres ou apertou enter
                            dataTableAPI.search(this.value).draw();
                        }

                        if (this.value === "") {
                            dataTableAPI.search("").draw();
                        }

                        return;
                    });

                    if (typeof cs_callback_DataTables != "undefined")
                        cs_callback_DataTables();
                },
                bStateSave: true,
                // Verificar dados salvos
                stateSaveCallback: function(settings,data) {
                    data.saved = true;
                    localStorage.setItem( 'DataTables_' + settings.sInstance + '_' + window.location.pathname, JSON.stringify(data) );
                },
                stateLoadCallback: function(settings) {
                    return JSON.parse( localStorage.getItem( 'DataTables_' + settings.sInstance + '_' + window.location.pathname ) );
                }
            });
        });
        </script>

kthorngren

You have serverSide: true, which enables Server side processing disabling client side processing. The only rows available to export are the rows displayed on the page. See the FAQ Allan linked to for for options.

Kevin

Dionatan

A possible solution to export everything while keeping serverSide: True and respecting filters applied in the datatable, it works for the buttons: copy, excel, csv, pdf, print.

{extend: 'csvHtml5', fieldSeparator: ';', extension: '.csv', filename: "Nome da fila", text: "Exportar para .CSV", "action": newexportaction}

function newexportaction(e, dt, button, config) {
        var self = this;
        var oldStart = dt.settings()[0]._iDisplayStart;
        dt.one('preXhr', function (e, s, data) {
            data.start = 0;
            data.length = 2147483647;
            dt.one('preDraw', function (e, settings) {
                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) {
                    settings._iDisplayStart = oldStart;  // Restaura o índice de início
                    data.start = oldStart;  // Restaura o valor de 'start' da consulta
                });
                setTimeout(dt.ajax.reload, 0);
                return false;
            });
        });
        dt.ajax.reload();
    }

allan

Sure, but that's downloading all information to the client-side. So the only thing server-side processing is doing for you is adding network latency. If you know your client is going to export all rows, and you are going to use a client-side export, then use client-side processing.

If you want the chunking benefits of server-side processing and you need CSV export, then you need to do the file building at the server-side.

Allan