Function realization of Vue elementui exporting excel form

vue elementUi export excel Table function implementation

In daily business , We often meet excel Export function , How to use it

Excel All imports and exports depend on js-xlsx To achieve .

stay js-xlsx On the basis of Export2Excel.js To facilitate the export of data .

install excel Required dependencies and on-demand loading

because Export2Excel Not only depends on js-xlsx Also depends on file-saver and script-loader.

So you need to install the following commands first ：

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

because js-xlsx It's still big , The export function is not a very common function , Therefore, it is recommended to use lazy loading . How to use it is as follows ：

import('@/vendor/Export2Excel').then(excel => {
excel.export_json_to_excel({
header: tHeader, // Header Required 
data, // Specific data Required 
filename: 'excel-list', // Not required 
autoWidth: true, // Not required 
bookType: 'xlsx' // Not required 
})
})
 Copy code 

excel Introduction to export parameters

vue-element-admin Provides exported function modules

Parameters

excel Export the basic structure

One of our most important things , This is to correspond the header to the data

Because... In the data key It's English , If the header you want to export is Chinese , You need to correspond Chinese and English

 const headers = {
' cell-phone number ': 'mobile',
' full name ': 'username',
' Job number ': 'workNumber',
}
 Copy code 

then , Complete export code

 // export excel data 
exportData() {
// Do something 
// Header correspondence 
const headers = {
' full name ': 'username',
' cell-phone number ': 'mobile',
' Job number ': '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: ' Employee information form ',
autoWidth: true,
bookType: 'xlsx'
})
})
},
// This method is responsible for converting an array into a two-dimensional array 
formatJson(headers, rows) {
// Traverse array first 
// [{ username: ' Zhang San '},{},{}] => [[’ Zhang San '],[],[]]
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 code 

Processing of export time format

 formatJson(headers, rows) {
return rows.map(item => {
// item It's an object { mobile: 132111,username: ' Zhang San ' }
// [" cell-phone number ", " full name ", " Date of entry " ..]
return Object.keys(headers).map(key => {
// You need to determine Field 
if (headers[key] === 'timeOfEntry' || headers[key] === 'correctionTime') {
// Format 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", ' Zhang San ’, ‘’,‘’,‘’d]
})
// return rows.map(item => Object.keys(headers).map(key => item[headers[key]]))
// Time format issues need to be addressed 
}
 Copy code 

Expand Export of complex header

When you need to export complex headers ,vue-element-admin This type of operation is also supported

vue-element-admin The provided export methods include multiHeader and merges Parameters of

multiHeader Inside is a two-dimensional array , An element inside is a row header , Suppose you want to get a structure as shown in the figure

mutiHeader It should be defined as

const multiHeader = [[' full name ', ' Main information ', '', '', '', '', ' department ']]
 Copy code 

multiHeader The number of fields in a row header should be equal to the actual number of columns , Suppose 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 = [' full name ', ' cell-phone number ', ' Date of entry ', ' Form of employment ', ' The date of employment ', ' Job number ', ' department ']
 Copy code 

If , We want to achieve the effect of its merger , Need to set merges Options

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

merges It doesn't matter the order of , Just configure these two properties , You can export complex header excel 了

 exportData() {
const headers = {
' full name ': 'username',
' cell-phone number ': 'mobile',
' Date of entry ': 'timeOfEntry',
' Form of employment ': 'formOfEmployment',
' The date of employment ': 'correctionTime',
' Job number ': 'workNumber',
' department ': 'departmentName'
}
// export excel
import('@/vendor/Export2Excel').then(async excel => {
// excel Is the export object of the import file 
// export header Where are from 
// data Where are from 
// Now there is no interface to get all the data 
// Get the employee's interface Page number Number of entries per page 100 1 10000
const { rows } = await getEmployeeList({ page: 1, size: this.page.total })
const data = this.formatJson(headers, rows) // Back to data Namely Structure to export 
const multiHeader = [[' full name ', ' Main information ', '', '', '', '', ' department ']]
const merges = ['A1:A2', 'B1:F1', 'G1:G2']
excel.export_json_to_excel({
header: Object.keys(headers),
data,
filename: ' Employee data sheet ',
multiHeader, // Complex header 
merges // Merge options 
})
})
},
// Corresponding header data to data 
// [{}] => [[]]
formatJson(headers, rows) {
return rows.map(item => {
// item It's an object { mobile: 132111,username: ' Zhang San ' }
// [" cell-phone number ", " full name ", " Date of entry " ..]
return Object.keys(headers).map(key => {
// You need to determine Field 
if (headers[key] === 'timeOfEntry' || headers[key] === 'correctionTime') {
// Format 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", ' Zhang San ’, ‘’,‘’,‘’d]
})
// return rows.map(item => Object.keys(headers).map(key => item[headers[key]]))
// Time format issues need to be addressed 
}
 Copy code 

That's all excel The front-end export of .

Summary

So we can do our project , js Elevation fourth edition link : pan.baidu.com/s/18P8ky1Ya… You can add the official account to get the extraction code .

If there is something you don't understand , Please add q Group 147936127 Communication or vx: ltby52119, thank you ~