javascript | JSON导出Excel

#需求

最近后端返回了一段json对象数据,要求导出Excel,同时兼容IE8,这可让我郁闷了
查了一下大多数插件,都只是兼容到ie9,本贴代码已测试,兼容ie6-11
那下面就让我们来会会这狗屎一样的IE吧

#工具

使用了 FileSaver.js & Blob.js

#代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
(function(){
var EXCEL_CONTENTTYPE = "application/vnd.ms-excel;",
EXCEL_URI = 'data:application/vnd.ms-excel;base64,',
EXCE_TEMPLATE = '<html><head><meta charset="UTF-8"></head><body>{html}</body></html>',
__PREVFIX = "\uFEFF",
ieVersion = window.navigator.userAgent.toLowerCase().match(/(msie\s|trident.*rv:)([\w.]+)/),
useIE = ieVersion && ieVersion[2] < 10,
isIE1011 = ieVersion && ieVersion[2] > 9;

var Export = {
/*
*@param datas Two-dimensional array : datas, export only with data
or String : DOM id, export html content
*@param fileName export file name
*/
toExcel: function(datas, fName){
var isId = typeof datas === 'string';
if(isId || datas instanceof Array){
if(useIE || isId && isIE1011){
Export.__ieExport(datas);
} else{
Export.__oTherExport(datas, fName);
}
} else{
alert("datas params need Two-dimensional array or String.");
}
},
__ieExport : function(datas){

var oXL = new ActiveXObject("Excel.Application"),
oWB = oXL.Workbooks.Add(),
oSheet = oWB.ActiveSheet,
i = 0,
j;

if(typeof datas === 'string'){

var elem = document.getElementById(datas);
var sel = document.body.createTextRange();
sel.moveToElementText(elem);
try{
sel.select();
//there ie10、11 will be error, i don't know why, but also can export
} catch(e){}
sel.execCommand("Copy");
oSheet.Paste();
} else {
for(; i < datas.length; i++){
var row = datas[i];
for (j = 0; j < row.length; j++) {
oSheet.Cells(i + 1, j + 1).value = row[j];

}
}
}
oXL.Visible = true;
},
__oTherExport : function(datas, fileName){

if(typeof datas === 'string'){

var elem = document.getElementById(datas),
content = EXCE_TEMPLATE.replace("{html}", elem.outerHTML);
//TODO: need test large amount of data
window.location.href = EXCEL_URI +
window.btoa(unescape(encodeURIComponent(content)));
} else {
var blob,
i = 0,
j,
str = __PREVFIX;

for(; i < datas.length; i++){
var row = datas[i];
// the value add double quotation marks on both sides, for separate values.
str += "\""+ row.join("\",\"") + "\"\n";
}
//on safari: TypeError: '[object BlobConstructor]' is not a constructor (evaluating 'new Blob([str],{
//import Blob.js to fix, but still have a problem : the fileName will be 'Unknown' ,
//but if you add suffix name, content can be seen.
blob = new Blob([str],{
type: EXCEL_CONTENTTYPE
});
saveAs(blob, fileName || "Download.xls");
}
}
}

window.ExportUtil = Export;
})();

#调用

1
2
3
4
5
6
7
8
9
10
//数据方式
ExportUtil.toExcel([
["学号", "姓名", "年龄"],
["1", "haha", "19"],
["2", "heihei", "10"],
["3", "lolo", "23"]
],"test.xls");

//table方式
ExportUtil.toExcel("id"); //id为table的id

#问题

  • 由于没有safari的需求,safari兼容性请自测
  • 建议用数据方式导出,HTML标签方式效果不太好

参考 github