<html>
<head lang="en">
<meta charset="UTF-8">
<title>html 表格导出Excel</title>
</head>
<body>
<div >
<button onclick="method('tableExcel')">表格导出Excel</button>
<button onclick="jsonMethod()">json导出Excel</button>
</div>
<div >
<table >
<tr><td colspan="5" align="center">html 表格导出道Excel</td></tr>
<tr><td>列标题1</td><td>列标题2</td><td>类标题3</td><td>列标题4</td><td>列标题5</td></tr>
<tr><td>aaa</td><td>bbb</td><td>ccc</td><td>ddd</td><td>eee</td></tr>
<tr><td>AAA</td><td>BBB</td><td>CCC</td><td>DDD</td><td>EEE</td></tr>
<tr><td>FFF</td><td>GGG</td><td>HHH</td><td>III</td><td>JJJ</td></tr>
</table>
</div>
</body>
</html>
<script language="JavaScript" type="text/javascript">
//---------------------------将JSON导出Excel---------------------------//
//需要进行数据转换，
// 我们使用ajax请求到的数据
var data = [{name:"hjm100",duty:'管理员',opensource:'http://git.oschina.net/hjm100',aa:'无用数据'},
{name:"zmjie100",duty:'前端开发',opensource:'http://git.oschina.net/zmjie100',aa:'无用数据'},
{name:"ghf100",duty:'前端开发',opensource:'http://git.oschina.net/ghf100',aa:'无用数据'},
{name:"zhengshanshan",duty:'前端开发',opensource:'http://git.oschina.net/zhengshanshan',aa:'无用数据'}];
//数据处理
var title = [{"value":"名称",key:'name'},
{"value":"职务",key:'duty'},
{"value":"开源地址",key:'opensource'}];
/**dataSwitch函数参数
* @param datas 原始的json数据
* @param titles 自己设置的表头数据
* 注意：
* 1.必须设置titles
* 2.titles是过滤datas的必要条件
* 3.titles中的key属性对应的是需要提取的原始数据的key值
* 4.datas与titles需要相互对应
*/
//数据转换函数
function dataSwitch(datas,titles){
if(datas == ''|| titles=='') return;
var res = [],resd = [];
for (var j = 0; j < datas.length; j++) {
//清空一下res
res = []
for (var i = 0; i < titles.length; i++) {
res.push({"value":datas[j][titles[i].key]})
}
resd.push(res)
}
return (resd)
}
//JSONToExcelConvertor函数需要的数据格式
// var datas = {"title":[{"value":"店铺"},{"value":"点击量"},{"value":"竞争力"}],
// "data":[[{"value":"运营淘宝代",}, {"value":3057},{"value":955}],
// [{"value":"淘宝网",}, {"value":412},{"value":1860}]]
// };
//空壳函数用来做调用,与方便理解！！
function jsonMethod(){
//执行导出函数
JSONToExcelConvertor(dataSwitch(data,title), "鸿基梦", title,'鸿基梦组织重要成员信息汇总');
}
/**JSONToExcelConvertor函数参数
* @param JSONData 需要导出的数据
* @param FileName Excel表名
* @param ShowLabel 表头
* @param Explain 表格说明（选填）
* 注意：
* 上述参数都为转化后数据
*/
function JSONToExcelConvertor(JSONData, FileName, ShowLabel,Explain) {
//先转化json
var arrData = typeof JSONData != 'object' ? JSON.parse(JSONData) : JSONData;
var excel = '<table>',row = '';
//添加说明(如果想要添加说明请传入Explain参数)
if(Explain)row +="<tr><td colspan='"+ShowLabel.length+"' align='center'>"+Explain+"</td></tr>";
//设置表头
row += "<tr>";
for (var i = 0, l = ShowLabel.length; i < l; i++){
row += "<td>" + ShowLabel[i].value + '</td>';
}
//换行
excel += row + "</tr>";
//设置数据
for (var i = 0; i < arrData.length; i++){
var row = "<tr>";
for (var index in arrData[i]) {
var value = arrData[i][index].value === "." ? "" : arrData[i][index].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 += FileName;
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>";
var uri = 'data:application/vnd.ms-excel;charset=utf-8,' + encodeURIComponent(excelFile);
var link = document.createElement("a");
link.href = uri;
link.style = "visibility:hidden";
link.download = FileName + ".xls";
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
}
//---------------------------将表格导出Excel---------------------------//
var idTmr;
//判断浏览器
function getExplorer(){
var explorer = window.navigator.userAgent ;
//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';
}
function method(tableid){ //整个表格拷贝到EXCEL中
if(getExplorer()=='ie'){
var curTbl = document.getElementById(tableid),
oXL = new ActiveXObject("Excel.Application"),
//创建AX对象excel
oWB = oXL.Workbooks.Add(),
//获取workbook对象
xlsheet = oWB.Worksheets(1),
//激活当前sheet
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("Excel.xls", "Excel Spreadsheets (*.xls), *.xls");
} catch (e) {
print("Nested catch caught " + e);
} finally {
oWB.SaveAs(fname);
oWB.Close(savechanges = false);
oXL.Quit();
oXL = null;
idTmr = window.setInterval("Cleanup();", 1);
}
}else{
tableToExcel(tableid)
}
oXL.worksheets(1).Paste;
}
//兼容ie之外浏览器
function Cleanup() {
window.clearInterval(idTmr);
CollectGarbage();
}
var tableToExcel = (function() {
var uri = 'data:application/vnd.ms-excel;base64,',
template = '<html><head><meta charset="UTF-8"></head><body><table>{table}</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]; })
};
return function(table, name) {
if(!table.nodeType) table = document.getElementById(table)
var ctx = {worksheet: name || 'Worksheet', table: table.innerHTML}
window.location.href = uri + base64(format(template, ctx))
}
})()
</script>
