最近公司有个需求每周要查看一次数据统计，页面有个四个表格内容
要求把四个表格的数据导出到一个excel里面，查了一下网上的导出excel方法，但都没有要把多个导出成一个excel的例子。
不过经过一番试验，目前试用了两种插件都能实现：
第一种SheetJs
(/rockboom/SheetJS-docs-zh-CN/)
注意sheetjs免费版有的样式不能调整，收费版才可以，官方的说法是为了不让花钱提出开发样式功能需求的公司吃亏做出的限制。
安装SheetJs
npm install xlsx -s
还有一个吧生成的excel导出下载的FileSaver(这里两个插件都要用到)
npm install file-saver --save
首先创建工作薄后，如果是数组使用XLSX.utils.sheet_add_aoa将数组连续输入到工作薄不同单元格中
/* Initial row */var ws = XLSX.utils.aoa_to_sheet([ "SheetJS".split("") ]);/* Write data starting at A2 */XLSX.utils.sheet_add_aoa(ws, [[1,2], [2,3], [3,4]], {origin: "A2"});/* Write data starting at E2 */XLSX.utils.sheet_add_aoa(ws, [[5,6,7], [6,7,8], [7,8,9]], {origin:{r:1, c:4}});/* Append row */XLSX.utils.sheet_add_aoa(ws, [[4,5,6,7,8,9,0]], {origin: -1});
如果是数组对象则使用XLSX.utils.sheet_add_json
/* Initial row */var ws = XLSX.utils.json_to_sheet([{A: "S", B: "h", C: "e", D: "e", E: "t", F: "J", G: "S" }], {header: ["A", "B", "C", "D", "E", "F", "G"], skipHeader: true});/* Write data starting at A2 */XLSX.utils.sheet_add_json(ws, [{A: 1, B: 2 }, {A: 2, B: 3 }, {A: 3, B: 4 }], {skipHeader: true, origin: "A2"});/* Write data starting at E2 */XLSX.utils.sheet_add_json(ws, [{A: 5, B: 6, C: 7 }, {A: 6, B: 7, C: 8 }, {A: 7, B: 8, C: 9 }], {skipHeader: true, origin: {r: 1, c: 4 }, header: [ "A", "B", "C" ]});/* Append row */XLSX.utils.sheet_add_json(ws, [{A: 4, B: 5, C: 6, D: 7, E: 8, F: 9, G: 0 }], {header: ["A", "B", "C", "D", "E", "F", "G"], skipHeader: true, origin: -1});
然后直接导出就行了
import FileSaver from "file-saver";import XLSX from "xlsx";...getExcel(){let workbook= XLSX.utils.aoa_to_sheet([ "SheetJS".split("") ]);/* Write data starting at A2 */XLSX.utils.sheet_add_aoa(workbook, [[1,2], [2,3], [3,4]], {origin: "A2"});XLSX.utils.sheet_add_aoa(workbook, [[1,2], [2,3], [3,4]], {origin: "G2"});let wopts = {bookType: "xlsx",bookSST: true,type: "binary",cellStyles: true};var wbout = XLSX.write(workbook, wopts);FileSaver.saveAs(new Blob([s2ab(wbout)], {type: "application/octet-stream" }),'使用情况.xlsx');},// 字符串转ArrayBufferfunction s2ab(s) {var buf = new ArrayBuffer(s.length);var view = new Uint8Array(buf);for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xff;return buf;}
第二种Exceljs
(/exceljs/exceljs/blob/master/README_zh.md)
安装exceljs
npm install exceljsnpm install file-saver --save
<script>const ExcelJS = require("exceljs");import FileSaver from "file-saver";export default {name: "usage",methods:{// 导出onExcels() {let valueDate = this.valueDate;let examExcels = this.examExcels; // 考试计划表格let spareCarExcels = this.spareCarExcels; // 备用车表格let heChangExcels = this.heChangExcels; // 合场表格let naturalExcels = this.naturalExcels; // 入籍表格const workbook = new ExcelJS.Workbook();workbook.creator = "使用情况";workbook.lastModifiedBy = "使用情况";workbook.created = new Date();workbook.modified = new Date();// 将工作簿日期设置为 1904 年日期系统workbook.properties.date1904 = true;const worksheet = workbook.addWorksheet("使用情况");worksheet.properties.defaultColWidth = 16; // 默认列宽worksheet.properties.defaultRowHeight = 16; // 默认行高// worksheet.columns = [ // 设置列宽// { width: 16 },// { width: 10 },// { width: 16 },// { width: 16 },// { width: 16 },// { width: 16 },// { width: 16 },// { width: 16 },// { width: 16 },// ];//定义考试计划标题const Examrow = worksheet.getRow(1);Examrow.height = 50;const Examcell = worksheet.getCell('A1');worksheet.mergeCells('A1:F1');Examcell.value = '考试计划使用情况';Examcell.font = {name: '宋体', family: 4, size: 14, bold: true }; // 字体Examcell.alignment = {vertical: 'middle', horizontal: 'center' };//对齐// 考试计划worksheet.addTable({name: "examTable",ref: "A2",displayName: "考试计划",headerRow: true, // 在表格顶部显示标题filterButton: false, // 切换标题中的过滤器控件totalsRow: false, // 在表格底部显示总计style: {theme: "TableStyleLight8",},columns: [{name: "公司" },{name: "科目" },{name: "考试计划数" },{name: "计划学员数" },{name: "计划成绩录入数" },{name: "临考成绩录入数" },],rows: examExcels,});//定义备用车标题const Sparecarrow = worksheet.getRow(examExcels.length + 5);Sparecarrow.height = 50;const Sparecarcell = worksheet.getCell(`A${examExcels.length + 5}`);worksheet.mergeCells(`A${examExcels.length + 5}:C${examExcels.length + 5}`);Sparecarcell.value = '备用车使用情况';Sparecarcell.font = {name: '宋体', family: 4, size: 14, bold: true }; // 字体Sparecarcell.alignment = {vertical: 'middle', horizontal: 'center' };//对齐// 备用车worksheet.addTable({name: "spareCarTable",displayName: "备用车",ref: `A${examExcels.length + 6}`,headerRow: true, // 在表格顶部显示标题filterButton: false, // 切换标题中的过滤器控件totalsRow: false, // 在表格底部显示总计style: {theme: "TableStyleLight8",},columns: [{name: "公司" }, {name: "学员安排数" }, {name: "学员签到数" }],rows: spareCarExcels,});//定义合场在线缴纳情况标题const Hechangcell = worksheet.getCell(`E${examExcels.length + 5}`);worksheet.mergeCells(`E${examExcels.length + 5}:F${examExcels.length + 5}`);Hechangcell.value = '合场在线缴纳情况';Hechangcell.font = {name: '宋体', family: 4, size: 14, bold: true }; // 字体Hechangcell.alignment = {vertical: 'middle', horizontal: 'center' };//对齐// 合场在线缴纳情况worksheet.addTable({name: "heChangTable",displayName: "合场在线缴纳情况",ref: `E${examExcels.length + 6}`,headerRow: true, // 在表格顶部显示标题filterButton: false, // 切换标题中的过滤器控件totalsRow: false, // 在表格底部显示总计style: {theme: "TableStyleLight8",},columns: [{name: "公司" }, {name: "缴费数" }],rows: heChangExcels,});// // 入籍数据const Naturalcell = worksheet.getCell(`H${examExcels.length + 5}`);worksheet.mergeCells(`H${examExcels.length + 5}:I${examExcels.length + 5}`);Naturalcell.value = '入籍数据';Naturalcell.font = {name: '宋体', family: 4, size: 14, bold: true }; // 字体Naturalcell.alignment = {vertical: 'middle', horizontal: 'center' };//对齐worksheet.addTable({name: "naturalTable",displayName: "入籍数据",ref: `H${examExcels.length + 6}`,headerRow: true, // 在表格顶部显示标题filterButton: false, // 切换标题中的过滤器控件totalsRow: false, // 在表格底部显示总计style: {theme: "TableStyleLight8",},columns: [{name: "类型" }, {name: "数量" }],rows: naturalExcels,});workbook.xlsx.writeBuffer().then((buffer) => {// eslint-disable-next-line no-undefFileSaver.saveAs(new Blob([buffer], {type: "application/octet-stream",}),`${valueDate[0]}-${valueDate[1]}使用情况.xlsx`);});},}
最终导出
