鞍山人才网官方网站,百度公司销售卖什么的,孝感网站开发公司,在线设计平台开源目录 1.前言2.数据定义3. 页面布局4.上传之前的事件5.解析excel文件,并组装系统表头与excel表头的对应关系6.下拉框改变事件 1.前言
最近有一个需求#xff0c;就是用户可以任意导入一个自定义的excel文件#xff0c;让用户可以自己选择#xff0c;组装表头的对应关系… 目录 1.前言2.数据定义3. 页面布局4.上传之前的事件5.解析excel文件,并组装系统表头与excel表头的对应关系6.下拉框改变事件 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. 页面布局
divel-uploadactionaccept.xlsx:limit1:before-uploadbeforeUpload:on-exceedhandleExceed:on-successhandleUploadSuccess:on-errorhandleUploadErrorel-button typeprimary plain选择文件/el-button/el-uploaddiv slottip classel-upload__tip只能上传xlsx/xls文件且不超过5MB/divdiv v-ifthis.fileName ! stylemargin-top: 10px;font-size: 15px;本次导入文件名span stylemargin-right: 15px;color: #dd1100{{ this.fileName }}/span导入数据条数span stylemargin-right: 15px;color: #dd1100{{this.tableSize -1}}/span展示数据条数 span stylemargin-right: 15px;color: #dd1100{{this.tableData.length}}/span/divdiv styleoverflow-x: auto;table classtext-center cellspacing0 cellpadding0 border1stylemargin-top: 10px;width: 3000pxthead styleheight: 35px;background-color: #f2dae2trth colspan1 rowspan1 stylewidth: 1500pxv-for(header, index) in systemHeaders:keyindex:styleheader.isRequire ? { color: red } : {}{{ header.title }}/th/tr/theadtbody v-iftableData.length 0 classtext-centertr styleheight: 35pxtd v-for(header, index) in excelHeaders :keyindexselect v-modelselectedHeaders[index]changehandleHeaderChange(index):disabledsystemHeaders[index].title -styleheight: 35px;width: 100%option v-foroption in headerOptions :keyoption :valueoption{{ option }}/option/select/td/trtr v-for(row, rowIndex) in tableData :keyrowIndex stylemin-height: 35pxtd v-for(cell, cellIndex) in row:keycellIndex:stylecellIndex systemHeaders.length ? { width: 1500px }: {{ cell }}/td/tr/tbody/table/divel-button typewarning plain clickhandleRemove :disabledthis.fileName 移除文件/el-buttonel-button typesuccess plain clickconfirmImport stylemargin-top: 15px:disabledthis.excelLimit 0 :loadingthis.uploadLoading确认导入/el-button
/div4.上传之前的事件 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)}}
