利用Exceljs/file-saver实现纯前端的同一个sheet页实现导出多个表格，拼接

最新推荐文章于 2024-06-13 15:17:07 发布

fairy_404

最新推荐文章于 2024-06-13 15:17:07 发布

阅读量1.8k

点赞数 3

分类专栏： vue 文章标签： vue

本文链接：https://blog.csdn.net/LY__lyxka/article/details/124190394

版权

vue 专栏收录该内容

12 篇文章 2 订阅

订阅专栏

vue版纯前端实现导出（同一个sheet页导出多个表格）- 组合

1.安装依赖

npm install exceljs --save
npm install file-saver --save

2.引入依赖

import FileSaver from "file-saver";
const ExcelJS = require("exceljs");

3.导出测试

// 导出测试
		onExcels() {
			let baseData = [
				["", "企业编号", "M00009201004952"],
				["", "企业名称", "盛夏花开科技股份有限技术公司"],
			];
			let spareCarExcels = [
				["测试门店2", 4, 10.0, 0.0, 0, 0.0],
				["测试门店1", 4, 10.0, 0.0, 0, 0.0],
				["测试门店1", 4, 10.0, 0.0, 0, 0.0],
				["测试门店2", 4, 10.0, 0.0, 0, 0.0],
				["测试门店1", 4, 10.0, 0.0, 0, 0.0],
				["测试门店2", 4, 10.0, 0.0, 0, 0.0],
				["测试门店1", 4, 10.0, 0.0, 0, 0.0],
				["测试门店2", 4, 10.0, 0.0, 0, 0.0],
				["汇总", 4, 10.0, 0.0, 0, 0.0],
			];
			let examExcels = [
				["测试门店1", "T0001234", "2022-03-09", "2022-03-10", "银联二维码主扫", 10.0, 0.0, "32839829389fbafjiaijo", "0239023902323", "——"],
				["测试门店1", "T0001234", "2022-03-09", "2022-03-10", "银联二维码主扫", 10.0, 0.0, "32839829389fbafjiaijo", "0239023902323", "——"],
				["测试门店1", "T0001234", "2022-03-09", "2022-03-10", "银联二维码主扫", 10.0, 0.0, "32839829389fbafjiaijo", "0239023902323", "——"],
				["测试门店1", "T0001234", "2022-03-09", "2022-03-10", "银联二维码主扫", 10.0, 0.0, "32839829389fbafjiaijo", "0239023902323", "——"],
				["测试门店1", "T0001234", "2022-03-09", "2022-03-10", "银联二维码主扫", 10.0, 0.0, "32839829389fbafjiaijo", "0239023902323", "——"],
				["测试门店1", "T0001234", "2022-03-09", "2022-03-10", "银联二维码主扫", 10.0, 0.0, "32839829389fbafjiaijo", "0239023902323", "——"],
				["测试门店1", "T0001234", "2022-03-09", "2022-03-10", "银联二维码主扫", 10.0, 0.0, "32839829389fbafjiaijo", "0239023902323", "——"],
				["测试门店1", "T0001234", "2022-03-09", "2022-03-10", "银联二维码主扫", 10.0, 0.0, "32839829389fbafjiaijo", "0239023902323", "——"],
			];
			const sumData = [["", "合计", "交易笔数：6 交易金额：33.00 退款金额：0.00 手续费：0.00 结算金额：33.00"]];
			const workbook = new ExcelJS.Workbook();
			workbook.creator = "sheet1";
			workbook.lastModifiedBy = "sheet1";
			workbook.created = new Date();
			workbook.modified = new Date();
			// 将工作簿日期设置为 1904 年日期系统
			workbook.properties.date1904 = true;
			const worksheet = workbook.addWorksheet("sheet1");
			worksheet.properties.defaultColWidth = 16; // 默认列宽
			worksheet.properties.defaultRowHeight = 16; // 默认行高
			worksheet.columns = [
				// 设置列宽
				{ width: 14 },
				{ width: 14 },
				{ width: 14 },
				{ width: 14 },
				{ width: 14 },
				{ width: 14 },
				{ width: 14 },
				{ width: 22 },
				{ width: 16 },
				{ width: 16 },
				{ width: 16 },
				{ width: 16 },
			];
			//定义表格标题
			const titleRow = worksheet.getRow(1);
			titleRow.height = 120;
			const titleCell = worksheet.getCell("A1");
			worksheet.mergeCells("A1:J1");
			titleCell.value = "盛夏花开科技股份有限公司交易结算单明细";
			titleCell.font = { name: "宋体", family: 4, size: 14, bold: true }; // 字体
			titleCell.alignment = { vertical: "middle", horizontal: "center" }; //对齐
			// 定义表格标题
			worksheet.addTable({
				name: "baseTable",
				ref: "A2",
				displayName: "盛夏花开科技股份有限公司交易结算单明细",
				headerRow: false, // 在表格顶部显示标题
				filterButton: false, // 切换标题中的过滤器控件
				totalsRow: false, // 在表格底部显示总计
				style: {
					theme: "TableStyleLight12",
				},

				columns: [{ name: "NAME" }, { name: "VALUE" }],
				rows: [],
			});
			//定义基本信息标题
			const baseRow = worksheet.getRow(1);
			baseRow.height = 20;
			const baseCell = worksheet.getCell("A2");
			worksheet.mergeCells("A2:J2");
			baseCell.value = "企业基本信息";
			baseCell.font = { name: "宋体", family: 4, size: 11, bold: true }; // 字体
			baseCell.alignment = { vertical: "middle", horizontal: "left" }; //对齐
			worksheet.mergeCells("A3", "B3");
			worksheet.mergeCells("C3", "J3");
			worksheet.mergeCells("A4", "B4");
			worksheet.mergeCells("C4", "J4");
			// 定义基本信息标题
			worksheet.addTable({
				name: "baseTable",
				ref: "A3",
				displayName: "基本信息",
				headerRow: false, // 在表格顶部显示标题
				filterButton: false, // 切换标题中的过滤器控件
				totalsRow: false, // 在表格底部显示总计
				style: {
					theme: "TableStyleLight12",
				},
				columns: [{ name: "NAME" }, { name: "VALUE" }, { name: "VALUEa" }],
				rows: baseData,
			});
			//定义交易汇总清单标题
			const Examrow = worksheet.getRow(baseData.length + 3);
			Examrow.height = 20;
			const Examcell = worksheet.getCell(`A${baseData.length + 3}`);
			worksheet.mergeCells(`A${baseData.length + 3}:J${baseData.length + 3}`);
			Examcell.value = "交易汇总清单";
			Examcell.font = { name: "宋体", family: 4, size: 11, bold: true }; // 字体
			Examcell.alignment = { vertical: "middle", horizontal: "left" }; //对齐
			// 定义交易汇总清单标题
			worksheet.addTable({
				name: "examTable",
				ref: `A${baseData.length + 4}`,
				displayName: "交易汇总清单",
				headerRow: true, // 在表格顶部显示标题
				filterButton: false, // 切换标题中的过滤器控件
				totalsRow: false, // 在表格底部显示总计
				style: {
					theme: "TableStyleDark12",
					showFirstColumn: true,
				},

				columns: [
					{ name: "终端编号", totalsRowLabel: "Totals:", filterButton: true },
					{ name: "交易笔数", totalsRowFunction: "sum", filterButton: false },
					{ name: "交易金额" },
					{ name: "手续费" },
					{ name: "退款笔数" },
					{ name: "退款金额" },
				],
				rows: spareCarExcels,
			});
			//定义明细标题
			// const Sparecarrow = worksheet.getRow(spareCarExcels.length + baseData.length + 4);
			// Sparecarrow.height = 20;
			// const Sparecarcell = worksheet.getCell(`A${spareCarExcels.length + baseData.length + 4}`);
			// worksheet.mergeCells(`A${spareCarExcels.length + baseData.length + 4}:J${spareCarExcels.length + baseData.length + 4}`);
			// Sparecarcell.value = "";
			// Sparecarcell.font = { name: "宋体", family: 4, size: 11, bold: false }; // 字体
			// Sparecarcell.alignment = { vertical: "middle", horizontal: "left" }; //对齐
			// 明细
			worksheet.addTable({
				name: "spareCarTable",
				displayName: "",
				ref: `A${spareCarExcels.length + baseData.length + 5}`,
				headerRow: true, // 在表格顶部显示标题
				filterButton: false, // 切换标题中的过滤器控件
				totalsRow: false, // 在表格底部显示总计
				style: {
					theme: "TableStyleDark12",
					showFirstColumn: true,
				},
				columns: [
					{ name: "企业名称", totalsRowLabel: "Totals:", filterButton: true },
					{ name: "终端编号", totalsRowLabel: "Totals:", filterButton: true },
					{ name: "交易日期" },
					{ name: "清算日期" },
					{ name: "交易类型" },
					{ name: "交易金额" },
					{ name: "手续费" },
					{ name: "终端流水" },
					{ name: "交易流水" },
					{ name: "原交易流水号" },
					{ name: "备注" },
				],
				rows: examExcels,
			});

			worksheet.mergeCells(`A${spareCarExcels.length + baseData.length + examExcels.length + 6}`, `B${spareCarExcels.length + baseData.length + examExcels.length + 6}`);
			worksheet.mergeCells(`C${spareCarExcels.length + baseData.length + examExcels.length + 6}`, `J${spareCarExcels.length + baseData.length + examExcels.length + 6}`);

			// 合计
			worksheet.addTable({
				name: "sumTable",
				displayName: "",
				ref: `A${spareCarExcels.length + baseData.length + examExcels.length + 6}`,
				headerRow: false, // 在表格顶部显示标题
				filterButton: false, // 切换标题中的过滤器控件
				totalsRow: false, // 在表格底部显示总计
				style: {
					theme: "TableStyleLight12",
				},
				columns: [{ name: "name" }, { name: "name2" }, { name: "value" }],
				rows: sumData,
			});

			for (let i = 1; i <= spareCarExcels.length + baseData.length + examExcels.length + 6; i++) {
				let border = {
					top: { style: "thin" },
					left: { style: "thin" },
					bottom: { style: "thin" },
					right: { style: "thin" },
				};
				let alignment = { vertical: "middle", horizontal: "center" };
				worksheet.getCell(`A${i}`).border = border;
				worksheet.getCell(`B${i}`).border = border;
				worksheet.getCell(`C${i}`).border = border;
				worksheet.getCell(`D${i}`).border = border;
				worksheet.getCell(`E${i}`).border = border;
				worksheet.getCell(`F${i}`).border = border;
				worksheet.getCell(`G${i}`).border = border;
				worksheet.getCell(`H${i}`).border = border;
				worksheet.getCell(`I${i}`).border = border;
				worksheet.getCell(`J${i}`).border = border;
				worksheet.getCell(`A${i}`).alignment = alignment;
				worksheet.getCell(`B${i}`).alignment = alignment;
				worksheet.getCell(`C${i}`).alignment = alignment;
				worksheet.getCell(`D${i}`).alignment = alignment;
				worksheet.getCell(`E${i}`).alignment = alignment;
				worksheet.getCell(`F${i}`).alignment = alignment;
				worksheet.getCell(`G${i}`).alignment = alignment;
				worksheet.getCell(`H${i}`).alignment = alignment;
				worksheet.getCell(`I${i}`).alignment = alignment;
				worksheet.getCell(`J${i}`).alignment = alignment;
			}
			let fill = {
				type: "gradient",
				gradient: "path",
				center: { left: 0.5, top: 0.5 },
				stops: [
					{ position: 0, color: { argb: "bec0c0" } },
					{ position: 1, color: { argb: "bec0c0" } },
				],
			};
			let alignment = { vertical: "middle", horizontal: "left" };
			worksheet.getCell("A2").fill = fill;
			worksheet.getCell("A5").fill = fill;
			worksheet.getCell("A2").alignment = alignment;
			worksheet.getCell("A5").alignment = alignment;
			workbook.xlsx.writeBuffer().then((buffer) => {
				// eslint-disable-next-line no-undef
				FileSaver.saveAs(
					new Blob([buffer], {
						type: "application/octet-stream",
					}),
					`excle数据表.xlsx`
				);
			});
		},

4.效果

在这里插入图片描述

fairy_404

关注

3
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
利用Exceljs/file-saver实现纯前端的同一个sheet页实现导出多个表格，拼接

vue版纯前端实现导出（同一个sheet页导出多个表格）- 组合1.安装依赖2.引入依赖3.导出测试4.效果1.安装依赖npm install exceljs --savenpm install file-saver --save2.引入依赖import FileSaver from "file-saver";const ExcelJS = require("exceljs");3.导出测试// 导出测试 onExcels() { let baseData = [ ["
复制链接

扫一扫