做网站哪个公司可以做,微商城手机网站设计,百度助手下载,正规网站优化哪个公司好前端开发之xlsx的使用和实例 前言效果图1、安装2、在页面中引用3、封装工具类#xff08;excel.js#xff09;4、在vue中使用 前言
在实现业务功能中导出是必不可少的功能#xff0c;接下来为大家演示在导出xlsx的时候的操作
效果图 1、安装 npm install xlsx -S
npm inst… 前端开发之xlsx的使用和实例 前言效果图1、安装2、在页面中引用3、封装工具类excel.js4、在vue中使用 前言
在实现业务功能中导出是必不可少的功能接下来为大家演示在导出xlsx的时候的操作
效果图 1、安装 npm install xlsx -S
npm install file-saver2、在页面中引用 值得注意的是再引用xlsx的时候vue3和vue2写法不同 vue2import xlsx from ‘xlsx’ vue3import * as XLSX from ‘xlsx’ 3、封装工具类excel.js
import { saveAs } from file-saver
import * as XLSX from xlsxfunction 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 nelse if (typeof cell.v boolean) cell.t belse if (cell.v instanceof Date) {cell.t ncell.z XLSX.SSF._table[14]cell.v datenum(cell.v)} else cell.t sws[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 Sheettsconst 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中使用
templatediv classexport-excel-containervab-query-formvab-query-form-left-panel :span24el-form :inlinetrue label-width100px submit.preventel-form-item label文件名el-input v-modelfilename placeholder请输出要导出文件的名称 //el-form-itemel-form-item label文件类型el-select v-modelbookTypeel-option v-foritem in options :keyitem :labelitem :valueitem //el-select/el-form-itemel-form-item label自动列宽el-radio-group v-modelautoWidthel-radio :labeltrue是/el-radioel-radio :labelfalse否/el-radio/el-radio-group/el-form-itemel-form-itemel-button typeprimary clickhandleDownload导出 Excel/el-button/el-form-item/el-form/vab-query-form-left-panel/vab-query-formel-table v-loadinglistLoading border :datalistel-table-column aligncenter label序号 width55template #default{ $index }{{ $index 1 }}/template/el-table-columnel-table-column aligncenter label标题template #default{ row }{{ row.title }}/template/el-table-columnel-table-column aligncenter label作者template #default{ row }el-tag{{ row.author }}/el-tag/template/el-table-columnel-table-column aligncenter label访问量template #default{ row }{{ row.pageViews }}/template/el-table-columnel-table-column aligncenter label时间template #default{ row }span{{ row.datetime }}/span/template/el-table-column/el-table/div
/templatescript
// import { getList } from /api/tableexport 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
