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
Parameters | explain | type | Optional value | The default value is |
---|---|---|---|---|
header | Header of exported data | Array | / | [] |
data | Specific data exported | Array | / | [[]] |
filename | Export filename | String | / | excel-list |
autoWidth | Whether the cell should adapt to the width | Boolean | true / false | true |
bookType | Export file type | String | xlsx, csv, txt, more | xlsx |
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
Parameters | explain | type | Optional value | The default value is |
---|---|---|---|---|
multiHeader | Part of the complex header | Array | / | [[]] |
merges | Parts to be merged | Array | / | [] |
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 ~