Implementation of importing and exporting vue-element-admin projects
vue-element-admin import component encapsulation Templates and styles
First, encapsulate a similar component. First of all, it should be noted that similar functions are already provided by vue-element-admin. We only need to modify it. npm i xlsx Create a new component with the import function provided by vue-element-admin, location: src/components/UploadExcel
import CommonTools from './CommonTools'
import UploadExcel from './UploadExcel'
export default {
install(Vue) {
Vue.component('CommonTools', CommonTools) // Register toolbar component Vue.component('UploadExcel', UploadExcel) // Register import excel component }
}
Modify style and layout
<template>
<div class="upload-excel">
<div class="btn-upload">
<el-button :loading="loading" size="mini" type="primary" @click="handleUpload">
Click to upload</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 files 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.sr
/* start in the first row */
for (C = range.sc; C <= range.ec; ++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>
Creating Routes and Components Create a public import page route, create a new public import page, and mount the route src/router/index.js
path: '/import',
component: Layout,
hidden: true, // hidden in the left menu children: [{
path: '', // No secondary routing path means secondary default routing component: () => import('@/views/import')
}]
},
Create the import routing component src/views/import/index.vue <template> <!-- Public import components --> <upload-excel :on-success="success" /> </template> Implementing importEncapsulate the API interface for importing users
export function importUser(data) {
return request({
url: 'user/batch',
method: 'post',
data
})
}
Get imported excel data, import excel interface
async success({ header, results }) {
// If you are importing users const userRelations = {
'Job Date': 'create_time',
'Mobile number': 'mobile',
'Username': 'username',
'Password': 'password',
'Email': 'email',
'Department': 'Department'
}
const arr = []
results.forEach(item => {
const userInfo = {}
Object.keys(item).forEach(key => {
userInfo[userRelations[key]] = item[key]
})
arr.push(userInfo)
})
await importUser(arr) //Call the import interface this.$router.back()
}
In order to make this page serve more import functions, we can use parameters in the page to determine whether it is importing users.
data() {
return {
type: this.$route.query.type
}
},
When there is a date format in Excel, the actual converted value is a number. We need a method to convert it
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)
}
Logical Reasoning
async success({ header, results }) {
if (this.type === 'user') {
const userRelations = {
'Job Date': 'create_time',
'Mobile number': 'mobile',
'Username': 'username',
'Password': 'password',
'Email': 'email',
'Department': 'Department'
}
const arr = []
// Traverse all arrays results.forEach(item => {
// You need to change the Chinese in each piece of data into English const userInfo = {}
Object.keys(item).forEach(key => {
// key is the current Chinese name to find the corresponding English name if (userRelations[key] === 'timeOfEntry' || userRelations[key] === 'correctionTime') {
userInfo[userRelations[key]] = new Date(this.formatDate(item[key], '/')) // Only in this way can it be stored in the database return
}
userInfo[userRelations[key]] = item[key]
})
// Finally userInfo becomes all English arr.push(userInfo)
})
await importUser(arr)
this.$message.success('Import successful')
}
this.$router.back() // Return to the previous page},
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)
}
User page jump
<el-button type="warning" size="small" @click="$router.push('/import?type=user')">Import</el-button>
User Export** In daily business, we often encounter Excel export function, how to use it? Install required dependencies npm install xlsx file-saver -S npm install script-loader -S -D Since js-xlsx is still very large and the export function is not a very commonly used function, it is recommended to use lazy loading when using it. Here’s how to use it:
import('@/vendor/Export2Excel').then(excel => {
excel.export_json_to_excel({
header: tHeader, //Required for table header data, //Required for specific data filename: 'excel-list', //Optional autoWidth: true, //Optional bookType: 'xlsx' //Optional})
})
Introduction to Excel export parameters
Excel export basic structure The following code will use the Export2Excel.js module, so first create a new vendor directory under the src directory, create a new Export2Excel.js, and enter the following code
/* eslint-disable */
import { saveAs } from 'file-saver'
import XLSX from 'xlsx'
function generateArray(table) {
var out = [];
var rows = table.querySelectorAll('tr');
var ranges = [];
for (var R = 0; R < rows.length; ++R) {
var outRow = [];
var row = rows[R];
var columns = row.querySelectorAll('td');
for (var C = 0; C < columns.length; ++C) {
var cell = columns[C];
var colspan = cell.getAttribute('colspan');
var rowspan = cell.getAttribute('rowspan');
var cellValue = cell.innerText;
if (cellValue !== "" && cellValue == +cellValue) cellValue = +cellValue;
//Skip ranges
ranges.forEach(function (range) {
if (R >= range.sr && R <= range.er && outRow.length >= range.sc && outRow.length <= range.ec) {
for (var i = 0; i <= range.ec - range.sc; ++i) outRow.push(null);
}
});
//Handle Row Span
if (rowspan || colspan) {
rowspan = rowspan || 1;
colspan = colspan || 1;
ranges.push({
s: {
r: R,
c: outRow.length
},
e: {
r: R + rowspan - 1,
c: outRow.length + colspan - 1
}
});
};
//Handle Value
outRow.push(cellValue !== "" ? cellValue : null);
//Handle Colspan
if (colspan)
for (var k = 0; k < colspan - 1; ++k) outRow.push(null);
}
out.push(outRow);
}
return [out, ranges];
};
function datenum(v, date1904) {
if (date1904) v += 1462;
var epoch = Date.parse(v);
return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
}
function sheet_from_array_of_arrays(data, opts) {
var ws = {};
var range = {
s: {
c: 10000000,
r: 10000000
},
e: {
c: 0,
r: 0
}
};
for (var R = 0; R != data.length; ++R) {
for (var C = 0; C != data[R].length; ++C) {
if (range.sr > R) range.sr = R;
if (range.sc > C) range.sc = C;
if (range.er < R) range.er = R;
if (range.ec < C) range.ec = C;
var cell = {
v: data[R][C]
};
if (cell.v == null) continue;
var cell_ref = XLSX.utils.encode_cell({
c: C,
r: R
});
if (typeof cell.v === 'number') cell.t = 'n';
else if (typeof cell.v === 'boolean') cell.t = 'b';
else if (cell.v instanceof Date) {
cell.t = 'n';
cell.z = XLSX.SSF._table[14];
cell.v = datenum(cell.v);
} else cell.t = 's';
ws[cell_ref] = cell;
}
}
if (range.sc < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
return ws;
}
function Workbook() {
if (!(this instanceof Workbook)) return new Workbook();
this.SheetNames = [];
this.Sheets = {};
}
function s2ab(s) {
var buf = new ArrayBuffer(s.length);
var view = new Uint8Array(buf);
for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
return buf;
}
export function export_table_to_excel(id) {
var theTable = document.getElementById(id);
var oo = generateArray(theTable);
var ranges = oo[1];
/* original data */
var data = oo[0];
var ws_name = "SheetJS";
var wb = new Workbook(),
ws = sheet_from_array_of_arrays(data);
/* add ranges to worksheet */
// ws['!cols'] = ['apple', 'banan'];
ws['!merges'] = ranges;
/* add worksheet to workbook */
wb.SheetNames.push(ws_name);
wb.Sheets[ws_name] = ws;
var wbout = XLSX.write(wb, {
bookType: 'xlsx',
bookSST: false,
type: 'binary'
});
saveAs(new Blob([s2ab(wbout)], {
type: "application/octet-stream"
}), "test.xlsx")
}
export function export_json_to_excel({
multiHeader = [],
header,
data,
filename,
merges = [],
autoWidth = true,
bookType = 'xlsx'
} = {}) {
/* original data */
filename = filename || 'excel-list'
data = [...data]
data.unshift(header);
for (let i = multiHeader.length - 1; i > -1; i--) {
data.unshift(multiHeader[i])
}
var ws_name = "SheetJS";
var wb = new Workbook(),
ws = sheet_from_array_of_arrays(data);
if (merges.length > 0) {
if (!ws['!merges']) ws['!merges'] = [];
merges.forEach(item => {
ws['!merges'].push(XLSX.utils.decode_range(item))
})
}
if (autoWidth) {
/*Set the maximum width of each worksheet column*/
const colWidth = data.map(row => row.map(val => {
/*First determine whether it is null/undefined*/
if (val == null) {
return {
'wch': 10
};
}
/*Judge whether it is Chinese*/
else if (val.toString().charCodeAt(0) > 255) {
return {
'wch': val.toString().length * 2
};
} else {
return {
'wch': val.toString().length
};
}
}))
/*Use the first line as the initial value*/
let result = colWidth[0];
for (let i = 1; i < colWidth.length; i++) {
for (let j = 0; j < colWidth[i].length; j++) {
if (result[j]['wch'] < colWidth[i][j]['wch']) {
result[j]['wch'] = colWidth[i][j]['wch'];
}
}
}
ws['!cols'] = result;
}
/* add worksheet to workbook */
wb.SheetNames.push(ws_name);
wb.Sheets[ws_name] = ws;
var wbout = XLSX.write(wb, {
bookType: bookType,
bookSST: false,
type: 'binary'
});
saveAs(new Blob([s2ab(wbout)], {
type: "application/octet-stream"
}), `${filename}.${bookType}`);
}
Because the key in the data is in English, if the header you want to export is in Chinese, you need to make a correspondence between Chinese and English.
const headers = {
'Job Date': 'create_time',
'Mobile number': 'mobile',
'Username': 'username',
'Role': 'role_name',
'Email': 'email',
'Department': 'department_name'
}
Complete export code
import { formatDate } from '@/filters'
// Export data exportData() {
const headers = {
'Job Date': 'create_time',
'Mobile number': 'mobile',
'Username': 'username',
'Role': 'role_name',
'Email': 'email',
'Department': 'department_name'
}
import('@/vendor/Export2Excel').then(async excel => {
const res = await getUserList({ query: '', pagenum: 1, pagesize: this.page.total })
// console.log(res)
const data = this.formatJson(headers, res.users)
console.log(data)
excel.export_json_to_excel({
header: Object.keys(headers),
data,
filename: 'User Information Table',
autoWidth: true,
bookType: 'xlsx'
})
})
}
}
Processing of export time format
// This method is responsible for converting the array into a two-dimensional array formatJson(headers, rows) {
return rows.map(item => {
return Object.keys(headers).map(key => {
if (headers[key] === 'create_time') {
return formatDate(item[headers[key]]) // formatDate function is a defined filter}
return item[headers[key]]
})
})
Filter formatDate
import moment from 'moment'
export function formatTime(value) {
return moment(value * 1000).format('YYYY-MM-DD HH:mm:ss')
}
export function formatDate(value) {
return moment(value * 1000).format('YYYY-MM-DD')
}
This is the end of this article about the implementation of vue-element-admin project import and export. For more relevant vue-element-admin project import and export content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future! You may also be interested in:
|
>>: Implementation of installing Docker in win10 environment
Recommend
Keep-alive multi-level routing cache problem in Vue
Table of contents 1. Problem Description 2. Cause...
What to do if you forget your mysql password
Forgot your MySQL password twice? At first I did ...
How to solve the problem of Ubuntu 18.04 looping login/stuck on the boot interface/unable to enter the graphical interface
Cause: NVIDIA graphics card driver is damaged Sol...
Making a simple game engine with React Native
Table of contents Introduction Get started A brie...
Detailed explanation of three ways to import CSS files
There are three ways to introduce CSS: inline sty...
Let's talk briefly about the changes in setup in vue3.0 sfc
Table of contents Preface Standard sfc writing me...
Navicat for MySql Visual Import CSV File
This article shares the specific code of Navicat ...
Collection of 12 practical web online tools
1. Favicon.cc To create ico icon websites online,...
jQuery implements the drop-down box for selecting the place of residence
The specific code for using jQuery to implement t...
Various correct postures for using environment variables in Webpack
Table of contents Write in front Business code us...
Detailed explanation of js event delegation
1. Each function is an object and occupies memory...
HTML form tag tutorial (1):
Forms are a major external form for implementing ...
Solution for Nginx installation without generating sbin directory
Error description: 1. After installing Nginx (1.1...
JavaScript Basics Operators
Table of contents 1. Operators Summarize 1. Opera...
Use iptables and firewalld tools to manage Linux firewall connection rules
Firewall A firewall is a set of rules. When a pac...