行业知识
【前端】Vue中引入excel模板并下载以及XLSX封装使用
2026-11-10 14:41:52
来源：互联网转载
模板存放位置 模板限定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() },}) 即可将模板下载到本地
下载配置模板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) // 点击后移除 }, },} 我使用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 /* 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; // 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 }; });} 下面是调用，抽离核心需要的，仅供参考
下载配置模板 上传xls文件 数据展示 // 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 row[this.formLabelTop.enableTime]) console.log(' ~ handleSelectChange ~ this.enableTimeTemp :', this.enableTimeTemp) break case 4: for (let i = 0; i row[this.formLabelTop.pointNum]) console.log(' ~ handleSelectChange ~ this.pointNumTemp:', this.pointNumTemp) break case 5: for (let i = 0; i row[this.formLabelTop.waterLevel]) console.log(' ~ handleSelectChange ~ this.waterLevelTemp:', this.waterLevelTemp) break case 6: for (let i = 0; i 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 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 arr.length)); i++) { // 创建新的对象 let newObj = { formulaId: null, stationCode: '', curveName: '', enableTime: '', pointNum: '', waterLevel: '', flow: '', columnComments: '', columnModitime: '', columnExkey: '', } // 遍历属性数组并赋值 propArrays.forEach((arr, index) => { if (i { this.$refs['formLabelTop'].clearValidate() }) }, },}
