How to export search criteria to excel/csv in Jquery Datatable

Question

I have used Jquery data table for exporting search result to EXCEL and CSV as shown below :

EmployeeList.html

    <form name="officeForm" id="officeForm" method="post" action="EmployeeList.action">
            <div class="pull-right">
                    <button class="btn btn-primary-outline btn-sm" type="submit">Search</button> 
            </div>   
            <table class="table table-form">
            <tbody>
                <tr> 
                    <td class="control-label">Office</td>
                    <td>
                        <select id="officeId" name="office">
                            <option value="0">ALL</option>
                            <option value="108">Bangalore</option>
                            <option value="109">Mumbai</option>
                            <option value="110">Pune</option>
                                                    </select>
                                  </td>
                              </tr>
                  <tr> 
                    <td class="control-label">Department</td>
                    <td>
                        <select id="departmentId" name="department">
                            <option value="0">ALL</option>
                            <option value="118">IT</option>
                            <option value="119">HR</option>
                            <option value="120">Operations</option>
</select>
                              </td>
                     </tr>
                  </tbody>
             </table> 

     </form>
     <div class="content-wrapper">
            <table class="table table-hover" id="employee-grid" >
                <thead>
                    <tr>
                        <th>Employee Id</th>
                        <th>Name</th>
                        <th>Department</th>
                        <th>Joined date</th>
                    </tr>
                </thead>
            </table>
    </div>

Employee.js

var dt = $("#employee-grid").DataTable({
            "scrollY": "500px","scrollCollapse":true,"paging":false,"bSortCellsTop": true,
            data : [],
            "columns" : [{"data":"Id"}, {"data":"name"}, {"data":"department"},{"data":"joinedDate"}]
         }); 


$("#officeForm").submit(function(event){
    event.preventDefault();
    var $form = $(this);
    data = $form.serializeArray();
    url = $form.attr("action");

    var posting = $.post(url,data);
    posting.done(function(dataset){
        dt.clear();
        dt.rows.add(dataset.searchResults.EMPLOYEE_LIST).draw();

                if(dataset.searchResults.EMPLOYEE_LIST != null && dataset.searchResults.EMPLOYEE_LIST.length != 0) 
                {
                    new $.fn.dataTable.Buttons( dt, {
                        buttons: [
                           {
                                extend: 'excelHtml5',
                                filename:'EmployeeList'
                           },    
                           {
                                extend: 'csvHtml5',
                                filename:'EmployeeList'
                           }
                        ]
                    });

                    dt.buttons( 0, null ).container().prependTo(
                            dt.table().container()
                    );
                }

});

Here, only the data table rows will be exported to excel/csv.
I have a requirement to export search options(office and department) also to excel/csv.
How to export search options also to excel/csv.

Answer 1

I hope this will help the others too.

1. save text of selected officeId
2. save text of selected departemenId
3. save value from datatables search input
4. use cusomize option for each button
5. [easy because this is only text] for csvhtml5 we only need "\n" as ENTER new row, then add them before created dt CSV element
6. [hard because this is OFFICE XML] for excelhtml5 we need to add OFFICE XML before created dt XML element. The hard point is we need to know first what is OFFICE XML and how to create OFFICE XML manually

here we goes

replace

buttons: [
   {
        extend: 'excelHtml5',
        filename:'EmployeeList'
   },    
   {
        extend: 'csvHtml5',
        filename:'EmployeeList'
   }
]

into this

buttons: [ 
            {
                extend: 'csvHtml5',
                filename:'EmployeeList',
                customize: function( csv ) {
                    var office = $('#officeId :selected').text();
                    var department = $('#officeId :selected').text();
                    var search = $('.dataTables_filter input').val();
                    return "Office: "+ office +"\n"+"Department: "+department+"\n"+"Search Keyword: "+search+"\n\n"+  csv;
                }
            },
            {
                extend: 'excelHtml5',
                filename:'EmployeeList',
                customize: function( xlsx ) {
                    var office = $('#officeId :selected').text();
                    var department = $('#officeId :selected').text();
                    var search = $('.dataTables_filter input').val();
                    var search = $('.dataTables_filter input').val();
                    var sheet = xlsx.xl.worksheets['sheet1.xml'];
                    var downrows = 4; //number of rows for heading
                    var clRow = $('row', sheet);
                    //update Row
                    clRow.each(function () {
                        var attr = $(this).attr('r');
                        var ind = parseInt(attr);
                        ind = ind + downrows;
                        $(this).attr("r",ind);
                    });

                    // Update  row > c
                    $('row c ', sheet).each(function () {
                        var attr = $(this).attr('r');
                        var pre = attr.substring(0, 1);
                        var ind = parseInt(attr.substring(1, attr.length));
                        ind = ind + downrows;
                        $(this).attr("r", pre + ind);
                    });

                    function Addrow(index,data) {
                        msg='<row r="'+index+'">'
                        for(i=0;i<data.length;i++){
                            var key=data[i].k;
                            var value=data[i].v;
                            msg += '<c t="inlineStr" r="' + key + index + '" s="0">';
                            msg += '<is>';
                            msg +=  '<t>'+value+'</t>';
                            msg+=  '</is>';
                            msg+='</c>';
                        }
                        msg += '</row>';
                        return msg;
                    }

                    //insert
                    var newline = Addrow(1, [{ k: 'A', v: 'Office: ' + office}]);
                    newline += Addrow(2, [{ k: 'A', v: 'Department: ' + department}]);
                    newline += Addrow(3, [{ k: 'A', v: 'Search Keyword: ' + search}]);

                    sheet.childNodes[0].childNodes[1].innerHTML = newline + sheet.childNodes[0].childNodes[1].innerHTML;
                }
            }
         ]    

DEMO : https://output.jsbin.com/teyupav

PASTEBIN : http://pastebin.com/ZGt61DCT

Thanks to AugustLEE, J e Harms (member) and Alan (site admin) from datatables.net forum

REFERENCE:

https://datatables.net/extensions/buttons/examples/initialisation/export.html

https://datatables.net/reference/button/excelHtml5

https://datatables.net/reference/api/buttons.exportData()

CSV export

https://datatables.net/forums/discussion/38275

EXCELHTML5 export

https://datatables.net/forums/discussion/39707

https://datatables.net/forums/discussion/36045/excel-export-add-rows-and-data

UPDATE 1: Fix innerHTML problem from safari and IE8 below

this fix is reference from Raghul in same datatables thread https://datatables.net//forums/discussion/comment/103911/#Comment_103911

REPLACE

function Addrow(index,data) {
        msg='<row r="'+index+'">'
        for(i=0;i<data.length;i++){
            var key=data[i].k;
            var value=data[i].v;
            msg += '<c t="inlineStr" r="' + key + index + '" s="0">';
            msg += '<is>';
            msg +=  '<t>'+value+'</t>';
            msg+=  '</is>';
            msg+='</c>';
        }
        msg += '</row>';
        return msg;
    }


                    //insert
                    var newline = Addrow(1, [{ k: 'A', v: 'Office: ' + office}]);
                    newline += Addrow(2, [{ k: 'A', v: 'Department: ' + department}]);
                    newline += Addrow(3, [{ k: 'A', v: 'Search Keyword: ' + search}]);

                    sheet.childNodes[0].childNodes[1].innerHTML = newline + sheet.childNodes[0].childNodes[1].innerHTML;

INTO

function Addrow(index, data) {
                    var row = sheet.createElement('row');
                    row.setAttribute("r", index);              
                       for (i = 0; i < data.length; i++) {
                           var key = data[i].key;
                           var value = data[i].value;

                           var c  = sheet.createElement('c');
                           c.setAttribute("t", "inlineStr");
                           c.setAttribute("s", "0"); 
                           c.setAttribute("r", key + index);

                           var is = sheet.createElement('is');
                           var t = sheet.createElement('t');
                           var text = sheet.createTextNode(value)

                           t.appendChild(text);                                      
                           is.appendChild(t);
                           c.appendChild(is);

                           row.appendChild(c);                                                                                                                         
                       }

                       return row;
                   }

                    var r1 = Addrow(1, [{ key: 'A', value: 'Office: ' + office }]);
                    var r2 = Addrow(2, [{ key: 'A', value: 'Department: ' + department }]);                          
                    var r3 = Addrow(3, [{ key: 'A', value: 'Search Keyword: ' + search }]);
                    var r4 = Addrow(4, [{ key: 'A', value: '' }]);            

                    var sheetData = sheet.getElementsByTagName('sheetData')[0];

                    sheetData.insertBefore(r4,sheetData.childNodes[0]);
                    sheetData.insertBefore(r3,sheetData.childNodes[0]);
                    sheetData.insertBefore(r2,sheetData.childNodes[0]);
                    sheetData.insertBefore(r1,sheetData.childNodes[0]);

DEMO: https://output.jsbin.com/kevosub/