模板存放位置 模板限定xls后缀，xlsx会有时间问题
src/assets/excelTemplate/模板.xls
安装模块包
npm install file-loader --save-dev //开发，Webpack 配置中使用它来处理文件加载npm i xlsx --save //生产，解析和处理 Excel 文件的库新增配置，在vue.config.js中，自己比较一下，最后一段新增的chainWebpack
module.exports = defineConfig({ transpileDependencies: true, assetsDir: 'static', //打包配置文件 parallel: false, publicPath: './', devServer: { port: port, open: true, proxy: { '/api': { target: process.env.VUE_APP_BASE_URL, changeOrigin: true, ws: true, pathRewrite: { '^/api': '', }, }, }, }, configureWebpack: { name: name, resolve: { alias: { '@': resolve('src'), }, }, }, chainWebpack(config) { config.module .rule('excel') .test(/\.(xls|xlsx)$/) .use('file-loader') .loader('file-loader') .options({ name: '[name].[ext]', }) .end() },})即可将模板下载到本地
<template> <el-button @click="downloadFile" icon="el-icon-download">下载配置模板</el-button></template><script>import excelFile from '@/assets/excelTemplate/模板.xls'export default { data() { return {} }, methods: { //下载 downloadFile() { const link = document.createElement('a') link.href = excelFile link.download = '模板.xls' link.style.display = 'none' // 隐藏元素 document.body.appendChild(link) // 添加到文档中 link.click() document.body.removeChild(link) // 点击后移除 }, },}</script><style></style>我使用XLSX的场景，在我上传excel的时候，我需要获取它的表头以及里面的数据进行渲染到表格中，在我编辑的时候需要请求Excel的地址，将返回流转JSON也拿里面的表格数据
封装公共的方法
/** * xlsx自身插件有bug，比如excel里17:00:00，从流获取会少了43s，跟时区有关，用下面2个无用，最好方法直接限制上传文件后最为xls即可，就不会出现时间偏差了 * @param {*} date * @returns */function fixPrecisionLoss(date) { const importBugHotfixDiff = (() => { const basedate = new Date(1899, 11, 30, 0, 0, 0); const dnthreshAsIs = (new Date().getTimezoneOffset() - basedate.getTimezoneOffset()) * 60000 - 1000; const dnthreshToBe = getTimezoneOffsetMS(new Date()) - getTimezoneOffsetMS(basedate); return dnthreshAsIs - dnthreshToBe; })(); return new Date(date.getTime() - importBugHotfixDiff);}function getTimezoneOffsetMS(date) { var time = date.getTime(); var utcTime = Date.UTC(date.getFullYear(), date.getMonth(), date.getDate(), date.getHours(), date.getMinutes(), date.getSeconds(), date.getMilliseconds()); return time - utcTime;}/** * 核心处理excel流，读取里面的数据 * @param {*} file * @param {*} callback * @param {*} XLSX * @param {*} dayjs */export function readExcelFile(file, callback, XLSX, dayjs) { const reader = new FileReader(); reader.onload = event => { const data = event.target.result; let workBook = XLSX.read(data, { type: 'array', cellDates: true }); try { const worksheet = workBook.Sheets[workBook.SheetNames[0]]; console.log('🚀 ~ readExcelFile ~ worksheet:', worksheet); const tableHeader = getHeaderRowA(worksheet, XLSX); console.log('🚀 ~ readExcelFile ~ tableHeader:', tableHeader); const excelData = XLSX.utils.sheet_to_json(worksheet, { raw: true, defval: '-' }); excelData.forEach(obj => { Object.keys(obj).forEach(v => { if (obj[v] instanceof Date) { obj[v] = dayjs((obj[v])).format('YYYY-MM-DD HH:mm:ss'); } }); }); callback(null, { tableHeader, excelData }); } catch (error) { callback(error); } }; reader.onerror = event => { callback(event.error); }; reader.readAsArrayBuffer(file);}/** * 获取excel的表头数组 * @param {*} sheet 工作簿 查看excel就知道有很多的sheet了一般读取[0]也就是第一个 * @param {*} XLSX 工具包 * @returns */function getHeaderRowA(sheet, XLSX) { const headers = []; // 定义数组，用于存放解析好的数据 const range = XLSX.utils.decode_range(sheet['!ref']); // 读取sheet的单元格数据 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; // 经过上方一波操作遍历，得到最终的第一行头数据}/** * 将excel的 Sun Feb 18 2024 11:53:44 GMT+0800日期格式化成这种格式YYYY-MM-DD HH:mm:ss * @param {} excelData * @returns */export function formatExcelDataA(excelData, dayjs) { return excelData.map(obj => { let newObj = {}; Object.keys(obj).forEach(v => { if (obj[v] instanceof Date) { newObj[v] = dayjs((obj[v])).format('YYYY-MM-DD HH:mm:ss'); } else { newObj[v] = obj[v]; } }); return newObj; });}// 定义一个方法来转换表头数据格式，符合elementUi里的tableexport function transformTableHeader(tableHeader) { return tableHeader.map((item, index) => ({ label: item, prop: `propTable${index + 1}` }));}// 定义一个方法来转换表格数据格式，符合elementUi里的dataexport function transformTableData(tableHeader, excelData) { return excelData.map(item => { let rowData = {}; Object.keys(item).forEach(value => { const propItem = tableHeader.find(p => p.label === value); rowData[propItem.prop] = item[value]; }); return { ...rowData }; });}// 定义一个方法来处理表头标签,因为上传的带有*的必填，去掉展示export function processTableHeaderLabel(tableHeader) { return tableHeader.map(obj => { const label = obj.label.startsWith('*') ? obj.label.slice(1) : obj.label; return { ...obj, label }; });}下面是调用，抽离核心需要的，仅供参考
<template> <div> <el-button @click="downloadFile" icon="el-icon-download">下载配置模板</el-button> <el-upload ref="upload" :limit="1" accept=".xls,.xlsx" class="upload-demo" :action="upload.url" :file-list="fileList" :on-success="handleFileSuccess"> <el-button type="primary" icon="el-icon-upload">上传xls文件</el-button> </el-upload> <el-card class="box-card" style="height: 250px"> <div slot="header" class="clearfix"> <span>数据展示</span> </div> <el-table ref="singleTable" :data="tableData" highlight-current-row height="170" @current-change="handleCurrentChange"> <el-table-column v-for="(item, index) in tableHeader" :prop="item.prop" :key="index" :label="item.label" show-overflow-tooltip></el-table-column> </el-table> </el-card> </div></template><script>// npm install file-loader --save-dev //开发，Webpack 配置中使用它来处理文件加载// npm i xlsx --save //生产，解析和处理 Excel 文件的库import excelFile from '@/assets/excelTemplate/模板.xls'import * as XLSX from 'xlsx'import dayjs from 'dayjs'import { readExcelFile, formatExcelDataA, transformTableHeader, transformTableData, processTableHeaderLabel } from '@/views/common/index.js' //算法模块公用方法lUCKY封装export default { data() { return { tableHeader: '', tableData: '', responseName: '', responseUrl: '', //上传的列表 fileList: [], // 上传参数 upload: { url: 'http://192.168.15.115:3737/luckyNwa/uploadPicLocal', // 请求地址 }, } }, methods: { //上传之前的限制 beforeUpload(file) { console.log('🚀 ~ beforeUpload ~ file:', file) const validFormats = ['.xls', '.xlsx'] const fileFormat = file.name.slice(file.name.lastIndexOf('.')).toLowerCase() if (!validFormats.includes(fileFormat)) { this.$message.error('只能上传xls或xlsx文件') return false // 阻止文件上传 } return true // 允许文件上传 }, //下载 downloadFile() { const link = document.createElement('a') link.href = excelFile link.download = '模板.xls' link.style.display = 'none' // 隐藏元素 document.body.appendChild(link) // 添加到文档中 link.click() document.body.removeChild(link) // 点击后移除 }, /** 文件上传成功处理 */ handleFileSuccess(response, f, fileList) { this.isUpload = true this.$refs.upload.clearFiles() this.responseUrl = f.response.data console.log('🚀 ~ handleFileSuccess ~ this.responseUrl:', this.responseUrl) this.responseName = f.raw.name const file = f.raw //获取上传的文件 console.log('🚀 ~ handleFileSuccess ~ file:', file) // this.$modal.msgSuccess('上传成功'); if (file) { this.isFileDeal(file) } }, //处理excel isFileDeal(file) { readExcelFile( file, (error, data) => { if (error) { console.log('里面的tryCatch捕获的异常：' + error) this.beforeRemove() this.$modal.msgWarning('表格数据不能为空！') } else { const tableHeader = data.tableHeader || [] const excelData = data.excelData || [] if (excelData.length === 0 || excelData === null) { this.beforeRemove() this.$modal.msgWarning('表格数据不能为空！') return } console.log('读取的excel表头数据（第一行）', tableHeader) console.log('读取所有excel数据', excelData) //这里进一步验证里面的数据，必填的里面不能为空也就是- let emptyFields = new Set() excelData.forEach((item) => { if (item['*曲线名称'] === '-' && item['*水位(m)'] === '-' && item['*流量(m³/s)'] === '-' && item['*测站编码'] === '-') { emptyFields.add('测站编码、曲线名称、水位、流量') } else { if (item['*曲线名称'] === '-') { emptyFields.add('曲线名称') } if (item['*水位(m)'] === '-') { emptyFields.add('水位') } if (item['*流量(m³/s)'] === '-') { emptyFields.add('流量') } if (item['*测站编码'] === '-') { emptyFields.add('测站编码') } } }) if (emptyFields.size > 0) { this.beforeRemove() const errorMessage = [...emptyFields].join('、') + '数据为空，请重新输入后上传' console.log(errorMessage) this.$confirm(errorMessage, '系统提示', { confirmButtonText: '确定', showCancelButton: false, type: 'warning', }).then(() => { console.log('确定') }) return } else { console.log('所有字段均有值，可以上传') } this.tableHeader = tableHeader this.excelData = formatExcelDataA(excelData, dayjs) this.dealExcelData() //处理成表格的数据模板 } }, XLSX, dayjs ) }, //处理数据 dealExcelData() { // 获取第一个对象的键数组作为表头，并确保顺序与对象中属性的顺序相同 this.tableHeader = transformTableHeader(this.tableHeader) console.log('表头:', this.tableHeader) this.tableData = transformTableData(this.tableHeader, this.excelData) // 转换数据格式 console.log('这里是表数据tableData:', this.tableData) this.tableHeader = processTableHeaderLabel(this.tableHeader) this.$refs.singleTable.doLayout() }, //监听下拉框变化 handleSelectChange(number) { const dateTimeRegex = /^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/ switch (number) { case 1: this.stationCodeTemp = this.tableData.map((row) => row[this.formLabelTop.stationCode]) console.log('🚀 ~ handleSelectChange ~ this.stationCodeTemp :', this.stationCodeTemp) break case 2: this.curveNameTemp = this.tableData.map((row) => row[this.formLabelTop.curveName]) console.log('🚀 ~ handleSelectChange ~ this.curveNameTemp:', this.curveNameTemp) break case 3: for (let i = 0; i < this.tableData.length; i++) { const row = this.tableData[i] const value = row[this.formLabelTop.enableTime] console.log('🚀 ~ handleSelectChange ~ value:', value) // 判断值是否不是 'YYYY-MM-DD HH:mm:ss' 格式 if (!dateTimeRegex.test(value)) { console.log('value不是YYYY-MM-DD HH:mm:ss格式') this.formLabelTop.enableTime = '' this.$modal.msgWarning('BGTM字段需为日期,字段类型不匹配,请重新选择') return } } this.enableTimeTemp = this.tableData.map((row) => row[this.formLabelTop.enableTime]) console.log('🚀 ~ handleSelectChange ~ this.enableTimeTemp :', this.enableTimeTemp) break case 4: for (let i = 0; i < this.tableData.length; i++) { const row = this.tableData[i] const value = row[this.formLabelTop.pointNum] console.log('🚀 ~ handleSelectChange ~ value:', value) // 在这里放置对 value 值的判断逻辑 if (typeof value !== 'number' || !Number.isInteger(value)) { console.log('value不是整数') this.formLabelTop.pointNum = '' this.$modal.msgWarning('PTNO字段需为整型,字段类型不匹配,请重新选择') return } } this.pointNumTemp = this.tableData.map((row) => row[this.formLabelTop.pointNum]) console.log('🚀 ~ handleSelectChange ~ this.pointNumTemp:', this.pointNumTemp) break case 5: for (let i = 0; i < this.tableData.length; i++) { const row = this.tableData[i] const value = row[this.formLabelTop.waterLevel] console.log('🚀 ~ handleSelectChange ~ value:', value) // 在这里放置对 value 值的判断逻辑 if (typeof value !== 'number') { console.log('waterLevel中有数据不是number') this.formLabelTop.waterLevel = '' this.$modal.msgWarning('Z字段需为浮点型,字段类型不匹配,请重新选择') return } } this.waterLevelTemp = this.tableData.map((row) => row[this.formLabelTop.waterLevel]) console.log('🚀 ~ handleSelectChange ~ this.waterLevelTemp:', this.waterLevelTemp) break case 6: for (let i = 0; i < this.tableData.length; i++) { const row = this.tableData[i] const value = row[this.formLabelTop.flow] console.log('🚀 ~ handleSelectChange ~ value:', value) // 在这里放置对 value 值的判断逻辑 if (typeof value !== 'number') { console.log('flow中有数据不是number') this.formLabelTop.flow = '' this.$modal.msgWarning('Q字段需为浮点型,字段类型不匹配,请重新选择') return } } console.log('flow++++++') this.flowTemp = this.tableData.map((row) => row[this.formLabelTop.flow]) console.log('🚀 ~ handleSelectChange ~ this.flowTemp:', this.flowTemp) break case 7: this.columnCommentsTemp = this.tableData.map((row) => row[this.formLabelTop.columnComments]) console.log('🚀 ~ handleSelectChange ~ this.columnCommentsTemp:', this.columnCommentsTemp) break case 8: for (let i = 0; i < this.tableData.length; i++) { const row = this.tableData[i] const value = row[this.formLabelTop.columnModitime] console.log('🚀 ~ handleSelectChange ~ value:', value) // 判断值是否不是 'YYYY-MM-DD HH:mm:ss' 格式 if (!dateTimeRegex.test(value)) { console.log('value不是YYYY-MM-DD HH:mm:ss格式') this.formLabelTop.columnModitime = '' this.$modal.msgWarning('MODITIME字段需为日期,字段类型不匹配,请重新选择') return } } this.columnModitimeTemp = this.tableData.map((row) => row[this.formLabelTop.columnModitime]) console.log('🚀 ~ handleSelectChange ~ this.columnModitimeTemp:', this.columnModitimeTemp) break case 9: console.log('🚀 ~ handleSelectChange ~ this.tableData:', this.tableData) this.columnExkeyTemp = this.tableData.map((row) => row[this.formLabelTop.columnExkey]) console.log('🚀 ~ handleSelectChange ~ this.columnExkeyTemp:', this.columnExkeyTemp) break default: console.log('number 参数不正确') break } }, //处理下拉框的曲线数据 dealData2() { // 定义属性数组 let propArrays = [ this.stationCodeTemp, this.curveNameTemp, this.enableTimeTemp, this.pointNumTemp, this.waterLevelTemp, this.flowTemp, this.columnCommentsTemp, this.columnModitimeTemp, this.columnExkeyTemp, ] // 遍历属性数组 for (let i = 0; i < Math.max(...propArrays.map((arr) => arr.length)); i++) { // 创建新的对象 let newObj = { formulaId: null, stationCode: '', curveName: '', enableTime: '', pointNum: '', waterLevel: '', flow: '', columnComments: '', columnModitime: '', columnExkey: '', } // 遍历属性数组并赋值 propArrays.forEach((arr, index) => { if (i < arr.length) { newObj[Object.keys(newObj)[index + 1]] = arr[i] } }) // 将对象添加到结果数组中 this.curveInfoTemp.push(newObj) } const obj = { tableHeader: this.tableHeader, fileName: this.responseName, stationCode: this.formLabelTop.stationCode, curveName: this.formLabelTop.curveName, enableTime: this.formLabelTop.enableTime, pointNum: this.formLabelTop.pointNum, waterLevel: this.formLabelTop.waterLevel, flow: this.formLabelTop.flow, columnComments: this.formLabelTop.columnComments, columnModitime: this.formLabelTop.columnModitime, columnExkey: this.formLabelTop.columnExkey, } this.curveColumnMappingTemp = JSON.stringify(obj) console.log(this.curveInfoTemp) }, //移除文件 beforeRemove(file, fileList) { this.isUpload = false this.tableHeader = [] this.tableData = [] this.responseName = '' this.formLabelTop = { stationCode: '', curveName: '', enableTime: '', pointNum: '', waterLevel: '', flow: '', columnComments: '', columnModitime: '', columnExkey: '', } this.$nextTick(() => { this.$refs['formLabelTop'].clearValidate() }) }, },}</script><style></style>
