vue+elementui导入Excel文件（基于vue-element-admin中的uploadExcel组件)
vue+elementui导入Excel文件（基于vue-element-admin中的uploadExcel组件)
需求说明
实现Excel文件的上传，将文件传给后端完成数据的批量导入。并在前端进行解析，将上传的Excel回显到表格中。
备注：本功能仅实现了简单excel表格数据的导入，限制一次只能上传一个excel。
接下来就让我们开动吧！
第一步：构建页面
我这边就拿我正在做的一个项目举例，其中有一个功能是物品信息的管理。需要实现批量新增（即excel导入）。
首先我们在已有的页面上新增一个按钮，用以激活dialog。我这边将导入excel的部分放到dialog中。
<el-button
class="filter-item"
size="mini"
type="primary"
icon="el-icon-upload"
@click="dialogVisible = true"
>批量导入</el-button>
<el-dialog title="导入表单" :visible.sync="dialogVisible">
<!-- 导入excel,一会会将这里补充 -->
</el-dialog>
data(){
return{
dialogVisible: false, //dialog是否可见
}
}
如下图所示，已经新增了一个批量导入的按钮。当我们点击时dialog会变为可见，即弹出dialog。
第二步：利用现成的组件
因为我这边用到的是el-admin的开源项目进行二次开发，其中涉及到的excel上传功能是来自于vue-element-admin开源项目的uploadExcel组件，所以我们直接去看封装好的组件，进行修改。
在vue-element-admin的在线文档中，我们找到关于Excel的文档说明。如下图所示：
官方Demo(不涉及提交给后台的动作)
我们点击在线代码去查看示例代码。方便起见，我直接搬运过来了。
<template>
<div class="app-container">
<upload-excel-component :on-success="handleSuccess" :before-upload="beforeUpload" />
<el-table :data="tableData" border highlight-current-row style="width: 100%;margin-top:20px;">
<el-table-column v-for="item of tableHeader" :key="item" :prop="item" :label="item" />
</el-table>
</div>
</template>
<script>
import UploadExcelComponent from '@/components/UploadExcel/index.vue'
export default {
name: 'UploadExcel',
components: { UploadExcelComponent },
data() {
return {
tableData: [],
tableHeader: []
}
},
methods: {
beforeUpload(file) {
const isLt1M = file.size / 1024 / 1024 < 1
if (isLt1M) {
return true
}
this.$message({
message: 'Please do not upload files larger than 1m in size.',
type: 'warning'
})
return false
},
handleSuccess({ results, header }) {
this.tableData = results
this.tableHeader = header
}
}
}
</script>
核心：UploadExcelComponent组件
可以看到，其中有一个UploadExcelComponent组件，我们继续去寻找该组件的代码。如下：
<template>
<div>
<input ref="excel-upload-input" class="excel-upload-input" type="file" accept=".xlsx, .xls" @change="handleClick">
<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 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;
}
</style>
这个组件是用来解析我们所上传的excel的，具体大家可以自行研究，主要是将excel中的内容解析成header和results,我们主要用解析后的信息回显给table。
在demo的基础上修改
了解了这些，我们就可以在dialog中完善相应的代码了。这边可以根据模板做修改。
<el-dialog title="导入表单" :visible.sync="dialogVisible">
<div class="app-container">
<div>
<el-button
class="filter-item"
size="mini"
type="primary"
icon="el-icon-download"
@click="downloadExceltoLocalFile()"
>下载模板</el-button>
</div>
<upload-excel-component
:on-success="handleSuccess"
:before-upload="beforeUpload"
/>
<el-table
max-height="300"
:data="tableData"
border
highlight-current-row
style="width: 100%; margin-top: 20px"
>
<el-table-column
v-for="item of tableHeader"
:key="item"
:prop="item"
:label="item"
>
</template>
</el-table-column>
</el-table>
<el-divider />
<el-button
style="float: right"
type="primary"
:loading="loading"
@click="submitExcel()"
>确认并导入</el-button>
</div>
</el-dialog>
-
这里我新增了一个下载模板的按钮，同样是调用后端接口去获得模板excel，然后点击进行下载。如果大家有需要我下次详细的讲怎么实现。
-
同时这边跟demo一样，用了uploadExcelComponent组件。怎么引入应该不用多说了吧。该组件提供了两个回调函数，在官方文档里都有描述。
before-upload: 在上传前所做的动作
on-success: 解析成功后触发的回调，我们在这里实现对表格中表头和内容的赋值
-
最后是一个确认的按钮，加入点击事件。
第三步：实现各个方法
- 下载模板按钮这边有一个
downloadExceltoLocalFile()事件。
downloadExceltoLocalFile() {
crudMaterial
.downloadExcel()
.then((res) => {
downloadFile(res, '物料模板', 'xlsx')
})
.catch((err) => {
this.$message.error = err.message
})
},
你会发现缺了一个downloadFile的方法，该方法这个框架已经提供了，我们只需要import { downloadFile } from '@/utils/index'即可，具体的内容自行查阅相关资料或评论。其中
crudMaterial
.downloadExcel()
这个是我自己调后台的API接口，请自行修改。
- 组件的回调函数
// before-upload
beforeUpload(file) {
this.files = file
console.log(this.files)
const extension = file.name.substring(file.name.lastIndexOf('.') + 1)
const isLt5M = file.size / 1024 / 1024 < 5
if (extension !== 'xlsx' && extension !== 'xls') {
this.$message({
message: '只能上传Excel（即后缀是.xlsx或者.xls)的文件.',
type: 'warning'
})
return false
}
if (isLt5M) {
return true
}
this.$message({
message: '请不要上传大于5MB的文件.',
type: 'warning'
})
return false
},
我这边只是一些简单的对后缀和大小的判断，大家根据实际自行修改。
handleSuccess({ header, results }) {
this.tableData = results
this.tableHeader = header
},
该回调即对提交的excel文件进行解析，同时给表格赋值。
- 点击提交的方法
submitExcel() {
// 装载成formdata数据
const formdata = new FormData()
formdata.append('file', this.files, this.files.name)
this.loading = true
this.doImport(formdata)
},
async doImport(data) {
try {
const res = await crudMaterial.importMaterial(data)
console.log('importMaterial', res)
this.loading = false
this.$message.success('导入成功')
} catch (err) {
console.log('importMaterial', err)
this.loading = false
this.$message.error('导入失败')
}
},
因为我们是要将excel传给后台服务器，所以我们需要新建一个formdata的变量，将formdata包装好数据传给后台，否则会出错。
doImport则是请求的方法，阅读应该没有难度。其中API接口如下：
至此，应该就差不多了。我们运行看一下效果。
点击浏览或者拖拽都可以实现文件的上传。
可以看到，上传的excel能成功回显，我们再点击确认进行提交。
提示导入成功。功能基本实现。
完整代码
api接口
import request from '@/utils/request'
export function downloadExcel() {
return request({
url: 'api/baseinfoMaterial/downloadExcel',
method: 'get',
responseType: 'blob'
})
}
export function importMaterial(data) {
return request({
url: 'api/baseinfoMaterial/importExcel',
method: 'post',
data
})
}
export default { downloadExcel, importMaterial }
index.vue 是这个页面所有的内容，按需取用
<template>
<div class="app-container">
<!--工具栏-->
<div class="head-container">
<div v-if="crud.props.searchToggle">
<!-- 搜索 -->
<label class="el-form-item-label">物料类别</label>
<el-select
v-model="query.modelName"
clearable
filterable
placeholder="请选择"
style="width: 185px"
class="filter-item"
@change="crud.toQuery"
>
<el-option
v-for="item in dict.material_category"
:key="item.id"
:label="item.label"
:value="item.value"
/>
</el-select>
<label class="el-form-item-label">物料名称</label>
<el-input
v-model="query.materialName"
clearable
size="small"
placeholder="输入物料名称搜索"
style="width: 185px"
class="filter-item"
@keyup.enter.native="crud.toQuery"
/>
<label class="el-form-item-label">供应商</label>
<el-input
v-model="query.supplier"
clearable
placeholder="供应商"
style="width: 185px"
class="filter-item"
@keyup.enter.native="crud.toQuery"
/>
<label class="el-form-item-label">状态</label>
<el-select
v-model="query.enabled"
clearable
size="small"
placeholder="请选择"
class="filter-item"
style="width: 90px"
@change="crud.toQuery"
>
<el-option
v-for="item in enabledTypeOptions"
:key="item.key"
:label="item.display_name"
:value="item.key"
/>
</el-select>
<label class="el-form-item-label">创建时间</label>
<date-range-picker v-model="query.createTime" class="date-item" />
<rrOperation :crud="crud" />
</div>
<!--如果想在工具栏加入更多按钮，可以使用插槽方式， slot = 'left' or 'right'-->
<crudOperation :permission="permission" />
<el-button
class="filter-item"
size="mini"
type="primary"
icon="el-icon-upload"
@click="dialogVisible = true"
>批量导入</el-button
>
<el-dialog title="导入表单" :visible.sync="dialogVisible">
<div class="app-container">
<div>
<el-button
class="filter-item"
size="mini"
type="primary"
icon="el-icon-download"
@click="downloadExceltoLocalFile()"
>下载模板</el-button
>
</div>
<upload-excel-component
:on-success="handleSuccess"
:before-upload="beforeUpload"
/>
<el-table
max-height="300"
:data="tableData"
border
highlight-current-row
style="width: 100%; margin-top: 20px"
>
<el-table-column
v-for="item of tableHeader"
:key="item"
:prop="item"
:label="item"
>
<template slot-scope="scope">
<template>
{{
scope.row[scope.column.property] === true
? '启用'
: scope.row[scope.column.property] === false
? '禁用'
: scope.row[scope.column.property]
}}
</template>
</template>
</el-table-column>
</el-table>
<el-divider />
<el-button
style="float: right"
type="primary"
:loading="loading"
@click="submitExcel()"
>确认并导入</el-button
>
</div>
</el-dialog>
</div>
<!--表单组件-->
<el-dialog
:close-on-click-modal="false"
:before-close="crud.cancelCU"
:visible.sync="crud.status.cu > 0"
:title="crud.status.title"
width="500px"
>
<el-form
ref="form"
:model="form"
:rules="rules"
size="small"
label-width="80px"
>
<el-form-item label="物料名称" prop="materialName">
<el-autocomplete
v-model="form.materialName"
style="width: 370px"
:fetch-suggestions="querySearchMaterials"
placeholder="请输入物料名称"
:trigger-on-focus="false"
>
<template slot-scope="{ item }">
<div>{{ (item.value = item.materialName) }}</div>
</template>
</el-autocomplete>
</el-form-item>
<el-form-item label="类别名称">
<el-select
v-model="form.modelName"
filterable
placeholder="请选择所属类别"
>
<el-option
v-for="item in dict.material_category"
:key="item.id"
:label="item.label"
:value="item.value"
/>
</el-select>
</el-form-item>
<el-form-item label="物料编号" prop="materialNum">
<el-input
v-model="form.materialNum"
style="width: 370px"
placeholder="请输入物料编号"
/>
</el-form-item>
<el-form-item label="物料型号" prop="materialType">
<el-input
v-model="form.materialType"
style="width: 370px"
placeholder="请输入物料型号"
/>
</el-form-item>
<el-form-item label="品牌" prop="brand">
<el-input
v-model="form.brand"
style="width: 370px"
placeholder="请输入品牌"
/>
</el-form-item>
<el-form-item label="单位" prop="unit">
<el-input
v-model="form.unit"
style="width: 370px"
placeholder="请输入单位"
/>
</el-form-item>
<el-form-item label="计价方式" prop="pricingMode">
<el-input v-model="form.pricingMode" style="width: 370px" disabled />
</el-form-item>
<el-form-item label="参考价格" prop="referencePrice">
<el-input
v-model="form.referencePrice"
style="width: 370px"
placeholder="请输入参考价格"
>
<template slot="append">元</template>
</el-input>
</el-form-item>
<el-form-item label="供货商" prop="supplier">
<el-input
v-model="form.supplier"
style="width: 370px"
placeholder="请输入供货商"
/>
</el-form-item>
<el-form-item label="状态" prop="enabled">
<el-radio
v-for="item in dict.material_status"
:key="item.id"
v-model="form.enabled"
:label="item.value === 'true'"
>{{ item.label }}</el-radio
>
</el-form-item>
</el-form>
<div slot="footer" class="dialog-footer">
<el-button type="text" @click="crud.cancelCU">取消</el-button>
<el-button
:loading="crud.status.cu === 2"
type="primary"
@click="crud.submitCU"
>确认</el-button
>
</div>
</el-dialog>
<!--表格渲染-->
<el-table
ref="table"
v-loading="crud.loading"
lazy
:data="crud.data"
size="small"
style="width: 100%"
@selection-change="crud.selectionChangeHandler"
>
<el-table-column type="selection" width="55" />
<el-table-column prop="modelName" label="类别名称">
<template slot-scope="scope">
{{ dict.label.material_category[scope.row.modelName] }}
</template>
</el-table-column>
<el-table-column prop="materialNum" label="物料编号" />
<el-table-column prop="materialName" label="物料名称" />
<el-table-column prop="materialType" label="物料型号" />
<el-table-column prop="brand" label="品 牌" />
<el-table-column prop="unit" label="单位" />
<el-table-column prop="pricingMode" label="计价方式" />
<el-table-column prop="referencePrice" label="参考价格" />
<el-table-column prop="supplier" label="供货商" />
<el-table-column prop="enabled" label="状态" align="center">
<template slot-scope="scope">
<a-switch
v-model="scope.row.enabled"
checked-children="启用"
un-checked-children="禁用"
:disabled="scope.row.id === 1"
@change="changeEnabled(scope.row, scope.row.enabled)"
/>
</template>
</el-table-column>
<el-table-column prop="createTime" label="创建时间" />
<el-table-column
v-if="checkPer(['admin', 'material:edit', 'material:del'])"
label="操作"
width="150px"
align="center"
>
<template slot-scope="scope">
<udOperation :data="scope.row" :permission="permission" />
</template>
</el-table-column>
</el-table>
<!--分页组件-->
<pagination />
</div>
</template>
<script>
import crudMaterial from '@/api/record/material'
import CRUD, { presenter, header, form, crud } from '@crud/crud'
import rrOperation from '@crud/RR.operation'
import crudOperation from '@crud/CRUD.operation'
import udOperation from '@crud/UD.operation'
import pagination from '@crud/Pagination'
import DateRangePicker from '@/components/DateRangePicker'
import UploadExcelComponent from '@/components/UploadExcel/index.vue'
import { downloadFile } from '@/utils/index'
const defaultForm = {
id: '',
materialNum: null,
materialName: null,
materialType: null,
unit: null,
pricingMode: '加权平均法',
referencePrice: null,
supplier: null,
createTime: null,
enabled: true,
modelName: null,
brand: null
}
export default {
name: 'Material',
components: {
pagination,
crudOperation,
rrOperation,
udOperation,
DateRangePicker,
UploadExcelComponent
},
mixins: [presenter(), header(), form(defaultForm), crud()],
dicts: ['material_category', 'material_status'],
cruds() {
return CRUD({
title: '物料管理',
url: 'api/baseinfoMaterial',
sort: 'id,desc',
crudMethod: { ...crudMaterial }
})
},
data() {
/* 金额格式校验 */
var validatePrice = (rule, value, callback) => {
const reg = /(^[1-9]\d*(\.\d{1,2})?$)|(^0(\.\d{1,2})?$)/
if (value === '' || value === undefined || value == null) {
callback()
} else {
if (!reg.test(value) && value !== '') {
callback(new Error('请输入正确的价格!整数或保留两位小数'))
} else {
callback()
}
}
}
return {
tableData: [],
tableHeader: [],
dialogVisible: false,
loading: false,
files: [],
enabledTypeOptions: [
{ key: 'true', display_name: '正常' },
{ key: 'false', display_name: '禁用' }
],
permission: {
add: ['admin', 'material:add'],
edit: ['admin', 'material:edit'],
del: ['admin', 'material:del']
},
rules: {
materialName: [
{
required: true,
message: '请输入物料名称',
trigger: 'blur'
}
],
materialNum: [
{ required: true, message: '请输入物料编号', trigger: 'blur' }
],
materialType: [
{ required: true, message: '请输入物料型号', trigger: 'blur' }
],
brand: [{ required: true, message: '请输入品牌', trigger: 'blur' }],
unit: [{ required: true, message: '请输入单位', trigger: 'blur' }],
referencePrice: [
{ required: true, message: '请输入参考价格', trigger: 'blur' },
{
validator: validatePrice,
trigger: 'blur'
}
]
},
queryTypeOptions: [
{ key: 'materialName', display_name: '物料名称' },
{ key: 'materialType', display_name: '物料型号' },
{ key: 'supplier', display_name: '供货商' },
{ key: 'modelName', display_name: '类别名称' }
]
}
},
mounted() {
this.loadAll()
},
methods: {
// 导入
submitExcel() {
// 装载成formdata数据
const formdata = new FormData()
formdata.append('file', this.files, this.files.name)
this.loading = true
this.doImport(formdata)
},
async doImport(data) {
try {
const res = await crudMaterial.importMaterial(data)
console.log('importMaterial', res)
this.loading = false
this.$message.success('导入成功')
} catch (err) {
console.log('importMaterial', err)
this.loading = false
this.$message.error('导入失败')
}
},
// 导入功能
beforeUpload(file) {
this.files = file
console.log(this.files)
const extension = file.name.substring(file.name.lastIndexOf('.') + 1)
const isLt5M = file.size / 1024 / 1024 < 5
if (extension !== 'xlsx' && extension !== 'xls') {
this.$message({
message: '只能上传Excel（即后缀是.xlsx或者.xls)的文件.',
type: 'warning'
})
return false
}
if (isLt5M) {
return true
}
this.$message({
message: '请不要上传大于5MB的文件.',
type: 'warning'
})
return false
},
transExcel(results) {
const mapInfo = {
类别名称: 'modelName',
物料编号: 'materialNum',
物料名称: 'materialName',
物料型号: 'materialType',
单位: 'unit',
计价方式: 'pricingMode',
参考价格: 'referencePrice',
供货商: 'supplier',
状态: 'enabled',
品牌: 'brand'
}
return results.map((zhObj) => {
const enObj = {}
const zhKeys = Object.keys(zhObj)
zhKeys.forEach((zhKey) => {
const enKey = mapInfo[zhKey]
enObj[enKey] = zhObj[zhKey]
})
return enObj
})
},
handleSuccess({ header, results }) {
this.tableData = results
this.tableHeader = header
},
// 改变状态
changeEnabled(data, val) {
this.$confirm(
'此操作将 "' +
this.dict.label.material_status[val] +
'" ' +
data.materialName +
'仓库, 是否继续？',
'提示',
{
confirmButtonText: '确定',
cancelButtonText: '取消',
type: 'warning'
}
)
.then(() => {
// eslint-disable-next-line no-undef
crudMaterial
.edit(data)
.then(() => {
// eslint-disable-next-line no-undef
this.crud.notify(
this.dict.label.material_status[val] + '成功',
'success'
)
})
.catch((err) => {
data.enabled = !data.enabled
console.log(err.data.message)
})
})
.catch(() => {
data.enabled = !data.enabled
})
},
loadAll() {
crudMaterial.getAllMaterial().then((res) => {
this.materials = res.content
})
},
downloadExceltoLocalFile() {
crudMaterial
.downloadExcel()
.then((res) => {
downloadFile(res, '物料模板', 'xlsx')
})
.catch((err) => {
this.$message.error = err.message
})
},
querySearchMaterials(queryString, cb) {
var materials = this.materials
var results = queryString
? materials.filter(this.createFilter(queryString))
: materials
cb(results)
},
// 查询输入字符是否存在
createFilter(queryString) {
return (form) => {
return (
form.materialName.toLowerCase().indexOf(queryString.toLowerCase()) !==
-1
)
}
},
// 钩子：在获取表格数据之前执行，false 则代表不获取数据
[CRUD.HOOK.beforeRefresh]() {
return true
}
}
}
</script>
<style scoped></style>
PS：注意一个细节
该图是后端controller层提供的接口，这边需要额外注意一点。这边的参数file需要和前面formdata中的第一个参数对应。否则会出现400错误。
总结
这次的开发仅仅是在单个页面上进行编写，还没有封装成一个通用的组件，同时由于本人能力有限，可能会存在一些疏漏，出现BUG。还望大家能够指出。
参考资料
vue-element-admin开源项目，文档地址
el-admin开源项目，文档地址
element-ui, 文档地址
element-ui/vue-element-admin上传excel等文件到服务器,文章地址
