vue+exceljs前端下载、导出xlsx文件

首先安装插件

npm install exceljs file-saver

第一种 简单导出

//页面引入
import ExcelJS from 'exceljs'; 
import {saveAs} from 'file-saver';
export default {methods: {
/** 导出操作 */async handleExportFun() {let that = this// 获取当前年月日 用户下载xlsx的文件名称设置const now = new Date();const year = now.getFullYear();const month = String(now.getMonth() + 1).padStart(2, '0'); // 月份从0开始，需要加1，并确保两位数const day = String(now.getDate()).padStart(2, '0'); // 确保两位数const hours = String(now.getHours()).padStart(2, '0'); // 确保两位数const minutes = String(now.getMinutes()).padStart(2, '0'); // 确保两位数var currentTime = `${year}${month}${day}${hours}${minutes}`;//创建const workbook = new ExcelJS.Workbook();const worksheet = workbook.addWorksheet('Sheet1');// 假设 exportData 的第一行是列头const headers = this.exportData[0]; // 确保这里是获取的第一行数据作为列头// 填充表头数据  因为我的数据是不固定的二维数组worksheet.columns = headers.map((headerText, index) => ({header: headerText,key: index.toString(),width: 22,}));// 设置表头行高const headerRow = worksheet.getRow(1);headerRow.height = 35; // 设置行高为35（可以根据需要调整）//申明样式let style = {alignment: {vertical: "middle",horizontal: "center",wrapText: true,},fill: {type: "pattern",pattern: "solid",fgColor: {argb: "EEEEEE"},},border: {top: {style: "thin",color: { rgb: 'E3F2D9' } // 设置左边框样式和颜色}, // 设置上边框样式为细线bottom: {style: "thin",color: { rgb: 'E3F2D9' } // 设置左边框样式和颜色}, // 设置下边框样式为细线left: {style: "thin",color: { rgb: 'E3F2D9' } // 设置左边框样式和颜色}, // 设置左边框样式为细线right: {style: "thin",color: { rgb: 'E3F2D9' } // 设置左边框样式和颜色}, // 设置右边框样式为细线},font: {bold: true, // 设置字体加粗name: 'Arial', // 设置字体名称（可选）size: 12 // 设置字体大小（可选）}};// 填充工作表的其他数据，并设置行高for (let rowIndex = 1; rowIndex < this.exportData.length; rowIndex++) {const row = this.exportData[rowIndex];const excelRow = worksheet.getRow(rowIndex + 1);excelRow.height = 30; // 设置其他行的行高为25（可以根据需要调整）row.forEach((cellValue, cellIndex) => {const cell = excelRow.getCell(cellIndex + 1);cell.value = cellValue;});}//给每行设置样式worksheet.eachRow((row, rowNumber) => {if (rowNumber > 0) {row.height = 35;row.alignment = style.alignment;// 你还可以根据单元格的内容或列号执行不同的操作row.eachCell((cell, colNumber) => {if (rowNumber === 1&&colNumber<=that.exportData[0].length) {//对第一列的单元格执行特殊操作cell.font=style.fontcell.fill=style.fill}cell.border = style.border;});}});try {const buffer = await workbook.xlsx.writeBuffer();const blob = new Blob([buffer], {type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'});saveAs(blob, currentTime + '_设备数据.xlsx'); // 导出的文件名可以根据需要动态设置} catch (err) {console.error('导出Excel时出错:', err);}}}}

第二种复杂的表单导出

如图
数据格式

this.summaryStatistics={"type": null,"xuhao": null,"user": "汇总","timeJson": [{\"bxPrice\":0.00,\"fyPrice\":0.00,\"fyzb\":0.00,\"htPrice\":0.00,\"swPrice\":0.00,\"time\":\"2023年\"},{\"bxPrice\":129584000.80,\"fyPrice\":129592366.80,\"fyzb\":738.96,\"htPrice\":17537026.00,\"swPrice\":8366.00,\"time\":\"2024年\"}],"htXj": 17537026,"htZb": null,"fyXj": 129592366.8,"fyZbXj": 738.96}this.times = this.summaryStatistics.timeJsonthis.userStatistics = [{"type": null,"xuhao": 1,"user": "admin","timeJson": "[{\"bxPrice\":0.00,\"fyPrice\":0.00,\"fyzb\":0.00,\"htPrice\":0.00,\"swPrice\":0.00,\"time\":\"2023年\"},{\"bxPrice\":0.00,\"fyPrice\":0.00,\"fyzb\":0.00,\"htPrice\":274000.00,\"swPrice\":0.00,\"time\":\"2024年\"}]","htXj": 274000,"htZb": 1.56,"fyXj": 0,"fyZbXj": 0},{"type": null,"xuhao": 2,"user": "肖永梅","timeJson": "[{\"bxPrice\":0.00,\"fyPrice\":0.00,\"fyzb\":0.00,\"htPrice\":0.00,\"swPrice\":0.00,\"time\":\"2023年\"},{\"bxPrice\":0.00,\"fyPrice\":0.00,\"fyzb\":0.00,\"htPrice\":0.00,\"swPrice\":0.00,\"time\":\"2024年\"}]","htXj": 0,"htZb": 0,"fyXj": 0,"fyZbXj": 0}]//将数据添在第一行this.userStatistics.unshift(this.summaryStatistics);this.userStatistics.forEach((item) => {if (item.timeJson != null) {JSON.parse(item.timeJson).forEach((child, index) => {item[`htPrice${index}`] = child.htPrice;item[`fyPrice${index}`] = child.fyPrice;item[`fyzb${index}`] = child.fyzb;});}});

数据准备好后就是导出方法了（handleExport）

/** 导出按钮操作 */handleExport() {const loading = this.$loading({lock: true,text: "导出excel中...（若长时间无响应请刷新页面）",spinner: "el-icon-loading",background: "rgba(0, 0, 0, 0.7)",});const Exceljs = require("exceljs"); // 引入exceljsconst workbook = new Exceljs.Workbook(); // 创建工作簿const workSheet = workbook.addWorksheet("sheet1"); // 创建工作表（sheet1）workSheet.getRow(1).height = 60;workSheet.getRow(2).height = 36;workSheet.getRow(3).height = 28;workSheet.getRow(4).height = 28;let style = {alignment: {vertical: "middle",horizontal: "center",wrapText: true,},fill: {type: "pattern",pattern: "solid",fgColor: { argb: "E3F2D9" },},border: {top: { style: "thin" }, // 设置上边框样式为细线bottom: { style: "thin" }, // 设置下边框样式为细线left: { style: "thin" }, // 设置左边框样式为细线right: { style: "thin" }, // 设置右边框样式为细线},};// 获取动态年份条数let years = this.times.length;workSheet.mergeCells(`A1:${this.numberToMatchLetter(3 * years + 6)}1`); //将A1到xx的单元格合并const cell_A1 = workSheet.getCell("A1"); // 获取A1单元格cell_A1.value = "碧朗科技销售员销售额及费用统计表";cell_A1.font = { size: 18, bold: true, name: "宋体" }; // 设置字体大小为16，加粗，仿宋cell_A1.alignment = style.alignment;workSheet.mergeCells(`${this.numberToMatchLetter(3 * years + 7)}1:${this.numberToMatchLetter(3 * years + 10)}1`); //将xx1到xx1的单元格合并const cell_P1 = workSheet.getCell(`${this.numberToMatchLetter(3 * years + 7)}1`); // 获取xx1单元格cell_P1.value = "注：第4行的年份根据筛选条件动态变化";cell_P1.font = {size: 11,name: "Times New Roman",color: { argb: "FF0000" },};cell_P1.alignment = style.alignment;workSheet.mergeCells("A2:E2"); //将A2到E2的单元格合并const cell_A2 = workSheet.getCell("A2"); // 获取A2单元格cell_A2.value = `年份：${this.queryParams.startYear ? this.queryParams.startYear : ""}-${this.queryParams.endYear ? this.queryParams.endYear : ""} 月份：${this.queryParams.startMonth ? this.queryParams.startMonth : ""}-${this.queryParams.endMonth ? this.queryParams.endMonth : ""}`;cell_A2.font = { size: 8, bold: true, name: "宋体" };cell_A2.alignment = {vertical: "middle",wrapText: true,};workSheet.mergeCells("A3:A4"); //将A3到A4的单元格合并const cell_A3 = workSheet.getCell("A3"); // 获取A3单元格cell_A3.value = "序号";cell_A3.style = style;cell_A3.font = { size: 11, bold: true, name: "宋体" };// 遍历合并A3到A4的单元格的区域，为其余部分设置边框for (let row = 3; row <= 4; row++) {for (let col = 1; col <= 1; col++) {const cell = workSheet.getCell(row, col);cell.border = cell_A3.border;}}workSheet.mergeCells("B3:B4"); //将B3到B4的单元格合并const cell_B3 = workSheet.getCell("B3"); // 获取B3单元格cell_B3.value = "销售员";cell_B3.style = style;cell_B3.font = { size: 11, bold: true, name: "宋体" };// 遍历合并B3到B4的单元格的区域，为其余部分设置边框for (let row = 3; row <= 4; row++) {for (let col = 2; col <= 2; col++) {const cell = workSheet.getCell(row, col);cell.border = cell_B3.border;}}workSheet.mergeCells(`C3:${this.numberToMatchLetter(years + 4)}3`);const cell_diy1 = workSheet.getCell("C3");cell_diy1.value = "销售额";cell_diy1.style = style;cell_diy1.font = { size: 11, bold: true, name: "宋体" };// 遍历合并的单元格的区域，为其余部分设置边框for (let row = 3; row <= 3; row++) {for (let col = 3; col <= years + 4; col++) {const cell = workSheet.getCell(row, col);cell.border = cell_diy1.border;}}workSheet.mergeCells(`${this.numberToMatchLetter(years + 5)}3:${this.numberToMatchLetter(2 * years + 5)}3`);const cell_diy2 = workSheet.getCell(`${this.numberToMatchLetter(years + 5)}3`);// 创建富文本内容const richText = [{text: "费用",font: {name: "宋体",size: 11,bold: true,},},{text: "（报销的费用+备案商务费）",font: {name: "宋体",size: 9,bold: true,color: { argb: "FF0000" },},},];cell_diy2.value = { richText: richText };cell_diy2.style = style;// 遍历合并的单元格的区域，为其余部分设置边框for (let row = 3; row <= 3; row++) {for (let col = years + 5; col <= 2 * years + 5; col++) {const cell = workSheet.getCell(row, col);cell.border = cell_diy2.border;}}workSheet.mergeCells(`${this.numberToMatchLetter(2 * years + 6)}3:${this.numberToMatchLetter(3 * years + 6)}3`);const cell_diy3 = workSheet.getCell(`${this.numberToMatchLetter(2 * years + 6)}3`);cell_diy3.value = "费用占比(%)";cell_diy3.style = style;cell_diy3.font = { size: 11, bold: true, name: "宋体" };// 遍历合并的单元格的区域，为其余部分设置边框for (let row = 3; row <= 3; row++) {for (let col = 2 * years + 6; col <= 3 * years + 6; col++) {const cell = workSheet.getCell(row, col);cell.border = cell_diy3.border;}}const cell_XJ1 = workSheet.getCell(`${this.numberToMatchLetter(3 + years)}4`); // 获取单元格cell_XJ1.value = "小计";cell_XJ1.style = style;cell_XJ1.font = { size: 11, bold: true, name: "宋体" };const cell_ZB1 = workSheet.getCell(`${this.numberToMatchLetter(4 + years)}4`); // 获取单元格cell_ZB1.value = "占比(%)";cell_ZB1.style = style;cell_ZB1.font = { size: 11, bold: true, name: "宋体" };const cell_XJ2 = workSheet.getCell(`${this.numberToMatchLetter(5 + 2 * years)}4`); // 获取单元格cell_XJ2.value = "小计";cell_XJ2.style = style;cell_XJ2.font = { size: 11, bold: true, name: "宋体" };const cell_XJ3 = workSheet.getCell(`${this.numberToMatchLetter(6 + 3 * years)}4`); // 获取单元格cell_XJ3.value = "小计";cell_XJ3.style = style;cell_XJ3.font = { size: 11, bold: true, name: "宋体" };// 销售额动态年份填充for (let i = 1; i <= years; i++) {workSheet.getCell(`${this.numberToMatchLetter(2 + i)}4`).value =this.times[i - 1].time;workSheet.getCell(`${this.numberToMatchLetter(2 + i)}4`).style = style;workSheet.getCell(`${this.numberToMatchLetter(2 + i)}4`).font = {size: 11,bold: true,name: "宋体",};workSheet.getCell(`${this.numberToMatchLetter(years + 4 + i)}4`).value =this.times[i - 1].time;workSheet.getCell(`${this.numberToMatchLetter(years + 4 + i)}4`).style =style;workSheet.getCell(`${this.numberToMatchLetter(years + 4 + i)}4`).font ={size: 11,bold: true,name: "宋体",};workSheet.getCell(`${this.numberToMatchLetter(2 * years + 5 + i)}4`).value = this.times[i - 1].time;workSheet.getCell(`${this.numberToMatchLetter(2 * years + 5 + i)}4`).style = style;workSheet.getCell(`${this.numberToMatchLetter(2 * years + 5 + i)}4`).font = {size: 11,bold: true,name: "宋体",};}let exportTableProp = [{ key: "xuhao" },{ key: "user" },{ key: "htXj", width: 18 },{ key: "htZb", width: 18 },{ key: "fyXj", width: 18 },{ key: "fyZbXj", width: 18 },];if (this.times.length > 0) {this.times.forEach((item, index) => {exportTableProp.splice(index + 2, 0, {key: `htPrice${index}`,width: 18,});});this.times.forEach((item, index) => {exportTableProp.splice(index + years + 4, 0, {key: `fyPrice${index}`,width: 18,});});this.times.forEach((item, index) => {exportTableProp.splice(index + 2 * years + 5, 0, {key: `fyzb${index}`,width: 18,});});}workSheet.columns = exportTableProp; // 工作表添加表头workSheet.addRows(this.userStatistics); // 往工作表插入数据workSheet.eachRow((row, rowNumber) => {if (rowNumber > 4) {row.height = 20;row.alignment = style.alignment;row.eachCell({start: 1,end: 3 * years + 6,},(cell, colNumber) => {cell.border = style.border;});}});// 下载工作簿workbook.xlsx.writeBuffer().then((buffer) => {saveAs(new Blob([buffer], { type: "application/octet-stream" }),"销售员销售额及费用表格导出.xlsx");});loading.close();}