js中json转excel

87 阅读 0 评论 58 点赞

我是靠谱客的博主贤惠黑裤，这篇文章主要介绍js中json转excel，现在分享给大家，希望可以做个参考。

1.利用xlsx插件

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
var arrData = json.excel_lists;
var data= [];
//js对象转为数组
for (var i in arrData) {
var item = [];
for (var j in arrData[i]) item.push(arrData[i][j]);
data.push(item)
}
/* Render data to table */
var ws = XLSX.utils.aoa_to_sheet(data);
var wb=XLSX.utils.book_new();
wb.SheetNames.push("{=date('Ymd')}");
wb.Sheets["{=date('Ymd')}"] = ws;
return
XLSX.writeFile(wb, 'test_{=date('YmdHis')}.xlsx');

相关文章：
PHP配合SheetJS/js-xlsx导出Excel大量数据
在线小工具：JSON 转换 Excel

2.不引用插件

但是用这种方法下载的数据中，比如05123会被直接转换成5123，不是我所设想的结果。

复制代码

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
function JSONToExcelConvertor(JSONData, FileName, ShowLabel) {
//先转化json
var arrData = typeof JSONData != 'object' ? jQuery.parseJSON(JSONData) : JSONData;
var excel = '<table>';
//设置表头
var row = "<tr>";
for (var i = 0, l = ShowLabel.length; i < l; i++) {
row += "<td>" + ShowLabel[i] + '</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] === "." ? "" : arrData[i][index];
row += '<td>' + value + '</td>';
}
excel += row + "</tr>";
}
excel += "</table>";
alert(excel);
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><x:ExcelWorksheets><x:ExcelWorksheet>";
excelFile += "<x:Name>{worksheet}</x:Name>";
excelFile += "<x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions>";
excelFile += "</x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook>";
excelFile += "</xml>";
excelFile += "<![endif]-->";
excelFile += "</head>";
excelFile += "<body>"+excel+"</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);
}
JSONToExcelConvertor(json.excel_lists, 'test_{=date('YmdHis')}', json.title)