工具js:
//url 导出数据的获取地址 var url="config/getexportServerData.action"; //parameter 导出数据的筛选条件 '{serverInfo.role:role,serverInfo.ip:ip}' //fileName 自定义导出文件名 '服务器配置' //header 自定义表格标题 //headerKey 设置表格标题对应的导出数据中的key值 //serverPage.js 有例子 //messager 确认导出的提示语 function exportExcel(url,parameter,fileName,header,headerKey,ifHandleValue,messager){ $.messager.confirm('系统提示', messager, function(r){ if (r) { $.ajax({ url:url, type:"POST", data:{parameter}, timeout:180000, success:function(data){ if(data){ // 先转化json var arrData = JSON.parse(data); JSONToExcelConvertor(arrData, fileName, header,headerKey,ifHandleValue) } } }); } }); } function exportRowsExcel(url,parameter,fileName,header,headerKey,ifHandleValue,messager){ $.messager.confirm('系统提示', messager, function(r){ if (r) { $.ajax({ url:url, type:"POST", data:{parameter}, timeout:180000, success:function(data){ if(data){ // 先转化json var arrData = JSON.parse(data); JSONToExcelConvertor(arrData.rows, fileName, header,headerKey,ifHandleValue) } } }); } }); } /** * JSONData json字符串List<Map> * fileName 自定义导出文件名 '服务器配置'var fileName="服务器配置"; * header 自定义表格标题 var header=new Array("IP","服务器名","角色","监控模版","监控状态","邮件","创建业务","机柜","地区","备注"); * headerKey 设置表格标题对应的导出数据中的key值 var headerKey=new Array("ip","machinename","rolename","template_name","mstatus","isemail","isbusiness","cabinet","region","remark"); * ifHandleValue 是否需要处理表格内数据 0 不需要，1 需要(需要自定义handleValue(headerKey,value)函数设置自定义表格内容) */ function JSONToExcelConvertor(arrData, FileName, header,headerKey,ifHandleValue) { var excel = '<table>'; // 设置表头 var row = "<tr>"; for (var i = 0, l = header.length; i < l; i++) { row += "<td>" + header[i] + '</td>'; } // 换行 excel += row + "</tr>"; // 设置数据 for (var i = 0; i < arrData.length; i++) { var row = "<tr>"; for (var j = 0; j < headerKey.length; j++) { var key=headerKey[j]; var value = (arrData[i][key]=== ""||null === arrData[i][key]||undefined === arrData[i][key]) ? "" : arrData[i][key]; if(ifHandleValue==1){ //key 为自定义表格标题对应的key值，value值为当前要处理的数据 value = handleValue(key,value); } row += '<td>' + 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); } //数据量大时用这个快 function exportCSV(arrData, fileName, header,headerKey){ var str = header + '\n'; for(var i = 0; i < arrData.length; i++){ for (var j = 0; j < headerKey.length; j++) { var key = headerKey[j]; if(key == 'createtime'){//解决日期问题 导出时间格式为yyyy-MM-dd HH:mm:ss str += '\t'; str += arrData[i][key] + '\t,'; }else{ str += arrData[i][key] + ','; } } str += '\n'; // str += i.toString()+',1234567890123456789\t,张三李四王五赵六,bbbb,\n' } // var blob = new Blob([str], {type: "text/plain;charset=utf-8"}); //text var blob = new Blob([str], {type: "application/vnd.ms-excel;charset=utf-8"}); //csv //解决中文乱码问题 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 = fileName; document.body.appendChild(link); link.click(); document.body.removeChild(link); }
调用1：
//导出Excel function exportServerData(){ var ip = $("#ip").val().trim(); var deviceType = $("#deviceType").combobox('getValue'); var device = $("#device").combobox('getValue'); var alarmLevel = $("#alarmLevel").combobox('getValue'); var starttime = $("#starttime").datetimebox("getValue"); var endtime = $("#endtime").datetimebox("getValue"); if(starttime == ""){ starttime = getNowFormatDate1(); } if(ip.trim().length == 0){ ip = ''; } if(alarmLevel == -1){ alarmLevel = ''; } if(device.trim() == "所有的"){ device = ''; } var url="report/getAlarmCensusList.action"; var fileName="报警查询统计"; $.messager.confirm('系统提示', "您确定要导出表单数据吗？", function(r){ if (r) { $.ajax({ url:url, type:"POST", data:{"ip":ip,'deviceType':deviceType,'device':device,'alarmLevel':alarmLevel, 'starttime':starttime,'endtime':endtime}, timeout:180000, success:function(data){ if(data){ var arrData = JSON.parse(data); var header=new Array("服务器名","IP","监控类型 ","监控项 ","报警方式 ","报警值 ","备注 ","报警时间 "); var headerKey=new Array("machinename","ip","dtname","dname","alarm_level","alarm_value","remark","alarm_time"); JSONToExcelConvertor(arrData.rows, fileName, header,headerKey,1) } } }); } }); } function handleValue(headerKey,value){ if(headerKey === "alarm_level"){ if(value === "1"){ value="预警"; }else if(value === "2"){ value = "告警"; } } return value; }
调用2：
// 导出Excel function exportData() { var ip = $("#hostIp").textbox("getValue").trim(); if (ip == '') { alert("请输入IP！"); return; } var start = $("#start").datetimebox("getValue"); if (start == '') { alert("请输入开始时间！"); return; } var end = $("#end").datetimebox("getValue"); if (end == '') { alert("请输入终止时间！"); return; } var url = "report/getAlarmSystem.action"; var fileName = ip + "虚机流量展示"; $.messager.confirm('系统提示', "您确定要导出数据吗？", function(r) { if (r) { $.ajax( { url : "report/getvirtual.action", type : "POST", data : { 'ip' : ip, 'startime' : start, 'endtime' : end, }, timeout : 180000, async : true, success : function(data) { if (data) { var arrData = JSON.parse(data); var header = new Array('外网进流量','外网出流量','内网进流量','内网出流量','报警流量','CPU','内存','时间'); var headerKey = new Array('net','net_out','lnet','lnet_out','net_avg','cpu','memory','createtime'); JSONToExcelConvertor(arrData, fileName, header,headerKey, 0) } } }); } }); }
调用3：
// 导出Excel function exportData() { var ip = $("#hostIp").textbox("getValue").trim(); if (ip == '') { alert("请输入IP！"); return; } var start = $("#start").datetimebox("getValue"); if (start == '') { alert("请输入开始时间！"); return; } var end = $("#end").datetimebox("getValue"); if (end == '') { alert("请输入终止时间！"); return; } var url = "report/getAlarmSystem.action"; var fileName = ip + "虚机流量展示"; $.messager.confirm('系统提示', "您确定要导出数据吗？", function(r) { if (r) { $.ajax( { url : "report/getvirtual.action", type : "POST", data : { 'ip' : ip, 'startime' : start, 'endtime' : end, }, timeout : 180000, async : true, success : function(data) { if (data) { var arrData = JSON.parse(data); // var header = new Array('外网进流量','外网出流量','内网进流量','内网出流量','报警流量','CPU','内存','时间'); var header = new Array('外网进流量,外网出流量,内网进流量,内网出流量,报警流量,CPU,内存,时间'); var headerKey = new Array('net','net_out','lnet','lnet_out','net_avg','cpu','memory','createtime'); // JSONToExcelConvertor(arrData, fileName, header,headerKey, 0) exportCSV(arrData, fileName, header,headerKey);//数据过多时使用 } } }); } }); }
