Vue elementUi export excel spreadsheet function implementation

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: tHeader, // The header is mandatory
    data, // Specific data is mandatory
    filename: 'excel-list'./ / not required
    autoWidth: true./ / not required
    bookType: 'xlsx' / / not required})})Copy the code

Excel export parameters

Vue-element-admin provides an export function module

parameter

parameter	instructions	type	An optional value	The default value
header	The header of the exported data	Array	/	[]
data	Specific data to be exported	Array	/	[[]]
filename	Export file name	String	/	excel-list
autoWidth	Whether the cell should be width adaptive	Boolean	true / false	true
bookType	Export file type	String	xlsx, csv, txt, more	xlsx

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 phone Number': 'mobile'.'name': 'username'.'working': 'workNumber',}Copy the code

Then, complete the export code

    // Export excel data
    exportData() {
      / / do operation
      // Table header mapping
      const headers = {
        'name': 'username'.'Mobile phone Number': 'mobile'.'working': 'workNumber',}/ / lazy loading
      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: 'Staff Information Sheet'.autoWidth: true.bookType: 'xlsx'})})},// This method converts an array into a two-dimensional array
    formatJson(headers, rows) {
      // Start by iterating through the array
      // [{username: 'zhang3 '},{},{}] => [[' zhang3 '],[],[]]
      return rows.map(item= > {
        return Object.keys(headers).map(key= > {
          if (headers[key] === 'timeOfEntry' || headers[key] === 'correctionTime') {
            return formatDate(item[headers[key]]) // Returns the time before formatting
          } 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 SAN "," 13811"," 2018","1", "2018"," 10002"]})}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 "...]
        return Object.keys(headers).map(key= > {
          // Need to judge the field
          if (headers[key] === 'timeOfEntry' || headers[key] === 'correctionTime') {
            // Format the date
            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 : 'unknown'
          }
          return item[headers[key]]
        })
        // ["132", 'd', 'd']
      })
      // return rows.map(item => Object.keys(headers).map(key => item[headers[key]]))
      // Time format issues need to be addressed
    }
Copy the code

`extension`Export 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

parameter	instructions	type	An optional value	The default value
multiHeader	Part of the complex table header	Array	/	[[]]
merges	The part that needs to be merged	Array	/	[]

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 Message'.' '.' '.' '.' '.'department']]
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'.'Mobile phone Number'.'Entry Date'.'Form of Employment'.'Date of conversion'.'working'.'department']
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 phone Number': 'mobile'.'Entry Date': 'timeOfEntry'.'Form of Employment': 'formOfEmployment'.'Date of conversion': 'correctionTime'.'working': 'workNumber'.'department': 'departmentName'
      }
      / / export excel
      import('@/vendor/Export2Excel').then(async excel => {
        // Excel is an export object that imports files
        // Where does the export header come from
        // Where does data come from
        // Now there is no interface to get all the data
        // Obtain the number of pages per page of the employee interface 100 1 10000
        const { rows } = await getEmployeeList({ page: 1.size: this.page.total })
        const data = this.formatJson(headers, rows) // The data returned is the structure to export
        const multiHeader = [['name'.'Main Message'.' '.' '.' '.' '.'department']]
        const merges = ['A1:A2'.'B1:F1'.'G1:G2']
        excel.export_json_to_excel({
          header: Object.keys(headers),
          data,
          filename: 'Staff Information Sheet',
          multiHeader, // Complex header
          merges // Merge options})})},// match header data to data
    / / / {} = > [[]]
    formatJson(headers, rows) {
      return rows.map(item= > {
        // item is an object {mobile: 132111,username: 'zhang SAN'}
        // [" mobile phone number ", "name "," start date "...]
        return Object.keys(headers).map(key= > {
          // Need to judge the field
          if (headers[key] === 'timeOfEntry' || headers[key] === 'correctionTime') {
            // Format the date
            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 : 'unknown'
          }
          return item[headers[key]]
        })
        // ["132", 'd', 'd']
      })
      // return rows.map(item => Object.keys(headers).map(key => item[headers[key]]))
      // Time format issues need to be addressed
    }
Copy the code

This is excel front-end export processing.

summary

So that we can to make our project, js elevation fourth edition link: pan.baidu.com/s/18P8ky1Ya… You can add the public number to obtain the extraction code.

If you don’t understand, please add q group 147936127 or VX: LTBY52119, thank you ~

Vue elementUi export excel spreadsheet function implementation