vue – 如何实现导入excel功能

实际情况中有时候需要我们一次性添加多个员工信息，这个时候就需要我们开发一个批量导入的功能：将事先以excel格式保存的文件批量导入进来。

思路1：前端主导（本文只对前端主导进行讲解）

上传excel文件，把excel文件的内容读出来，还原成最基本的行列结构，按后端的接口要求回传过去。

思路2：后端主导

3.1 组件封装

3.1.1 将vue-element-admin提供的组件复制到我们自己的项目下

复制以下代码即可

<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">       拖入excel文件 or       <el-button :loading="loading" style="margin-left:16px;" size="mini" type="primary" @click="handleUpload">         浏览       </el-button>     </div>   </div> </template>  <script> import XLSX from 'xlsx'  export default {   name: 'UploadExcel',   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>

3.1.2 安装插件

npm install xlsx -S

3.1.3 引入UploadExcel组件并注册为全局

import PageTools from './PageTools' import UploadExcel from './UploadExcel'  export default {   // 插件的初始化, 插件给你提供的全局的功能, 都可以在这里配置   install(Vue) {     // 进行组件的全局注册     Vue.component('PageTools', PageTools) // 注册工具栏组件     Vue.component('UploadExcel', UploadExcel) // 注册导入excel组件   } }

3.2 准备页面

3.2.1 建立路由

<template>   <upload-excel :on-success="handleSuccess" /> </template>  <script> export default {   name: 'Import',   methods: {     handleSuccess({ header, results }) {       console.log(header, results)     }   } } </script>

3.2.2 配置路由

{     path: '/import',     component: Layout,     hidden: true, // 不显示到左侧菜单     children: [{       path: '',        component: () => import('@/views/import')     }] }

3.2.3 测试结果

3.3 数据处理

3.3.1 目标

将excel解析好的数据经过处理后，转成可以传给接口调用的数据

下面是后端接口要求的示例格式 ：

处理内容：

1.字段中文转英文。excel中读入的是姓名,而后端需要的是username

2.日期处理。从excel中读入的时间是一个number值，而后端需要的是标准日期。

3.3.2 代码：

    transExcel(results) {       const mapInfo = {         '入职日期': 'timeOfEntry',         '手机号': 'mobile',         '姓名': 'username',         '转正日期': 'correctionTime',         '工号': 'workNumber',         '部门': 'departmentName',         '聘用形式': 'formOfEmployment'       }       return results.map(zhObj => {         const enObj = {}         const zhKeys = Object.keys(zhObj) // ['姓名', '手机号']          zhKeys.forEach(zhKey => {           const enKey = mapInfo[zhKey]            enObj[enKey] = zhObj[zhKey]         })          return enObj       })     }

3.4 封装日期处理函数

// 把excel文件中的日期格式的内容转回成标准时间 // https://blog.csdn.net/qq_15054679/article/details/107712966 export function formatExcelDate(numb, format = '/') {   const time = new Date((numb - 25567) * 24 * 3600000 - 5 * 60 * 1000 - 43 * 1000 - 24 * 3600000 - 8 * 3600000)   time.setYear(time.getFullYear())   const year = time.getFullYear() + ''   const month = time.getMonth() + 1 + ''   const date = time.getDate() + ''   if (format && format.length === 1) {     return year + format + month + format + date   }   return year + (month < 10 ? '0' + month : month) + (date < 10 ? '0' + date : date) }

更新代码：

transExcel(results) {       const mapInfo = {         '入职日期': 'timeOfEntry',         '手机号': 'mobile',         '姓名': 'username',         '转正日期': 'correctionTime',         '工号': 'workNumber',         '部门': 'departmentName',         '聘用形式': 'formOfEmployment'       }       return results.map(zhObj => {         const enObj = {}         const zhKeys = Object.keys(zhObj) // ['姓名', '手机号']          zhKeys.forEach(zhKey => {           const enKey = mapInfo[zhKey]            if (enKey === 'timeOfEntry' || enKey === 'correctionTime') {             // 后端需要的日期格式是标准时间              enObj[enKey] = new Date(formatExcelDate(zhObj[zhKey]))           } else {             enObj[enKey] = zhObj[zhKey]           }         })          return enObj       })     }

 最后，根据实际情况，封装接口并调用即可