使用ExcelJS实现excel的前端导出功能(Vue3+TS)

ExcelJS：读取，操作并写入电子表格数据和样式到 XLSX 和 JSON 文件。一个 Excel 电子表格文件逆向工程项目。
github中文文档：https://github.com/exceljs/exceljs/blob/master/README_zh.md

封装excel.ts工具文件

import ExcelJS from 'exceljs'// 导入参数数据类型export interface importExcelType {// 第 i 张工作表i?: number;// 表格表头字段数组header: readonly any[];}// 导出参数数据类型export interface exportExcelType {// 数据data: { [key: string]: any }[];// 文件名name: string;// 表头字段header: string[];// 表头字段对应中文customHeader: string[];// 工作表名sheetName?: string;// 标题title?: string;// 小标题subTitle?: string;// 工作表保护密码password?: string;// 对齐方式alignment?: Partial;// 合并单元格mergeList?: mergeListType[];// 标题样式titleStyle?: Partial;// 小标题样式subTitleStyle?: Partial;// 表头样式headerStyle?: Partial;// 单元格统一样式cellStyle?: Partial;}// 合并单元格数据类型export interface mergeListType {startRow: number;startColumn: number;endRow: number;endColumn: number;}// exceljs相关方法export class Excel {blob?: Blob; // 导入的blob文件worksheet?: ExcelJS.Worksheet;// 当前工作表header: string[]; // 表头字段数组constructor(blob?: Blob) {this.blob = blob;this.worksheet = undefined;this.header = [];}// blob转ArrayBuffer（用于后续生成文件数据）private readFile(): Promise {return new Promise((resolve, reject) => {let reader = new FileReader();if (!this.blob) {reject('上传文件异常!');} else {reader.readAsArrayBuffer(this.blob);reader.onload = (ev) => {resolve(ev.target!.result as ArrayBuffer);};}});}// 导入excel文件获取workbook（workbook属性方法参考exceljs文档）public async getWorkBook(): Promise {let buffer = await this.readFile();const workbook = new ExcelJS.Workbook();return await workbook.xlsx.load(buffer);}// 获取第i张工作表i 工作表序号public async getWorkSheet(i: number = 0): Promise {const workbook = await this.getWorkBook();return workbook.getWorksheet(i);}// 将 excel 第i张工作表的数据转为对象数据// {Promise<Record[]>}传入表头作为字段的对象数组(每个元素对象对应每一行)public async importExcel(options: importExcelType): Promise<Record[]> {const { i = 1, header } = options;const workbook = await this.getWorkBook();const worksheet = workbook.getWorksheet(i);// excel导入后返回的数组const excelList: Record[] = [];worksheet.getSheetValues().filter(temp => !!temp?.length).forEach(item => {// 移除空行// 移除每行首个空元素(item as string[]).shift();// 定义临时对象存储每一行内容let tempObj: Record = {};(item as string[]).forEach((item2, index2) => {tempObj[header[index2]] = item2;})excelList.push(tempObj);})return excelList} // 导出excel，参数信息参考exceljs // data 数据 // name 文件名 // header 表头字段 // customHeader 表头字段对应中文 // sheetName 工作表名 // title 标题 // subTitle 副标题（日期） // password 冻结表格密码 // mergeList 合并单元格数组 // titleStyle 标题样式(按需补充方法) // subTitleStyle 小标题样式(按需补充方法) // headerStyle 表头字段样式(按需补充方法) // cellStyle 单元格样式(按需补充方法)public async exportExcel(options: exportExcelType): Promise {const { data, name, header, customHeader, sheetName = 'sheet1', title = '', subTitle = '', password = '', mergeList = [], titleStyle, subTitleStyle, headerStyle, cellStyle } = options;// 创建工作簿const workbook = new ExcelJS.Workbook();workbook.creator = '侃侃';workbook.created = new Date();// 添加sheetthis.worksheet = workbook.addWorksheet(sheetName, { properties: { tabColor: { argb: 'FF00FF00' } } });this.header = header;// 表头行序号let headerRowId = 1;if (!!title) headerRowId++;if (!!subTitle) headerRowId++;// 插入单元格数据this.setCells(data, customHeader, cellStyle);// 插入大标题this.getTitle(title, titleStyle);// 插入小标题this.getSubTitle(subTitle, subTitleStyle);// 处理表头this.setHeaderStyle(headerRowId, data, headerStyle);// 更多处理this.handleDealExcel(password, mergeList, headerRowId);// 导出excel(此处也可用file-saver将blob导出到excel文件fs.saveAs(blob, name+'.xlsx');)workbook.xlsx.writeBuffer().then((data) => {let blob = new Blob([data], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });const a = document.createElement('a');a.href = URL.createObjectURL(blob);a.download = name + '.xlsx';document.body.appendChild(a);a.click();document.body.removeChild(a);window.URL.revokeObjectURL(a.href);});} // 合并单元格数组 // merge1 需要合并的行/列起止对象数组1 // 例：[{ startRow: 1, endRow: 1},{ startRow: 10, endRow: 10}] // merge2 需要合并的列/行起止对象数组2 // 例：[{ startColumn: 1, endColumn: 2 }] // mergeArr 合并后数组 // 例：[{ startRow: 1, endRow: 1, startColumn: 1, endColumn: 2},{ startRow: 10, endRow: 10, startColumn: 1, endColumn: 2}]public merge(merge1: Partial[], merge2: Partial[]): mergeListType[] {const mergeArr: any[] = [];merge1.forEach(item1 => {mergeArr.push(...merge2.map(item2 => {return { ...item2, ...item1 };}))})return mergeArr;} // 单元格数据处理// data 表格数据 // customHeader表头中文字段private setCells(data: exportExcelType['data'], customHeader: string[], style?: Partial): void {// 设置列，插入中文表头const column: Partial[] = [];this.header.forEach((item, index) => {column.push({header: customHeader[index],key: item,width: style?.width || 25,})})this.worksheet!.columns = column;// 设置行，添加数据this.worksheet?.addRows(data);// 设置行高this.worksheet?.eachRow({ includeEmpty: true }, (row, rowNumber) => {row.height = style?.height || 20;})// 获取每一列数据，再依次对齐this.worksheet!.columns.forEach(column => {column.alignment = style?.alignment || { vertical: 'middle', horizontal: 'center', wrapText: true }})} // 添加大标题// title 标题 // style 标题样式private getTitle(title: string, style?: Partial): void {if (!!title) {// 插入标题this.worksheet?.spliceRows(1, 0, [title]);this.worksheet?.mergeCells(1, 1, 1, this.header.length);// 调整标题样式const titleRow = this.worksheet!.getRow(1);// 高度titleRow.height = style?.height || 40;// 字体设置titleRow.font = style?.font || {size: 20,bold: true}// 背景色titleRow.fill = style?.fill || {bgColor: {'argb': 'FFFFFF00'},type: 'pattern',pattern: 'none'}// 对齐方式titleRow.alignment = style?.alignment || {horizontal: "center",vertical: "middle"};}} // 添加小标题// subTitle 标题 // style 小标题样式private getSubTitle(subTitle: string, style?: Partial): void {if (!!subTitle) {this.worksheet?.spliceRows(2, 0, [subTitle]);this.worksheet?.mergeCells(2, 1, 2, this.header.length);// 调整标题样式const subtitleRow = this.worksheet!.getRow(2);// 高度subtitleRow.height = style?.height || 20;// 字体设置subtitleRow.font = style?.font || {size: 14,}// 背景色subtitleRow.fill = style?.fill || {bgColor: {'argb': 'FFFFFF00'},type: 'pattern',pattern: 'none'}// 对齐方式subtitleRow.alignment = style?.alignment || {horizontal: "right",vertical: "middle"};}} // 设置表头样式 // num 表头在第几行 // data 总数据 // style 表头样式private setHeaderStyle(num: number, data: any, style?: Partial): void {// 自动筛选器this.worksheet!.autoFilter = {from: {row: num,column: 1},to: {row: data.length,column: this.header.length}}// 给表头添加背景色let headerRow = this.worksheet!.getRow(num);headerRow!.height = style?.height || 30;// 通过 cell 设置背景色，更精准headerRow?.eachCell((cell) => {cell.fill = style?.fill || {type: 'pattern',pattern: 'solid',fgColor: { argb: 'dde0e7' },}cell.font = style?.font || {size: 12}})} // 其他内容处理 // password 密码 // mergeList 合并行列数组 // headerRowId表头行序号private handleDealExcel(password: string, mergeList: mergeListType[], headerRowId: number) {// 添加工作表保护if (!!password) {this.worksheet?.protect(password, {autoFilter: true,selectLockedCells: false,});}// 合并单元格mergeList.forEach(item => {// 行数为表格数据所在行行数+表头行序号headerRowIdconst startRow = item.startRow + headerRowId;const endRow = item.endRow + headerRowId;this.worksheet?.mergeCells(startRow, item.startColumn, endRow, item.endColumn);})// 冻结前几行this.worksheet!.views = [{ state: 'frozen', xSplit: 0, ySplit: headerRowId }];}}

表格页面调用excel工具文件

// export.vue导出import { Excel, type mergeListType } from '@/utils/excel';import { reactive } from 'vue';const tableData = reactive([{ id: 1, name: "张三", age: 18 },{ id: 2, name: "张三", age: 18 }])const header = ['id', 'name', 'age'];const customHeader = ['id', '姓名', '年龄'];const mergeList: mergeListType[] = reactive([{ startRow: 1, endRow: 2, startColumn: 3, endColumn: 3 }, { startRow: 3, endRow: 5, startColumn: 3, endColumn: 3 }])const exportExcel = () => {const excel = new Excel();excel.exportExcel({name: '表格文件名称',title: '表格标题',data: tableData,header,customHeader,mergeList})}