首先下载 npm i xlsx
创建一个excle的vue文件注册为全局组件
<template>
<div>
<input
ref="excel-upload-input"
class="excel-upload-input"
type="file"
accept=".xlsx, .xls"
@change="handleClick"
/>
<div class="drop">
<div class="left">
<el-button
:loading="loading"
style="margin-left:16px;"
size="mini"
type="primary"
@click="handleUpload"
>
Browse
</el-button>
</div>
<div
class="right"
@drop="handleDrop"
@dragover="handleDragover"
@dragenter="handleDragover"
>
<div class="myicon"><i class="el-icon-upload"></i></div>
<div>将文件拖到此处</div>
</div>
</div>
<!-- <div
class="drop"
@drop="handleDrop"
@dragover="handleDragover"
@dragenter="handleDragover"
>
Drop excel file here or
<el-button
:loading="loading"
style="margin-left:16px;"
size="mini"
type="primary"
@click="handleUpload"
>
Browse
</el-button>
</div> -->
</div>
</template>
<script>
import XLSX from 'xlsx'
export default {
props: {
beforeUpload: Function, // eslint-disable-line
onSuccess: Function // eslint-disable-line
},
data () {
return {
loading: false,
excelData: {
header: null,
results: null
}
}
},
methods: {
generateData ({ header, results }) {
this.excelData.header = header
this.excelData.results = results
this.onSuccess && this.onSuccess(this.excelData)
},
handleDrop (e) {
e.stopPropagation()
e.preventDefault()
if (this.loading) return
const files = e.dataTransfer.files
if (files.length !== 1) {
this.$message.error('Only support uploading one file!')
return
}
const rawFile = files[0] // only use files[0]
if (!this.isExcel(rawFile)) {
this.$message.error(
'Only supports upload .xlsx, .xls, .csv suffix files'
)
return false
}
this.upload(rawFile)
e.stopPropagation()
e.preventDefault()
},
handleDragover (e) {
e.stopPropagation()
e.preventDefault()
e.dataTransfer.dropEffect = 'copy'
},
handleUpload () {
this.$refs['excel-upload-input'].click()
},
handleClick (e) {
const files = e.target.files
const rawFile = files[0] // only use files[0]
if (!rawFile) return
this.upload(rawFile)
},
upload (rawFile) {
this.$refs['excel-upload-input'].value = null // fix can't select the same excel
if (!this.beforeUpload) {
this.readerData(rawFile)
return
}
const before = this.beforeUpload(rawFile)
if (before) {
this.readerData(rawFile)
}
},
readerData (rawFile) {
this.loading = true
return new Promise((resolve, reject) => {
const reader = new FileReader()
reader.onload = e => {
const data = e.target.result
const workbook = XLSX.read(data, { type: 'array' })
const firstSheetName = workbook.SheetNames[0]
const worksheet = workbook.Sheets[firstSheetName]
const header = this.getHeaderRow(worksheet)
const results = XLSX.utils.sheet_to_json(worksheet)
this.generateData({ header, results })
this.loading = false
resolve()
}
reader.readAsArrayBuffer(rawFile)
})
},
getHeaderRow (sheet) {
const headers = []
const range = XLSX.utils.decode_range(sheet['!ref'])
let C
const R = range.s.r
/* start in the first row */
for (C = range.s.c; C <= range.e.c; ++C) {
/* walk every column in the range */
const cell = sheet[XLSX.utils.encode_cell({ c: C, r: R })]
/* find the cell in the first row */
let hdr = 'UNKNOWN ' + C // <-- replace with your desired default
if (cell && cell.t) hdr = XLSX.utils.format_cell(cell)
headers.push(hdr)
}
return headers
},
isExcel (file) {
return /\.(xlsx|xls|csv)$/.test(file.name)
}
}
}
</script>
<style lang="scss" scoped>
.excel-upload-input {
display: none;
z-index: -9999;
}
.drop {
border: 2px dashed #bbb;
width: 600px;
height: 160px;
line-height: 160px;
margin: 0 auto;
font-size: 24px;
border-radius: 5px;
text-align: center;
color: #bbb;
position: relative;
display: flex;
.left {
flex: 1;
border-right: 2px dashed #bbb;
}
.right {
flex: 1;
line-height: 20px;
font-size: 14px;
.myicon {
margin-top: 40px;
font-size: 50px;
}
}
}
</style>
创建模板组件
<template>
<div class="import">
<el-card>
<h3>员工导入</h3>
<el-alert
title="每次导入仅可添加1000名员工、姓名、手机、入职时间、聘用形式为必填项"
type="warning"
show-icon
>
</el-alert>
<myexcel
class="myexcel"
:beforeUpload="beforeUpload"
:onSuccess="onSuccess"
></myexcel>
</el-card>
</div>
</template>
<script>
// 导入批量添加的接口
import { xxxxxxx } from 'xxxxxxxxxxx'
export default {
methods: {
// 上传文件之前会触发的回调函数
beforeUpload (file) {
// file:上传的文件
// file.size 的大小单位是: b
// 电脑中存储数据的单位:
// b kb MB GB TB
// 1kb = 1024b 1mb = 1024kb 1gb = 1024mb 1tb = 1024gb
// 判断文件的大小:是否小于 2M
const res = file.size < 1024 * 1024 * 2
// 小于 2M: 上传
// 大于 2M: 结束本次操作
if (!res) {
this.$message.error('对不起,您选择的文件大于 2M')
}
return res
},
// 定义一个将天数转换为日期的方法
turnday (days) {
// 1.0 得到时间戳
const mytime = (days - 1) * 24 * 60 * 60 * 1000
// 2.0 转为具体时间
const newtime = new Date(mytime)
// 3.0 将年份减去 70
newtime.setFullYear(newtime.getFullYear() - 70)
// 返回时间
return newtime
},
// 上传文件之后,将并组件将数据处理完成后会执行
async onSuccess (data) {
// console.log(data) // header 表头 result 数据源
// 将中文属性改为英文属性
// 1.0 定义一个对象:保存中文属性对应的英文属性
var obj = {
入职日期: 'timeOfEntry',
姓名: 'username',
工号: 'workNumber',
手机号: 'mobile',
转正日期: 'correctionTime'
}
// 2.0 将数据源中的中文全部替换成对应的英文属性
// 数据源 data.results: []
const newData = data.results.map(item => {
// 得到 item 中所有的属性
const keys = Object.keys(item) // keys: ['入职日期', '姓名', '工号','手机号','转正日期']
// 创建一个新的对象
const newObj = {}
// 将所有的中文属性改为英文属性
keys.forEach(key => {
// 得到中文属性对应的英文属性(属性键)
const enkey = obj[key]
// 判断属性是否是入职日期 或者 是转正日期
let val
if (key === '入职日期' || key === '转正日期') {
// 再得到中属性对应的值(属性值)
val = this.turnday(item[key])
} else {
val = item[key]
}
// 添加键值对
newObj[enkey] = val
})
return newObj
})
// 3.0 将数据提交到服务器
await xxxxxxxxxx(newData)
// 4.0 提示成功 跳转回员工列表页面
this.$message.success('添加成功')
this.$router.push('/employees')
}
}
}
</script>
<style lang="scss">
.import {
margin: 20px;
h3 {
text-align: center;
}
.myexcel {
margin: 40px 0;
}
}
</style>
就可以啦