封装通用工具栏组件

  1. <template>
  2. <el-card>
  3.   <div class="page-tools">
  4.     <!-- 左侧 -->
  5.     <div class="left">
  6.       <div class="tips">
  7.         <i class="el-icon-info" />
  8.         <span>本月: 社保在缴 公积金在缴</span>
  9.   </div>
  10.   </div>
  11.     <div class="right">
  12.       <!-- 右侧 -->
  13.       <el-button type="primary" size="small">历史归档</el-button>
  14.       <el-button type="primary" size="small">导出</el-button>
  15.   </div>
  16.   </div>
  17.   </el-card>
  18. </template>
  20. <script>
  21.   export default {}
  22. </script>
  24. <style lang="scss" scoped>
  25.   .page-tools {
  26.     display: flex;
  27.     justify-content: space-between;
  28.     align-items: center;
  29.     ::v-deep.tips {
  30.       line-height: 34px;
  31.       padding: 0px 15px;
  32.       border-radius: 5px;
  33.       border: 1px solid rgba(145, 213, 255, 1);
  34.       background: rgba(230, 247, 255, 1);
  35.       i {
  36.         margin-right: 10px;
  37.         color: #409eff;
  38.       }
  39.     }
  40.   }
  41. </style>

里面的内容可以通过插槽来配置这样就可以根据不同页面配置不同内容 具名插槽将来在使用组件的时候，只需要按照对应的插槽名称就可以在特定的位置插入内容

  1. <template>
  2. <el-card>
  3.   <div class="page-tools">
  4.     <!-- 左侧 -->
  5.     <div class="left">
  6.       <slot name="left" />
  7.   </div>
  8.     <div class="right">
  9.       <!-- 右侧 -->
  10.       <slot name="right" />
  11.   </div>
  12.   </div>
  13.   </el-card>
  14. </template>
  1. <template>
  2.   <div class="employees-container">
  3.     <div class="app-container">
  4.       <page-tools>
  5.         <!-- 插入到left插槽位置 -->
  6.         <template #left>
  7.           <i class="el-icon-info" />
  8.           <span>本月: 社保在缴 公积金在缴</span>
  9.         </template>
  10.          <!-- 插入到right插槽位置 -->
  11.         <template #right>
  12.           <el-button type="primary" size="small">导入excel</el-button>
  13.           <el-button type="primary" size="small">导出excel</el-button>
  14.           <el-button type="primary" size="small">新增员工</el-button>
  15.         </template>
  16.       </page-tools>
  17.     </div>
  18.   </div>
  19. </template>
  20. <script>
  21.   import PageTools from '@/components/PageTools'
  22.   export default {
  23.       components:{
  24.          PageTools
  25.       }
  26.   }
  27. </script>

全局注册组件

  1. import PageTools from '@/components/PageTools'
  2. Vue.component('PageTools', PageTools)

封装成插件

开发 install是一个固定方法 有一个参数是在Vue

  • 函数写法 函数本身会被当成install方法，只需要在函数内部写
  • 对象写法 需要写入install方法

插件注册

  • 需要在new Vue之前
  1. // 1.开发
  2. import PageTools from '@/components/PageTools'
  3. const pageTools = {
  4.   // install方法固定写法 vue文档中规定的插件需要暴漏一个install方法
  5.   install(Vue) {
  6.     Vue.component('page-tools', PageTools)
  7.   }
  8. }
  10. export default pageTools
  12. // 2.vue.use(pageTools) 相当于执行了 这个方法内部的 install方法并传入了一个实参 Vue构造函数

员工列表渲染

  1. <template>
  2.   <div class="employees-container">
  3.     <div class="app-container">
  4.       <page-tools>
  5.         <template #left>
  6.           <div class="tips">
  7.           <i class="el-icon-info" />
  8.           <span>总记录数: 200</span>
  9.           <div>
  10.         </template>
  11.         <template #right>
  12.           <el-button type="warning" size="small">excel导入</el-button>
  13.           <el-button type="danger" size="small">excel导出</el-button>
  14.           <el-button type="primary" size="small">新增员工</el-button>
  15.         </template>
  16.       </page-tools>
  18.       <el-card style="margin-top: 10px;">
  19.         <el-table border>
  20.           <el-table-column label="序号"/>
  21.           <el-table-column label="姓名"/>
  22.           <el-table-column label="工号"/>
  23.           <el-table-column label="聘用形式"/>
  24.           <el-table-column label="部门"/>
  25.           <el-table-column label="入职时间" width="180"/>
  26.           <el-table-column label="操作"  fixed="right" width="200">
  27.             <template>
  28.               <el-button type="text" size="small">查看</el-button>
  29.               <el-button type="text" size="small">分配角色</el-button>
  30.               <el-button type="text" size="small">删除</el-button>
  31.             </template>
  32.           </el-table-column>
  33.         </el-table>
  34.       </el-card>
  35.     </div>
  36.   </div>
  37. </template>
  1. /**
  2. *
  3. * @param {*} params { page 当前页数  size每页条数 }
  4. * @returns
  5. */
  6. export function getEmployeeListApi(params) {
  7.   return request({
  8.     methods: 'get',
  9.     url: '/sys/user',
  10.     params
  11.   })
  12. }

获取到的 入职方式和时间 不符合要求 格式化处理 采用插槽获取到当前行的数据 {{}} 显示数据 这样就可以处理到数据 定义一个方法 传入当前的数据 函数的返回值 会显示在{{}}这里

枚举

枚举写法 一次性列出 所有的对应情况 然后通过对象取值 称之为枚举 比多分枝语句对应关系更加清晰 方便拓展 通常少量的 不会发生变化的使用前端枚举 大量的变化必须接口来做

  1. disFormOfEmployment(value) {
  2.   const type = {
  3.     1: '正式员工',
  4.     2: '非正式员工',
  5.     3: '老板亲戚',
  6.     4: '临时工'
  7.   }
  8.   return type[value]
  9.     },
  1. <template>
  2. <div class="employees-container">
  3.   <div class="app-container">
  4.     <page-tools>
  5.       <template #left>
  6.         <div class="tips">
  7.           <i class="el-icon-info" />
  8.           <span>总记录数: 200</span>
  9.   </div>
  10. </template>
  11. <template #right>
  12. <el-button type="warning" size="small">excel导入</el-button>
  13. <el-button type="danger" size="small">excel导出</el-button>
  14. <el-button type="primary" size="small">新增员工</el-button>
  15. </template>
  16. </page-tools>
  18. <el-card style="margin-top: 10px;">
  19.   <el-table :data="employeeList" border>
  20.     <el-table-column label="序号" type="index" />
  21.     <el-table-column label="姓名" prop="username" />
  22.     <el-table-column label="工号" prop="workNumber" />
  23.     <el-table-column label="聘用形式">
  24.       <template #default="{row}">
  25. {{ disFormOfEmployment(row.formOfEmployment) }}
  26.       </template>
  27.     </el-table-column>
  28.     <el-table-column label="部门" prop="departmentName" />
  29.     <el-table-column label="入职时间" width="180">
  30.       <template #default="{row}">
  31. {{ disTimeOfEntry(row.timeOfEntry) }}
  32.       </template>
  33.     </el-table-column>
  34.     <el-table-column>
  35.       <el-table-column label="操作" fixed="right" width="200">
  36.         <template>
  37. <el-button type="text" size="small">查看</el-button>
  38. <el-button type="text" size="small">分配角色</el-button>
  39. <el-button type="text" size="small">删除</el-button>
  40.         </template>
  41.       </el-table-column>
  42.     </el-table-column>
  43.   </el-table>
  45. </el-card>
  46. </div>
  47. </div>
  48. </template>
  50. <script>
  51.   import dayjs from 'dayjs'
  52.   import { getEmployeeListApi } from '@/api/employeeSimple'
  53.   export default {
  54.     data() {
  55.       return {
  56.         parmas: {
  57.           page: 1, // 当前页
  58.           size: 1 // 每页条数
  59.         },
  60.         employeeList: [],
  61.         total: 0
  62.       }
  63.     },
  64.     mounted () {
  65.       this.getEmployeeList()
  66.     },
  67.     methods: {
  68.       async getEmployeeList() {
  69.         const res = await getEmployeeListApi(this.parmas)
  70.         this.employeeList = res.data.rows
  71.         this.total = res.total
  72.         // console.log(data)
  73.       },
  74.       disFormOfEmployment(value) {
  75.         const type = {
  76.           1: '正式员工',
  77.           2: '非正式员工',
  78.           3: '老板亲戚',
  79.           4: '临时工'
  80.         }
  81.         return type[value]
  82.       },
  83.       disTimeOfEntry(time) {
  84.         return dayjs(time).format('YYYY-MM-DD')
  85.       },
  86.       pageChange(page) {
  87.         this.parmas.page = page
  88.         this.getEmployeeList()
  89.       }
  90.     }
  91.   }
  92. </script>
  94. <style lang="scss" scoped></style>

分页器

@current-change="pageChange" 添加这个事件 点击会调用该函数且传入当前页码

  1. <div style="height: 60px; margin-top: 10px">
  2.   <el-pagination
  3.                  :total="total"
  4.                  :current-page="parmas.page"
  5.                  :page-size="parmas.size"
  6.                  layout="prev, pager, next"
  7.                  @current-change="pageChange"
  8.                  />
  9.   </div>
  1. export default {
  2.   data() {
  3.     return {
  4.       parmas: {
  5.         page: 1, // 当前页
  6.         size: 1 // 每页条数
  7.       },
  8.       employeeList: [],
  9.       total: 0
  10.     }
  11.   },
  12.   mounted () {
  13.     this.getEmployeeList()
  14.   },
  15.   methods: {
  16.     async getEmployeeList() {
  17.       const res = await getEmployeeListApi(this.parmas)
  18.       this.employeeList = res.data.rows
  19.       this.total = res.data.total
  20.     },
  21.     pageChange(page) {
  22.       this.parmas.page = page
  23.       this.getEmployeeList()
  24.     }
  25.   }
  26. }
  27. </script>

删除功能

默认插槽 #defalut=”{row}” 获取当前行数据 给删除按钮添加click 传入id 

  1. // 删除角色
  2. /**
  3.  *
  4.  * @param {*} id 员工id
  5.  * @returns
  6.  */
  7. export const delEmployee = id => request.delete(`/sys/user/${id}`)
  1. // 删除角色
  2.     async delEmployee(id) {
  3.       try {
  4.         await delEmployee(id)
  5.         // 重新获取列表渲染
  6.         this.getEmployeeList()
  7.         this.$message.success('删除角色成功')
  8.       } catch (error) {
  9.         this.$message.error(error)
  10.       }
  11.     },

新增功能

封装弹层组件 父子通信来控制弹层显隐 弹层组件内表单收集信息 （这里的联级组件选择的时候 会返回给一个数组 数组的最后一条才是需要的数据） departmentName: this.formData.departmentName[this.formData.departmentName.length - 1] 如果一个对象中发生了key重复 后面的会覆盖前面的 提交过去的就是处理好的那一条字符串 不再是组件提供的数组

  1. <template>
  2. <el-dialog title="新增员工" :visible="showDialog" @open="openDialog" @close="closeDialog">
  3.   <!-- 表单 -->
  4.   <el-form ref="formRef" label-width="120px" :model="formData" :rules="rules">
  5.     <el-form-item label="姓名" prop="username">
  6.       <el-input v-model="formData.username" style="width:50%" placeholder="请输入姓名" />
  7.   </el-form-item>
  8.     <el-form-item label="手机" prop="mobile">
  9.       <el-input v-model="formData.mobile" style="width:50%" placeholder="请输入手机号" />
  10.   </el-form-item>
  11.     <el-form-item label="入职时间" prop="timeOfEntry">
  12.       <el-date-picker v-model="formData.timeOfEntry" style="width:50%" placeholder="请选择入职时间" />
  13.   </el-form-item>
  14.     <el-form-item label="聘用形式" prop="formOfEmployment">
  15.       <el-select v-model="formData.formOfEmployment" style="width:50%" placeholder="请选择">
  16.         <el-option
  17.                    v-for="item in options"
  18.                    :key="item.type"
  19.                    :label="item.name"
  20.                    :value="item.id"
  21.                    />
  22.   </el-select></el-form-item>
  23.     <el-form-item label="工号" prop="workNumber">
  24.       <el-input v-model="formData.workNumber" style="width:50%" placeholder="请输入工号" />
  25.   </el-form-item>
  26.     <el-form-item label="部门" prop="departmentName">
  27.       <el-cascader
  28.                    v-model="formData.departmentName"
  29.                    placeholder="请选择部门"
  30.                    :options="departmentList"
  31.                    :props="{ checkStrictly: true, value:'name' ,label:'name' }"
  32.                    clearable
  33.                    />
  34.   </el-form-item>
  35.     <el-form-item label="转正时间">
  36.       <el-date-picker v-model="formData.correctionTime" style="width:50%" placeholder="请选择转正时间" />
  37.   </el-form-item>
  38.   </el-form>
  39.   <!-- footer插槽 -->
  40.   <template #footer>
  41.     <el-button>取消</el-button>
  42.     <el-button type="primary" @click="submit">确定</el-button>
  43. </template>
  44. </el-dialog>
  45. </template>
  47. <script>
  48.   import transTree from '@/utils/transTree'
  49.   import { getDepartmentsApi } from '@/api/departments'
  50.   import { addEmployee } from '@/api/employeeSimple'
  51.   export default {
  52.     props: {
  53.       showDialog: {
  54.         type: Boolean
  55.       }
  56.     },
  57.     data() {
  58.       return {
  59.         // 部门数据
  60.         departmentList: [],
  61.         // 录用形式
  62.         options: [
  63.           { id: 1, name: '正式员工' },
  64.           { id: 2, name: '非正式员工' }
  65.         ],
  66.         formData: {
  67.           username: '', // 用户名
  68.           mobile: '', // 手机号
  69.           formOfEmployment: '', // 聘用形式
  70.           workNumber: '', // 工号
  71.           departmentName: '', // 部门
  72.           timeOfEntry: '', // 入职时间
  73.           correctionTime: '' // 转正时间
  74.         },
  75.         rules: {
  76.           username: [
  77.             { required: true, message: '用户姓名不能为空', trigger: ['blur', 'change'] },
  78.             { min: 1, max: 4, message: '用户姓名为1-4位', trigger: ['blur', 'change'] }
  79.           ],
  80.           mobile: [
  81.             { required: true, message: '手机号不能为空', trigger: ['blur', 'change'] },
  82.             { pattern: /^1[3-9]\d{9}$/, message: '手机号格式不正确', trigger: ['blur', 'change'] }
  83.           ],
  84.           formOfEmployment: [
  85.             { required: true, message: '聘用形式不能为空', trigger: ['blur', 'change'] }
  86.           ],
  87.           workNumber: [
  88.             { required: true, message: '工号不能为空', trigger: ['blur', 'change'] }
  89.           ],
  90.           departmentName: [
  91.             { required: true, message: '部门不能为空', trigger: ['blur', 'change'] }
  92.           ],
  93.           timeOfEntry: [
  94.             { required: true, message: '请选择入职时间', trigger: ['blur', 'change'] }
  95.           ]
  96.         }
  97.       }
  98.     },
  99.     methods: {
  100.       closeDialog() {
  101.         this.$emit('close_dialog')
  102.         // 重置表单数据
  103.         this.formData = {
  104.           username: '',
  105.           mobile: '',
  106.           formOfEmployment: '',
  107.           workNumber: '',
  108.           departmentName: '',
  109.           timeOfEntry: '',
  110.           correctionTime: ''
  111.         }
  112.         // 重置校验结果
  113.         this.$refs.addForm.resetFields()
  114.       },
  115.       async getdepartment() {
  116.         const res = await getDepartmentsApi()
  117.         this.departmentList = transTree(res.data.depts)
  118.         console.log(this.departmentList)
  119.       },
  120.       openDialog() {
  121.         this.getdepartment()
  122.       },
  123.       submit() {
  124.         this.$refs.formRef.validate(async(isOk) => {
  125.           if (isOk) {
  126.             // departmentName 会出现多个 这里只需要上传一个
  127.             // 如果一个对象中发生了key重复 后面的会覆盖前面的 提交过去的就是处理好的那一条字符串 不再是组件提供的数组
  128.             await addEmployee({ ...this.formData,
  129.                                departmentName: this.formData.departmentName[this.formData.departmentName.length - 1]
  130.                               })
  131.             this.$message.success('新增成功')
  132.             this.closeDialog()
  133.           }
  134.         })
  135.       }
  136.     }
  137.   }
  138. </script>

导入导出功能

导入

新建一个页面在点击导入的时候他会跳转到这个页面

  1. {
  2.     path: '/import',
  3.     component: Layout,
  4.     redirect: '/dashboard',
  5.     children: [{
  6.       path: '',
  7.       name: 'import',
  8.       component: () => import('@/views/import')
  9.     }]
  10.   },

在项目模板中寻找到相同的功能 根据路由找到

项目地址 https://gitee.com/panjiachen/vue-element-admin
预览地址 https://panjiachen.gitee.io/vue-element-admin/#/excel/upload-excel

页面

这里依赖一个uploadexcel组件 需要下载 xlsx 依赖组 ，件提供了俩个props， 一个为解析Excel成功之后调用的函数 一个为上传之前的校验函数

  1. <template>
  2. <div class="app-container">
  3.   <upload-excel-component :on-success="handleSuccess" :before-upload="beforeUpload" />
  4.   <el-table :data="tableData" border highlight-current-row style="width: 100%;margin-top:20px;">
  5.     <el-table-column v-for="item of tableHeader" :key="item" :prop="item" :label="item" />
  6.   </el-table>
  7.   </div>
  8. </template>
  10. <script>
  11.   import UploadExcelComponent from '@/components/UploadExcel/index.vue'
  12.   import { importEmployee } from '@/api/employeeSimple'
  13.   import { getImportJsData } from '@/utils/excel'
  15.   export default {
  16.     name: 'UploadExcel',
  17.     components: { UploadExcelComponent },
  18.     data() {
  19.       return {
  20.         tableData: [],
  21.         tableHeader: []
  22.       }
  23.     },
  24.     methods: {
  25.       beforeUpload(file) {
  26.         // 文件大小 /1024 两次 就换算到单位为m
  27.         const isLt1M = file.size / 1024 / 1024 < 1
  29.         if (isLt1M) {
  30.           return true
  31.         }
  33.         this.$message({
  34.           message: 'Please do not upload files larger than 1m in size.',
  35.           type: 'warning'
  36.         })
  37.         return false
  38.       },
  39.       async handleSuccess({ results, header }) {
  40.         // 1.拿到数据 通过格式化方法 处理成后端要求的数据格式
  41.         // 2.调用真实接口传递数据
  42.         const headerRelation = {
  43.           '姓名': 'username',
  44.           '手机号': 'mobile',
  45.           '入职日期': 'timeOfEntry',
  46.           '工号': 'workNumber',
  47.           '聘用形式': 'formOfEmployment',
  48.           '部门': 'departmentName'
  49.         }
  50.         const formatData = getImportJsData(results, headerRelation)
  51.         await importEmployee(formatData)
  52.         this.$message.success('提交成功')
  53.         this.$router.back()
  54.       }
  55.     }
  56.   }
  57. </script>

uploadexcel组件

这里的使用的xlsx版本比较老 ，需要导出所有并改别名为之前的 import * as XLSX from 'xlsx' 否则导入会报错如下 image.png

  1. <template>
  2.   <div>
  3.     <input ref="excel-upload-input" class="excel-upload-input" type="file" accept=".xlsx, .xls" @change="handleClick">
  4.     <div class="drop" @drop="handleDrop" @dragover="handleDragover" @dragenter="handleDragover">
  5.       Drop excel file here or
  6.       <el-button :loading="loading" style="margin-left:16px;" size="mini" type="primary" @click="handleUpload">
  7.         Browse
  8.       </el-button>
  9.     </div>
  10.   </div>
  11. </template>
  13. <script>
  14. import * as XLSX from 'xlsx'
  16. export default {
  17.   props: {
  18.     // 解析前的校验函数 看文件是否大于1m 格式是否满足
  19.     beforeUpload: Function, // eslint-disable-line
  20.     // 解析成功之后的回调函数 会自动调用 并且会把解析之后的数据当成实参传入
  21.     onSuccess: Function// eslint-disable-line
  22.   },
  23.   data() {
  24.     return {
  25.       loading: false,
  26.       excelData: {
  27.         header: null,
  28.         results: null
  29.       }
  30.     }
  31.   },
  32.   methods: {
  33.     generateData({ header, results }) {
  34.       this.excelData.header = header
  35.       this.excelData.results = results
  36.       this.onSuccess && this.onSuccess(this.excelData)
  37.     },
  38.     handleDrop(e) {
  39.       e.stopPropagation()
  40.       e.preventDefault()
  41.       if (this.loading) return
  42.       const files = e.dataTransfer.files
  43.       if (files.length !== 1) {
  44.         this.$message.error('Only support uploading one file!')
  45.         return
  46.       }
  47.       const rawFile = files[0] // only use files[0]
  49.       if (!this.isExcel(rawFile)) {
  50.         this.$message.error('Only supports upload .xlsx, .xls, .csv suffix files')
  51.         return false
  52.       }
  53.       this.upload(rawFile)
  54.       e.stopPropagation()
  55.       e.preventDefault()
  56.     },
  57.     handleDragover(e) {
  58.       e.stopPropagation()
  59.       e.preventDefault()
  60.       e.dataTransfer.dropEffect = 'copy'
  61.     },
  62.     handleUpload() {
  63.       this.$refs['excel-upload-input'].click()
  64.     },
  65.     handleClick(e) {
  66.       const files = e.target.files
  67.       const rawFile = files[0] // only use files[0]
  68.       if (!rawFile) return
  69.       this.upload(rawFile)
  70.     },
  71.     upload(rawFile) {
  72.       this.$refs['excel-upload-input'].value = null // fix can't select the same excel
  74.       if (!this.beforeUpload) {
  75.         this.readerData(rawFile)
  76.         return
  77.       }
  78.       const before = this.beforeUpload(rawFile)
  79.       if (before) {
  80.         this.readerData(rawFile)
  81.       }
  82.     },
  83.     readerData(rawFile) {
  84.       this.loading = true
  85.       return new Promise((resolve, reject) => {
  86.         const reader = new FileReader()
  87.         reader.onload = e => {
  88.           const data = e.target.result
  89.           const workbook = XLSX.read(data, { type: 'array' })
  90.           const firstSheetName = workbook.SheetNames[0]
  91.           const worksheet = workbook.Sheets[firstSheetName]
  92.           const header = this.getHeaderRow(worksheet)
  93.           const results = XLSX.utils.sheet_to_json(worksheet)
  94.           this.generateData({ header, results })
  95.           this.loading = false
  96.           resolve()
  97.         }
  98.         reader.readAsArrayBuffer(rawFile)
  99.       })
  100.     },
  101.     getHeaderRow(sheet) {
  102.       const headers = []
  103.       const range = XLSX.utils.decode_range(sheet['!ref'])
  104.       let C
  105.       const R = range.s.r
  106.       /* start in the first row */
  107.       for (C = range.s.c; C <= range.e.c; ++C) { /* walk every column in the range */
  108.         const cell = sheet[XLSX.utils.encode_cell({ c: C, r: R })]
  109.         /* find the cell in the first row */
  110.         let hdr = 'UNKNOWN ' + C // <-- replace with your desired default
  111.         if (cell && cell.t) hdr = XLSX.utils.format_cell(cell)
  112.         headers.push(hdr)
  113.       }
  114.       return headers
  115.     },
  116.     isExcel(file) {
  117.       return /\.(xlsx|xls|csv)$/.test(file.name)
  118.     }
  119.   }
  120. }
  121. </script>
  123. <style scoped>
  124. .excel-upload-input{
  125.   display: none;
  126.   z-index: -9999;
  127. }
  128. .drop{
  129.   border: 2px dashed #bbb;
  130.   width: 600px;
  131.   height: 160px;
  132.   line-height: 160px;
  133.   margin: 0 auto;
  134.   font-size: 24px;
  135.   border-radius: 5px;
  136.   text-align: center;
  137.   color: #bbb;
  138.   position: relative;
  139. }
  140. </style>

使用excel表格批量导入

封装一个工具uitils/excel导入里面的getImportJsData方法

  1. /**
  2. * @description: 获取导出时的表头数据和表格数据
  3. * @param {*} { sourceData:后端返回的源数据,header:表格头中因为对应关系}
  4. * @return {*}
  5. */
  7. // 处理函数中对key判断如果发生当前它是聘用形式 通过枚举做一下处理 返回中文
  9. // 枚举处理函数 根据1/2返回正式或者非正式
  10. function transEmployment(value) {
  11.   const TYPES = {
  12.     1: '正式',
  13.     2: '非正式'
  14.   }
  15.   return TYPES[value]
  16. }
  18. export function getExportData(sourceData, headerRelation) {
  19.   const data = sourceData.map(item => {
  20.     const arr = []
  21.     Object.values(headerRelation).forEach(key => {
  22.       // 关键位置：把所有的value不做任何处理直接扔到了数组中 导致excel是源数据
  23.       // 如果是当前要处理的是聘用形式 就先转化一步再添加到数组中
  24.       if (key === 'formOfEmployment') {
  25.         const formatValue = transEmployment(item[key])
  26.         arr.push(formatValue)
  27.       } else {
  28.         arr.push(item[key])
  29.       }
  30.     })
  31.     return arr
  32.   })
  33.   return {
  34.     data
  35.   }
  36. }
  38. /**
  39. * @description: 获取导入时的处理之后的接口数据
  40. * @param {*} results
  41. * @return {*}
  42. */
  43. export function getImportJsData(results, headerRelation) {
  44.   const newArr = []
  45.   // 将所有的中文key转换成英文key 然后添加到新数组中
  46.   results.forEach(item => {
  47.     const map = {}
  48.     Object.keys(item).forEach(key => {
  49.       map[headerRelation[key]] = item[key]
  50.     })
  51.     newArr.push(map)
  52.   })
  53.   // 时间处理
  54.   newArr.forEach(item => {
  55.     Object.keys(item).forEach(key => {
  56.       if (key === 'timeOfEntry') {
  57.         item[key] = new Date(formatDate(item[key], '/'))
  58.       }
  59.     })
  60.   })
  61.   return newArr
  62. }
  64. export function formatDate(numb, format) {
  65.   const time = new Date((numb - 1) * 24 * 3600000 + 1)
  66.   time.setYear(time.getFullYear() - 70)
  67.   const year = time.getFullYear() + ''
  68.   const month = time.getMonth() + 1 + ''
  69.   const date = time.getDate() - 1 + ''
  70.   if (format && format.length === 1) {
  71.     return year + format + (month < 10 ? '0' + month : month) + format + (date < 10 ? '0' + date : date)
  72.   }
  73.   return year + (month < 10 ? '0' + month : month) + (date < 10 ? '0' + date : date)
  74. }

调用接口提交处理好的数据

  1. /**
  2.  * @description: 导入excel
  3.  * @param {*} data 
  4.     [{
  5.            mobile: 15751786320
  6.            timeOfEntry: "2019-03-09T16:00:00.000Z"
  7.           username: "测试人员1"
  8.           workNumber: 88088,
  9.           formOfEmployment: 0,
  10.           departmentName: "技术部"
  11.       }
  12.     ]
  13.  * @return {*}
  14.  */
  15. export function importEmployee(data) {
  16.   return request({
  17.     url: '/sys/user/batch',
  18.     method: 'post',
  19.     data
  20.   })
  21. }
  1. import { importEmployee } from '@/api/employees'
  2. import { getImportJsData } from '@/utils/excelData'
  3. export default {
  4.   name: 'ImportExcel',
  5.   methods: {
  6.     async handleSuccess({ results, header }) {
  7.       // 1.拿到数据 通过格式化方法 处理成后端要求的数据格式
  8.       // 2.调用真实接口传递数据
  9.       const headerRelation = {
  10.         '姓名': 'username',
  11.         '手机号': 'mobile',
  12.         '入职日期': 'timeOfEntry',
  13.         '工号': 'workNumber',
  14.         '聘用形式': 'formOfEmployment',
  15.         '部门': 'departmentName'
  16.       }
  17.       const formatData = getImportJsData(results, headerRelation)
  18.       await importEmployee(formatData)
  19.     }
  20.   }
  21. }

导出

查看导出功能 ，根据路由查找

找到了点击导出的事件绑定的函数handleDownload

import(@/vendor/Export2Execel.js) 动态导入 这里写在函数内部 调用了函数才会动态导入这个文件
常见于比较大的包且不是使用频繁的功能 什么时候用 什么时候导入

安装依赖的包 npm i file-saver

使用相同的方法 先创建一个样的js文件

  1. /* eslint-disable */
  2. import { saveAs } from 'file-saver'
  3. import * as XLSX from 'xlsx'
  5. function generateArray(table) {
  6.   var out = [];
  7.   var rows = table.querySelectorAll('tr');
  8.   var ranges = [];
  9.   for (var R = 0; R < rows.length; ++R) {
  10.     var outRow = [];
  11.     var row = rows[R];
  12.     var columns = row.querySelectorAll('td');
  13.     for (var C = 0; C < columns.length; ++C) {
  14.       var cell = columns[C];
  15.       var colspan = cell.getAttribute('colspan');
  16.       var rowspan = cell.getAttribute('rowspan');
  17.       var cellValue = cell.innerText;
  18.       if (cellValue !== "" && cellValue == +cellValue) cellValue = +cellValue;
  20.       //Skip ranges
  21.       ranges.forEach(function (range) {
  22.         if (R >= range.s.r && R <= range.e.r && outRow.length >= range.s.c && outRow.length <= range.e.c) {
  23.           for (var i = 0; i <= range.e.c - range.s.c; ++i) outRow.push(null);
  24.         }
  25.       });
  27.       //Handle Row Span
  28.       if (rowspan || colspan) {
  29.         rowspan = rowspan || 1;
  30.         colspan = colspan || 1;
  31.         ranges.push({
  32.           s: {
  33.             r: R,
  34.             c: outRow.length
  35.           },
  36.           e: {
  37.             r: R + rowspan - 1,
  38.             c: outRow.length + colspan - 1
  39.           }
  40.         });
  41.       };
  43.       //Handle Value
  44.       outRow.push(cellValue !== "" ? cellValue : null);
  46.       //Handle Colspan
  47.       if (colspan)
  48.         for (var k = 0; k < colspan - 1; ++k) outRow.push(null);
  49.     }
  50.     out.push(outRow);
  51.   }
  52.   return [out, ranges];
  53. };
  55. function datenum(v, date1904) {
  56.   if (date1904) v += 1462;
  57.   var epoch = Date.parse(v);
  58.   return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
  59. }
  61. function sheet_from_array_of_arrays(data, opts) {
  62.   var ws = {};
  63.   var range = {
  64.     s: {
  65.       c: 10000000,
  66.       r: 10000000
  67.     },
  68.     e: {
  69.       c: 0,
  70.       r: 0
  71.     }
  72.   };
  73.   for (var R = 0; R != data.length; ++R) {
  74.     for (var C = 0; C != data[R].length; ++C) {
  75.       if (range.s.r > R) range.s.r = R;
  76.       if (range.s.c > C) range.s.c = C;
  77.       if (range.e.r < R) range.e.r = R;
  78.       if (range.e.c < C) range.e.c = C;
  79.       var cell = {
  80.         v: data[R][C]
  81.       };
  82.       if (cell.v == null) continue;
  83.       var cell_ref = XLSX.utils.encode_cell({
  84.         c: C,
  85.         r: R
  86.       });
  88.       if (typeof cell.v === 'number') cell.t = 'n';
  89.       else if (typeof cell.v === 'boolean') cell.t = 'b';
  90.       else if (cell.v instanceof Date) {
  91.         cell.t = 'n';
  92.         cell.z = XLSX.SSF._table[14];
  93.         cell.v = datenum(cell.v);
  94.       } else cell.t = 's';
  96.       ws[cell_ref] = cell;
  97.     }
  98.   }
  99.   if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
  100.   return ws;
  101. }
  103. function Workbook() {
  104.   if (!(this instanceof Workbook)) return new Workbook();
  105.   this.SheetNames = [];
  106.   this.Sheets = {};
  107. }
  109. function s2ab(s) {
  110.   var buf = new ArrayBuffer(s.length);
  111.   var view = new Uint8Array(buf);
  112.   for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
  113.   return buf;
  114. }
  116. export function export_table_to_excel(id) {
  117.   var theTable = document.getElementById(id);
  118.   var oo = generateArray(theTable);
  119.   var ranges = oo[1];
  121.   /* original data */
  122.   var data = oo[0];
  123.   var ws_name = "SheetJS";
  125.   var wb = new Workbook(),
  126.     ws = sheet_from_array_of_arrays(data);
  128.   /* add ranges to worksheet */
  129.   // ws['!cols'] = ['apple', 'banan'];
  130.   ws['!merges'] = ranges;
  132.   /* add worksheet to workbook */
  133.   wb.SheetNames.push(ws_name);
  134.   wb.Sheets[ws_name] = ws;
  136.   var wbout = XLSX.write(wb, {
  137.     bookType: 'xlsx',
  138.     bookSST: false,
  139.     type: 'binary'
  140.   });
  142.   saveAs(new Blob([s2ab(wbout)], {
  143.     type: "application/octet-stream"
  144.   }), "test.xlsx")
  145. }
  147. export function export_json_to_excel({
  148.   multiHeader = [],
  149.   header,
  150.   data,
  151.   filename,
  152.   merges = [],
  153.   autoWidth = true,
  154.   bookType = 'xlsx'
  155. } = {}) {
  156.   /* original data */
  157.   filename = filename || 'excel-list'
  158.   data = [...data]
  159.   data.unshift(header);
  161.   for (let i = multiHeader.length - 1; i > -1; i--) {
  162.     data.unshift(multiHeader[i])
  163.   }
  165.   var ws_name = "SheetJS";
  166.   var wb = new Workbook(),
  167.     ws = sheet_from_array_of_arrays(data);
  169.   if (merges.length > 0) {
  170.     if (!ws['!merges']) ws['!merges'] = [];
  171.     merges.forEach(item => {
  172.       ws['!merges'].push(XLSX.utils.decode_range(item))
  173.     })
  174.   }
  176.   if (autoWidth) {
  177.     /*设置worksheet每列的最大宽度*/
  178.     const colWidth = data.map(row => row.map(val => {
  179.       /*先判断是否为null/undefined*/
  180.       if (val == null) {
  181.         return {
  182.           'wch': 10
  183.         };
  184.       }
  185.       /*再判断是否为中文*/
  186.       else if (val.toString().charCodeAt(0) > 255) {
  187.         return {
  188.           'wch': val.toString().length * 2
  189.         };
  190.       } else {
  191.         return {
  192.           'wch': val.toString().length
  193.         };
  194.       }
  195.     }))
  196.     /*以第一行为初始值*/
  197.     let result = colWidth[0];
  198.     for (let i = 1; i < colWidth.length; i++) {
  199.       for (let j = 0; j < colWidth[i].length; j++) {
  200.         if (result[j]['wch'] < colWidth[i][j]['wch']) {
  201.           result[j]['wch'] = colWidth[i][j]['wch'];
  202.         }
  203.       }
  204.     }
  205.     ws['!cols'] = result;
  206.   }
  208.   /* add worksheet to workbook */
  209.   wb.SheetNames.push(ws_name);
  210.   wb.Sheets[ws_name] = ws;
  212.   var wbout = XLSX.write(wb, {
  213.     bookType: bookType,
  214.     bookSST: false,
  215.     type: 'binary'
  216.   });
  217.   saveAs(new Blob([s2ab(wbout)], {
  218.     type: "application/octet-stream"
  219.   }), `${filename}.${bookType}`);
  220. }

给导出绑定点击事件，写入导出方法

export_json_to_excel方法传入的参数 header 导出数据的表头
data 导出的具体数据 要求是二维数组[ [ ] ]
filename 导出的文件名
autoWidth 单元格宽度自适应 true bookType 导出的文件类型 xlsx

  1. import('@/vendor/Export2Excel').then(excel => {
  2.   // excel表示导入的模块对象
  3.   excel.export_json_to_excel({
  4.     header: ['姓名', '工资'], // 表头 必填
  5.     data: [
  6.       ['刘备', 100],
  7.       ['关羽', 500]
  8.     ], // 具体数据 必填
  9.     filename: 'excel-list', // 文件名称
  10.     autoWidth: true, // 宽度是否自适应
  11.     bookType: 'xlsx' // 生成的文件类型
  12.   })
  13. })

格式化数据 为需要的数据

使用util/excel里getExportData方法 传入导入的数据和对应关系

  1. export function getExportData(sourceData, headerRelation) {
  2.   const data = sourceData.map(item => {
  3.     const arr = []
  4.     Object.values(headerRelation).forEach(key => {
  5.       // 关键位置：把所有的value不做任何处理直接扔到了数组中 导致excel是源数据
  6.       // 如果是当前要处理的是聘用形式 就先转化一步再添加到数组中
  7.       if (key === 'formOfEmployment') {
  8.         const formatValue = transEmployment(item[key])
  9.         arr.push(formatValue)
  10.       } else {
  11.         arr.push(item[key])
  12.       }
  13.     })
  14.     return arr
  15.   })
  16.   return {
  17.     data
  18.   }
  19. }
  1. // 导出excel表格
  2.     exportExcel() {
  3.       import('@/views/vendor/Export2Execel.js').then(async excel => {
  4.         const res = await getEmployeeListApi(this.parmas)
  5.         console.log(res)
  6.         const headerRelation = {
  7.           '姓名': 'username',
  8.           '手机号': 'mobile',
  9.           '入职日期': 'timeOfEntry',
  10.           '工号': 'workNumber',
  11.           '聘用形式': 'formOfEmployment',
  12.           '部门': 'departmentName'
  13.         }
  14.         const { data } = getExportData(res.data.rows, headerRelation)
  15.         // excel表示导入的模块对象
  16.         excel.export_json_to_excel({
  17.           header: Object.keys(headerRelation), // 表头 必填
  18.           data: data, // 具体数据 必填
  19.           filename: 'excel-list', // 文件名称
  20.           autoWidth: true, // 宽度是否自适应
  21.           bookType: 'xlsx' // 生成的文件类型
  22.         })
  23.       })
  24.     }