一、前言

后臺管理項目有時會有需要前端導出excel表格的功能，有時還需要導出多個sheet，并給每個sheet重新命名，下面我們就來實現一下。

二、實現效果圖

三、實現步驟

1、 安裝

命令行安裝 xlsx 和 file-saver

npm install xlsx -S
npm 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.innerTextif (cellValue !== '' && cellValue === +cellValue) cellValue = +cellValue// Skip rangesranges.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 Spanif (rowspan || colspan) {rowspan = rowspan || 1colspan = colspan || 1ranges.push({s: {r: R,c: outRow.length},e: {r: R + rowspan - 1,c: outRow.length + colspan - 1}})}// Handle ValueoutRow.push(cellValue !== '' ? cellValue : null)// Handle Colspanif (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 += 1462const 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 = Rif (range.s.c > C) range.s.c = Cif (range.e.r < R) range.e.r = Rif (range.e.c < C) range.e.c = Cconst cell = {v: data[R][C]}if (cell.v === null) continueconst 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) & 0xffreturn 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寫入到xlsxconst wbout = XLSX.write(wb, {bookType: 'xlsx',bookSST: false,type: 'binary'})//通過s2absaveAs(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'})//導出xlsxsaveAs(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 = trueconst 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