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

前言

在实现业务功能中导出是必不可少的功能，接下来为大家演示在导出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>