第三步：导出两个tab面板中的数据
导出方法： baseTable、alarmTable 与图中的保持一致
exportExcel1() { var workbook = XLSX.utils.book_new(); /* convert table 'table1' to worksheet named "Sheet1" */ var ws1 = XLSX.utils.table_to_sheet(document.querySelector('#baseTable')); XLSX.utils.book_append_sheet(workbook, ws1, "历史数据");/* convert table 'table2' to worksheet named "Sheet2" */ var ws2 = XLSX.utils.table_to_sheet(document.querySelector('#alarmTable')); XLSX.utils.book_append_sheet(workbook, ws2, "报警数据");
/* get binary string as output */ var wbOut = XLSX.write(workbook, { bookType: "xlsx", bookSST: true, type: "array" }); try { FileSaver.saveAs( new Blob([wbOut], { type: "application/octet-stream" }), "数据查询.xlsx" ); } catch (e) { if (typeof console !== "undefined") console.log(e, wbOut); } return wbout; }, 以上导出的缺陷就是，只支持导出当前页面的数据，不能导出全部数据，另外导出到文件后xlsx格式不能自定义。
下面说一下自定义样式、及全部数据导出，直接贴代码： exportExcel() { var that = this; //要导出历史数据标题 var arry = [['排灌站', '设备名称', '运行时长（分钟）', '开启时间', '关闭时间']]; // 要导出的历史数据 that.exportData.map(a => { var _arry = []; _arry.push(a.pumpstationName.toString()); _arry.push(a.itemName.toString()); _arry.push(a.runtime_Minute.toString()); _arry.push(a.startTime == null ? "" : this.$moment(a.startTime).format("YYYY-MM-DD HH:mm:ss")); //格式化日期没有就返回空 _arry.push(a.endTime == null ? "" : this.$moment(a.endTime).format('YYYY-MM-DD HH:mm:ss')); //格式化日期没有就返回空 return _arry; }).forEach(a => { arry.push(a); }); //要导出的报警数据标题 var alarmInfoArry = [['排灌站', '预警类型', '当前值', '阈值']]; // 要导出的报警数据 that.alarmInfo.map(b => { var _alarmInfoArry = []; _alarmInfoArry.push(b.pumpName.toString()); _alarmInfoArry.push(b.alarmType.toString()); _alarmInfoArry.push(b.cuurentValue.toString()); _alarmInfoArry.push(b.thresholdValue.toString()); return _alarmInfoArry; }).forEach(b => { alarmInfoArry.push(b); }); var sheetHistory = XLSX.utils.aoa_to_sheet(arry);//历史数据sheet var sheetAlarm = XLSX.utils.aoa_to_sheet(alarmInfoArry);//报警数据sheet var workbook = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(workbook, sheetHistory, "历史数据"); XLSX.utils.book_append_sheet(workbook, sheetAlarm, "报警数据"); //循环历史数据单元格设置样式 var s = sheetHistory['!ref']; sheetHistory.s = { font: { name: '宋体', sz: 18, color: { rgb: "#FFFF0000" }, bold: true, italic: false, underline: false }, alignment: { horizontal: "center", vertical: "center" } }; var rows = s.substr(s.length - 1, 1); var cloums = ['A', 'B', 'C', 'D', 'E']; for (var j = 0; j < cloums.length; j++) { for (var i = 1; i <= rows; i++) { if (i == 1) { sheetHistory[cloums[j] + i].s = { //样式 font: { bold: true, italic: false, underline: false }, alignment: { horizontal: "left", vertical: "left", wrap_text: false } }; } else { sheetHistory[cloums[j] + i].s = { //样式 alignment: { horizontal: "left", vertical: "left", wrap_text: false } }; } } } sheetHistory["!cols"] = [{ wpx: 150 }, { wpx: 90 }, { wpx: 150 }, { wpx: 150 }, { wpx: 180 }]; //单元格列宽
//循环设置报警数据单元格样式 var styleAlarm = sheetAlarm['!ref']; sheetAlarm.styleAlarm = { font: { name: '宋体', sz: 18, color: { rgb: "#FFFF0000" }, bold: true, italic: false, underline: false }, alignment: { horizontal: "center", vertical: "center" } }; var rows2 = styleAlarm.substr(styleAlarm.length - 1, 1); var cloums2 = ['A', 'B', 'C', 'D']; for (var k = 0; k < cloums2.length; k++) { for (var L = 1; L <= rows2; L++) { if (i == 1) { sheetAlarm[cloums2[k] + L].s = { //样式 font: { bold: true, italic: false, underline: false }, alignment: { horizontal: "left", vertical: "left", wrap_text: false } }; } else { sheetAlarm[cloums2[k] + L].s = { //样式 alignment: { horizontal: "left", vertical: "left", wrap_text: false } }; } } } sheetAlarm["!cols"] = [{ wpx: 150 }, { wpx: 90 }, { wpx: 150 }, { wpx: 150 }]; //单元格列宽 try { var wbOut = XLSX.write(workbook, { bookType: "xlsx", bookSST: true, type: "array" }); FileSaver.saveAs( new Blob([wbOut], { type: "application/octet-stream" }), "数据查询.xlsx" ); } catch (e) { if (typeof console !== "undefined") console.log(e, wbOut); } // return wbout; }, 导出后表格样式如图：