前端使用js导出json数据为excel文件
最近在项目中需要用到将后端返回的json数据(部分在页面上以表格展示)导出为excel文件的功能。
查阅资料,找到了一种较为简便的方法,没有使用库,而是自己封装js函数。
1.新建exportExcel.js文件,封装导出函数exportExcel(JSONData, FileName, title, filter)
export function exportExcel(JSONData, FileName, title, filter) {
if (!JSONData) return;
//转化json为object
var arrData = typeof JSONData != "object" ? JSON.parse(JSONData) : JSONData;
var excel = "<table>";
//设置表头
var row = "<tr>";
if (title) { //使用标题项
for (var i in title) {
row += "<th align='center'>" + title[i] + "</th>";
}
} else {//不使用标题项
for (var i in arrData[0]) {
row += "<th align='center'>" + i + "</th>";
}
}
excel += row + "</tr>";
//设置数据
for (var i = 0; i < arrData.length; i++) {
var row = "<tr>";
for (var index in arrData[i]) {
//判断是否有过滤行
if (filter) {
if (filter.indexOf(index) == -1) {
var value = arrData[i][index] == null ? "" : arrData[i][index];
row += "<td>" + value + "</td>";
}
} else {
var value = arrData[i][index] == null ? "" : arrData[i][index];
row += "<td align='center'>" + 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>";
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);
}
2.使用exportExcel()函数导出excel文件
定义事件函数:
- 调用api函数从后端获取数据res.data
- 定义各个参数
- 调用exportExcel()函数,导出文件
handleDownload3 = async () => {
let data = [] //需要导出的数据
let title = [] //列名(即json单条数据的各个字段名)
let fillter = [] //不需要导出的列(字段)
let name = '测试文件' //导出的文件名
let params={}
let res = await api_getData(params)
data = res.data
for (let item in data[0]) {
title.push(item)
}
exportExcel(data, name, title, fillter)
}
给按钮添加点击事件:
<button onClick={() => this.handleDownload3()}>下载测试</button>
转自:原生js导出json数据