Vue elementUi export excel table function realization

Vue elementUi export excel table function realization

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

The import and export of Excel rely on js-xlsx to achieve.

In js-xlsx based on and encapsulates Export2Excel.js to facilitate export data.

Dependencies required to install excel and load on demand

Because Export2Excel depends not only on js-xlsx but also on file-saver and script-loader .

So you first need to install the following commands:

npm install xlsx file-saver -S
npm install script-loader -S -D

Since js-xlsx is still very large, the export function is not a very commonly used function, so it is recommended to use lazy loading when using it. The method of use is as follows:

import('@/vendor/Export2Excel').then(excel => {
  excel.export_json_to_excel({
    header: tHeader, //表头 必填
    data, //具体数据 必填
    filename: 'excel-list', //非必填
    autoWidth: true, //非必填
    bookType: 'xlsx' //非必填
  })
})

Introduction to excel export parameters

vue-element-admin provides exported functional modules

Parameters

parameter	illustrate	type	Optional value	Defaults
header	The header of the exported data	Array	/	[]
data	Specific data exported	Array	/	[[]]
filename	Export file name	String	/	excel-list
autoWidth	Whether the cell needs to adapt to the width	Boolean	true / false	true
bookType	Export file type	String	xlsx, csv, txt, more	xlsx

Excel export basic structure

The most important thing for us is to correspond the header and data accordingly

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

   const headers = {
        '手机号': 'mobile',
        '姓名': 'username',
        '工号': 'workNumber',
      }

Then, completes the export code

    // 导出excel数据
    exportData() {
      //  做操作
      // 表头对应关系
      const headers = {
        '姓名': 'username',
        '手机号': 'mobile',
        '工号': 'workNumber',
      }
      // 懒加载
      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: Object.keys(headers),
          data,
          filename: '员工信息表',
          autoWidth: true,
          bookType: 'xlsx'

        })
      })
    },
    // 该方法负责将数组转化成二维数组
    formatJson(headers, rows) {
      // 首先遍历数组
      // [{ username: '张三'},{},{}]  => [[’张三'],[],[]]
      return rows.map(item => {
        return Object.keys(headers).map(key => {
          if (headers[key] === 'timeOfEntry' || headers[key] === 'correctionTime') {
            return formatDate(item[headers[key]]) // 返回格式化之前的时间
          } else if (headers[key] === 'formOfEmployment') {
            var en = EmployeeEnum.hireType.find(obj => obj.id === item[headers[key]])
            return en ? en.value : '未知'
          }
          return item[headers[key]]
        }) // => ["张三", "13811"，"2018","1", "2018", "10002"]
      })
    }

Export time format processing

    formatJson(headers, rows) {
      return rows.map(item => {
        // item是一个对象  { mobile: 132111,username: '张三'  }
        // ["手机号", "姓名", "入职日期" 。。]
        return Object.keys(headers).map(key => {
          // 需要判断 字段
          if (headers[key] === 'timeOfEntry' || headers[key] === 'correctionTime') {
            // 格式化日期
            return formatDate(item[headers[key]])
          } else if (headers[key] === 'formOfEmployment') {
            const obj = EmployeeEnum.hireType.find(obj => obj.id === item[headers[key]])
            return obj ? obj.value : '未知'
          }
          return item[headers[key]]
        })
        // ["132", '张三’， ‘’，‘’，‘’d]
      })
      // return rows.map(item => Object.keys(headers).map(key => item[headers[key]]))
      // 需要处理时间格式问题
    }

extension export of complex headers

When you need to export complex headers, vue-element-admin also supports this type of operation

vue-element-admin provides the export method with multiHeader and merges parameters

parameter	illustrate	type	Optional value	Defaults
multiHeader	Complex header part	Array	/	[[]]
merges	The part that needs to be merged	Array	/	[]

There is a two-dimensional array in multiHeader, and one element in it is a row header. Suppose you want to get a structure as shown in the figure.

mutiHeader should be defined like this

const multiHeader = [['姓名', '主要信息', '', '', '', '', '部门']]

The number of fields in the header of a row in multiHeader needs to be equal to the actual number of columns. Assuming that you want to span columns, the extra space needs to be defined as an empty string

It mainly corresponds to the standard header

const header = ['姓名', '手机号', '入职日期', '聘用形式', '转正日期', '工号', '部门']

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

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

The order of merges does not matter, as long as you configure these two properties, you can export excel with complex headers

  exportData() {
      const headers = {
        '姓名': 'username',
        '手机号': 'mobile',
        '入职日期': 'timeOfEntry',
        '聘用形式': 'formOfEmployment',
        '转正日期': 'correctionTime',
        '工号': 'workNumber',
        '部门': 'departmentName'
      }
      // 导出excel
      import('@/vendor/Export2Excel').then(async excel => {
        //  excel是引入文件的导出对象
        // 导出  header从哪里来
        // data从哪里来
        // 现在没有一个接口获取所有的数据
        // 获取员工的接口 页码 每页条数    100   1 10000
        const { rows } = await getEmployeeList({ page: 1, size: this.page.total })
        const data = this.formatJson(headers, rows) // 返回的data就是 要导出的结构
        const multiHeader = [['姓名', '主要信息', '', '', '', '', '部门']]
        const merges = ['A1:A2', 'B1:F1', 'G1:G2']
        excel.export_json_to_excel({
          header: Object.keys(headers),
          data,
          filename: '员工资料表',
          multiHeader, // 复杂表头
          merges // 合并选项
        })
      })
    },
    // 将表头数据和数据进行对应
    // [{}]  =>   [[]]
    formatJson(headers, rows) {
      return rows.map(item => {
        // item是一个对象  { mobile: 132111,username: '张三'  }
        // ["手机号", "姓名", "入职日期" 。。]
        return Object.keys(headers).map(key => {
          // 需要判断 字段
          if (headers[key] === 'timeOfEntry' || headers[key] === 'correctionTime') {
            // 格式化日期
            return formatDate(item[headers[key]])
          } else if (headers[key] === 'formOfEmployment') {
            const obj = EmployeeEnum.hireType.find(obj => obj.id === item[headers[key]])
            return obj ? obj.value : '未知'
          }
          return item[headers[key]]
        })
        // ["132", '张三’， ‘’，‘’，‘’d]
      })
      // return rows.map(item => Object.keys(headers).map(key => item[headers[key]]))
      // 需要处理时间格式问题
    }

The above is the front-end export processing of excel.

summary

So we can do our project, js elevation fourth edition link: https://pan.baidu.com/s/18P8ky1YalApRb-HDRENZBQ You can add the public account to get the extraction code.

If there is something you don’t understand, please add q group 147936127 to exchange or vx: ltby52119, thank you~