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

news/2024/11/13 5:04:34/

首先安装插件

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();}

http://www.ppmy.cn/news/1545900.html

相关文章

无人机之飞行管控平台篇

无人机之飞行管控平台篇

无人机的飞行管控平台是一种基于互联网和物联网技术的智能管理系统&#xff0c;旨在实现对无人机飞行任务的全自动化、全过程化管理。 一、主要功能 飞行计划管理&#xff1a;用户可以通过平台提前设置好无人机的飞行计划&#xff0c;包括起飞时间、航线、飞行高度等信息。平…
阅读更多...
《EasyQuotation 与MongoDB在股市信息的奇妙融合》

《EasyQuotation 与MongoDB在股市信息的奇妙融合》

《EasyQuotation 与MongoDB在股市信息的奇妙融合》 一、EasyQuotation 的强大功能二、数据存入 MongoDB&#xff08;一&#xff09;配置与连接&#xff08;二&#xff09;存储方法 三、K 线图监视股市信息&#xff08;一&#xff09;自定义性能趋势图表&#xff08;二&#xff…
阅读更多...
亚马逊评论爬虫+数据分析

亚马逊评论爬虫+数据分析

爬取评论 做分析首先得有数据&#xff0c;数据是核心&#xff0c;而且要准确&#xff01; 1、爬虫必要步骤&#xff0c;选好框架 2、开发所需数据 3、最后测试流程 这里我所选框架是seleniumrequest&#xff0c;很多人觉得selenium慢&#xff0c;确实不快&#xff0c;仅针对此…
阅读更多...
富格林：揭露欺诈陷阱用心追损

富格林：揭露欺诈陷阱用心追损

富格林指出&#xff0c;现货黄金投资作为一种比较受青睐的投资方式&#xff0c;已经赢得了很多投资理财者的关注了。但是投资现货黄金需要对黄金相关的交易操作和技巧有深入的了解&#xff0c;才能够规避欺诈陷阱用心追损。事实上&#xff0c;掌握一定的交易技巧对于规避欺诈陷…
阅读更多...
CSS3中的2D变换（位移、缩放、旋转、扭曲、多重变换、变换原点）

CSS3中的2D变换（位移、缩放、旋转、扭曲、多重变换、变换原点）

1.2D位移&#xff1a; 2.2D缩放 3.2D旋转 4.2D扭曲 5.2D多重变换 6.2D变换原点
阅读更多...
【jmeter】jmeter的线程组功能的详细介绍

【jmeter】jmeter的线程组功能的详细介绍

初衷 之前在公司做的性能测试基本上都是关于数据库的&#xff0c;针对接口的性能测试还是比较少一点。考虑到后边大模型问答产品的推广&#xff0c;公司方面也要求对相关接口进行压测&#xff0c;也趁着这个机会&#xff0c;对jmeter进行深入研究&#xff0c;进一步加强自己性…
阅读更多...
使用Python分析股票价格数据并计算移动平均线的实用指南

使用Python分析股票价格数据并计算移动平均线的实用指南

使用Python分析股票价格数据并计算移动平均线的实用指南 在金融市场中，移动平均线（Moving Average, MA）是一种常用的技术分析工具，用于平滑价格数据，帮助投资者识别趋势。本文将详细介绍如何使用Python分析股票价格数据，并计算移动平均线。我们将通过一个实际的案例来演…
阅读更多...
OpenMVS OpenMVG 笔记

OpenMVS OpenMVG 笔记

OpenMVS & OpenMVG 笔记 OpenMVS 和 OpenMVG 都是计算机视觉中用于三维重建的开源库。两者都可以实现从图像集合中计算出相机位姿和三维点云&#xff0c;但它们的重点略有不同。 OpenMVG 主要关注于从输入图像集合中提取稠密的特征匹配&#xff0c;通过这些匹配计算相机的…
阅读更多...
最新文章

Copyright @ 2022~2023