xlsx配合xlsx-style前端下载excel

package.json记录的安装版本

 "xlsx": "^0.17.1",
 "xlsx-style": "^0.8.13"

 某个.vue文件

引入

import XLSX from 'xlsx';
import XLSXStyle from 'xlsx-style'

点击下载按钮的方法

async handleClick(query = this.queryInfo) {
	// 格式化部门信息
	const departmentFormatFilter = this.$options.filters['departmentFormat'];
	// 格式化星期信息
	const weekFormatDateTimeFilter = this.$options.filters['weekFormatDateTime'];
	let agendaIdList = query.agendaIdList;//数据源

	// 定义一个导出对象
	let xlsxList = [['日程统计'], ['序号', '部门', '姓名', '开始日期', '', '主题', '地点', '描述']];
	let arr1 = [], arr2 = [], arr3 = [];
	console.log('agendaIdList',agendaIdList)
	//开始对先对数据进行处理	
	agendaIdList.forEach((val=>{
		if(val.organizationIds.length === 1){
			arr1.push(val)
		}else{
			arr2.push(val)
		}
	}))
	arr1.sort(function compareFunction(a, b) {
		
		return a.organizationIds[0].localeCompare(b.organizationIds[0], "zh");
	})
	
	this.departmentInfo.forEach(item => {
		const arr1List = arr1.filter(onePiece => onePiece.organizationIds[0] === item.id )
		arr3 = arr3.concat(arr1List)
	})

	const finalArr = arr3.concat(arr2)
	// 数据源处理结束
	// 开始组装数据
	finalArr.forEach((agendaId, i)=>{
		if (agendaId.remark != null) {
			agendaId.remark = agendaId.remark.replace(/[\r\n]/g, '');
		}
		let tmp = weekFormatDateTimeFilter(agendaId.beginTime)
		let xlsxInfo = [
			i + 1,
			'' + departmentFormatFilter(agendaId.organizationIds, this.organizationList) || '',
			'' + this.getUserAccountNameWithId(agendaId.creater) || '',
			this.dateExtraFormat1(tmp) || '',
			this.dateExtraFormat2(tmp) || '',
			(agendaId.title || ''),
			agendaId.location || '',
			agendaId.remark || ''
		];
		// 导出对象填充
		xlsxList.push(xlsxInfo);
	})
	// 调用sheet方法 生成新的sheet对象
	var sheet = XLSX.utils.aoa_to_sheet(xlsxList);
	// 合并单元格 s是开始 e结束 c是列号 r是行号
	let tmpArr = [
		{
		  s: {
			  c: 0,
			  r: 0,
		  },
		  e: {
			  c: 7,
			  r: 0,
		  }
		},	
		{
		  s: {
			  c: 3,
			  r: 1,
		  },
		  e: {
			  c: 4,
			  r: 1,
		}
	}]
	let tmp = null
	let startIdx = 0
	// 为了后面合并单元格
	for(let i=1; i<=3; i++){
		xlsxList.forEach((val, idx)=>{
			if(idx == 1){
				tmp = val[i]
				startIdx = idx
			}
			if(idx > 1){
				if(tmp !== val[i]){
					tmpArr.push({s: {r: startIdx, c: i}, e: {r: idx-1, c: i}})
					tmp = val[i]
					startIdx = idx
				}
				if(idx == xlsxList.length-1){
					tmpArr.push({s: {r: startIdx, c: i}, e: {r: idx, c: i}})
				}
			}
		})
		tmp = null
		startIdx = 0
	}
	// A到I列宽度
	sheet['!cols'] = [
		{ wpx: 60 },
		{ wpx: 140},
		{ wpx: 80 },
		{ wpx: 80 },
		{ wpx: 60 },
		{ wpx: 200 },
		{ wpx: 200 },
		{ wpx: 200 },
		{ wpx: 200 }];
	// 合并单元格 赋值给这个属性
	sheet["!merges"] = tmpArr;
	
	// 开始样式处理
	// A B C D E 列表居中 设置字体 
	for(let item in sheet){
		if(!sheet.hasOwnProperty(item)){
			continue;
		}
		if(/^[A|B|C|D|E]/.test(item)){
			sheet[item].s = {
					font:{name:'Dialog',sz:12},
					alignment: { horizontal: "center", vertical: "center", wrap_text: true },
					border:{
						bottom:{style:'thin',color:"FF0000"},
						right:{style:'thin',color:"FF0000"}
					}
			}
		}
		if(/^[G|H|F]/.test(item)){
			sheet[item].s = {
					font:{name:'宋体',sz:12},
					alignment: { horizontal: "center", vertical: "center", wrap_text: true },
					border:{
						bottom:{style:'thin',color:"000000"},
						right:{style:'thin',color:"000000"}
					}
			}
		}
	}
	// 二级标题样式修改 A2 B2...
	for(let i = 0; i < 8; i++){
		const letterList = ['A','B','C','D','E','F','G','H']
		sheet[`${letterList[i]}2`].s = {
				border:{
						bottom:{style:'thin',color:"000000"},
						right:{style:'thin',color:"000000"}
				},
				font:{name:'Dialog',sz:12,color: { rgb: "FFFFFF" }},
				alignment: { horizontal: "center", vertical: "center", wrap_text: true },
				fill: { fgColor: { rgb: 'FF8000' } }
		}
	}
	// 一级标题样式
	sheet['A1'].s.border = {}
	sheet['A1'].s.font = {
							name:'Dialog',
							sz: 12,
							color: { rgb: "FFFFFF" }
						 }
	sheet['A1'].s.fill = { fgColor: { rgb: 'FF8000' } }
	
	this.sheet2blob2Download(sheet, '日程导出.xlsx');
},

下载方法

sheet2blob2Download(sheet, fileName) {
	var workbook = {
		SheetNames: ['sheet1'],
		Sheets: {
			'sheet1': Object.assign({}, sheet)
		}
	};
	// workbook.Sheets['sheet1'] = sheet;
	// 生成excel的配置项
	var wopts = {
		bookType: 'xlsx', // 要生成的文件类型
		bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
		type: 'binary'
	};
	// 这里使用xlsx-style 让样式生效
	var wbout = XLSXStyle.write(workbook, wopts);
	var blob = new Blob([s2ab(wbout)], {type:"application/octet-stream"}); // application/octet-stream
	// 字符串转ArrayBuffer
	function s2ab(s) {
		var buf = new ArrayBuffer(s.length);
		var view = new Uint8Array(buf);
		for (var i=0; i!==s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
		return buf;
	}

	//下载
	var href = URL.createObjectURL(blob);
	const eleLink = document.createElement('a')
	eleLink.download = fileName
	eleLink.style.display = 'none'
	eleLink.href = href
	document.body.appendChild(eleLink)
	eleLink.click()
}