File to import

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

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

Install the XLSX plug-in

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

$ npm i xlsx
Copy the code

Registering global components

import PageTools from './PageTools' import UploadExcel from './UploadExcel' export default { install(Vue) { // Vue.component('UploadExcel', 'PageTools'); Vue.component('UploadExcel', 'PageTools'); UploadExcel) // Import excel component}Copy the code

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, // Children: [{path: '/import', Component: Layout, hidden: true, // Children: [{path: 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

Style layout

Contains the basic logic for uploading files

<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> <script> import XLSX from 'xlsx' export default { props: { beforeUpload: Function, // eslint-disable-line onSuccess: Function// eslint-disable-line }, data() { return { loading: false, excelData: { header: null, results: null } } }, methods: { generateData({ header, results }) { this.excelData.header = header this.excelData.results = results this.onSuccess && this.onSuccess(this.excelData) }, handleDrop(e) { e.stopPropagation() e.preventDefault() if (this.loading) return const files = e.dataTransfer.files if (files.length ! == 1) { this.$message.error('Only support uploading one file! ') return } const rawFile = files[0] // only use files[0] if (! this.isExcel(rawFile)) { this.$message.error('Only supports upload .xlsx, .xls, .csv suffix files') return false } this.upload(rawFile) e.stopPropagation() e.preventDefault() }, handleDragover(e) { e.stopPropagation() e.preventDefault() e.dataTransfer.dropEffect = 'copy' }, handleUpload() { this.$refs['excel-upload-input'].click() }, handleClick(e) { const files = e.target.files const rawFile = files[0] // only use files[0] if (! rawFile) return this.upload(rawFile) }, upload(rawFile) { this.$refs['excel-upload-input'].value = null // fix can't select the same excel if (! this.beforeUpload) { this.readerData(rawFile) return } const before = this.beforeUpload(rawFile) if (before) { this.readerData(rawFile) } }, readerData(rawFile) { this.loading = true return new Promise((resolve, reject) => { const reader = new FileReader() reader.onload = e => { const data = e.target.result const workbook = XLSX.read(data, { type: 'array' }) const firstSheetName = workbook.SheetNames[0] const worksheet = workbook.Sheets[firstSheetName] const header = this.getHeaderRow(worksheet) const results = XLSX.utils.sheet_to_json(worksheet) this.generateData({ header, results }) this.loading = false resolve() } reader.readAsArrayBuffer(rawFile) }) }, getHeaderRow(sheet) { const headers = [] const range = XLSX.utils.decode_range(sheet['!ref']) let C const R = range.s.r /* start in the first row */ for (C = range.s.c; C <= range.e.c; ++C) { /* walk every column in the range */ const cell = sheet[XLSX.utils.encode_cell({ c: C, r: R })] /* find the cell in the first row */ let hdr = 'UNKNOWN ' + C // <-- replace with your desired default if (cell &&  cell.t) hdr = XLSX.utils.format_cell(cell) headers.push(hdr) } return headers }, isExcel(file) { return /\.(xlsx|xls|csv)$/.test(file.name) } } } </script> <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

Total page logic code

<template> <! -- public import component --> <upload-excel :on-success="success" /> </template> <script> import {importEmployee} from '@/ API /employees' export default {// Component name name: ', // component parameters to receive data from the parent component props: {}, // Locally registered component components: {}, // Component state value data() {return {// type: this.$route.query.type}}, // Computed properties: {}, // listener watch: {}, // Components methods: {async success({header, results}) {const userRelations = {' start date ': 'timeOfEntry', 'mobile number ': 'mobile', 'name' : 'username' and 'positive date:' correctionTime ', 'working status: 'workNumber' } const arr = [] var newArr = results.map(item => { var userInfo = {} Object.keys(item).forEach(key => { if  (userRelations[key] === 'timeOfEntry' || userRelations[key] === 'correctionTimee') { userInfo[userRelations[key]] = new  Date(this.formatDate(item[key]), '/') } else { userInfo[userRelations[key]] = item[key] } }) return userInfo }) await importEmployee(newArr) $router.back()}, 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)}}, // Here are the lifecycle hooks: /** * created() {/** * created(vm.$el) {/** * created(VM.$el) {** * created(VM. * If root mounts a document element, vm.$el is also in the document when Mounted is called. */ mounted() { }, } </script> <style scoped lang="less"> </style>Copy the code

File export

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.

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

The export plug-in

Two files as the name suggests, one exported to Excel and one exported to the zip package

Wwr.lanzoui.com/b02c8alji password: HQWM

Excel export plug-in password: HQWM

1. Import a method component

import { export_json_to_excel } from ‘@/vendor/Export2Excel’

Export file code _(not time formatted)

ExprotData () {const headers = {' name' : 'username', 'mobile' : 'timeOfEntry', 'form of employment ': 'formOfEmployment', 'date ': 'correctionTime', 'workNumber' :' department ': 'departmentName' } import('@/vendor/Export2Excel').then(async excel => { const { rows } = await getEmployeeList({ page: 1, size: }) const data = this.formatjson (headers, rows) export_json_to_excel({header:}) Keys (headers), // assign the following attributes to data: })})}, formatJson(headers, Return rows.map(item => {// item is each object that contains the data for each row in the table Keys (headers) {name:' headers ',age:18} // Object.keys(headers) is a method that loop through the name of the header and returns an array Return item[headers[key]] // headers[key] is the value of each attribute in the headers object Return rows.map(item => object.keys (headers). Map (key =>item[headers]]))}Copy the code

Perfect code (time formatting + employment-style enumeration formatting)

Export code _ Processed time formatting

Time filter plug-in enumeration file

Import time formatting filters and enumeration files (formatting employment form – formal informal)

Plug-in wwr.lanzoui.com/b02c8arni password: time

Enumeration file wwr.lanzoui.com/b02c8at5c password: GGGG

Import {formatDate} from '@/filters' // Import {formatDate} from '@/filters' // Import {formatDate} from '@/filters' // Import {formatDate(item[headers[key]] EmployeeEnum from '@/ API /constant/employees' Imports enumeration dataCopy the code

ExprotData () {const headers = {' name' : 'username', 'mobile' : 'timeOfEntry', 'form of employment ': 'formOfEmployment', 'date ': 'correctionTime', 'workNumber' :' department ': 'departmentName' } import('@/vendor/Export2Excel').then(async excel => { const { rows } = await getEmployeeList({ page: 1, size: }) const data = this.formatjson (headers, rows) export_json_to_excel({header:}) Keys (headers), // assign the following attributes to data: })})}, formatJson(headers, Return rows.map(item => {// item is each object that contains the data for each row in the table Key (headers).map(key => {if).key (headers).map(key => {if).key (headers).map(key => {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 : Return item[key] [headers[key]] // }) return rows.map(item => object.keys (headers).map(key) =>item[headers[key]])) }Copy the code