前端实现 excel 数据导出，封装方法支持一次导出多个Sheet_前端导出带有多个echarts的excel

一、前言

后台管理项目有时会有需要前端导出excel表格的功能，有时还需要导出多个sheet，并给每个sheet重新命名，下面我们就来实现一下。

二、实现效果图

三、实现步骤

1、 安装

命令行安装 xlsx 和 file-saver

npm install xlsx -Snpm install file-saver

注意：vue2和vue3中引入xlsx写法不同

vue2：import xlsx from ‘xlsx’vue3：import * as XLSX from ‘xlsx’

2、封装工具类

utils文件夹中新建exportToExcel.js文件封装公用导出excel方法。

exportToExcel.js

/* eslint-disable */import { saveAs } from \'file-saver\'import XLSX from \'xlsx\'function generateArray (table) { const out = [] const rows = table.querySelectorAll(\'tr\') const ranges = [] for (let R = 0; R < rows.length; ++R) { const outRow = [] const row = rows[R] const columns = row.querySelectorAll(\'td\') for (let C = 0; C < columns.length; ++C) { const cell = columns[C] let colspan = cell.getAttribute(\'colspan\') let rowspan = cell.getAttribute(\'rowspan\') let cellValue = cell.innerText if (cellValue !== \'\' && cellValue === +cellValue) cellValue = +cellValue // Skip ranges ranges.forEach(function (range) { if ( R >= range.s.r && R <= range.e.r && outRow.length >= range.s.c && outRow.length <= range.e.c ) { for (let i = 0; i <= range.e.c - range.s.c; ++i) outRow.push(null) } }) // Handle Row Span if (rowspan || colspan) { rowspan = rowspan || 1 colspan = colspan || 1 ranges.push({ s: { r: R, c: outRow.length }, e: { r: R + rowspan - 1, c: outRow.length + colspan - 1 } }) } // Handle Value outRow.push(cellValue !== \'\' ? cellValue : null) // Handle Colspan if (colspan) for (let k = 0; k < colspan - 1; ++k) outRow.push(null) } out.push(outRow) } return [out, ranges]}function datenum (v, date1904) { if (date1904) v += 1462 const epoch = Date.parse(v) return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000)}function sheet_from_array_of_arrays (data) { const ws = {} const 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 const cell = { v: data[R][C] } if (cell.v === null) continue const cellRef = 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[cellRef] = 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) { const buf = new ArrayBuffer(s.length) const view = new Uint8Array(buf) for (let i = 0; i !== s.length; ++i) view[i] = s.charCodeAt(i) & 0xff return buf}//导出单个excel的方法export function export_table_to_excel (id) {//获取table的dom节点 const theTable = document.getElementById(id) //获取table的所有数据 const oo = generateArray(theTable) const ranges = oo[1] const data = oo[0] //设置导出的文件名称 const ws_name = \'SheetJS\' //设置工作文件 const wb = new Workbook() //设置sheet内容 const ws = sheet_from_array_of_arrays(data) //设置多级表头 ws[\'!merges\'] = ranges //设置sheet的名称 可push多个 wb.SheetNames.push(ws_name) //设置sheet的内容 wb.Sheets[ws_name] = ws //将wb写入到xlsx const wbout = XLSX.write(wb, { bookType: \'xlsx\', bookSST: false, type: \'binary\' }) //通过s2ab saveAs(new Blob([s2ab(wbout)], { type: \'application/octet-stream\' }), \'test.xlsx\' )}export function export_json_to_excel ({ multiHeader = [], header, data, filename, merges = [], autoWidth = true, bookType = \'xlsx\'} = {}) {//文件名称 filename = filename || \'excel-list\' //文件数据 data = [...data] //将表头添加到数据的顶部 data.unshift(header) for (let i = multiHeader.length - 1; i > -1; i--) { data.unshift(multiHeader[i]) }//设置工作文本 const wb = new Workbook()//设置sheet名称 const ws_name = \'SheetJS\' // 设置sheet数据 const ws = sheet_from_array_of_arrays (data)//设置多级表头 if (merges.length > 0) { if (!ws[\'!merges\']) ws[\'!merges\'] = [] merges.forEach((item) => { ws[\'!merges\'].push(XLSX.utils.decode_range(item)) }) }//设置自适应行宽 if (autoWidth) { /* 设置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 } } else { return { \'wch\': val.toString().length } } }) ) /* 以第一行为初始值 */ const 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\'] } } } ws[\'!cols\'] = result } //将数据添加到工作文本 wb.SheetNames.push(ws_name) wb.Sheets[ws_name] = ws //生成xlsx bookType生成的文件类型 const wbout = XLSX.write(wb, { bookType: bookType, bookSST: false, type: \'binary\' }) //导出xlsx saveAs(new Blob([s2ab(wbout)], { type: \'application/octet-stream\' }), `${filename}.${bookType}`) } /** * 导出多个sheet的excel表格 * @param sheetsData [{sheetName:\'sheet1\', header:[\'名称1\',\'名称2\'], data: [value1, value2]}, {sheetName:\'sheet2\', header:[\'名称1\',\'名称2\'], data: [value1, value2]}] * | 名称1 | 名称2 | * | value1 | value2 | */export function exportMultiSheetToExcel({ sheetsData, filename = \'excel-file\' }) { const wb = new Workbook() sheetsData.forEach((sheet, index) => { const { data, sheetName = `Sheet${index + 1}`, // 默认值 multiHeader = [], header, merges = [], autoWidth = true, } = sheet // 处理数据，添加多级表头 const sheetData = [...data] if (header) sheetData.unshift(header) // 过滤非法字符 const safeSheetName = sanitizeSheetName(sheetName) for (let i = multiHeader.length - 1; i > -1; i--) { sheetData.unshift(multiHeader[i]) } // 创建工作表 const ws = sheet_from_array_of_arrays(sheetData) // 处理合并单元格 if (merges.length > 0) { if (!ws[\'!merges\']) ws[\'!merges\'] = [] merges.forEach((item) => { ws[\'!merges\'].push(XLSX.utils.decode_range(item)) }) } // 处理自适应宽度 if (autoWidth) { /* 设置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, }  } else { return { wch: val.toString().length, }  } }) ) /* 以第一行为初始值 */ const 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\']  } } } ws[\'!cols\'] = result } // 添加工作表到工作簿 wb.SheetNames.push(safeSheetName) wb.Sheets[safeSheetName] = ws }) // 生成并保存Excel文件 const wbout = XLSX.write(wb, { bookType: \'xlsx\', bookSST: false, type: \'binary\', }) saveAs( new Blob([s2ab(wbout)], { type: \'application/octet-stream\', }), `${filename}.xlsx` )}// 过滤Excel工作表名称中的非法字符function sanitizeSheetName(name) { if (!name) return \'Sheet\' return name .replace(/[\\\\/:*?\"[\\]]/g, \'_\') // 将非法字符串替换为下划线 .substring(0, 31) // Excel工作表名称最大长度为31个字符}

3. 在vue2中使用excel单个sheet导出

<template> <div class=\"app-container\"> <el-button :loading=\"exportLoading\" type=\"primary\" plain icon=\"el-icon-download\" @click=\"handleExport\"> 数据导出 </el-button> </div></template><script>export default { name:\'\', data(){ return { exportLoading: false, // 模拟接口返回数据 tableProp: [\'2025-01\', \'2025-02\', \'2025-03\', \'2025-04\', \'2025-05\', \'2025-06\', \'2025-07\'], resTableData: [{ // 模拟接口返回数据 \'2025-01\':\'47.21\', \'2025-02\':\'40.49\', \'2025-03\':\'43.87\', \'2025-04\':\'40.65\', \'2025-05\':\'40.30\', \'2025-06\':\'37.95\', \'2025-07\':\'38.51\', \'2025-08\':\'47.21\', odr: 1, tableThName: \'（厂商）手续费/服务费总计\', tableThProp: \'amortizeProvisionChange\', 合计: \'336.69\' },{ \'2025-01\':\'4528,826.01\', \'2025-02\':\'4090,552.52\', \'2025-03\':\'4528,826.01\', \'2025-04\':\'4382,734.85\', \'2025-05\':\'44528,826.01\', \'2025-06\':\'4382,734.85\', \'2025-07\':\'4529,483.94\', \'2025-08\':\'47.21\', odr: 2, tableThName: \'（其它）手续费/服务费总计\', tableThProp: \'amortizeOtherChange\', 合计: \'30,971,984.19\' }] } }, menthods: { async handleExport () { try{ await this.$comfirm(\'确认导出数据吗？\') this.exportLoading = true const tHeader = [\'项目名称\', ...this.tableProp] const filterVal = [\'tableThName\', ...this.tableProp] const data = await this.formatAmoJson(filterVal) if(!data) { return (this.exportLoading = false) } const excel = await import(\'@/utils/excel\') excel.export_json_to_excel({ header: tHeader, data, filename: \'摊销计提\' }) } catch (error) { console.log(error) } finally{ this.exportLoading = false } }, async formatAmoJson(filterVal){ return this.resTableData.map(v => filterVal.map(j => { return v[j] })) } }}

效果图如下

4. 在vue2中实现excel表格中多个sheet导出

<template> <div class=\"app-container\"> <el-button type=\"primary\" plain icon=\"el-icon-download\" @click=\"handleExport\"> 多个sheet导出 </el-button> </div></template><script>export default { name:\'\', data(){ return { // 模拟接口返回数据 resTableData: [ { \"detailName\": \'测试-查询停息放款明细\', \"reportDetailUid\": \'357896657400820001\', \"headerEn\" : \"GRANT_NUM, PLN_REPYMT, ODUE_DYS, MTNR\", \"headerCn\" : \"放款编号, 计划还款日, 逾期天数, 维护人员\", \"odr\" : 1, \"result\":{ \"curPage\": 1, \"pageSize\": 20, \"total\": 130, \"data\": [ [ \"0000000000153\", \"2021-03-20\", \"285\", \"1170000001\" ],[ \"0000000000159\", \"2021-01-25\", \"768\", \"1170000001\" ],[ \"0000000000198\", \"2021-02-08\", \"476\", \"1170000001\" ],[ \"0000000000205\", \"2021-03-15\", \"285\", \"1170000001\" ],[ \"0000000000219\", \"2021-01-15\", \"285\", \"1170000001\" ], ] } }, { \"detailName\": \'测试2-查询停息放款明细22\', \"reportDetailUid\": \'357896657400820001\', \"headerEn\" : \"ETL_DT, PRJ_NUM, CTR_NUM, MTNR\", \"headerCn\" : \"ETL,项目编号, 合同编号, 放款编号, 维护人员\", \"odr\" : 4, \"result\":{ \"curPage\": 1, \"pageSize\": 20, \"total\": 4908, \"data\": [ [ \"0000000000153\", \"2021-03-20\", \"285\", \"1170000001\" ],[ \"0000000000159\", \"2021-01-25\", \"768\", \"1170000001\" ],[ \"0000000000198\", \"2021-02-08\", \"476\", \"1170000001\" ],[ \"0000000000205\", \"2021-03-15\", \"285\", \"1170000001\" ],[ \"0000000000219\", \"2021-01-15\", \"285\", \"1170000001\" ], ] } } ] } }, menthods: { async handleExport () { const sheetsData = this.resTableData.map((item) => { const sheetItem = { sheetName: item.detailName, header: item.headerCn.split(\',\'), data: item.result } return sheetItem }) const excel = await import(\'@/utils/Export2Excel\') excel.exportMultiSheetToExcel({ sheetsData, filename: \'摊销计提详情\' }) }

效果图如下

参考：前端开发之xlsx的使用和实例，并导出多个sheet