一、excel的上传

1.先定义el-upload

<template><div class="uploadBox"><!-- 返回主页 --><div class="homeBox"><router-link to="/"><el-tooltip content="返回列表页面" placement="top"><i class="el-icon-tickets"></i></el-tooltip></router-link></div><!-- 上传文件按钮 --><div class="buttonBox"><el-uploadactionaccept=".xlsx,.xls":auto-upload="false":show-file-list="false":on-change="handle"><el-button type="primary" slot="trigger">选取 Excel 文件</el-button></el-upload><el-button type="success" @click="submit">采集数据提交</el-button></div><!-- 解析出来的数据 --><div class="tableBox" v-show="show"><h3><i class="el-icon-info">小主，以下是采集完成的数据，请您检查无误后，点击“采集数据提交”按钮上传至服务器</i></h3><el-table :data="tempData" border style="width: 100%" :height="height"><el-table-columnprop="name"label="姓名"min-width="50%"></el-table-column><el-table-columnprop="phone"label="电话"min-width="50%"></el-table-column></el-table></div></div>
</template><script>
import xlsx from 'xlsx'
import { Loading } from 'element-ui'
import { readFile, character, delay } from '../assets/js/utils'
export default {name: 'Upload',data() {return {height: document.documentElement.clientHeight - 130,tempData: [],show: false}},methods: {// 采集 EXCEL 数据async handle(e) {let file = e.rawif (!file) returnthis.show = falselet loading = Loading.service({text: '小主，请您稍等片刻，奴家正在玩命处理中...',background: 'rgba(0,0,0,.5)'})await delay(300)// 读取FILE中的数据let data = await readFile(file)//转化二进制let workbook = xlsx.read(data, { type: 'binary' }),worksheet = workbook.Sheets[workbook.SheetNames[0]],//读取二进制list = xlsx.utils.sheet_to_json(worksheet)//转json格式// console.log(list)// 把读取出来的数据变为可以提交为服务器的数据格式let arr = []let oldData = JSON.parse(window.localStorage.getItem('excel') || '[]')let index = oldData.lengthlist.forEach(item => {let obj = {}for (let key in character) {// character字段对应表if (!character.hasOwnProperty(key)) breaklet v = character[key],text = v.text,type = v.typev = item[text] || ''type === 'string' ? (v = String(v)) : nulltype === 'number' ? (v = Number(v)) : nullobj[key] = v}obj.id = ++indexobj.time = new Date()arr.push(obj)})await delay(300)// 展示到页面中this.show = true// console.log(arr)this.tempData = arrloading.close()},// 提交数据给服务器submit() {if (this.tempData.length <= 0) {return this.$message({message: '小主，请您先选择 EXCEL 文件！',type: 'warning',showClose: true})}let oldData = JSON.parse(window.localStorage.getItem('excel') || '[]'),newData = [...oldData, ...this.tempData]window.localStorage.setItem('excel', JSON.stringify(newData))}}
}
</script><style lang="less" scoped>
.homeBox {position: fixed;top: 10px;right: 20px;z-index: 9999;font-size: 40px;
}.buttonBox {padding: 15px;display: flex;width: 35%;justify-content: flex-start;& .el-button {margin-right: 20px !important;}
}.tableBox {padding: 0 15px;h3 {font-size: 18px;color: #f56c6c;padding-bottom: 15px;}
}
</style>

2.导入utils方法

import xlsx from 'xlsx'  //下载xlsx
import { Loading } from 'element-ui'  
import { readFile, character, delay } from '../assets/js/utils' //导入utils方法

//  utils文件包// 设置异步延迟间隔
export function delay(interval = 0) {return new Promise(resolve => {let timer = setTimeout(_ => {clearTimeout(timer)resolve()}, interval)})
}// 按照二进制读取文件
export function readFile(file) {return new Promise(resolve => {let reader = new FileReader()reader.readAsBinaryString(file)reader.onload = e => {resolve(e.target.result)}})
}// 字段对应表
export let character = {name: {text: '姓名',type: 'string'},phone: {text: '电话',type: 'string'}
}// 时间字符串格式化
export function formatTime(str, tpl) {let arr = str.match(/\d+/g).map(item => {return item.length < 2 ? '0' + item : item})tpl = tpl || '{0}年{1}月{2}日 {3}时{4}分{5}秒'return tpl.replace(/\{(\d+)\}/g, (_, group) => {return arr[group] || '00'})
}

二、excel的下载

<template><div class="container"><!-- 上传文件按钮 --><div class="buttonBox"><router-link to="/upload"><el-tooltip content="数据采集页面" placement="top"><el-button type="primary" icon="el-icon-edit" circle></el-button></el-tooltip></router-link></div><!-- 搜索区域 --><div class="searchBox"><el-inputv-model="search"placeholder="基于姓名、手机号模糊搜索"@change="searchHandle"clearable></el-input><el-button type="success" @click="submit" :disabled="disabled">导出选中的数据</el-button></div><!-- 列表区域 --><div class="tableBox"><el-table:data="tableData":height="height"borderstyle="width: 100%"v-loading="loading"element-loading-text="主人，奴家正在努力加载中..."@selection-change="selectionChange"><el-table-columntype="selection"width="50"align="center"></el-table-column><el-table-columnprop="id"label="编号"min-width="10%"></el-table-column><el-table-columnprop="name"label="姓名"min-width="20%"></el-table-column><el-table-columnprop="phone"label="电话"min-width="20%"></el-table-column><el-table-columnprop="time"label="创建时间"min-width="25%":formatter="formatter"></el-table-column></el-table></div><!-- 分页区域 --><div class="pageBox"><el-paginationbackgroundhide-on-single-pagelayout="total,sizes,prev,pager,next":page-size="pageSize":current-page="page":total="total"@size-change="sizeChange"@current-change="prevNext"@prev-click="prevNext"@next-click="prevNext"></el-pagination></div></div>
</template><script>
import xlsx from 'xlsx'
import { Loading } from 'element-ui'
import { formatTime } from '../assets/js/utils'
export default {name: 'Home',data() {return {tableData: [],loading: false,height: document.documentElement.clientHeight - 120,// 分页相关pageSize: 20,page: 1,total: 10,search: '',// 选中数据selectionList: [],disabled: false}},created() {this.queryData()},methods: {// 权限selectionChange(val) {this.selectionList = val},// 格式化formatter(row, col, val) {return formatTime(val, '{0}-{1}-{2} {3}:{4}:{5}')},// 获取数据queryData() {this.loading = truelet data = window.localStorage.getItem('excel') || '[]'this.tableData = JSON.parse(data)this.loading = false},// 搜索按钮searchHandle() {this.page = valthis.queryData()},// 分页出路sizeChange(val) {this.pageSize = valthis.page = 1this.queryData()},prevNext(val) {this.page = valthis.queryData()},// 导出数据submit() {if (this.selectionList.length <= 0) {return this.$message({message: '小主，请您先选择要导出的数据库哦！',type: 'warning',showClose: true})}this.disabled = truelet loading = Loading.service({text: '小主，请您稍等片刻，女家正在玩命处理中...',background: 'rgba(0,0,0,.5)'})let arr = this.selectionList.map(item => {return {编号: item.id,姓名: item.name,电话: item.phone}})let sheet = xlsx.utils.json_to_sheet(arr),//转excel格式book = xlsx.utils.book_new()//创建excelxlsx.utils.book_append_sheet(book, sheet, 'Sheet1')//添加xlsx.writeFile(book, `user${new Date().getTime()}.xls`)//写入loading.close()this.disabled = false}}
}
</script><style lang="less" scoped>
.buttonBox {position: fixed;top: 10px;right: 20px;z-index: 9999;
}
.searchBox {padding: 20px;display: flex;justify-content: flex-start;& .el-input {width: 25%;}& .el-button {margin-left: 20px !important;}
}.tableBox {padding: 0 15px;
}
</style>