我使用Jquery数据表将搜索结果导出到EXCEL和CSV,如下所示:
EmployeeList.html
| Employee Id | Name | Department | Joined date |
|---|
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()
);
}
});
此处,只有数据表行将导出到excel/csv.
我还要求将搜索选项(办公室和部门)导出到excel/csv.
如何将搜索选项也导出到excel/csv.
我希望这也能帮助其他人.
保存所选officeId的文本
保存所选离开的文本
从数据表搜索输入中保存值
为每个按钮使用cusomize选项
[容易因为这只是文本]对于csvhtml5我们只需要"\n"作为ENTER新行,然后在创建dt CSV元素之前添加它们
[很难,因为这是OFFICE XML]对于excelhtml5我们需要在创建dt XML元素之前添加OFFICE XML.难点是我们首先需要知道什么是OFFICE XML以及如何手动创建OFFICE XML
我们走了
更换
buttons: [
{
extend: 'excelHtml5',
filename:'EmployeeList'
},
{
extend: 'csvHtml5',
filename:'EmployeeList'
}
]
进入这个
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=''
for(i=0;i';
msg += '';
msg += ''+value+' ';
msg+= ' ';
msg+='';
}
msg += '
';
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;
}
}
]
演示:https://output.jsbin.com/teyupav
PASTEBIN:http://pastebin.com/ZGt61DCT
感谢来自datatables.net论坛的AugustLEE,J e Harms(成员)和Alan(网站管理员)
参考:
https://datatables.net/extensions/buttons/examples/initialisation/export.html
https://datatables.net/reference/button/excelHtml5
https://datatables.net/reference/api/buttons.exportData()
CSV导出
https://datatables.net/forums/discussion/38275
EXCELHTML5导出
https://datatables.net/forums/discussion/39707
https://datatables.net/forums/discussion/36045/excel-export-add-rows-and-data
更新1:修复safari和IE8下面的innerHTML问题
此修复程序是来自Raghul的相同数据表线索https://datatables.net//forums/discussion/comment/103911/#Comment_103911
更换
function Addrow(index,data) {
msg=''
for(i=0;i';
msg += '';
msg += ''+value+' ';
msg+= ' ';
msg+='';
}
msg += '
';
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]);
演示:https://output.jsbin.com/kevosub/
京公网安备 11010802040832号 | 京ICP备19059560号-6 