JavaScript:使用xlsx-style插件导出多个sheet

参考：

JavaScript:在vue里使用xlsx-style插件创建带样式的excel文件_粉末的沉淀的博客-CSDN博客

<template>
	<div style="width: 400px; margin:100px auto;display: flex;justify-content: space-around;" >
		<button @click="exportExcel">导出单个sheet</button>
		<button @click="exportExcel2">导出多个sheet</button>
		<table id="table">
			<thead>
				<tr>
					<td colspan="4">人员信息表</td>
				</tr>
			</thead>
			<thead>
				<tr>
					<td>序号</td>
					<td>姓名</td>
					<td>年龄</td>
					<td>兴趣</td>
				</tr>
			</thead>
			<tbody>
				<tr>
					<td>1</td>
					<td>张三</td>
					<td>18</td>
					<td>打游戏</td>
				<tr>
					<td>2</td>
					<td>李四</td>
					<td>88</td>
					<td>看电影</td>
				</tr>
				<tr>
					<td>3</td>
					<td>王五</td>
					<td>81</td>
					<td>睡觉</td>
				</tr>
			</tbody>
		</table>
		
	</div>
</template>
<script>
	import moment from 'moment';
	export default {
		data() {
			return {
				aoa:[
					['人员信息表'],
					['姓名','性别','年龄'],
					['张三','男',25],
					['李四','女',20],
					['王五','男',23],
					['赵六','女',21]
				],
				json:[
					{name:'姓名',sex:'性别',age:'年龄'},
					{name:'赵丽',sex:'女',age:'20'},
				]
			}
		},
		methods: {
			exportExcel() {
				var table = document.querySelector("#table");
				var sheet = XLSX2.utils.table_to_sheet(table); //将一个table对象转换成一个sheet对象
				//这个就是修改格式的代码
				sheet["A1"].s = {
					font: {
						sz: 14,
						bold: true,
						color: {
							rgb: "0000FF"
						}
					},
					alignment: {
					                horizontal: "center",
					                vertical: "center",
					                wrap_text: true
					            },
					fill: {
						bgColor: {
							indexed: 64
						},
						fgColor: {
							rgb: "FFFF00"
						}
					}
				}; //<====设置xlsx单元格样式
				const workbook = sheet2blob(sheet,'人员信息表')
				let fileName = '导出单个sheet-'+moment().format('YYYYMMDDHHmmss')+'.xlsx'
				openDownloadDialog(workbook, fileName);
			},
			exportExcel2() {
				var table = document.querySelector("#table");
				var sheet1 = XLSX2.utils.table_to_sheet(table); //将一个table对象转换成一个sheet对象
				var sheet2 = XLSX2.utils.aoa_to_sheet(this.aoa);
				var sheet3 = XLSX2.utils.json_to_sheet(this.json);
				sheet2['!merges'] = [
					// 设置A1-C1的单元格合并
					// s表示start，e表示end，r表示row，c表示column
					{s: {r: 0, c: 0}, e: {r: 0, c: 2}}
				];
				// json比较特殊，表头不在json数据里定义，要在sheet这里设置A1单元格的值
				sheet3['A1'].v = '人员信息表';
				sheet3['!merges'] = [
					// 设置A1-C1的单元格合并
					// s表示start，e表示end，r表示row，c表示column
					{s: {r: 0, c: 0}, e: {r: 0, c: 2}}
				];
				//这个就是修改格式的代码
				sheet1["A1"].s = {
					font: {
						sz: 14,
						bold: true,
						color: {
							rgb: "0000FF"
						}
					},
					alignment: {
					                horizontal: "center",
					                vertical: "center",
					                wrap_text: true
					            },
					fill: {
						bgColor: {
							indexed: 64
						},
						fgColor: {
							rgb: "FFFF00"
						}
					}
				}; //<====设置xlsx单元格样式
				const wb = XLSX2.utils.book_new();
				  XLSX2.utils.book_append_sheet(wb, sheet1, 'table');
				  XLSX2.utils.book_append_sheet(wb, sheet2, 'aoa');
				  XLSX2.utils.book_append_sheet(wb, sheet3, 'json');
				  const workbook = workbook2blob(wb);
				  // 导出最后的总表
				let fileName = '导出多个sheet-'+moment().format('YYYYMMDDHHmmss')+'.xlsx'
				openDownloadDialog(workbook, fileName);
			}
		}
	}
</script>
<style scoped>
	table {
		border-collapse: collapse;
	}

	td {
		border: 1px solid #999999
	}
	img {
		width: 20%;
	}
</style>

关键代码：

最终效果：

本文的案例代码可以在这里下载：

xlsx-style插件创建带样式的excel文件-Javascript文档类资源-CSDN下载