preface
In the project, we usually need to add data. We can add a small amount of data one by one, but once the amount of data is large, the operation efficiency will be very low, so we need the batch import function. Similarly, it is very inefficient for us to export a large amount of data only relying on CV lines, so we also need a batch export function.
Implement Excel import function
1. Install necessary plug-ins
npm install xlsx -S
2. In the SRC/components/UploadExcel created under UploadExcel. Vue components
This article is to provide components will vue – element – admin is copied to the SRC/components/UploadExcel, vue – element of – admin provide component code:
<template>
<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">
Drop excel file here or
<el-button :loading="loading" style="margin-left:16px;" size="mini" type="primary" @click="handleUpload">
Browse
</el-button>
</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>
.excel-upload-input{
display: none;
z-index: -9999;
}
.drop{
border: 2px dashed #bbb;
width: 600px;
height: 160px;
line-height: 160px;
margin: 0 auto;
font-size: 24px;
border-radius: 5px;
text-align: center;
color: #bbb;
position: relative;
}
</style>
Copy the code3. Register as a global component in index.js under SRC/Components
import uploadExcel from './UploadExcel'
export default {
// Plugin initialization, plugin to provide you with global functions, can be configured here
install(Vue) {
// Register the component globallyOmit other code.... Vue.component('uploadExcel', uploadExcel) // Register to import excel components}}Copy the code4. Under the SRC/views/employees/import new index. The vue
<template>
<uploadExcel :on-success="handleSuccess" />
</template>
<script>
export default {
name: 'Import'.methods: {
handleSuccess({ header, results }) {
console.log(header, results)
}
}
}
</script>
Copy the code5. Configure the route permission
Configure the route in the employees.js file
6. The SRC/views/employees/import/index. The new method vue file
import { formatExcelDate } from '@/utils/index.js'
/ / will [{' name ':' zhang ', 'phone number', '13712345678'}, {...}] into [{' username ':' zhang ', 'mobile' : '13712345678'}, {.....}], that is, change the property name of each object in an array of objects from Chinese to English
// For each object in the original array
// (1) find all the Chinese keys
// (2) get the corresponding English key
// (3) join a new object
transExcel(results) {
const mapInfo = {
'Entry Date': 'timeOfEntry'.'Mobile phone Number': 'mobile'.'name': 'username'.'Date of conversion': 'correctionTime'.'working': 'workNumber'.'department': 'departmentName'.'Form of Employment': 'formOfEmployment'
}
return results.map(zhObj= > {
const enObj = {}
const zhKeys = Object.keys(zhObj) // [' name ', 'mobile number ']
zhKeys.forEach(zhKey= > {
const enKey = mapInfo[zhKey]
// Only the entry time and the transition time need to be handled
if (enKey === 'timeOfEntry' || enKey === 'correctionTime') {
// The date format required by the backend is standard time
enObj[enKey] = new Date(formatExcelDate(zhObj[zhKey]))
} else {
enObj[enKey] = zhObj[zhKey]
}
})
return enObj
})
}
Copy the code7. Add the following method in the utils/index.js file:
Because there’s special coding inside Excel that distorts the time read from Excel.
// Convert the date format in excel files back to standard time
// https://blog.csdn.net/qq_15054679/article/details/107712966
export function formatExcelDate(numb, format = '/') {
const time = new Date((numb - 25567) * 24 * 3600000 - 5 * 60 * 1000 - 43 * 1000 - 24 * 3600000 - 8 * 3600000)
time.setYear(time.getFullYear())
const year = time.getFullYear() + ' '
const month = time.getMonth() + 1 + ' '
const date = time.getDate() + ' '
if (format && format.length === 1) {
return year + format + month + format + date
}
return year + (month < 10 ? '0' + month : month) + (date < 10 ? '0' + date : date)
}
Copy the code8. Prepare the interface in the SRC/API /employee.js file
export function importEmployee(data) {
return request({
url: '/sys/user/batch'.method: 'post',
data
})
}
Copy the code9. Use it on the page
Import {importEmployee} from ‘@/ API /employees’ // import API
async doImport(data) {
try {
const res = await importEmployee(data)
console.log('importEmployee', res)
this.$message.success('Import successful')
// Back up the page
this.$router.back()
} catch (err) {
console.log('importEmployee', err)
this.$message.error('Import failed')}},// 1. Read data from the Excel file into the browser memory
handleSuccess({ header, results }) {
console.log(header, results)
// 2. Assemble data according to interface requirements
const data = this.transExcel(results)
console.log('Assemble data according to interface requirements', data)
// 3. Invoke the interface to upload
this.doImport(data)
}
Copy the codeImplement excel export function
1. Install dependencies
npm install file-saver script-loader xlsx --save
2. Create the export2Excel. Js file in the SRC /vendor file
This article copies the vue-element-admin file to SRC /vendor. Here is the vue-element-admin component 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.s.r && R <= range.e.r && outRow.length >= range.s.c && outRow.length <= range.e.c) {
for (var i = 0; i <= range.e.c - range.s.c; ++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 ValueoutRow.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.s.r > R) range.s.r = R;
if (range.s.c > C) range.s.c = C;
if (range.e.r < R) range.e.r = R;
if (range.e.c < C) range.e.c = 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.s.c < 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) {
/* Sets the maximum width of each column of the worksheet */
const colWidth = data.map(row= > row.map(val= > {
/* Check whether the value is null/undefined*/
if (val == null) {
return {
'wch': 10
};
}
/* Check whether it is Chinese */
else if (val.toString().charCodeAt(0) > 255) {
return {
'wch': val.toString().length * 2
};
} else {
return {
'wch': val.toString().length }; }}))/* starts with the first behavior */
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}`);
}
Copy the code3. The SRC/view/employees/employees. The new method in vue
formatData(list) {
const map = {
'id': 'number'.'password': 'password'.'mobile': 'Mobile phone Number'.'username': 'name'.'timeOfEntry': 'Entry Date'.'formOfEmployment': 'Form of Employment'.'correctionTime': 'Date of conversion'.'workNumber': 'working'.'departmentName': 'department'.'staffPhoto': 'Avatar address'
}
console.log(list)
let header = []
// header = ['id', 'mobile', 'username', .....]
// data = [
// ['65c2', '1380000002', 'administrator ',....] .
// [' 65C3 ', '1380000003', 'Sun CAI ',....] .
// ]
let data = []
// Start code
// Find an element
const one = list[0]
// Is used to determine whether there is data
if(! one) {return { header, data }
}
header = Object.keys(one).map(key= > {
return map[key]
})
// data converts each object in the list to the corresponding value array
data = list.map(obj= > {
/ / the Obj [' formOfEmployment] : 1, 2 - > 'formal', 'informal'
const key = obj['formOfEmployment'] / / 1. 2
obj['formOfEmployment'] =this.hireType[key] // hireType:{1:' formal ', '2':' informal '}
return Object.values(obj)
})
return { header, data }
},
hExport() {
import('@/vendor/Export2Excel').then(async excel => {
// Make an Ajax request to get data
const res = await getEmployee(this.page, this.size)
const list = res.data.rows
console.log('Data retrieved from the back end', list)
const { header, data } = this.formatData(list)
// Excel represents the imported module object
console.log(header, data)
excel.export_json_to_excel({
// header: [' name ', 'salary '], // The header must be filled
header: header, // The header is mandatory
data: data,
// data: [
// [' liu Bei 1111111111111111 ', 100],
// [' Guan Yu ', 500]
//], // Specific data must be filled in
filename: 'excel-list'.// File name
autoWidth: true.// Whether the width is adaptive
bookType: 'xlsx' // The generated file type})})}Copy the codeThe last
Hope to organize the information to help you, like the words please help to like
If you have any suggestions, feel free to leave them in the comments section
Please also criticize the inadequacies, thank you!