看標(biāo)題你一定會(huì)覺得很奇怪,js導(dǎo)出excel?直接導(dǎo)出頁(yè)面里的table這不很簡(jiǎn)單嗎递惋?
但是柔滔。。萍虽。睛廊。。杉编。有一些老ui框架的表格沒有導(dǎo)出功能而且dom不是純table構(gòu)成的超全。。邓馒。所以你懂的嘶朱。。光酣。
然后疏遏,這個(gè)前端js功能好寫是好寫,但是讓我考慮兼容性救军?抱歉财异,我覺得不行。
因?yàn)閖son數(shù)據(jù)和頁(yè)面上的表格可能會(huì)順序不一樣唱遭,所以我提供了重置數(shù)據(jù)順序的功能戳寸,就是要讓你按我的數(shù)據(jù)格式傳個(gè)參數(shù)
別亂傳參哦,因?yàn)闆]有判定胆萧,所以代碼的健壯性不好庆揩,可能完全拋不出錯(cuò)誤
html 代碼
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8">
</head>
<body>
<b id = "title" >統(tǒng)計(jì)</b>
<input id="btnExport" type="button" class="buttonEx" value="導(dǎo)出統(tǒng)計(jì)" />
<script src="http://libs.baidu.com/jquery/2.0.0/jquery.min.js"></script>
<script type="text/javascript">
//js導(dǎo)出excel處理
var exportExcel = {
idTmr: '',
fileName: '', //文件名
tableId: '', //生成table的id
title: [], //table的大標(biāo)題(占幾列的那種)
dataPre: [], //拿到的json數(shù)據(jù)
dataAfter: [], //處理后的json數(shù)據(jù)
dataNameSort: [], //決定table的小標(biāo)題和的標(biāo)題順序
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 = [];
//如果后臺(tái)傳的數(shù)據(jù)為空俐东,則退出
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;
//先轉(zhuǎn)化json
var arrData = typeof JSONData != 'object' ? JSON.parse(JSONData) : JSONData;
var excel = '<table id=' + tableId + ' style="display:none">';
//設(shè)置表頭
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>";
//設(shè)置數(shù)據(jù)
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("要生成該報(bào)表,必須將瀏覽器須設(shè)置為可使用“ActiveX控件”订晌。IE瀏覽器->工具->Internet選項(xiàng)->安全->自定義級(jí)別->設(shè)置->“對(duì)未標(biāo)記為可安全執(zhí)行腳本的ActiveX控件初始化并執(zhí)行腳本”設(shè)置為啟用虏辫,確定即可。如有疑問锈拨,請(qǐng)點(diǎn)擊瀏覽器的“幫助”了解瀏覽器設(shè)置方法砌庄!");
}
//創(chuàng)建AX對(duì)象excel
var oWB = oXL.Workbooks.Add();
//獲取workbook對(duì)象
var xlsheet = oWB.Worksheets(1);
//激活當(dāng)前sheet
var sel = document.body.createTextRange();
sel.moveToElementText(curTbl);
//把表格中的內(nèi)容移到TextRange中
sel.select;
//全選TextRange中內(nèi)容
sel.execCommand("Copy");
//復(fù)制TextRange中內(nèi)容
xlsheet.Paste();
//粘貼到活動(dòng)的EXCEL中
oXL.Visible = true;
//設(shè)置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;
//結(jié)束excel進(jìn)程,退出完成
//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導(dǎo)出
$('body').on('click', '#btnExport', function () {
//文件名
var fileName = $('#pageFooter_title').html() ? $('#pageFooter_title').html() : '統(tǒng)計(jì)表';
var tableId = 'ta';
var title = [
{ "value": "大標(biāo)題",colSpan: 2, "type": "ROW_HEADER_HEADER", "datatype": "string" }
];
var dataNameSort = [
{
'corporation':'一級(jí)標(biāo)題集團(tuán)',
'time':'一級(jí)標(biāo)題日期'
},
{
'corporation':'二級(jí)標(biāo)題集團(tuán)',
'time':'二級(jí)標(biāo)題日期'
}
];
var dataPre = [
{
'corporation':'太極集團(tuán)',
'time':'2012-5-6'
},
{
'corporation':'大極集團(tuán)',
'time':'2017-4-7'
},
{
'corporation':'小極集團(tuán)',
'time':'2016-4-7'
}
];
//exportExcel.start(文件名, 生成table的id, table的大標(biāo)題奕枢,table的小標(biāo)題, 拿到的json數(shù)據(jù));
exportExcel.start(fileName, tableId,title, dataNameSort, dataPre);
})
</script>
</body>
</html>