第一种导出excel无需自己设置，直接根据json生成 （缺点：json数据全部展示，且只能按获取数据的顺序显示）
//json数据转excel function JSONToExcelConvertor(JSONData, FileName) { //先转化json var arrData = typeof JSONData != 'object' ? JSON.parse(JSONData) : JSONData; var excel = '<table>'; var row = "<tr>"; //设置表头 var keys = Object.keys(JSONData[0]); keys.forEach(function (item) { row += "<td>" + item + '</td>'; }); //换行 excel += row + "</tr>"; //设置数据 for (var i = 0; i < arrData.length; i++) { var row = "<tr>"; for (var index in arrData[i]) { console.log(arrData[i][index]); //var value = arrData[i][index] === "." ? "" : arrData[i][index]; row += '<td>' + arrData[i][index] + '</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); }
第二种json排序，可手动设置数据显示(缺点：无法对列间距进行设置，生成的excel会挤在一起)
//导出访问路径Excel function exportPathMethod(data) { //要导出的json数据 var jsonData = []; for(var i=0; i<data.length ; 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数据中一些数据不想展示给用户时采取的措施（转载：https://blog.csdn.net/qq_34623560/article/details/79928248）
<html> <head> <meta http-equiv="content-type" content="text/html; charset=utf-8"> <script type="text/javascript" src="js/jquery-3.3.1.min.js" ></script> <script type="text/javascript" src="js/JSONToExcelConvertor.js" ></script> <script type="text/javascript"> $(document).ready(function(){ $('#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); }); }); </script> </head> <body> <input type="button" id="wwo" value="导出" /> </body> </html> function JSONToExcelConvertor(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); }
再次简化：终极简化导出excel（一万条数据可在10秒内导出）
//json数据转excel function JSONToOrderExcelConvertor(JSONData) { var str = '序号,订单号,订单时间,主要用途,客户名称,电话,产品型号,是否形成有效线索\n'; for(let i=0;i<JSONData.length;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
<script src="https://cuikangjie.github.io/JsonExportExcel/dist/JsonExportExcel.min.js"></script>
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 链接超时