一、安装相关依赖
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 || "导入" }}
<input
type="file"
accept=".xls,.xlsx"
class="upload_file"
@change="readExcel($event)"
/>
</div>
</div>
2、JS部分
readExcel (e) {
// 读取表格文件
const that = this
const files = e.target.files
if (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.result
const 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-table
id="out-table"
:data="lists"
border
style="width:20%">
<el-table-column
prop="username"
label="用户名">
</el-table-column>
<el-table-column
prop="phone_number"
label="手机号">
</el-table-column>
<el-table-column
label="备注">
<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:
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 || "导入" }}
<input
type="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-table
id="out-table"
:data="lists"
border
style="width:20%">
<el-table-column
prop="username"
label="用户名">
</el-table-column>
<el-table-column
prop="phone_number"
label="手机号">
</el-table-column>
<el-table-column
label="备注">
<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 = this
const files = e.target.files
if (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.result
const 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:
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>