一、安装相关依赖
npm install --save xlsx file-saver
二、使用
import * as XLSX from 'xlsx/xlsx.mjs'const FileSaver = require('file-saver')//import XLXS from "xlsx";import 语法引入模块,XLSX 对象为undefined
三、纯前端导入与解析excel表格文件
1、html部分
<div class="flex-center"><div class="container">{{upload_file || "导入" }}<inputtype="file"accept=".xls,.xlsx"class="upload_file"@change="readExcel($event)"/></div></div>
2、JS部分
readExcel (e) {// 读取表格文件const that = thisconst files = e.target.filesif (files.length <= 0) {return false} else if (!/\.(xls|xlsx)$/.test(files[0].name.toLowerCase())) {this.$message({message: '上传格式不正确,请上传xls或者xlsx格式',type: 'warning'})return false} else {// 更新获取文件名that.upload_file = files[0].name}const fileReader = new FileReader()fileReader.onload = ev => {try {const data = ev.target.resultconst workbook = XLSX.read(data, {type: 'binary'})const wsname = workbook.SheetNames[0] // 取第一张表const ws = XLSX.utils.sheet_to_json(workbook.Sheets[wsname]) // 生成json表格内容that.lists = []// 从解析出来的数据中提取相应的数据ws.forEach(item => {that.lists.push({username: item['用户名'],phone_number: item['手机号']})})} catch (e) {return console.log(e)}}fileReader.readAsBinaryString(files[0])}
四、el-table 数据导出Excel
1、html部分
<el-button class="m-t20" type="primary" @click="add()">添加数据</el-button><el-button class="m-t20" type="primary" @click="exportExcel()">导出数据</el-button><el-tableid="out-table":data="lists"borderstyle="width:20%"><el-table-columnprop="username"label="用户名"></el-table-column><el-table-columnprop="phone_number"label="手机号"></el-table-column><el-table-columnlabel="备注"><template>11</template></el-table-column></el-table>
2、JS部分
exportExcel () {const tables = document.getElementById('out-table')const tablebook = XLSX.utils.table_to_book(tables)const tablewrite = XLSX.write(tablebook, {bookType: 'xlsx',bookSST: true,type: 'array'})try {FileSaver.saveAs(new Blob([tablewrite], {type: 'application/octet-stream' }),'下载的excel表单数据.xlsx')} catch (e) {if (typeof console !== 'undefined') console.log(e, tablewrite)}return tablewrite},add () {const arr = [{username: '小一' + this.a++,phone_number: this.phone++},{username: '小二' + this.a++,phone_number: this.phone++}]this.lists = [...arr, ...this.lists]},
五、样式
<style lang="less" scoped>.container {border: none;border-radius: 4px;background-color: #409eff;height: 40px;margin-top: 8px;display: flex;align-items: center;justify-content: center;padding: 0 15px;min-width: 80px;*zoom: 1;}.upload_file {font-size: 20px;opacity: 0;position: absolute;filter: alpha(opacity=0);width: 60px;}</style>
六、完整代码
<template><div class=""><div class="flex-center"><div class="container">{{upload_file || "导入" }}<inputtype="file"accept=".xls,.xlsx"class="upload_file"@change="readExcel($event)"/></div></div><el-button class="m-t20" type="primary" @click="add()">添加数据</el-button><el-button class="m-t20" type="primary" @click="exportExcel()">导出数据</el-button><el-tableid="out-table":data="lists"borderstyle="width:20%"><el-table-columnprop="username"label="用户名"></el-table-column><el-table-columnprop="phone_number"label="手机号"></el-table-column><el-table-columnlabel="备注"><template>11</template></el-table-column></el-table></div></template><script>// const XLSX = require('xlsx')import * as XLSX from 'xlsx/xlsx.mjs'const FileSaver = require('file-saver') export default {data () {return {upload_file: '',lists: [],a: 0,phone: 13456765432}},methods: {exportExcel () {const tables = document.getElementById('out-table')const tablebook = XLSX.utils.table_to_book(tables)const tablewrite = XLSX.write(tablebook, {bookType: 'xlsx',bookSST: true,type: 'array'})try {FileSaver.saveAs(new Blob([tablewrite], {type: 'application/octet-stream' }),'下载的excel表单数据.xlsx')} catch (e) {if (typeof console !== 'undefined') console.log(e, tablewrite)}return tablewrite},add () {const arr = [{username: '小一' + this.a++,phone_number: this.phone++},{username: '小二' + this.a++,phone_number: this.phone++}]this.lists = [...arr, ...this.lists]},submit_form () {// 给后端发送请求,更新数据console.log('假装给后端发了个请求...')},readExcel (e) {// 读取表格文件const that = thisconst files = e.target.filesif (files.length <= 0) {return false} else if (!/\.(xls|xlsx)$/.test(files[0].name.toLowerCase())) {this.$message({message: '上传格式不正确,请上传xls或者xlsx格式',type: 'warning'})return false} else {// 更新获取文件名that.upload_file = files[0].name}const fileReader = new FileReader()fileReader.onload = ev => {try {const data = ev.target.resultconst workbook = XLSX.read(data, {type: 'binary'})const wsname = workbook.SheetNames[0] // 取第一张表const ws = XLSX.utils.sheet_to_json(workbook.Sheets[wsname]) // 生成json表格内容that.lists = []// 从解析出来的数据中提取相应的数据ws.forEach(item => {that.lists.push({username: item['用户名'],phone_number: item['手机号']})})// 给后端发请求// this.submit_form()} catch (e) {return console.log(e)}}fileReader.readAsBinaryString(files[0])}}}</script><style lang="less" scoped>.container {border: none;border-radius: 4px;background-color: #409eff;height: 40px;margin-top: 8px;display: flex;align-items: center;justify-content: center;padding: 0 15px;min-width: 80px;*zoom: 1;}.upload_file {font-size: 20px;opacity: 0;position: absolute;filter: alpha(opacity=0);width: 60px;}</style>