JSON导出excel,可导出外键,可排序
js文件JsonToExcel.js代码如下
/**
* @author qizichuan@outlook.com
* 2021年5月2日00:31:54
* 幸福是靠奋斗出来的
*/
function JSONToExcelConvertor(JSONData, FileName,title,content,state) {
//JSONData json数据
//FileName 导出excel名字
//title 标题,导出的字段名(编号,用户姓名)
//content 字段名,需要导出的字段名(userId,dept.deptId)
//state 将传入的值,0/1,不导出0/1,0导出否/1导出是
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>";
if(content){
//自定义字段
for(var con in content){
//json中的的字段
for (var index in arrData[i]) {
//state存在
if(state){
//1/是 0/否
if(state.indexOf(index)!=-1&&index==content[con]){
if(arrData[i][index]==1){
//console.log("state"+"="+index);
row += "<td style='text-align:center;'>" + '是' + '</td>';
}else{
//console.log("state"+"="+index);
row += "<td style='text-align:center;'>" + '否' + '</td>';
}
break;
//如user中的普通字段
}else if(index==content[con]){
//console.log("普通字段"+index+"======"+arrData[i][index]);
row += "<td style='text-align:center;mso-number-format:\@;'>" + arrData[i][index] + "</td>";
break;
//外键(假设传入dept.deptId,这里需要把它转换为[dept][deptId])
}else if(arrData[i][index]=='[object Object]'){
var dian=content[con].lastIndexOf(".");
var pojo=content[con].substring(0,dian);
if(pojo==index){
var dian=content[con].lastIndexOf(".");
var pojo=content[con].substring(0,dian);
var attribute=content[con].substring(dian+1,content[con].length);
//console.log("外键中的值"+index+"======+arrData[i][pojo][attribute])
row += "<td style='text-align:center;mso-number-format:\@;'>" + arrData[i][pojo][attribute] + "</td>";
break;
}
}
}
}
}
}else {
var value = arrData[i][index] == null ? "" : arrData[i][index];
//console.log("values="+value);
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);
}
html,jsp等web前段界面
JSONToExcelConvertor('json数据')/调用js自定义方法
success: function(res){
console.log(res.data);
//测试的json数据
//console.log(res);
//自定义标题栏
var title=['客户编号','客户名称','员工编号','员工姓名','员工电话','员工所属部门','员工职位','客户信息来源','客户所属行业','客户级别','联系人姓名','联系电话','联系手机','邮编','客户地址','创建时间'];
//自定义过滤栏(不需要导出的行)
var filter=['LAY_TABLE_INDEX','createUser'];
var state=[''];
var foreign=['userId','userName','userTel','userPosition','deptName'];
//原始导出
// JSONToExcelConvertor(data3,"reportss");
//自定义导出
JSONToExcelConvertor(res.data,"客户信息表",title,filter,foreign,state);
},
error:function(data) {
layer.msg('导出失败!',{icon:2,time:1500});
console.log("导出失败!");
}
});