模板存放位置 模板限定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里的table
export function transformTableHeader(tableHeader) {
return tableHeader.map((item, index) => ({
label: item,
prop: `propTable${index + 1}`
}));
}
// 定义一个方法来转换表格数据格式，符合elementUi里的data
export 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>
4045
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
