前端Vue 结合xlxs库实现解析excel文件，并动态组装表头！

1.前言

最近有一个需求，就是用户可以任意导入一个自定义的excel文件，让用户可以自己选择，组装表头的对应关系，这样做的目的是为了解决模板的局限性，更加的灵活的导入

2.数据定义

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: '编号',isRequire: true},{title: '交易日期',isRequire: true},{title: '付款人',isRequire: true},{title: '付款人账号',isRequire: true},// {//     title: '收款人',//     isRequire: true// },// {//     title: '收款人账号',//     isRequire: true// },{title: '金额',isRequire: true},{title: '摘要',isRequire: false},{title: '备注',isRequire: false},],initSystemHeaders: [{title: '编号',isRequire: true},{title: '交易日期',isRequire: true},{title: '付款人',isRequire: true},{title: '付款人账号',isRequire: true},// {//     title: '收款人',//     isRequire: true// },// {//     title: '收款人账号',//     isRequire: true// },{title: '金额',isRequire: true},{title: '摘要',isRequire: false},{title: '备注',isRequire: false},],fileFormat: ['xlsx', 'xlc', 'xlm', 'xls', 'xlt', 'xlw', 'csv'],batchStatus: '',headerMap: new Map,scanLoading: false,batchNumber: '',tableSize: 0,showTableSize: 0,}}

3. 页面布局

<div><el-uploadaction=""accept=".xlsx":limit="1":before-upload="beforeUpload":on-exceed="handleExceed":on-success="handleUploadSuccess":on-error="handleUploadError"><el-button type="primary" plain>选择文件</el-button></el-upload><div slot="tip" class="el-upload__tip">只能上传xlsx/xls文件，且不超过5MB</div><div v-if="this.fileName !==''" style="margin-top: 10px;font-size: 15px;">本次导入文件名：<span style="margin-right: 15px;color: #dd1100">{{ this.fileName }}</span>导入数据条数：<span style="margin-right: 15px;color: #dd1100">{{this.tableSize -1}}</span>展示数据条数： <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 === ''">移除文件</el-button><el-button type="success" plain @click="confirmImport" style="margin-top: 15px":disabled="this.excelLimit === 0" :loading="this.uploadLoading">确认导入</el-button>
</div>

4.上传之前的事件

 beforeUpload(file) {this.file = file;this.fileName = file.namelet fileSize = file.sizeconst FIVE_M = 5 * 1024 * 1024//不允许上传大于5Mif (fileSize > FIVE_M) {this.$message.error("最大上传5MB")return false}const suffix = file.name.split('.').reverse()[0];if (!this.fileFormat.includes(suffix)) {this.$message.error('只能上传.xlsx或者.xls文件')return false}this.handleFileUploaded(file)this.excelLimit = 1return false; // 阻止默认上传行为}

5.解析excel文件,并组装系统表头与excel表头的对应关系

 handleFileUploaded(file) {console.log("==============>>开始解析excel文件<<==============")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.lengthconst regex = /^[a-zA-Z]{3} [a-zA-Z]{3} \d{2} \d{4} \d{2}:\d{2}:\d{2} GMT\+\d{4} \(中国标准时间\)$/;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;});});// 获取Excel的表头_this.excelHeaders = tableData[0]// 根据系统表头和Excel表头生成下拉框选项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('缺失')}// 初始化选中的表头_this.initSelectHeader();// 获取对应列的数据（这里只展示前5条）_this.tableData = JSON.parse(JSON.stringify(tableData.slice(1, 6)))_this.tempExcelData = JSON.parse(JSON.stringify(tableData.slice(1, 6)))// 初始化表格数据_this.initExcelData()//组装表头初始关系_this.handHeaderMapping()}reader.readAsArrayBuffer(file)},initSelectHeader() {this.systemHeaders.forEach((systemHeader) => {let selectedHeader = '缺失';let _excelHeader = '';for (let index = 0; index < this.excelHeaders.length; index++) {const excelHeader = this.excelHeaders[index]if (excelHeader === systemHeader.title) {_excelHeader = excelHeaderbreak;}}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.lengthfor (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.下拉框改变事件

handleHeaderChange(index) {const selectedHeader = this.selectedHeaders[index]if (selectedHeader === '缺失') {// 如果选择了缺失，则清空对应列的数据this.tableData.forEach(row => {row[index] = ''})} else {// 如果选择了Excel表头，则将对应列的数据展示到系统表头下this.tableData.forEach((row, _index) => {const rowIndex = this.excelHeaders.findIndex(item => item === selectedHeader);if (rowIndex >= 0) {row[index] = this.tempExcelData[_index][rowIndex]}})}//更新头部映射if (this.systemHeaders[index] !== undefined && this.systemHeaders[index] !== null) {let _systemHeader = this.systemHeaders[index].titlethis.headerMap.set(_systemHeader, selectedHeader)}}