看标题你一定会觉得很奇怪，js导出excel?直接导出页面里的table这不很简单吗？
但是。。。。。。有一些老ui框架的表格没有导出功能而且dom不是纯table构成的。。。所以你懂的。。。
然后，这个前端js功能好写是好写，但是让我考虑兼容性？抱歉，我觉得不行。
因为json数据和页面上的表格可能会顺序不一样，所以我提供了重置数据顺序的功能，就是要让你按我的数据格式传个参数
别乱传参哦，因为没有判定，所以代码的健壮性不好，可能完全抛不出错误
html 代码
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8">
</head>
<body>
<b id = "title" >统计</b>
<input id="btnExport" type="button" class="buttonEx" value="导出统计" />
<script src="http://libs.baidu.com/jquery/2.0.0/jquery.min.js"></script>
<script type="text/javascript">
//js导出excel处理
var exportExcel = {
idTmr: '',
fileName: '', //文件名
tableId: '', //生成table的id
title: [], //table的大标题（占几列的那种）
dataPre: [], //拿到的json数据
dataAfter: [], //处理后的json数据
dataNameSort: [], //决定table的小标题和的标题顺序
start: function (fileName, tableId, title,dataNameSort, dataPre) {
this.fileName = fileName;
this.tableId = tableId;
this.title=title;
this.dataNameSort = dataNameSort.reverse();
this.dataPre = dataPre;
this.dataSortSet();
},
dataSortSet: function () {
var that = this;
//重置dataAfter
that.dataAfter = [];
//如果后台传的数据为空，则退出
if ((this.title == '') || (this.dataPre == '') || (this.dataNameSort == ''))
{ return; }
//插入到最前面
$.each(this.dataNameSort, function (i, n) {
that.dataPre.unshift(n)
})
$.each(this.dataPre, function (i, n) {
var tempArr = [];
$.each(that.dataPre[0], function (j, k) {
var tempObj = { "value": n[j], "type": "ROW_HEADER" };
tempArr.push(tempObj);
});
that.dataAfter.push(tempArr)
})
//将插入的删除
$.each(this.dataNameSort, function (i, n) {
that.dataPre.splice(0, 1);
})
this.JSONToExcelConvertor(this.dataAfter, this.fileName, this.title, this.tableId);
},
JSONToExcelConvertor: function (JSONData, FileName, ShowLabel, tableId) {
var that = this;
//先转化json
var arrData = typeof JSONData != 'object' ? JSON.parse(JSONData) : JSONData;
var excel = '<table id=' + tableId + ' style="display:none">';
//设置表头
var row = "<tr>";
for (var i = 0, l = ShowLabel.length; i < l; i++) {
if (ShowLabel[i].colSpan) {
row += "<td colSpan=" + ShowLabel[i].colSpan + ">" + ShowLabel[i].value + '</td>';
} else {
row += "<td>" + ShowLabel[i].value + '</td>';
}
}
//换行
excel += row + "</tr>";
//设置数据
for (var i = 0; i < arrData.length; i++) {
var row = "<tr>";
for (var j = 0; j < arrData[i].length; j++) {
var value = arrData[i][j].value === "." ? "" : arrData[i][j].value;
row += '<td>' + value + '</td>';
}
excel += row + "</tr>";
}
excel += "</table>";
var excelFile = "<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'>";
excelFile += '<meta http-equiv="content-type" content="application/vnd.ms-excel; charset=UTF-8">';
excelFile += '<meta http-equiv="content-type" content="application/vnd.ms-excel';
excelFile += '; charset=UTF-8">';
excelFile += "<head>";
excelFile += "<!--[if gte mso 9]>";
excelFile += "<xml>";
excelFile += "<x:ExcelWorkbook>";
excelFile += "<x:ExcelWorksheets>";
excelFile += "<x:ExcelWorksheet>";
excelFile += "<x:Name>";
excelFile += "{worksheet}";
excelFile += "</x:Name>";
excelFile += "<x:WorksheetOptions>";
excelFile += "<x:DisplayGridlines/>";
excelFile += "</x:WorksheetOptions>";
excelFile += "</x:ExcelWorksheet>";
excelFile += "</x:ExcelWorksheets>";
excelFile += "</x:ExcelWorkbook>";
excelFile += "</xml>";
excelFile += "<![endif]-->";
excelFile += "</head>";
excelFile += "<body>";
excelFile += excel;
excelFile += "</body>";
excelFile += "</html>";
if (this.getExplorer() == 'ie') {
$('body').append(excel);
var curTbl = document.getElementById(tableId);
try {
var oXL = new ActiveXObject("Excel.Application");
}
catch (exp) {
alert("要生成该报表，必须将浏览器须设置为可使用“ActiveX控件”。IE浏览器->工具->Internet选项->安全->自定义级别->设置->“对未标记为可安全执行脚本的ActiveX控件初始化并执行脚本”设置为启用，确定即可。如有疑问，请点击浏览器的“帮助”了解浏览器设置方法！");
}
//创建AX对象excel
var oWB = oXL.Workbooks.Add();
//获取workbook对象
var xlsheet = oWB.Worksheets(1);
//激活当前sheet
var sel = document.body.createTextRange();
sel.moveToElementText(curTbl);
//把表格中的内容移到TextRange中
sel.select;
//全选TextRange中内容
sel.execCommand("Copy");
//复制TextRange中内容
xlsheet.Paste();
//粘贴到活动的EXCEL中
oXL.Visible = true;
//设置excel可见属性
try {
var fname = oXL.Application.GetSaveAsFilename(FileName, "Excel Spreadsheets (*.xls), *.xls");
} catch (e) {
print("Nested catch caught " + e);
} finally {
oWB.SaveAs(fname);
oWB.Close(savechanges = false);
//xls.visible = false;
oXL.Quit();
oXL = null;
//结束excel进程，退出完成
//window.setInterval("that.Cleanup();",1);
this.idTmr = window.setInterval(that.Cleanup, 1);
//移除excelDOM
$remove()
}
} else {
var uri = 'data:application/vnd.ms-excel;charset=utf-8,' + encodeURIComponent(excelFile);
var link = document.createElement("a");
link.href = uri;
link.id = 'xlsDownload';
link.style = "visibility:hidden";
link.download = FileName + ".xls";
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
}
},
getExplorer: function () {
var explorer = window.navigator.userAgent;
var rMsie = /(msie\s|trident.*rv:)([\w.]+)/;
var match = rMsie.exec(explorer.toLowerCase());
//ie 11
if (match != null) {
return 'ie';
}
//ie
if (explorer.indexOf("MSIE") >= 0) {
return 'ie';
}
//firefox
else if (explorer.indexOf("Firefox") >= 0) {
return 'Firefox';
}
//Chrome
else if (explorer.indexOf("Chrome") >= 0) {
return 'Chrome';
}
//Opera
else if (explorer.indexOf("Opera") >= 0) {
return 'Opera';
}
//Safari
else if (explorer.indexOf("Safari") >= 0) {
return 'Safari';
}
},
Cleanup: function () {
window.clearInterval(this.idTmr);
window.CollectGarbage();
}
}
//添加click事件excel导出
$('body').on('click', '#btnExport', function () {
//文件名
var fileName = $('#pageFooter_title').html() ? $('#pageFooter_title').html() : '统计表';
var tableId = 'ta';
var title = [
{ "value": "大标题",colSpan: 2, "type": "ROW_HEADER_HEADER", "datatype": "string" }
];
var dataNameSort = [
{
'corporation':'一级标题集团',
'time':'一级标题日期'
},
{
'corporation':'二级标题集团',
'time':'二级标题日期'
}
];
var dataPre = [
{
'corporation':'太极集团',
'time':'2012-5-6'
},
{
'corporation':'大极集团',
'time':'2017-4-7'
},
{
'corporation':'小极集团',
'time':'2016-4-7'
}
];
//exportExcel.start(文件名, 生成table的id, table的大标题，table的小标题, 拿到的json数据);
exportExcel.start(fileName, tableId,title, dataNameSort, dataPre);
})
</script>
</body>
</html>
网友评论