前端导出 excel 并自定义格式

xlsx-js-style 是一个JavaScript库，可以创建带有样式的Excel文件

// 下载 xlsx-js-style 依赖

npm install xlsx-js-style -S;

// 引入

import XLSXS from 'xlsx-js-style';

<a-button type="primary" @click="exportExcel"> 导出 </a-button>


exportExcel() {
			    const list = [
				{
					Name: 'Bill Clinton',
					Date: '2023-01-01',
					'Source category name': 'Excise Taxes',
					'Source subcategory name': 'Corporation Income Taxes',
				},
				{
					Name: 'GeorgeW Bush',
					Date: '2023-01-01',
					'Source category name': 'Excise Taxes',
					'Source subcategory name': 'Corporation Income Taxes',
				},
				{
					Name: 'Barack Obama',
					Date: '2023-01-01',
					'Source category name': 'Excise Taxes',
					'Source subcategory name': 'Corporation Income Taxes',
				},
				{
					Name: 'Donald Trump',
					Date: '2023-01-01',
					'Source category name': 'Excise Taxes',
					'Source subcategory name': 'Corporation Income Taxes',
				},
				{
					Name: 'Joseph Biden',
					Date: '2023-01-01',
					'Source category name': 'Excise Taxes',
					'Source subcategory name': 'Corporation Income Taxes',
				},
			]

			let newData = [["Name", "Date", "Source category name", "Source subcategory name"]]; //定义一个数组方便拼接后面的数据
			list.map((item, index) => {
				newData.push([
					item.Name,
					item.Date,
					item['Source category name'],
					item['Source subcategory name'],
				])
			})

			// 创建一个工作簿 workbook
			const workBook = XLSXS.utils.book_new()
			// 创建工作表 worksheet
			const workSheet = XLSXS.utils.aoa_to_sheet(newData);

			// 设置列宽
			// cols 为一个对象数组，依次表示每一列的宽度
			// wpx 字段表示以像素为单位，wch 字段表示以字符为单位
			// hidden 如果为真，则隐藏该列
			workSheet['!cols'] = [
				{ wch: 8 },
				{ wch: 8 },
				{ wch: 8 },
				{ wch: 8 }
			]

			// 设置行高
			// rows 为一个对象数组，依次表示每一行的高度
			// workSheet['!rows'] = [{ hpx: 30 }, { hpt: 50 }, { hidden: true }]

			Object.keys(workSheet).forEach((key) => {
				// 非!开头的属性都是单元格
				if (!key.startsWith("!")) {
					console.log("key：", key);
					//设置从第三行开始的数据样式
					for (let i = 1; i <= newData.length; i++) {
						//用正则表达式判断是否是某一行
						if (key.replace(/[^0-9]/gi, "") == i) {
							workSheet[key].s = {
								//设置字体
								font: {
									name: "Arial",
									sz: 10,
									bold: false,
									color: { rgb: "000000" },
								},
								//设置居中
								alignment: {
									horizontal: "center",
									vertical: "center",
								},
								//设置边框
								border: {
									top: { style: "thin" },
									right: { style: "thin" },
									bottom: { style: "thin" },
									left: { style: "thin" },
								},
							};
						}
					}
					//设置第一行样式 第一行合并了五个单元格 因此只用设置A1
					if (key == "A1" || key == "B1" || key == "C1" || key == "D1") {
						workSheet[key].s = {
							fill: { fgColor: { rgb: "c0c0c0" } },
							//设置字体
							font: {
								name: "Arial",
								sz: 10,
								bold: false,
								color: { rgb: "000000" },
							},
							//设置居中
							alignment: {
								horizontal: "center",
								vertical: "center",
							},
							//设置边框
							border: {
								top: { style: "thin" },
								right: { style: "thin" },
								bottom: { style: "thin" },
								left: { style: "thin" },
							},
						};
					}
				}
			});

			// 将工作表放入工作簿中
			// utils.book_append_sheet(workbook, worksheet, name, true);
			XLSXS.utils.book_append_sheet(workBook, workSheet, 'Sheet1')
			// 生成数据保存
			XLSXS.writeFile(workBook, `SheetJSVueAoO.xlsx`);
		}