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
![css input[type=file] style beautification (input upload file style)](/upload/images/67cacb9e46867.webp)
Recommend
Getting Started with Front-End Vue Unit Testing
Table of contents 1. Why do we need unit testing?...
Analysis of rel attribute in HTML
.y { background: url(//img.jbzj.com/images/o_y.pn...
WeChat applet example of using functions directly in {{ }}
Preface In WeChat applet development (native wxml...
React non-parent-child component parameter passing example code
React is a JAVASCRIPT library for building user i...
MySQL ID starts to increase from 1 to quickly solve the problem of discontinuous ID
mysql id starts from 1 and increases automaticall...
Summary of the differences between Html, sHtml and XHtml
For example: <u> This has no ending characte...
Steps to deploy ingress-nginx on k8s
Table of contents Preface 1. Deployment and Confi...
Vue uses echarts to draw an organizational chart
Yesterday, I wrote a blog about the circular prog...
Detailed explanation of how Angular handles unexpected exception errors
Written in front No matter how well the code is w...
Detailed process of upgrading gcc (version 10.2.0) under CentOS7 environment
Table of contents Short Introduction 1. Check the...
Summary of common Linux distribution mirror source configuration
I have been researching Linux recently and tried ...
JS implements simple addition and subtraction of shopping cart effects
This article example shares the specific code of ...
DIV and image horizontal and vertical centering compatible with multiple browsers
The first type: full CSS control, layer floating ...
mysql implements adding time automatically adding and updating time automatically updating operation
Time fields are often used in database usage. Com...
18 Nginx proxy cache configuration tips that operators must know (which ones do you know?)
We all know that the performance of applications ...