easyui前端框架,使用datagrid-export.js 导出 复杂表头 和 表格底部信息 的 excel 处理。我的项目中表头表尾的信息都是用div标签包裹的,主要是单据编号、日期、联系方式等信息。
主要处理方法是:
1、获取表头表尾部分html代码的文本内容,text()
2、将获取到的文本部分字符串转换为数组
3、将数组分别添加到td标签里面
// html代码部分
<div id="depotHeadDlgShow" class="easyui-dialog" style="padding:10px 20px;" fit="true" closed="true"
buttons="#dlg-buttons-show" modal="true" cache="false" collapsible="false" closable="true">
<div class="m-top">
<div class="title"><span>表格标题</span></div>
<div class="info">
// 这里是表头部分需要展示的信息
</div>
</div>
<table id="materialDataShow" width="100%"></table>
<div class="m-bottom">
<div class="info remark">
// 这里是需要整行显示的信息
<div><span>单据备注:<span id="RemarkShow"></span></span></div>
</div>
<div class="info">
// 这里是表尾需要展示的信息
</div>
</div>
</div>
<a href="javascript:void(0)" id="exportDepotHeadShow" class="easyui-linkbutton" iconCls="icon-excel">导出</a>
// js代码部分
//导出单据
$("#exportDepotHeadShow").off("click").on("click", function () {
var title = $('.m-top .title').text()
// 这里获取到的top和bottom是字符串,在用的时候需要进行处理,见datagrid-export.js部分
var top = $('.m-top .info').text()
var bottom = $('.m-bottom').text()
var listTitle = $('.m-top #defaultNumber').text()
var footer = $('#materialDataShow').datagrid('getFooterRows');
var url = '' // 接口地址
var data = '' // 接口参数
$.ajax({
type: "get",
url: url,
data: data,
dataType: "json",
success: function (res) {
if (res && res.code === 200) {
var rows = res.data.rows
var totalNum = 0
rows.map((item, index) => {
item.Index = index + 1
totalNum += item['OperNumber'];
})
// totalNum 为在表格底部新增的合计数,没有就不需要
footer[0].OperNumber = totalNum
$('#materialDataShow').datagrid('toExcel', {
filename: listTitle + '.xls',
rows: rows,
worksheet: 'Worksheet',
caption: title,
footer: footer,
top: top, // 新增的表头部分
bottom: bottom // 新增的表尾部分
});
}
}
})
})
datagrid-export.js 在easyui的扩展组件里面下载
然后在toHtml()部分进行处理,下面只贴出修改部分的代码
(function ($) {
function getRows(target) {
var state = $(target).data('datagrid');
if (state.filterSource) {
return state.filterSource.rows;
} else {
return state.data.rows;
}
}
function getFooterRows(target) {
var state = $(target).data('datagrid');
return state.data.footer || [];
}
function toHtml(target, rows, footer, caption, top, bottom) {
rows = rows || getRows(target);
rows = rows.concat(footer || getFooterRows(target));
var dg = $(target);
var data = ['<table border="1" rull="all" style="border-collapse:collapse">'];
var fields = dg.datagrid('getColumnFields', true).concat(dg.datagrid('getColumnFields', false));
var thStyle = 'height: 40px;font-weight:bold;text-align:center;font-size: 18px;';
var trStyle = 'height:32px';
var tdStyle0 = 'vertical-align:middle;padding:0 4px';
var mtdStyle = 'algin:left;vertical-align:middle;padding:0 4px';
if (caption) {
// 因为caption不好设样式,我修改为tr,设置colspan是因为我需要设置复杂表头表尾的页面的单元格固定是12个,也以通过fields.length直接获取
// data.push('<caption>' + caption + '</caption>');
data.push('<tr style="' + thStyle + '"><th style="' + thStyle + '" colspan="12">' + caption + '</th></tr>')
}
// 处理top部分数据,将top部分的字符串用换行符进行切割,并去除空的字符串,得到一个数组
if (top) {
var topArr = top.split(/[(\r\n)\r\n]+/); // 以换行符进行切割
topArr = topArr.filter(i => { return i && i.trim() }) // 去除空的字符串
for (var j = 0; j < topArr.length; j++) {
var str = ''
// 示条信息,然后根据fields.length和内容的长度觉得合并单元格的个数,注意不要漏掉<tr></tr>标签
if (j % 3 == 0) {
str = '<tr style="' + trStyle + '"><td colspan="4" style="' + mtdStyle + '">' + topArr[j] + '</td>'
} else if (j % 3 == 2) {
str = '<td colspan="4" style="' + mtdStyle + '">' + topArr[j] + '</td></tr>'
} else {
str = '<td colspan="4" style="' + mtdStyle + '">' + topArr[j] + '</td>'
}
data.push(str);
}
}
data.push('<tr style="' + trStyle + '">');
for (var i = 0; i < fields.length; i++) {
var col = dg.datagrid('getColumnOption', fields[i]);
var tdStyle = tdStyle0 + ';width:' + col.boxWidth + 'px;';
tdStyle += ';text-align:' + (col.halign || col.align || '');
data.push('<td style="' + tdStyle + '">' + col.title + '</td>');
}
data.push('</tr>');
$.map(rows, function (row) {
data.push('<tr style="' + trStyle + '">');
for (var i = 0; i < fields.length; i++) {
var field = fields[i];
var col = dg.datagrid('getColumnOption', field);
var value = row[field];
if (value == undefined) {
value = '';
}
var tdStyle = tdStyle0;
tdStyle += ';text-align:' + (col.align || '');
data.push(
'<td style="' + tdStyle + '">' + value + '</td>'
);
}
data.push('</tr>');
});
// 底部内容部分同表头部分一样处理,根据实际情况,决定一个tr里面td的个数
if (bottom) {
var bottomArr = bottom.split(/[(\r\n)\r\n]+/);
bottomArr = bottomArr.filter(i => { return i && i.trim() })
for (var j = 0; j < bottomArr.length; j++) {
var str = ''
if (bottomArr[j].indexOf('单据备注') > -1) {
str = '<tr style="' + trStyle + '"><td colspan="12" style="' + mtdStyle + '">' + bottomArr[j] + '</td></tr>'
} else {
if (j % 3 == 2) {
str = '<tr style="' + trStyle + '"><td colspan="4" style="' + mtdStyle + '">' + bottomArr[j] + '</td>'
} else if (j % 3 == 1) {
str = '<td colspan="4" style="' + mtdStyle + '">' + bottomArr[j] + '</td></tr>'
} else {
str = '<td colspan="4" style="' + mtdStyle + '">' + bottomArr[j] + '</td>'
}
}
data.push(str);
}
}
data.push('</table>');
return data.join('');
}
function b64toBlob(data) {
var sliceSize = 512;
var chars = atob(data);
var byteArrays = [];
for (var offset = 0; offset < chars.length; offset += sliceSize) {
var slice = chars.slice(offset, offset + sliceSize);
var byteNumbers = new Array(slice.length);
for (var i = 0; i < slice.length; i++) {
byteNumbers[i] = slice.charCodeAt(i);
}
var byteArray = new Uint8Array(byteNumbers);
byteArrays.push(byteArray);
}
return new Blob(byteArrays, {
type: ''
});
}
function toExcel(target, param) {
console.log(target, param, '导出')
var filename = null;
var rows = null;
var footer = null;
var caption = null;
var top = null;
var bottom = null;
var worksheet = 'Worksheet';
if (typeof param == 'string') {
filename = param;
} else {
filename = param['filename'];
rows = param['rows'];
footer = param['footer'];
caption = param['caption'];
top = param['top'];
bottom = param['bottom'];
worksheet = param['worksheet'] || 'Worksheet';
}
var dg = $(target);
var uri = 'data:application/vnd.ms-excel;base64,'
, template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><meta http-equiv="content-type" content="application/vnd.ms-excel; charset=UTF-8"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body>{table}</body></html>'
, base64 = function (s) { return window.btoa(unescape(encodeURIComponent(s))) }
, format = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }) }
// 这里调用时需要加上新增的top和bottom参数
var table = toHtml(target, rows, footer, caption, top, bottom);
var ctx = { worksheet: worksheet, table: table };
var data = base64(format(template, ctx));
if (window.navigator.msSaveBlob) {
var blob = b64toBlob(data);
window.navigator.msSaveBlob(blob, filename);
} else {
var alink = $('<a style="display:none"></a>').appendTo('body');
alink[0].href = uri + data;
alink[0].download = filename;
alink[0].click();
alink.remove();
}
}
$.extend($.fn.datagrid.methods, {
toHtml: function (jq, rows) {
return toHtml(jq[0], rows);
}
toExcel: function (jq, param) {
return jq.each(function () {
toExcel(this, param);
});
}
});
})(jQuery);