Vue表格导出Excel数据，自定义表头，使用xlsx-style修饰

停留的章小鱼

已于 2023-08-07 08:24:40 修改

阅读量1.8k

点赞数 2

文章标签： vue.js excel 前端

于 2023-08-05 16:01:54 首次发布

本文链接：https://blog.csdn.net/2201_75870706/article/details/132119511

版权

继续上篇文章封装导出方法:Vue表格导出Excel数据（支持多级表头）

效果图：

1、安装xlsx-style依赖：

yarn add xlsx-style

2、安装node-polyfill-webpack-plugin依赖：

yarn add node-polyfill-webpack-plugin -D

解决报错：jszip is not a constructor

3、配置vue.config.js：！！！配置完重新运行程序

解决报错：Module not found: Error: Can't resolve 'fs' in \***\xlsx-style

和 Can‘t resolve ‘./cptable‘ in ‘xxx\node_modules_xlsx

const NodePolyfillPlugin = require("node-polyfill-webpack-plugin")

configureWebpack: {
	plugins: [
		new NodePolyfillPlugin()
	],
	resolve: {
		fallback: {
			fs: false
		}
	},
	externals: {
		'./cptable': 'var cptable',
	},
},

4、在根目录src文件夹下新建excel文件导入Blob.js文件：

/*! @source http://purl.eligrey.com/github/Blob.js/blob/master/Blob.js */
(function(view){view.URL=view.URL||view.webkitURL;if(view.Blob&&view.URL){try{new Blob;return}catch(e){}}var BlobBuilder=view.BlobBuilder||view.WebKitBlobBuilder||view.MozBlobBuilder||(function(view){var get_class=function(object){return Object.prototype.toString.call(object).match(/^\[object\s(.*)\]$/)[1]},FakeBlobBuilder=function BlobBuilder(){this.data=[]},FakeBlob=function Blob(data,type,encoding){this.data=data;this.size=data.length;this.type=type;this.encoding=encoding},FBB_proto=FakeBlobBuilder.prototype,FB_proto=FakeBlob.prototype,FileReaderSync=view.FileReaderSync,FileException=function(type){this.code=this[this.name=type]},file_ex_codes=("NOT_FOUND_ERR SECURITY_ERR ABORT_ERR NOT_READABLE_ERR ENCODING_ERR "+"NO_MODIFICATION_ALLOWED_ERR INVALID_STATE_ERR SYNTAX_ERR").split(" "),file_ex_code=file_ex_codes.length,real_URL=view.URL||view.webkitURL||view,real_create_object_URL=real_URL.createObjectURL,real_revoke_object_URL=real_URL.revokeObjectURL,URL=real_URL,btoa=view.btoa,atob=view.atob,ArrayBuffer=view.ArrayBuffer,Uint8Array=view.Uint8Array;FakeBlob.fake=FB_proto.fake=true;while(file_ex_code--){FileException.prototype[file_ex_codes[file_ex_code]]=file_ex_code+1}if(!real_URL.createObjectURL){URL=view.URL={}}URL.createObjectURL=function(blob){var type=blob.type,data_URI_header;if(type===null){type="application/octet-stream"}if(blob instanceof FakeBlob){data_URI_header="data:"+type;if(blob.encoding==="base64"){return data_URI_header+";base64,"+blob.data}else{if(blob.encoding==="URI"){return data_URI_header+","+decodeURIComponent(blob.data)}}if(btoa){return data_URI_header+";base64,"+btoa(blob.data)}else{return data_URI_header+","+encodeURIComponent(blob.data)}}else{if(real_create_object_URL){return real_create_object_URL.call(real_URL,blob)}}};URL.revokeObjectURL=function(object_URL){if(object_URL.substring(0,5)!=="data:"&&real_revoke_object_URL){real_revoke_object_URL.call(real_URL,object_URL)}};FBB_proto.append=function(data){var bb=this.data;if(Uint8Array&&(data instanceof ArrayBuffer||data instanceof Uint8Array)){var str="",buf=new Uint8Array(data),i=0,buf_len=buf.length;for(;i<buf_len;i++){str+=String.fromCharCode(buf[i])}bb.push(str)}else{if(get_class(data)==="Blob"||get_class(data)==="File"){if(FileReaderSync){var fr=new FileReaderSync;bb.push(fr.readAsBinaryString(data))}else{throw new FileException("NOT_READABLE_ERR")}}else{if(data instanceof FakeBlob){if(data.encoding==="base64"&&atob){bb.push(atob(data.data))}else{if(data.encoding==="URI"){bb.push(decodeURIComponent(data.data))}else{if(data.encoding==="raw"){bb.push(data.data)}}}}else{if(typeof data!=="string"){data+=""}bb.push(unescape(encodeURIComponent(data)))}}}};FBB_proto.getBlob=function(type){if(!arguments.length){type=null}return new FakeBlob(this.data.join(""),type,"raw")};FBB_proto.toString=function(){return"[object BlobBuilder]"};FB_proto.slice=function(start,end,type){var args=arguments.length;if(args<3){type=null}return new FakeBlob(this.data.slice(start,args>1?end:this.data.length),type,this.encoding)};FB_proto.toString=function(){return"[object Blob]"};FB_proto.close=function(){this.size=this.data.length=0};return FakeBlobBuilder}(view));view.Blob=function Blob(blobParts,options){var type=options?(options.type||""):"";var builder=new BlobBuilder();if(blobParts){for(var i=0,len=blobParts.length;i<len;i++){builder.append(blobParts[i])}}return builder.getBlob(type)}}(typeof self!=="undefined"&&self||typeof window!=="undefined"&&window||this.content||this));

5、在文件夹/utils/TableToExcel.js引入（全部代码，复制使用即可）：

import * as XLSX from "xlsx";
import XLSXS from "xlsx-style";
import FileSaver from "file-saver";
import '@/excel/Blob.js';

function datenum(v, date1904) {
	if (date1904) v += 1462;
	let epoch = Date.parse(v);
	return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
}

function sheet_from_array_of_arrays(data) {
	let ws = {};
	let range = {
		s: {
			c: 10000000,
			r: 10000000
		},
		e: {
			c: 0,
			r: 0
		}
	};
	for (let R = 0; R !== data.length; ++R) {
		for (let C = 0; C !== data[R].length; ++C) {
			if (range.s.r > R) range.s.r = R;
			if (range.s.c > C) range.s.c = C;
			if (range.e.r < R) range.e.r = R;
			if (range.e.c < C) range.e.c = C;
			let cell = {
				v: data[R][C]
			};
			if (cell.v == null) continue;
			let cell_ref = XLSX.utils.encode_cell({
				c: C,
				r: R
			});
			if (typeof cell.v === 'number') cell.t = 'n';
			else if (typeof cell.v === 'boolean') cell.t = 'b';
			else if (cell.v instanceof Date) {
				cell.t = 'n';
				cell.z = XLSX.SSF._table[14];
				cell.v = datenum(cell.v);
			} else cell.t = 's';

			ws[cell_ref] = cell;
		}
	}
	if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
	return ws;
}

function Workbook() {
	if (!(this instanceof Workbook)) return new Workbook();
	this.SheetNames = [];
	this.Sheets = {};
}

function s2ab(s) {
	let buf = new ArrayBuffer(s.length);
	let view = new Uint8Array(buf);
	for (let i = 0; i !== s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
	return buf;
}

function autoWidth(data) {
	/*设置worksheet每列的最大宽度*/
	const colWidth = data.map(row => row.map(val => {
		/*先判断是否为null/undefined*/
		if (val == null) {
			return {
				'wch': 10
			};
		}
		/*再判断是否为中文*/
		else if (val.toString().charCodeAt(0) > 255) {
			return {
				'wch': val.toString().length * 2 + 4
			};
		} else {
			return {
				'wch': val.toString().length + 4
			};
		}
	}))
	/*以第一行为初始值*/
	let result = colWidth[0];
	for (let i = 1; i < colWidth.length; i++) {
		for (let j = 0; j < colWidth[i].length; j++) {
			if (result[j]['wch'] < colWidth[i][j]['wch']) {
				result[j]['wch'] = colWidth[i][j]['wch'];
			}
		}
	}
	return result;
}

const uppercaseAlphabet = [];
for (let i = 65; i <= 90; i++) {
	const letter = String.fromCharCode(i);
	uppercaseAlphabet.push(letter);
}


function isKey(key) {//判断是不是表头
	let Y = false
	for (let i = 65; i <= 90; i++) {
		const letter = String.fromCharCode(i);
		if (key === `${letter}1`) {
			Y = true
		}
	}
	return Y
}

function export_json_to_excel(tHeader, jsonData, defaultTitle) {
	/* original data */
	let data = jsonData;
	data.unshift(tHeader);
	let ws_name = "SheetJS";

	let wb = new Workbook();
	let ws = sheet_from_array_of_arrays(data);
	ws['!cols'] = autoWidth(data); //设置单元格宽度自适应
	Object.keys(ws).forEach((key) => { //这里遍历单元格给单元格对象设置属性,s为控制样式的属性
		if (key.indexOf('!') < 0) {
			// console.log(key);

			if (isKey(key)) {
				// 设置表头文字加粗字号突出
				ws[key].s = {
					alignment: { //对齐方式
						horizontal: 'center', //水平居中
						vertical: 'center', //竖直居中
					},
					font: {
						sz: 13, // 字号
						name: '楷体', // 字体
						bold: true, //加粗
					},
					fill: {
						fgColor: { //单元格背景颜色
							rgb: "FFFFAA00"
						},
					}
				}
			} else {
				//为所有的单元格设置样式
				ws[key].s = {
					alignment: { //对齐方式
						horizontal: 'center', //水平居中
						vertical: 'center', //竖直居中
						wrapText: true, //自动换行
					},
					font: {
						sz: 12, // 字号
						name: '楷体' // 字体
					},
				}
			}

		}
	})

	wb.SheetNames.push(ws_name);
	wb.Sheets[ws_name] = ws;

	let wbout = XLSXS.write(wb, {
		bookType: 'xlsx',
		bookSST: false,
		type: 'binary'
	});
	let title = defaultTitle || '表单';
	saveAs(new Blob([s2ab(wbout)], {
		type: "application/octet-stream"
	}), title + ".xlsx")
}

export default {
	// 导出Excel表格
	// name表示生成excel的文件名     IDName表示表格的id
	exportExcel(name, IDName) {
		var fix_l = document.querySelector('.el-table__fixed'); //fixed 或者 fixed='left'
		var fix_r = document.querySelector('.el-table__fixed-right'); //fixed='right'
		var wb;
		// 以下代码解决table中使用fixed属性导致导出的数据重复问题
		if (fix_l) { //判断是否有浮动或者是左浮动
			wb = XLSX.utils.table_to_book(document.querySelector(IDName).removeChild(fix_l));
			document.querySelector(IDName).appendChild(fix_l);
		} else if (fix_r) { //判断是否有右浮动
			wb = XLSX.utils.table_to_book(document.querySelector(IDName).removeChild(fix_r));
			document.querySelector(IDName).appendChild(fix_r);
		} else if (fix_l && fix_r) { //左右浮动都存在
			wb = XLSX.utils.table_to_book(document.querySelector(IDName).removeChild(fix_l).removeChild(fix_r));
			document.querySelector(IDName).appendChild(fix_l).appendChild(fix_r);
		} else { //没有浮动
			wb = XLSX.utils.table_to_book(document.querySelector(IDName))
		}
		// var wb = XLSX.utils.table_to_book(document.querySelector(IDName))
		var wbout = XLSX.write(wb, {
			bookType: 'xlsx',
			bookSST: true,
			type: 'array'
		})
		try {
			FileSaver.saveAs(new Blob([wbout], {
				type: 'application/octet-stream'
			}), `${name}.xlsx`);
		} catch (e) {
			if (typeof console !== 'undefined') console.log(e, wbout)
		}
		return wbout;
	},

	// 导出Excel表格自定义标头和内容
	// excelData表示el-table数据       tableName表示生成excel的文件名
	export2Excel(excelData, excelObj, tableName) {
		require.ensure([], () => {
			// 导出的excel的表头字段可自定义
			let tHeader = []
			// 对象属性，对应于tHeader，即prop的值，可自定义导出字段
			let filterVal = []
			for (let i in excelObj) {
				tHeader.push(i)
				filterVal.push(excelObj[i])
			}
			// 格式转换数据
			const data = excelData.map(v => filterVal.map(j => v[j]))
			// console.log(data);
			export_json_to_excel(tHeader, data, tableName) // 导出的表格名称，可自定义
		})
	},
}

6、全局引入注册main.js：

import TableToExcel from '@/utils/TableToExcel';
Vue.prototype.$TableToExcels = TableToExcel

7、页面使用：

exportBtn() {
	//导出
	// this.$TableToExcels.exportExcel('导出的Excel名', '#table的id名')

	let excelData = this.tableData//导出的所有数据集合
	let excelObj = { //自定义表头 自定义对应的字段值 即prop的值
		'所属疗区':'OrgName',
		'坐落名称':'StandName',
		'土地总面积(m²)':'StandArea',
		'坐标所在地':'StandEncode'
	}
	this.$TableToExcels.export2Excel(excelData,excelObj,'导出的Excel名')
},

另外使用xlsx-style还可以设置单元格的其他的格式，可以参考这篇文章，很全。
JavaScript导出excel文件，并修改文件样式