xlsx+vue+elementUI完成excel的导入

上传想要导入的excel表后，点击导入数据就有弹框导出，弹框展示的是导入的excel表的数据，可以在对应的input框上进行数据修改，确认无误后就可以点击确定post到后端，进行数据库存储。

 

<template>
<el-button type="success" @click="showExcelData()">导入excel数据</el-button>
<el-dialog title="确定上传的名单" :visible.sync="dialogTableVisible">
	<el-form :model="ruleForm" :rules="rules" ref="ruleForm">
		<el-table :data="ruleForm.accountList">
			<el-table-column type="index" label="序号" min-width="20" align="center"></el-table-column>
			<el-table-column align="center" label="操作" min-width="20">
				<template slot-scope="scope">
					<el-button type="text" @click="delSealRow(scope.row,scope.$index)" icon="el-icon-delete" size="small"></el-button>
				</template>
			</el-table-column>
			<el-table-column label="姓名" width="140">
				<template slot-scope="scope">
					<el-form-item :prop="'accountList.' + scope.$index + '.username'" :rules='rules.username'>
						<el-input v-model="scope.row.username" size="small" :maxlength="30"></el-input>
					</el-form-item>
				</template>
			</el-table-column>
			<el-table-column label="班级" width="140">
				<template slot-scope="scope">
					<el-form-item :prop="'accountList.' + scope.$index + '.classname'" :rules='rules.classname'>
						<el-input v-model="scope.row.classname" size="small" :maxlength="30"></el-input>
					</el-form-item>
				</template>
			</el-table-column>
		</el-table>
	</el-form>
	<div slot="footer" class="dialog-footer">
		<el-button @click="dialogTableVisible = false">取 消</el-button>
		<el-button type="primary" @click="importData('ruleForm')">确 定</el-button>
	</div>
</el-dialog>
</template>

 

<script>
import XLSX from 'xlsx'
export default {
	data() {
		return {
			dialogTableVisible: false, // 导入的名单信息弹框
			ruleForm: {
				accountList: [] //excel表的名单数据
			},
			rules: {
				username: [{
					required: true,
					message: '请输入姓名',
					trigger: 'blur'
				}],
				classname: [{
					required: true,
					message: '请输入班级',
					trigger: 'blur'
				}],
			},
		}
	},
	methods:{
		//读取excel表数据
		exportData(event) {
			if (!event.currentTarget.files.length) {
				this.ruleForm.accountList = []
				return
			}
			const that = this
			// 拿取文件对象
			let f = event.currentTarget.files[0]
			// 用FileReader来读取
			let reader = new FileReader()
			// 重写FileReader上的readAsBinaryString方法
			FileReader.prototype.readAsBinaryString = function(f) {
				let binary = ''
				let wb // 读取完成的数据
				let outdata
				let reader = new FileReader()
				reader.onload = function(e) {
					// 读取成Uint8Array，再转换为Unicode编码（Unicode占两个字节）
					let bytes = new Uint8Array(reader.result)
					let length = bytes.byteLength
					for (let i = 0; i < length; i++) {
						binary += String.fromCharCode(bytes[i])
					}
					// 接下来就是xlsx了，具体可看api
					wb = XLSX.read(binary, {
						type: 'binary'
					})
					outdata = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]])
					let arr = []
					//取对应的表名数据
					outdata.map(v => {
						let obj = {}
						obj.username = v.姓名
						obj.classname = v.班级
						arr.push(obj)
					})
					//赋值数据到data中的变量
					that.ruleForm.accountList = [...arr];
				}
				reader.readAsArrayBuffer(f)
			}
			reader.readAsBinaryString(f)
		},
		//删除一条数据
		delSealRow(item,index){
			this.ruleForm.accountList.splice(index,1);
		},
		//显示弹框
		showExcelData() {
			this.dialogTableVisible = true;
		},
		//将数据post到后台数据库存储
		importData(formName) {
			let that = this
			this.$refs[formName].validate(valid => {
				//如果必填项都已填
				if (valid) {
					// 发送请求
					this.$axios.post('/roster/addUsers', {users: JSON.stringify(that.ruleForm.accountList)}).then(res => {
						
					})
				} else {
					this.$message({
						type: 'info',
						message: '有必填项为空'
					});
					return false;
				}
			})
		}
	}
}
</script>