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.
1 Answers
Answers 1
I hope this will help the others too.
- save text of selected officeId
- save text of selected departemenId
- save value from datatables search input
- use cusomize option for each button
- [easy because this is only text] for csvhtml5 we only need "\n" as ENTER new row, then add them before created dt CSV element
- [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]); 
0 comments:
Post a Comment