Import and export Excel files

Employee import component encapsulation

Target: Encapsulate a file that imports Excel data

The first thing to note is that vue-element-admin already provides similar functionality, so we just need to modify itThe code address

Similarly functional components, we only need to be able to use and package

The Excel import function requires the NPM package ** XLSX, so you need to install the XLSX ** plug-in

$ npm i xlsx
Copy the code

Will provide the import function of vue – element – the admin to create a new component, location: the SRC/components/UploadExcel

Register the global import Excel component

import PageTools from './PageTools'
import UploadExcel from './UploadExcel'
export default {
  install(Vue) {
    Vue.component('PageTools', PageTools) // Register the toolbar component
    Vue.component('UploadExcel', UploadExcel) // Register to import excel components}}Copy the code

Modify styles and layouts

<template> <div class="upload-excel"> <div class="btn-upload"> <el-button :loading="loading" size="mini" type="primary" </el-button> </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"> < I class="el-icon-upload" /> <span> Drag the file here </span> </div> </div> </template> <style scoped lang="scss"> .upload-excel { display: flex; justify-content: center; margin-top: 100px; .excel-upload-input{ display: none; z-index: -9999; } .btn-upload , .drop{ border: 1px dashed #bbb; width: 350px; height: 160px; text-align: center; line-height: 160px; } .drop{ line-height: 80px; color: #bbb; i { font-size: 60px; display: block; } } } </style>Copy the code

Submit code

Task of this section: Employee import component encapsulation

Employee import

Objective: To achieve employee recruitment

Establish a common imported page route

Create a new public import page and mount the route SRC /router/index.js

{
    path: '/import'.component: Layout,
    hidden: true.// Hide it in the left menu
    children: [{
      path: ' '.// The path is not written to indicate the default secondary route
      component: () = > import('@/views/import')}}],Copy the code

Create the import routing component SRC/views/import/index. Vue

<template> <! <upload-excel :on-success="success" /> </template>Copy the code

Analyze excel import code and encapsulate interface

Encapsulate the API interface for importing employees

/** ** encapsulates an interface for importing employees ** ***/

export function importEmployee(data) {
  return request({
    url: '/sys/user/batch'.method: 'post',
    data
  })
}
Copy the code

Implementing Excel import

Obtain the imported Excel data and import the Excel interface

    async  success({ header, results }) {
      // If you are importing employees
        const userRelations = {
          'Entry Date': 'timeOfEntry'.'Mobile phone Number': 'mobile'.'name': 'username'.'Date of conversion': 'correctionTime'.'working': 'workNumber'
        }
        const arr = []
       results.forEach(item= > {
          const userInfo = {}
          Object.keys(item).forEach(key= > {
            userInfo[userRelations[key]] = item[key]
          })
         arr.push(userInfo) 
        })
        await importEmployee(arr) // Call the import interface
        this.$router.back()
    }
Copy the code

In order to enable this page to serve more import functions, we can use parameters in the page to determine whether to import employees

 data() {
    return {
      type: this.$route.query.type
    }
  },
Copy the code

When the date format is available in Excel, the actual converted value is a number, and we need a method to convert it

    formatDate(numb, format) {
      const time = new Date((numb - 1) * 24 * 3600000 + 1)
      time.setYear(time.getFullYear() - 70)
      const year = time.getFullYear() + ' '
      const month = time.getMonth() + 1 + ' '
      const date = time.getDate() - 1 + ' '
      if (format && format.length === 1) {
        return year + format + month + format + date
      }
      return year + (month < 10 ? '0' + month : month) + (date < 10 ? '0' + date : date)
    }
Copy the code

The imported mobile phone number cannot be the same as the existing mobile phone number

logic

 async  success({ header, results }) {
      if (this.type === 'user') {
        const userRelations = {
          'Entry Date': 'timeOfEntry'.'Mobile phone Number': 'mobile'.'name': 'username'.'Date of conversion': 'correctionTime'.'working': 'workNumber'
        }
        const arr = []
        // Iterate over all arrays
        results.forEach(item= > {
        // We need to change the Chinese characters in each entry into English
          const userInfo = {}
          Object.keys(item).forEach(key= > {
          // key is the current Chinese name
            if (userRelations[key] === 'timeOfEntry' || userRelations[key] === 'correctionTime') {
              userInfo[userRelations[key]] = new Date(this.formatDate(item[key], '/')) // Only in this way can the database be stored
              return
            }
            userInfo[userRelations[key]] = item[key]
          })
          // Finally userInfo becomes all English
          arr.push(userInfo)
        })
        await importEmployee(arr)
        this.$message.success('Import successful')}this.$router.back() // Return to the previous page
    },
    formatDate(numb, format) {
      const time = new Date((numb - 1) * 24 * 3600000 + 1)
      time.setYear(time.getFullYear() - 70)
      const year = time.getFullYear() + ' '
      const month = time.getMonth() + 1 + ' '
      const date = time.getDate() - 1 + ' '
      if (format && format.length === 1) {
        return year + format + month + format + date
      }
      return year + (month < 10 ? '0' + month : month) + (date < 10 ? '0' + date : date)
    }
Copy the code

Employee page Redirect

<el-button type="warning" size="small" @click="$router.push('/import? Type = user ') "> import < / el - button >Copy the code

Objective: To achieve employee recruitment

Employee export Excel function

Objective: To realize the function of exporting employee data

In daily business, we often encounter the Excel export function, how to use it

Excel import and export are dependent on JS-XLSX to achieve.

On the basis of JS-XlsX, Export2Excel. Js is encapsulated to export data conveniently.

Installing Excel requires dependencies and loading on demand

Because Export2Excel relies not only on Js-xlsx but also on file-saver and script-loader.

So you need to install the following command first:

npm install xlsx file-saver -S
npm install script-loader -S -D
Copy the code

Because jS-XLSX volume is still very large, the export function is not a very common function, so the use of lazy loading is recommended. The usage method is as follows:

import('@/vendor/Export2Excel').then(excel => { excel.export_json_to_excel({ header: Filename: 'excel-list', autoWidth: true, // bookType: 'XLSX' // optional})})Copy the code

Excel export parameters

Vue-element-admin provides the export function module, which is placed in the course Resources/Excel export directory in the SRC directory

parameter

Excel exports the basic structure

One of the most important things we did was to match the header to the data

Because the key in the data is in English, if you want to export the table header in Chinese, you need to match Chinese and English

Const headers = {'mobile' : 'mobile', 'name' : 'username', 'timeOfEntry', 'form ': 'formOfEmployment',' date ': 'correctionTime', 'workNumber' : 'departmentName'}Copy the code

Then, complete the export code

Const headers = {' name' : 'username', 'mobile' : 'mobile', 'start date ': 'timeOfEntry', 'form ': 'formOfEmployment', 'date ': 'correctionTime', 'workNumber' :' department ': 'departmentName'} // lazy load import('@/vendor/Export2Excel'). Then (async excel => {const {rows} = await getEmployeeList({  page: 1, size: this.page.total }) const data = this.formatJson(headers, rows) excel.export_json_to_excel({ header: Keys (headers), data, filename: 'employee information table ', autoWidth: true, bookType: 'XLSX}) / / get all the data / / excel export_json_to_excel ({/ / header: [' name', 'pay'], / / data: [[' zhang, 12000], [' bill ', 5000]], / / filename: 'wages table', / / autoWidth: true, / / bookType: 'CSV' //})})}, // This method is used to convert the array formatJson(headers, rows) {// [{username: 'Joe'}, {}, {}] = > [[' zhang '], [], []] return rows. The map (item = > {return Object. The keys (headers). The map (key = > {the if (headers [key] = = = 'timeOfEntry' | | headers [key] = = = 'correctionTime') {return formatDate (item [headers [key]]) / / before returning to format the time} else if (headers[key] === 'formOfEmployment') { var en = EmployeeEnum.hireType.find(obj => obj.id === item[headers[key]]) return  en ? en.value : 'unknown'} return item [headers [key]]}) / / = > [" zhang ", "13811", "2018", "1", "2018", "10002"]}) // return data // return rows.map(item => {// // item is an object => convert to an array of values => array values are dependent on headers {username: 'Joe'} / / / / Object. The keys (headers) = > (" name ", "phone number",... / / return Object. Keys (headers). The map (key = > {/ / return item [headers [key]] / /}) / / / get [' zhang ', '129', 'dd', 'dd'] / /})}Copy the code

Export time format processing

FormatJson (headers, rows) {return rows.map(item => {// item is an object {mobile: 132111,username: 'Zhang SAN'} // [" mobile phone number ", "name "," start date ".. The return Object. Keys (headers). The map (key field = > {/ / need to decide if (headers [key] = = = 'timeOfEntry' | | headers [key] = = = 'correctionTime') {return formatDate(item[key]])} else if (headers[key] === 'formOfEmployment') { const obj = EmployeeEnum.hireType.find(obj => obj.id === item[headers[key]]) return obj ? obj.value : 'unknown'} return item [headers [key]]}) / / / "132", 'zhang', ' ', ' ', }) return rows.map(item => object.keys (headers).map(key => item[headers]])Copy the code

extensionExport of complex table headers

Vue-element-admin also supports this type of operation when exporting complex table headers

Vue-element-admin provides export methods with parameters for multiHeader and merges

The multiHeader is a two-dimensional array, and one of its elements is a row of table headers, assuming you want a structure like the one shown here

MutiHeader should be defined like this

Const multiHeader = [[' name ', 'main information', ' 'and', ', ', 'departments']]Copy the code

The number of fields in the header of a row in multiHeader needs to be equal to the actual number of columns, assuming that to span columns, the extra space needs to be defined as empty strings

It mainly corresponds to the standard header

Const header = [' name ', 'phone number', 'entry date', 'employment form', 'positive date', 'working', 'departments']Copy the code

If we want to achieve the merges effect, we need to set the Merges option

 const merges = ['A1:A2', 'B1:F1', 'G1:G2']
Copy the code

The merges order doesn’t matter; you can export Excel for complex table headers by configuring these two properties

ExportData () {const headers = {' name' : 'username', 'mobile' : 'timeOfEntry', 'form of employment ': 'formOfEmployment', 'date ': 'correctionTime', 'workNumber' :' department ': 'departmentName'} // Export excel import('@/vendor/Export2Excel'). Then (async Excel => {// Excel is an export object that imports files // Where export headers come from Const {rows} = await getEmployeeList({page: 1, size: 0) {rows} = await getEmployeeList({page: 1, size: 0) This.page. Total}) const data = this.formatjson (headers, rows) const multiHeader = [[' name ', 'main info ', ' ', ' ', ' 'and', 'departments']] const merges = [' A1, A2, B1:' F1 'and' G1, G2] excel. Export_json_to_excel ({header: Object.keys(headers), data, filename: Export_json_to_excel ({// header: [' name ', 'salary '], // data: [[' zhang, 3000], [' bill ', 5000]], / / filename: 'payroll employees') / / / /} [{the username:' Joe Smith, mobile: 13112345678}] => [[]] => [[]] => [[]] => [[]] => [[]] => [[]] => [[]] Rows) {return rows.map(item => {// item is an object {mobile: 132111,username: '3'} // [" phone number ", "name "," date ". The return Object. Keys (headers). The map (key field = > {/ / need to decide if (headers [key] = = = 'timeOfEntry' | | headers [key] = = = 'correctionTime') {return formatDate(item[key]])} else if (headers[key] === 'formOfEmployment') { const obj = EmployeeEnum.hireType.find(obj => obj.id === item[headers[key]]) return obj ? obj.value : 'unknown'} return item [headers [key]]}) / / / "132", 'zhang', ' ', ' ', }) return rows.map(item => object.keys (headers).map(key => item[headers]])Copy the code

Submit code