html转化excel为json对象,纯js前端json转exl的几种方法

第一种导出excel无需自己设置，直接根据json生成 (缺点：json数据全部展示，且只能按获取数据的顺序显示)

//json数据转excel

function JSONToExcelConvertor(JSONData, FileName) {

//先转化json

var arrData = typeof JSONData != 'object' ? JSON.parse(JSONData) : JSONData;

var excel = '

var row = "

";

//设置表头

var keys = Object.keys(JSONData[0]);

keys.forEach(function (item) {

row += "

" + item + '';

});

//换行

excel += row + "

";

//设置数据

for (var i = 0; i < arrData.length; i++) {

var row = "

";

for (var index in arrData[i]) {

console.log(arrData[i][index]);

//var value = arrData[i][index] === "." ? "" : arrData[i][index];

row += '

' + arrData[i][index] + '';

}

excel += row + "

";

}

excel += "

";

var excelFile = "";

excelFile += '';

excelFile += '';

excelFile += "

";

excelFile += "";

excelFile += "";

excelFile += "

";

excelFile += excel;

excelFile += "";

excelFile += "";

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);

}

第二种json排序，可手动设置数据显示(缺点：无法对列间距进行设置，生成的excel会挤在一起)

//导出访问路径Excel

function exportPathMethod(data) {

//要导出的json数据

var jsonData = [];

for(var i=0; i

jsonData.push({

index :i+1,

title: data[i].title,

url: data[i].url,

createTime :data[i].createTime

});

}

//列标题，逗号隔开，每一个逗号就是隔开一个单元格

let str = `序号,标题,地址,时间\n`; //增加\t为了不让表格显示科学计数法或者其他格式

for(let i = 0 ; i < jsonData.length ; i++ ){

for(let item in jsonData[i]){

str+=`${jsonData[i][item] + '\t'},`;

}

str+='\n'; }

//encodeURIComponent解决中文乱码

let uri = 'data:text/csv;charset=utf-8,\ufeff' + encodeURIComponent(str);

//通过创建a标签实现

var link = document.createElement("a"); link.href = uri; //对下载的文件命名

link.download = "json数据表.xls";

document.body.appendChild(link);

link.click();

}

第三种方法 是为了解决返回的json数据中一些数据不想展示给用户时采取的措施

$('#wwo').click(function(){ //测试的json数据

var data3=[{"id":10000,"username":"user-0","sex":"女","city":"城市-0","sign":"签名-0","experience":255,"logins":24},

{"id":10001,"username":"user-1","sex":"男","city":"城市-1","sign":"签名-1","experience":884,"logins":58} ,

{"id":10002,"username":"user-2","sex":"女","city":"城市-2","sign":"签名-2","experience":650,"logins":77}] //自定义标题栏

var title=['用户名','性别','城市','签名','经验'] //自定义过滤栏(不需要导出的行)

var filter=['id','logins'] //原始导出

JSONToExcelConvertor(data3,"report"); //自定义导出

//JSONToExcelConvertor(data3,"report",title,filter);

});

});

function JSONToExcelConvertor(JSONData, FileName,title,filter) {

if(!JSONData)

return;

//转化json为object

var arrData = typeof JSONData != 'object' ? JSON.parse(JSONData) : JSONData;

var excel = "

//设置表头

var row = "

";

if(title)

{

//使用标题项

for (var i in title) {

row += "

" + title[i] + '';

}

}

else{

//不使用标题项

for (var i in arrData[0]) {

row += "

" + i + '';

}

}

excel += row + "

";

//设置数据

for (var i = 0; i < arrData.length; i++) {

var row = "

";

for (var index in arrData[i]) {

//判断是否有过滤行

if(filter)

{

if(filter.indexOf(index)==-1) {

var value = arrData[i][index] == null ? "" : arrData[i][index];

row += '

' + value + '';

}

}

else

{

var value = arrData[i][index] == null ? "" : arrData[i][index];

row += "

" + value + "";

}

}

excel += row + "

";

}

excel += "

";

var excelFile = "";

excelFile += '';

excelFile += '';

excelFile += "

";

excelFile += "";

excelFile += "";

excelFile += "

";

excelFile += excel;

excelFile += "";

excelFile += "";

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(一万条数据可在10秒内导出)

//json数据转excel

function JSONToOrderExcelConvertor(JSONData) {

var str = '序号,订单号,订单时间,主要用途,客户名称,电话,产品型号,是否形成有效线索\n';

for(let i=0;i

var result =''; if (JSONData[i].orderStatusc=='0'){ result="是";

} else {

result="否";

}

str += (i+1).toString()+','+JSONData[i].orderId+'\t'+','+formateOrderTime(JSONData[i].orderTime)+'\t'+','+JSONData[i].p1+'\t'+','+JSONData[i].userName+'\t'+','+JSONData[i].recMobile+'\t'+','+JSONData[i].productName+'\t'+','+result+'\t'+',\n' }

var blob = new Blob([str], {type: "text/plain;charset=utf-8"}); //解决中文乱码问题

blob = new Blob([String.fromCharCode(0xFEFF), blob], {type: blob.type});

object_url = window.URL.createObjectURL(blob); var link = document.createElement("a"); link.href = object_url; link.download = "导出订单.xls";

document.body.appendChild(link);

link.click();

document.body.removeChild(link);

}

第四种、使用插件导出js

引入js

function JsonToExcel(jsonData,fileName,sheetName,sheetHeader) {

var option = {};

option.fileName = fileName;

option.datas = [

{

sheetData : jsonData,

sheetName : sheetName,

sheetHeader : sheetHeader

}

];

var toExcel=new ExportJsonExcel(option);

toExcel.saveExcel();

}

由于使用nginx ，数据量超过俩万条时，请求时间超出nginx要求的响应时间就会报504 链接超时