Directory
-
- 1 Introduction
- 2.Data definition
- 3. Page layout
- 4. Upload previous events
- 5. Parse the excel file and assemble the corresponding relationship between the system header and the excel header
- 6. Drop-down box change event
1. Preface
Recently, there is a demand that users can import a custom excel file at will, so that users can choose and assemble the corresponding relationships of table headers. The purpose of this is to solve the limitations of the template and make the import more flexible.
2. Data definition
data() {<!-- -->
return {<!-- -->
file: null,
excelHeaders: [],
tableData: [],
page: {<!-- -->},
scanPage: {<!-- -->},
list: [],
total: 0,
scanList: [],
scanTotal: 0,
excelLimit: 0,
dataLoading: false,
visible: false,
importDescVisible: false,
importVisible: false,
uploadLoading: false,
fileName: '',
users: [],
selectedHeaders: [],
headerOptions: [],
excelData: [],
tempExcelData: [],
headerMappings: [],
systemHeaders: [
{<!-- -->
title: 'number',
isRequire: true
},
{<!-- -->
title: 'Transaction Date',
isRequire: true
},
{<!-- -->
title: 'Payer',
isRequire: true
},
{<!-- -->
title: 'Payer Account',
isRequire: true
},
// {<!-- -->
// title: 'Payee',
// isRequire: true
// },
// {<!-- -->
// title: 'Payee account number',
// isRequire: true
// },
{<!-- -->
title: 'Amount',
isRequire: true
},
{<!-- -->
title: 'Abstract',
isRequire: false
},
{<!-- -->
title: 'Remarks',
isRequire: false
},
],
initSystemHeaders: [
{<!-- -->
title: 'number',
isRequire: true
},
{<!-- -->
title: 'Transaction Date',
isRequire: true
},
{<!-- -->
title: 'Payer',
isRequire: true
},
{<!-- -->
title: 'Payer Account',
isRequire: true
},
// {<!-- -->
// title: 'Payee',
// isRequire: true
// },
// {<!-- -->
// title: 'Payee account number',
// isRequire: true
// },
{<!-- -->
title: 'Amount',
isRequire: true
},
{<!-- -->
title: 'Abstract',
isRequire: false
},
{<!-- -->
title: 'Remarks',
isRequire: false
},
],
fileFormat: ['xlsx', 'xlc', 'xlm', 'xls', 'xlt', 'xlw', 'csv'],
batchStatus: '',
headerMap: new Map,
scanLoading: false,
batchNumber: '',
tableSize: 0,
showTableSize: 0,
}
}
3. Page layout
<div>
<el-upload
action=""
accept=".xlsx"
:limit="1"
:before-upload="beforeUpload"
:on-exceed="handleExceed"
:on-success="handleUploadSuccess"
:on-error="handleUploadError">
<el-button type="primary" plain>Select file</el-button>
</el-upload>
<div slot="tip" class="el-upload__tip">Only xlsx/xls files can be uploaded, and no more than 5MB</div>
<div v-if="this.fileName !==''" style="margin-top: 10px;font-size: 15px;">
This import file name:
<span style="margin-right: 15px;color: #dd1100">
{<!-- -->{ this.fileName }}
</span>
Number of imported data items:
<span style="margin-right: 15px;color: #dd1100">
{<!-- -->{this.tableSize -1}}
</span>
Display number of data items:
<span style="margin-right: 15px;color: #dd1100">
{<!-- -->{this.tableData.length}}
</span>
</div>
<div style="overflow-x: auto;">
<table class="text-center" cellspacing="0" cellpadding="0" border="1"
style="margin-top: 10px;width: 3000px">
<thead style="height: 35px;background-color: #f2dae2">
<tr>
<th colspan="1" rowspan="1" style="width: 1500px"
v-for="(header, index) in systemHeaders"
:key="index"
:style="header.isRequire ? { color: 'red' } : {}">
{<!-- -->{ header.title }}
</th>
</tr>
</thead>
<tbody v-if="tableData.length >0" class="text-center">
<tr style="height: 35px">
<td v-for="(header, index) in excelHeaders" :key="index">
<select v-model="selectedHeaders[index]"
@change="handleHeaderChange(index)"
:disabled="systemHeaders[index].title === '-'"
style="height: 35px;width: 100%">
<option v-for="option in headerOptions" :key="option" :value="option">
{<!-- -->{ option }}
</option>
</select>
</td>
</tr>
<tr v-for="(row, rowIndex) in tableData" :key="rowIndex" style="min-height: 35px">
<td v-for="(cell, cellIndex) in row"
:key="cellIndex"
:style="cellIndex >= systemHeaders.length ? { 'width': '1500px' }: '' ">
{<!-- -->{ cell }}
</td>
</tr>
</tbody>
</table>
</div>
<el-button type="warning" plain @click="handleRemove" :disabled="this.fileName === ''">
Remove files
</el-button>
<el-button type="success" plain @click="confirmImport" style="margin-top: 15px"
:disabled="this.excelLimit === 0" :loading="this.uploadLoading">
Confirm import
</el-button>
</div>
4. Upload previous events
beforeUpload(file) {<!-- -->
this.file = file;
this.fileName = file.name
let fileSize = file.size
const FIVE_M = 5 * 1024 * 1024
//Do not allow uploads larger than 5M
if (fileSize > FIVE_M) {<!-- -->
this.$message.error("Maximum upload 5MB")
return false
}
const suffix = file.name.split('.').reverse()[0];
if (!this.fileFormat.includes(suffix)) {<!-- -->
this.$message.error('Only .xlsx or .xls files can be uploaded')
return false
}
this.handleFileUploaded(file)
this.excelLimit = 1
return false; // Prevent default upload behavior
}
5. Parse the excel file and assemble the corresponding relationship between the system header and the excel header
handleFileUploaded(file) {<!-- -->
console.log("==============>>Start parsing excel file<<==============")
let reader = new FileReader()
let _this = this;
reader.onload = (e) => {<!-- -->
const data = new Uint8Array(e.target.result)
const workbook = XLSX.read(data, {<!-- -->
type: 'array',
cellDates: true
})
const worksheet = workbook.Sheets[workbook.SheetNames[0]]
const jsonData = XLSX.utils.sheet_to_json(worksheet, {<!-- -->header: 1})
_this.tableSize = jsonData.length
const regex = /^[a-zA-Z]{3} [a-zA-Z]{3} \d{2} \d{4} \d{2}:\d{2} :\d{2} GMT\ + \d{4} \(China Standard Time\)$/;
const tableData = jsonData.map(row => {<!-- -->
return row.map(cell => {<!-- -->
if (cell !== '' || cell !== null) {<!-- -->
if (regex.test(cell.toString())) {<!-- -->
let date = new Date(cell);
date.setDate(date.getDate() + 1);
const isoDateString = date.toISOString();
return isoDateString.slice(0, 10).replace('T', '-');
}
}
return cell;
});
});
// Get the header of Excel
_this.excelHeaders = tableData[0]
// Generate drop-down box options based on system header and Excel header
for (let index = 0; index < _this.excelHeaders.length; index + + ) {<!-- -->
const excelHeader = _this.excelHeaders[index]
_this.headerOptions.push(excelHeader)
}
if (!_this.objectsAreEqual(_this.systemHeaders, _this.excelHeaders)) {<!-- -->
_this.headerOptions.unshift('missing')
}
//Initialize the selected header
_this.initSelectHeader();
// Get the data of the corresponding column (only the first 5 items are displayed here)
_this.tableData = JSON.parse(JSON.stringify(tableData.slice(1, 6)))
_this.tempExcelData = JSON.parse(JSON.stringify(tableData.slice(1, 6)))
//Initialize table data
_this.initExcelData()
//Assemble header initial relationship
_this.handHeaderMapping()
}
reader.readAsArrayBuffer(file)
},
initSelectHeader() {<!-- -->
this.systemHeaders.forEach((systemHeader) => {<!-- -->
let selectedHeader = 'Missing';
let _excelHeader = '';
for (let index = 0; index <this.excelHeaders.length; index + + ) {<!-- -->
const excelHeader = this.excelHeaders[index]
if (excelHeader === systemHeader.title) {<!-- -->
_excelHeader = excelHeader
break;
}
}
if (_excelHeader !== '') {<!-- -->
this.selectedHeaders.push(_excelHeader)
} else {<!-- -->
this.selectedHeaders.push(selectedHeader)
}
});
if (this.excelHeaders.length > this.systemHeaders.length) {<!-- -->
this.selectedHeaders = this.selectedHeaders.concat(this.excelHeaders.slice(this.selectedHeaders.length));
const moreLength = this.excelHeaders.length - this.systemHeaders.length
for (let index = 0; index < moreLength; index + + ) {<!-- -->
this.systemHeaders.push({<!-- -->title: '-', isRequire: false})
}
}
},
initExcelData() {<!-- -->
for (let index = 0; index <this.selectedHeaders.length; index + + ) {<!-- -->
this.handleHeaderChange(index)
}
},
objectsAreEqual(obj1, obj2) {<!-- -->
return JSON.stringify(obj1) === JSON.stringify(obj2);
},
handHeaderMapping() {<!-- -->
const headerMap = new Map();
let filteredSystemHeaders = this.systemHeaders.filter(header => header.title !== '-')
filteredSystemHeaders.forEach((item, index) => {<!-- -->
let key = this.selectedHeaders[index]
headerMap.set(item.title, key)
})
this.headerMap = headerMap;
}
6. Drop-down box change event
handleHeaderChange(index) {<!-- -->
const selectedHeader = this.selectedHeaders[index]
if (selectedHeader === 'missing') {<!-- -->
// If missing is selected, clear the data in the corresponding column
this.tableData.forEach(row => {<!-- -->
row[index] = ''
})
} else {<!-- -->
// If the Excel header is selected, the data of the corresponding column will be displayed under the system header.
this.tableData.forEach((row, _index) => {<!-- -->
const rowIndex = this.excelHeaders.findIndex(item => item === selectedHeader);
if (rowIndex >= 0) {<!-- -->
row[index] = this.tempExcelData[_index][rowIndex]
}
})
}
//Update header mapping
if (this.systemHeaders[index] !== undefined & amp; & amp; this.systemHeaders[index] !== null) {<!-- -->
let _systemHeader = this.systemHeaders[index].title
this.headerMap.set(_systemHeader, selectedHeader)
}
}