前言

在實現業務功能中導出是必不可少的功能，接下來為大家演示在導出xlsx的時候的操作

效果圖

1、安裝

npm install xlsx -S
npm install file-saver

2、在頁面中引用

值得注意的是再引用xlsx的時候vue3和vue2寫法不同
vue2：import xlsx from ‘xlsx’
vue3：import * as XLSX from ‘xlsx’

3、封裝工具類（excel.js）

import { saveAs } from 'file-saver'
import * as 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 = +cellValueranges.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)}})if (rowspan || colspan) {rowspan = rowspan || 1colspan = colspan || 1ranges.push({s: {r: R,c: outRow.length},e: {r: R + rowspan - 1,c: outRow.length + colspan - 1}})}outRow.push(cellValue !== '' ? cellValue : null)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 += 1462const epoch = Date.parse(v)return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000)
}function sheetFromArrayOfArrays (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 exportTableToExcel (id) {//獲取table的dom節點const theTable = document.getElementById(id)//獲取table的所有數據const oo = generateArray(theTable)const ranges = oo[1]const data = oo[0]//設置導出的文件名稱const wsName = 'SheetJS'//設置工作文件const wb = new Workbook()//設置sheet內容const ws = sheetFromArrayOfArrays(data)//設置多級表頭ws['!merges'] = ranges//設置sheet的名稱  可push多個wb.SheetNames.push(wsName)//設置sheet的內容wb.Sheets[wsName] = 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 exportJsonToExcel ({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 wsName = 'SheetJS'// 設置sheet數據const ws = sheetFromArrayOfArrays(data)const tsName = 'Sheetts'const ts = sheetFromArrayOfArrays(data)//設置多級表頭if (merges.length > 0) {if (!ws['!merges']) ws['!merges'] = []merges.forEach((item) => {ws['!merges'].push(XLSX.utils.decode_range(item))})}//設置自適應行寬if (autoWidth) {const colWidth = data.map((row) =>row.map((val) => {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(tsName)wb.Sheets[tsName] = tswb.SheetNames.push(wsName)wb.Sheets[wsName] = wsconsole.log('ws', 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}`)
}

4、在vue中使用

<template><div class="export-excel-container"><vab-query-form><vab-query-form-left-panel :span="24"><el-form :inline="true" label-width="100px" @submit.prevent><el-form-item label="文件名"><el-input v-model="filename" placeholder="請輸出要導出文件的名稱" /></el-form-item><el-form-item label="文件類型"><el-select v-model="bookType"><el-option v-for="item in options" :key="item" :label="item" :value="item" /></el-select></el-form-item><el-form-item label="自動列寬"><el-radio-group v-model="autoWidth"><el-radio :label="true">是</el-radio><el-radio :label="false">否</el-radio></el-radio-group></el-form-item><el-form-item><el-button type="primary" @click="handleDownload">導出 Excel</el-button></el-form-item></el-form></vab-query-form-left-panel></vab-query-form><el-table v-loading="listLoading" border :data="list"><el-table-column align="center" label="序號" width="55"><template #default="{ $index }">{{ $index + 1 }}</template></el-table-column><el-table-column align="center" label="標題"><template #default="{ row }">{{ row.title }}</template></el-table-column><el-table-column align="center" label="作者"><template #default="{ row }"><el-tag>{{ row.author }}</el-tag></template></el-table-column><el-table-column align="center" label="訪問量"><template #default="{ row }">{{ row.pageViews }}</template></el-table-column><el-table-column align="center" label="時間"><template #default="{ row }"><span>{{ row.datetime }}</span></template></el-table-column></el-table></div>
</template><script>
// import { getList } from '@/api/table'export default {name: 'ExportExcel',data () {return {list: [],listLoading: true,downloadLoading: false,filename: '',autoWidth: true,bookType: 'xlsx',options: ['xlsx', 'csv', 'txt']}},created () {this.fetchData()},methods: {async fetchData () {this.listLoading = true// const { data } = await getList()// const { list } = dataconst list = [{uuid: '6Ee7E1dc-d7B2-892C-f880-beDCcE9Fb0A7',id: '150000199410253945',title: 'Vncrburdy Kqnxftj',description: '反工能頭書斷卻在政始保展通數。',status: 'deleted',author: '武敏',datetime: '2010-06-18 02:49:53',pageViews: 519,img: 'https://gitee.com/chu1204505056/image/raw/master/table/vab-image-7.jpg',switch: false,percent: 99,rate: 4,percentage: 66},{uuid: 'cb41dCdD-f6f2-fDbC-6ebA-981e2A7bcFbA',id: '350000200904177578',title: 'Nkfehdu Ywjgmgvy',description: '軍族切飛公叫象熱各高長則主少。',status: 'deleted',author: '康娜',datetime: '1998-09-19 04:51:45',pageViews: 1764,img: 'https://gitee.com/chu1204505056/image/raw/master/table/vab-image-18.jpg',switch: true,percent: 98,rate: 3,percentage: 28},{uuid: '12D12deF-f5Dc-0aBf-26E7-85f788fADf79',id: '320000197711238151',title: 'Usplivuxjz',description: '太型經經約率放金本屬東率確據響查十。',status: 'draft',author: '蔡霞',datetime: '2000-09-10 12:48:00',pageViews: 4756,img: 'https://gitee.com/chu1204505056/image/raw/master/table/vab-image-20.jpg',switch: false,percent: 95,rate: 3,percentage: 14}]this.list = listthis.listLoading = false},handleDownload () {this.downloadLoading = trueimport('@/utils/excel').then((excel) => {const tHeader = ['Id', 'Title', 'Author', 'Readings', 'Date']const filterVal = ['id', 'title', 'author', 'pageViews', 'datetime']const list = this.listconst data = this.formatJson(filterVal, list)excel.exportJsonToExcel({header: tHeader,data,filename: this.filename,autoWidth: this.autoWidth,bookType: this.bookType})this.downloadLoading = false})},formatJson (filterVal, jsonData) {return jsonData.map((v) =>filterVal.map((j) => {return v[j]}))}}
}
</script>