preface
When you need to implement Excel import and export to complete batch adding information in your work, please read this article carefully. In actual work, this function can be realized at both the front and back ends. If your company’s backend brother has handled it well, then congratulations, you do not need to read this article
1. Excel import
steps
- The back end provides an Excel template file
- The user fills out this Excel template file
- Upload this file to achieve batch import function
Train of thought
Front-end dominance (lots of work on the front end)
Upload the Excel file, read out the content of the Excel file, restore it to the most basic column structure, and send it back according to the interface requirements of the back end.
1. There is an import scheme in vue-admin-Element
- Defining components:
src/components/UploadExcel/index.vue
<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 code2. Install necessary plug-ins
npm install xlsx -S
3. Import UploadExcel
<template>
// 3. Use components
<upload-excel :on-success="handleSuccess" :before-upload="beforeUpload" />
</template>
<script>
// 1 imports components
import UploadExcel from '@/UploadExcel/index.vue'
export default {
name: 'Import'.components: {
// 2. Register components
UploadExcel
},
methods: {
beforeUpload(file) {
const isLt1M = file.size / 1024 / 1024 < 1
if (isLt1M) {
return true
}
this.$message({
message: 'Please do not upload files larger than 1m in size.'.type: 'warning'
})
return false
},
handleSuccess({ header, results }) {
console.log(header, results)
this.tableData = results
this.tableHeader = header
}
}
}
</script>
Copy the codeThe above two methods are the core code provided by vue-admin-Element and can be copied directly
- BeforeUpload: Function before uploading
- OnSuccess: function after successful call
4. Processed data imported by Excel according to interface requirements
(1). The format you import looks like this
// The date format in the Excel file is reverted to standard time
import { formatExcelDate } from '@/utils/index.js'
// Successfully executed function
async handleSuccess({ results, header }) {
console.log('The data is', results, header)
this.tableData = results
this.tableHeader = header
// 1. Convert format
// Const backend format = this.format(results, header)
const data = this.format(results)
console.log('Backend format', data)
// 2. 发ajax
try {
const res = await importEmployee(data)
console.log('Upload result is', res)
this.$router.go(-1)}catch (err) {
this.$message.error(err.message)
}
//2. Send ajax(backend format)
},
/ / starting point: results: [{' name ':' zhang ', 'working', '9002'}, {' name ':' wang ', 'working' : '9001'}]
//
//
/ / end: [{' name ':' zhang ', 'workNumber' : '9002'}, {' name ':' wang ', 'workNumber' : '9001'}]
// Encapsulates the format function required by the back end
format(results) {
const mapInfo = {
'Entry Date': 'timeOfEntry'.'Mobile phone Number': 'mobile'.'name': 'username'.'Date of conversion': 'correctionTime'.'working': 'workNumber'.'department': 'departmentName'.'Form of Employment': 'formOfEmployment'
}
console.log(results)
// return []
const res = results.map(obj= > {
// obj attribute name is Chinese
// 1. Fetch all attribute names --> array
// 2 loop through the array
// Add key-value pairs to newObj
// key: from Chinese -> English
// Value: obj value
const newObj = {}
// Store objects as an array for each key
const zhKeys = Object.keys(obj)
console.log(zhKeys, 'zhKeys')
zhKeys.forEach(zhkey= > {
const enKey = mapInfo[zhkey]
console.log(enKey)
console.log(mapInfo[zhkey])
console.log(obj[zhkey], 'obj') // Prints out the value of each object
// if(current key is date-dependent, extra processing)
if (enKey === 'timeOfEntry' || enKey === 'correctionTime') {
// The date format in Excel is inconsistent with the format in the programming language
/ / -- -- -- -- -- -- -- > 44892 2022-11-11
newObj[enKey] = new Date(formatExcelDate(obj[zhkey]))
} else {
newObj[enKey] = obj[zhkey]
}
})
return newObj
})
return res
}
Copy the code5. The date format in Excel is inconsistent with the format in the programming language
2. Excel export
Train of thought
- Copy SRC /vendor/export2Excel from vue-element-admin into this project and use it directly
- Install dependencies in your project
- Export2Excel source
/* 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 code2. Download the export2Excel dependency package
npm install file-saver script-loader xlsx --save
3. Convert the exported data format
// Encapsulate the format needed to export to Excel
formatDate(rows) {
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('Do format conversion', rows)
/** * * rows: * [* {mobile: '13612345678', the username: 'administrators'...} * {mobile:' 13612545678 ', the username: 'wang'...} *] * * * * header: [handset, the user name] * data: [[' 13612345678 ', 'administrators'], [' 13612545678', 'wang']] * /
// Convert the English key to the Chinese table header
const enKeys = Object.keys(rows[0])
const header = enKeys.map(enKey= > map[enKey])
const data = rows.map(obj= > {
return Object.values(obj)
})
console.log(enKeys, header, data)
return {
header,
data // [['13612345678', 'admin '], []]}},/ / export
async hExportExcel() {
// 1
const res = await getEmployeeList(this.pageParams)
console.log('Employee information is', res.data.rows)
// 2. Do format conversion
const { header, data } = this.formatDate(res.data.rows)
console.log('results', header, data)
// 3. Call Export2Excel to save data to Excel
import('@/vendor/Export2Excel').then(excel= > {
// Excel represents the imported module object
console.log(excel)
excel.export_json_to_excel({
header, // The header is mandatory
data, // Specific data is mandatory
filename: 'excel'.// File name
autoWidth: true.// Whether the width is adaptive
bookType: 'xlsx' // The generated file type})})}Copy the codeThe above is to sort out the overall use of the steps, you can go to GitHub to download vue-element-admin to use, I put the core code in this article, you can also directly use
PS: What I think is more difficult is the format conversion