Exporting to Excel does not export all rows

Exporting to Excel does not export all rows

SujitJSujitJ Posts: 19Questions: 8Answers: 0

Hello

Im using datatables for displaying data on the webpage. Have also added and Export button which would export the data in Excel format.
Currently im using search() (based on column no. 17) in the javascript to only show specific data in the datatables. On clicking export only the filtered data is being exported. I would want to export the entire data without any filter being applied.
1. in the view it should only show the rows where column 17 has a specific value 'abc' (Working)
2. While exporting, it should export entire data i.e column 17 should be having all values as without the search being applied. (Not Working)

Code Snippet:

var table = $('.tableContent').DataTable({
        "autoWidth": false,
        "pageLength": 50,
        dom : 'lBfrtip',
        buttons : [ {
            extend : 'excel',
            text : 'Export to Excel'
        } ],
        "columnDefs" : [{
            "targets" : [ 0, 1, 2, 8, 9, 10, 11,  17, 18 ],
            "visible" : true,
            "searchable" : true
        }, {
            "targets" : [ 3, 4, 5, 6, 7, 12, 13, 14, 15, 16, 19, 20, 21 ],
            "visible" : false,
            "searchable" : false
        }]
    });

    table.column(17).search('abc', false, false, true).draw();

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 54,921Questions: 1Answers: 8,611 Site admin
    Answer ✓

    Happy to take a look if you post a link to a page showing the issue please, per the forum rules.

    1. While exporting, it should export entire data i.e column 17 should be having all values as without the search being applied. (Not Working)

    This is correct and not a bug. The search will be applied automatically to the exported data so the exported data matches was the end user sees as the table state.

    If you want to change that us the modifier option of the buttons.exportData() method, which you can access using the exportOptions parameter for excelHtml5.

    Allan

  • SujitJSujitJ Posts: 19Questions: 8Answers: 0
    edited January 2017

    Allan,

    Thanks for your reply.

    I looked at the exportOptions and added the below, and it worked after a few attempts.

    buttons : [ {
                extend : 'excel',
                text : 'Export to Excel',
                exportOptions : {
                    modifier : {
                        // DataTables core
                        order : 'index',  // 'current', 'applied', 'index',  'original'
                        page : 'all',      // 'all',     'current'
                        search : 'none'     // 'none',    'applied', 'removed'
                    }
                }
            } ]
    

    Thanks for all your help.

This discussion has been closed.