vue前端xlsx解析excel文件，前端校验数据格式，分流发送到后端校验数据冲突及插入
目录excelUtils.jsdemo（excel上传页面）自己封装解析excel文件的excelUtils.jsfunction excelUtils(){async function getAllDataOfSheets(file) {return new Promise(function (resolve, reject) {const fileReader = new FileReader
·
目录
自己封装解析excel文件的
excelUtils.js
function excelUtils(){
async function getAllDataOfSheets(file) {
return new Promise(function (resolve, reject) {
const fileReader = new FileReader();
let excelName = file.name;
try {
fileReader.onload = ev => {
let execl = {
name:excelName,
data:{}
}
let obj={};
const data = ev.target.result;
const workbook = XLSX.read(data, {
type: "binary",
cellDates: true
});
for (let sheet in workbook.Sheets) {
//循环读取每个文件
obj[sheet] = XLSX.utils.sheet_to_json(workbook.Sheets[sheet]);
}
execl.data = obj
// console.log(obj)
resolve(execl);
};
} catch (e) {
console.log(e);
reject({});
}
fileReader.readAsBinaryString(file);
})
}
function exportExcel(fileName, data, columnHeaders, wscols) {
let sheetNames = [];
let sheetsList = {};
const wb = XLSX.utils.book_new();
for (let key in data) {
sheetNames.push(key);
sheetsList[key] = XLSX.utils.aoa_to_sheet(transferData(data[key], columnHeaders[key].prop));
if (arguments.length == 4) {
sheetsList[key]["!cols"] = wscols[key];
}
}
wb["SheetNames"] = sheetNames;
wb["Sheets"] = sheetsList;
XLSX.writeFile(wb, fileName);
}
function transferPropertiesName(data, columnHeaders) {
let tmpData = {};
for (let sheetName in columnHeaders) {
if(typeof data[sheetName]!="undefined"){
if (data[sheetName].length != 0 ) {
let arr = data[sheetName].map(v => {
let obj = {};
for (let prop in columnHeaders[sheetName].prop) {
if (typeof v[columnHeaders[sheetName].prop[prop]] == "undefined") {
//如果没填这个字段设置空串，不需要可以注释掉
obj[prop] = "";
} else {
obj[prop] = v[columnHeaders[sheetName].prop[prop]];
}
}
return obj;
});
tmpData[sheetName] = arr;
} else {
tmpData[sheetName] = [];
}
}
}
return tmpData;
}
function transferData(data, columnHeader) {
let content = [],
header = [];
for (let i in columnHeader) {
header.push(columnHeader[i]);
}
content.push(header);
data.forEach((item, index) => {
let arr = [];
for (let i in columnHeader) {
arr.push(item[i]);
}
content.push(arr);
});
return content;
}
return {
getAllDataOfSheets:getAllDataOfSheets,
exportExcel:exportExcel,
transferPropertiesName:transferPropertiesName
}
}
import XLSX from 'xlsx';
export {excelUtils}
demo（excel上传页面）
<template>
<div style="text-align: center;width: 100%;">
<div style="width: 400px;text-align: left; margin: 0 auto;">
<p>
请先下载
<i style="color:#409EFF;cursor: pointer;" @click="downloadVslmTemplate">下载流水线导入模板</i>
</p>
<p>使用说明 : </p>
<div style="font-size: small">
<p>(1)必须上传 '产线模板基础数据.xlsx' 和 '流水线数据导入表.xlsx'文件</p>
<p>(2)excel名称不可修改</p>
<p>(3)上传后如返回错误流水线配置信息,根据反馈excel修改即可</p>
<p>(4)请保持文件结构，填写数据时需删除示例数据</p>
</div>
<br/>
</div>
<!-- excel表格上传 -->
<el-upload
:limit="2"
:multiple="true"
:drag="true"
:show-file-list="true"
ref="upload"
action="noAction"
:before-upload="beforeUpload"
:on-remove="handleRemove"
:on-success="handleSuccess"
:on-progress="handleProgress"
:on-change="handleOnchange"
:file-list="fileList"
accept=".xls, .xlsx"
:auto-upload="false"
:data="localeData"
:http-request="uploadSectionFileFunc">
<i class="el-icon-upload"></i>
<div class="el-upload__text">
将文件拖到此处，或
<em>点击上传</em>
</div>
<div class="el-upload__tip" slot="tip">请依次或同时上传'产线模板基础数据.xlsx' 和 '流水线数据导入表.xlsx'</div>
</el-upload>
<el-button :disabled="!isPassTheFormatCheck" style="margin-right: 10px;" size="small" type="success"
@click="submitUpload">开始导入
</el-button>
<el-progress
:text-inside="true"
:stroke-width="26"
style="width: 500px;margin: 5px auto;"
:percentage="upload.percent"
:color="progressColor"
></el-progress>
</div>
</template>
<script>
const SUCCESS = 200;
import moment from "moment";
import {excelUtils} from '../configuration/excelUtils'
export default {
name: "SlineImport",
//props: ["display"],
data(){
return{
progressColor: "#409eff",
//vslm_api: 'vslm_api',
vslm_api: 'http://localhost:9137',
fileTip: "",
upload: {
percent: 0,
show: false
},
percent:0,
fileList: [],
localeData: {
locale: this.$i18n.locale
},
data: {
percentage: 0,
source: {},//导出表的数据
target: {}//编排的数据
},
//数据格式化要求
format: {
source: {
validate: {
产线模板基础数据: {
prop: {
tempSerialNumber: "模板序列号",
tempId: "模板内部编号",
tempName: "模板名称",
vplId: "产线内部编号",
vplName: "产线名称",
},
requiredProp: ['tempSerialNumber', 'tempId', 'tempName','vplId','vplName'],
errorMsg: ['请填写模板序列号', '请填写模板内部编号', '请填写模板名称','请填写产线内部编号','请填写产线名称']
},
},
templateWscols: {
"产线模板基础数据": [{ wch: 20 }, { wch: 50 }, { wch: 25 }, { wch: 20 }, { wch: 20 }], //设置字段宽度
},
},
target: {
validate: {
流水线配置: {
field: 'streamLineBasicDataList',
prop: {
streamLineName: "流水线名称",
tempSerialNumber: "模板序列号",
streamLineArea: "流水线区域",
startTime: "开始时间",
endTime: "结束时间",
},
// requiredProp: ['streamLineName', 'tempSerialNumber', 'streamLineArea', 'startTime', 'endTime'],
// errorMsg: ['请填写流水线名称', '请填写模板序列号', '请填写流水线区域', '请填写开始时间', '请填写结束时间']
requiredProp: ['streamLineName', 'tempSerialNumber', 'streamLineArea', 'startTime'],
errorMsg: ['请填写流水线名称', '请填写模板序列号', '请填写流水线区域', '请填写开始时间']
},
},
slineWscols: {
"流水线配置": [{ wch: 30 }, { wch: 20 }, { wch: 25 }, { wch: 30 }, { wch: 30 }], //设置字段宽度
},
slineConflictWscols: {
"流水线配置": [{ wch: 45 }, { wch: 20 }, { wch: 25 }, { wch: 30 }, { wch: 30 }], //设置字段宽度
},
}
},
sourceTable: '流水线数据导入表.xlsx',
replacedHead: ['tempSerialNumber'],//待替换数据的Head 'tempSerialNumber','specId' 即两个excel相同序列号要替换的数据列
count: 0,//解析的记录数
finish: 0,//完成数
step: 2,//步长，多少条数据发送一次请求
waitFinish: 0,//待完成解析的数量
current: {//记录当前发送到后端的数据的位置
name: '',
index: 0
},
order: ['streamLineBasicDataList'],//记录发送数据的顺序
checkDate: {//记录当前发送到后端的数据的位置
sheets: ['流水线配置'],//需要处理时间格式的sheet
startTimeField: "startTime",
endTimeField: "endTime",
streamLineNameField: "streamLineName",
vplIdField: "vplId",
tempId: "tempId",
},
isPassTheFormatCheck: false,
tempSlineValidateSave: {}, //完整的excel信息，包含替换后的模板内部编号，产线信息
needReplaceSheet: ["流水线配置"],
conflictObj: {//后台校验返回的错误数据
notExistTempIdList: [],//不存在的模板
slineConflictList: [],//冲突的流水线数据
},
passCheckNeedSendData: {},
replacedOriginalData: {},
noNeedInsertFileName : '产线模板基础数据.xlsx'
}
},
created() {
},
methods: {
downloadVslmTemplate(){
this.$message.warning("想什么呢，还想下载下载流水线导入模板，没开发呢！！！")
},
//解析excel数据
async analyzeImportExcelData(){
let checkName = this.checkFileName();
if (!checkName) return;
//拿到所导入文件
let file1 = this.fileList[0].raw
let file2 = this.fileList[1].raw
let excelData1 = await excelUtils().getAllDataOfSheets(file1)
let excelData2 = await excelUtils().getAllDataOfSheets(file2)
if (excelData1.name == this.sourceTable) {
this.data.target = excelData1.data
this.data.source = excelData2.data
} else {
this.data.source = excelData1.data
this.data.target = excelData2.data
}
//console.log(this.data.target)
let tempExcelData = this.data.source;
let streamLineExcelData = this.data.target;
//验证Excel内sheet中是否有数据
let sourceExistData = this.validateSheetExistData(tempExcelData)
let targetExistData = this.validateSheetExistData(streamLineExcelData)
if (sourceExistData) {
this.$message.error("产线模板基础数据表中第" + sourceExistData + "个sheet无数据，请完整填写表格数据！")
this.fileList = [];
return;
}
if (targetExistData) {
this.$message.error("流水线数据导入表中第" + targetExistData + "个sheet无数据，请完整填写表格数据！")
this.fileList = [];
return;
}
//验证Excel内的sheet名称是否与格式一致
if (!this.validateSheetName(this.format.source.validate, tempExcelData)) {
this.$message.error("产线模板基础数据表Excel内的sheet名与格式规定的不一致！")
this.fileList = [];
return;
}
if (!this.validateSheetName(this.format.target.validate, streamLineExcelData)) {
this.$message.error("流水线数据导入表Excel内的sheet名与格式规定的不一致！")
this.fileList = [];
return;
}
//将excel中文列数据转成DTO
let sourceData = excelUtils().transferPropertiesName(tempExcelData, this.format.source.validate);
let targetData = excelUtils().transferPropertiesName(streamLineExcelData, this.format.target.validate);
//验证是否存在必填数据未填
let sourceNoError = this.validateRequiredFieldsData(sourceData, this.format.source.validate)
let targetNoError = this.validateRequiredFieldsData(targetData, this.format.target.validate)
//校验行数据
let dateFormatNoError = this.validateExcelRow(targetData);
//后端校验模板数据
this.validateTemplateData(sourceData).then(tempErrorList => {
if (tempErrorList.length > 0) {
this.transExportErrorTempTable(sourceData, tempErrorList);
return;
}
if (sourceNoError && targetNoError && dateFormatNoError) {
//如果没有错误，替换表数据，以及验证产线模板表数据data
this.data.source = sourceData
this.transExportTableToMap()//将产线模板表数据转换为map数据结构，excel<sheetName,<serialNumber,row>>
let noError = this.validateReplaceColumnDataIsExists(targetData)
if (noError) {
//流水线名称重复，冲突问题检查后数据还回提前备份
let tExcelData = {};
Object.assign(tExcelData, targetData)
//将需要替换的列内容替换成数据库中对应的数据
let replace = this.replaceColumnData(targetData)
//资源配置冲突检查
let isConflict = this.checkExcelPuIsConflict(tExcelData);
if (isConflict) {
this.$message.error("上传的流水线数据表数据有错误，详情请看返回的错误excel表！")
excelUtils().exportExcel('error.xlsx', tExcelData, this.format.target.validate, this.format.target.slineWscols)
this.initExcelData()
this.fileList = [];
this.close()
return;
}
//接口参数对象
let serviceData = this.buildServiceData(replace, this.format.target.validate)
//excel所有sheet数据的总条数
this.setTableRecordToCount(serviceData)
//发送需要校验的数据
this.postRequest(serviceData,tExcelData)
} else {
this.$message.error("上传的流水线数据表数据有错误，详情请看返回的错误excel表！")
excelUtils().exportExcel('error_format.xlsx', targetData, this.format.target.validate, this.format.target.slineWscols)
this.initExcelData()
this.fileList = [];
this.close()
return;
}
} else {
//验证不通过，写出到前端
this.$message.error("上传的流水线数据表数据有错误，详情请看返回的错误excel表！")
excelUtils().exportExcel('error_format.xlsx', targetData, this.format.target.validate, this.format.target.slineWscols)
this.initExcelData()
this.fileList = [];
this.close()
return;
}
})
},
async postRequest(serviceData,tExcelData) {//分段发送数据给接口处理
let sendData;
this.conflictObj.notExistTempIdList = []
this.conflictObj.slineConflictList = []
//发送请求到后端校验，校验excel数据和数据库已存在的流水线是否资源冲突
while (this.finish != this.count) {
sendData = this.getRequesteSendPartData(serviceData)
//console.log(sendData)
//this.finish += this.waitFinish
var post_url = this.vslm_api + "/vslm/streamline/checkExcelImportData"
await this.$axios.post(post_url, sendData).then(response => {
if (response.data.code == SUCCESS) {
//let notExistTempIdList = response.data.data.notExistTempIdList
let slineConflictList = response.data.data.slineConflictList
if (slineConflictList){//流水线冲突
slineConflictList.forEach(vo=>{
this.conflictObj.slineConflictList.push(vo.streamLineName)
})
}
}
this.finish += this.waitFinish
}).catch(error => {
console.log(error)
})
}
if (this.conflictObj.slineConflictList.length>0) {
// console.log("冲突的流水线")
// console.log(this.conflictObj.slineConflictList)
this.transExportErrorSlineTable(tExcelData, this.conflictObj.slineConflictList);
return
}
if (this.conflictObj.slineConflictList.length ==0 && this.conflictObj.notExistTempIdList.length ==0){
//校验通过
//console.log("校验通过,可以上传了")
this.passCheckNeedSendData = serviceData
//this.replacedOriginalData = tExcelData
this.isPassTheFormatCheck = true;
}
},
uploadSectionFileFunc() {
//if (param.file.name === this.noNeedInsertFileName) return
this.current.name = ''
this.current.index = 0
this.finish = 0
this.waitFinish = 0
this.setTableRecordToCount(this.passCheckNeedSendData)
this.postBatchInsertRequest(this.passCheckNeedSendData)
},
async postBatchInsertRequest(serviceData) {//分段发送数据给接口处理
if (!this.isPassTheFormatCheck) return
let sendData;
let successCount = 0 //成功的数量
while (this.finish != this.count) {
sendData = this.getRequesteSendPartData(serviceData)
/*console.log("插入数据")
console.log(sendData)
this.finish += this.waitFinish
successCount += 2*/
let post_url = this.vslm_api + "/vslm/streamline/excelBatchImportSlineData"
await this.$axios.post(post_url, sendData).then(response => {
if (response.data.code == SUCCESS) {
//console.log(response.data.data)
successCount += response.data.data
}
this.finish += this.waitFinish
}).catch(error => {
console.log(error)
})
this.upload.percent = 100 * (this.finish/this.count)
}
//console.log("总数:" + successCount)
let resultTip = "excel总数量:" + this.count +",成功插入条数:" + successCount + ",失败条数:" + (this.count - successCount)
let tipColor = 'success'
if ((this.count - successCount >0) && (this.count - successCount < this.count)){
tipColor = 'warning'
}else if (this.count - successCount == this.count){
tipColor = 'error'
}
this.$message({
showClose: true,
duration: 5000,
message: resultTip,
type: tipColor
});
this.initExcelData();
//this.close();
},
getRequesteSendPartData(serviceData) {
if (this.current.name == '') {//当前发送到后端的数据的位置
this.current.name = 'streamLineBasicDataList'
}
let length = serviceData[this.current.name].length //一个sheet中的数据量
let index = this.current.index
if (length - index == 0) {
let indexInOrder = this.order.indexOf(this.current.name) //sheet下标
if (indexInOrder != this.order.length - 1) {//非最后一个sheet
this.current.name = this.order[indexInOrder + 1]
this.current.index = 0
index = 0
length = serviceData[this.current.name].length
} else {
return null
}
}
//部分数据的处理
let sendData = {}
if (length - index >= this.step) {
//将array切片
//sendData[this.current.name] = serviceData[this.current.name].slice(index, (index + this.step))
sendData = serviceData[this.current.name].slice(index, (index + this.step))
this.waitFinish = this.step
this.current.index = (index + this.step)
} else {
//sendData[this.current.name] = serviceData[this.current.name].slice(index, length)
sendData = serviceData[this.current.name].slice(index, length)
this.waitFinish = (length - index)
this.current.index = length
}
return sendData
},
setTableRecordToCount(serviceData) {
let count = 0;
for (let key in serviceData) {
count += serviceData[key].length
}
this.count = count
},
buildServiceData(data, validate) {//构建发送到后端的数据结构
let tmpData = {};
for (let sheetName in validate) {
if (typeof data[sheetName] != "undefined") {
if (data[sheetName].length != 0) {
tmpData[validate[sheetName].field] = data[sheetName]
}
}
}
return tmpData
},
replaceColumnData(data) {//解析Excel中需要替换的数据，进行替换(模板、产线id)
for (let sheet in data) {
if (this.needReplaceSheet.indexOf(sheet) > -1){
let tempData = [];
let vplTempId = '';
for (let rowIndex in data[sheet]) {
let row = data[sheet][rowIndex]
for (let columnName in row) {
if (this.replacedHead.indexOf(columnName) > -1) {
let serialNumber = row[columnName]
//如果在导出表中找到了序列号，就替换掉
let sheetMap = this.data.source;
let tempId = sheetMap.get(parseInt(serialNumber))["tempId"]
vplTempId = sheetMap.get(parseInt(serialNumber))[this.checkDate.vplIdField]
data[sheet][rowIndex][columnName] = tempId
}
}
//保存产线数据
let tempRow ={};
Object.assign(tempRow, row)
tempRow.vplId = vplTempId
tempData.push(tempRow)
}
this.tempSlineValidateSave[sheet] = tempData
}
}
return data
},
initExcelData(){
/*this.upload.percent = 0;
this.upload.show =false;*/
this.data.source = {}
this.data.target = {}
this.isPassTheFormatCheck = false
this.tempSlineValidateSave = {}
this.conflictObj.notExistTempIdList = {}
this.conflictObj.slineConflictList = {}
this.current.name = ''
this.current.index = 0
this.count = 0
this.finish = 0
this.waitFinish = 0
},
getRequiredPron(sheetName, sheetFormatArr) {//获取必填属性有哪些
// console.log(sheetFormatArr)
for (let key in sheetFormatArr) {
// console.log(key)
let sheetFormat = sheetFormatArr[key]
// console.log(sheetFormat)
if (key == sheetName) {
return sheetFormat.requiredProp
}
}
},
getRequiredPronErrorMsg(sheetName, sheetFormatArr) {//获取必填属性缺失时，需要提醒的错误信息
for (let key in sheetFormatArr) {
let sheetFormat = sheetFormatArr[key]
if (key == sheetName) {
return sheetFormat.errorMsg
}
}
},
convertExcelDateFormat: function (row, columnName) {//日期转换
var date = row[columnName]
if (date === undefined || date === null || date === "") {
return null;
}
//非时间格式问题 返回Invalid date
let retFormat = moment(date).format('YYYY-MM-DD');
if (retFormat === "Invalid date"){
return retFormat;
}
return moment(date).add(1, 'days').format('YYYY-MM-DD')
},
checkExcelPuIsConflict(tExcelData){//检查excel中资源配置是否冲突
// console.log("原数据")
// console.log(tExcelData)
let errorCount = 0
var tempData =this.tempSlineValidateSave ;
// console.log("包含产线信息的对象")
// console.log(tempData)
for (let sheet in tempData) {
if (this.checkDate.sheets.indexOf(sheet) > -1){
for (let rowIndex in tempData[sheet]) {
let row = tempData[sheet][rowIndex] ;
let checkRow ={};
Object.assign(checkRow, row)
//行资源冲突
let isConflict = this.computeVplPuIsConflict(checkRow);
// console.log("返回的数据")
// console.log(checkRow)
if (isConflict){
errorCount++
//tempData[sheet][rowIndex][columnName] = row[columnName] + '名称重复'
tExcelData[sheet][rowIndex][this.checkDate.startTimeField] = checkRow[this.checkDate.startTimeField]
tExcelData[sheet][rowIndex][this.checkDate.endTimeField] = checkRow[this.checkDate.endTimeField]
}
continue;
}
}
}
if (errorCount > 0) {
return true
}
return false
},
computeVplPuIsConflict(checkRow){
let slineName = checkRow[this.checkDate.streamLineNameField]
let vplId = checkRow[this.checkDate.vplIdField]
var tempData =this.tempSlineValidateSave ;
// console.log("检查的行数据")
// console.log(checkRow)
for (let sheet in tempData) {
if (this.checkDate.sheets.indexOf(sheet) > -1){
for (let rowIndex in tempData[sheet]) {
let row = tempData[sheet][rowIndex] ;
//过滤同一条数据
if (slineName === row[this.checkDate.streamLineNameField]){
continue;
}
for (let columnName in row) {
let isvplIdField = this.checkDate.vplIdField === columnName;
if (!isvplIdField){
continue;
}else{
if (row[columnName] === vplId){
// console.log("同产线下的另一条数据")
// console.log(row)
//同一产线下的流水线检查是否存在资源冲突 sline1的结束时间 < sline2开始时间
let checkStartTime = checkRow[this.checkDate.startTimeField]
let checkEndTime = checkRow[this.checkDate.endTimeField]
let otherStartTime = row[this.checkDate.startTimeField]
let otherEndTime = row[this.checkDate.endTimeField]
//流水线结束时间可为空判断
if(checkEndTime == null || checkEndTime ===""){
if (moment(checkStartTime).diff(otherStartTime) <= 0){
checkRow[this.checkDate.startTimeField] = "同产线下，存在一条永久流水线，导致资源冲突，名称为 " + row[this.checkDate.streamLineNameField]
return true;
}
if (moment(checkStartTime).diff(otherStartTime) > 0){
if (otherEndTime == null || otherEndTime ===""){
checkRow[this.checkDate.startTimeField] = "同产线下，存在一条永久流水线，导致资源冲突，名称为 " + row[this.checkDate.streamLineNameField]
return true;
}
}
}
if (moment(checkStartTime).diff(otherStartTime) == 0 || moment(checkStartTime).diff(otherEndTime) == 0 ){
checkRow[this.checkDate.startTimeField] = "该流水线的开始时间和名称为 " + row[this.checkDate.streamLineNameField] + " 的流水线资源冲突"
return true;
}
if (moment(checkEndTime).diff(otherStartTime) == 0 || moment(checkEndTime).diff(otherEndTime) == 0 ){
checkRow[this.checkDate.startTimeField] = "该流水线的结束时间和名称为 " + row[this.checkDate.streamLineNameField] + " 的流水线资源冲突"
return true;
}
if (moment(checkStartTime).diff(otherStartTime) > 0){
//这条流水线之前有其他的流水线,则这条流水线的开始时间需要大于其他的流水线的结束时间
if (moment(otherEndTime).diff(checkStartTime) >= 0){
checkRow[this.checkDate.startTimeField] = "该流水线的开始时间和名称为 " + row[this.checkDate.streamLineNameField] + " 的流水线资源冲突"
return true;
}
}
if (moment(checkStartTime).diff(otherStartTime) < 0){
//这条流水线之前有其他的流水线,则这条流水线的开始时间需要大于其他的流水线的结束时间
if (moment(checkEndTime).diff(otherStartTime) >= 0){
checkRow[this.checkDate.endTimeField] = "该流水线的结束时间和名称为 " + row[this.checkDate.streamLineNameField] + " 的流水线资源冲突"
return true;
}
}
}
}
}
}
}
}
return false;
},
validateSlineNameConflict(data,slineName){//校验流水线名称是否重复，同一产线下的流水线冲突问题
let nameCount = 0
for (let sheet in data) {
if (this.checkDate.sheets.indexOf(sheet) > -1){
for (let rowIndex in data[sheet]) {
let row = data[sheet][rowIndex] ;
/*let dateFormatStart ;
let dateFormatEnd ;*/
for (let columnName in row) {
let isLineName = this.checkDate.streamLineNameField === columnName ? true:false;
if (!isLineName){
continue;
}else{
if (slineName === row[columnName]){
nameCount ++;
}
}
}
}
}
}
if (nameCount > 1) {
return true
}
return false
},
validateExcelRow(data){//校验行数据，如日期格式,资源冲突，流水线重名等
let errorCount = 0
for (let sheet in data) {
if (this.checkDate.sheets.indexOf(sheet) > -1){
for (let rowIndex in data[sheet]) {
let row = data[sheet][rowIndex] ;
let dateFormatStart ;
let dateFormatEnd ;
//行资源冲突
for (let columnName in row) {
let isLineName = this.checkDate.streamLineNameField === columnName;
let isStartTime = this.checkDate.startTimeField === columnName;
let isEndTime = this.checkDate.endTimeField === columnName;
//let columnText = row[columnName]
if (isLineName){
//流水线名称检查
let isConflict = this.validateSlineNameConflict();
if (isConflict){
errorCount++
data[sheet][rowIndex][columnName] = row[columnName] + '名称重复'
}
continue;
}
if (!isStartTime && !isEndTime){
continue;
}else if (isStartTime){
dateFormatStart = this.convertExcelDateFormat(row,columnName);
if (dateFormatStart ==null || dateFormatStart ==="Invalid date"){
errorCount++
data[sheet][rowIndex][columnName] = '开始时间日期格式错误'
continue;
}
if (moment().startOf('day').diff(dateFormatStart)>0){
errorCount++
data[sheet][rowIndex][columnName] = '开始时间不能小于当天'
continue;
}
data[sheet][rowIndex][columnName] = dateFormatStart
}else if (isEndTime){
//moment().startOf('day')
dateFormatEnd = this.convertExcelDateFormat(row,columnName);
//console.log(dateFormatStr)
if (dateFormatEnd ==="Invalid date"){
//结束时间允许为空
errorCount++
data[sheet][rowIndex][columnName] = '结束时间日期格式错误'
continue;
}
data[sheet][rowIndex][columnName] = dateFormatEnd
if (dateFormatEnd != null){
//判断，结束时间需要大于开始时间
//let checkTime = moment(dateFormatStart).diff(dateFormatEnd) >0 ? true : false ;
if (moment(dateFormatStart).diff(dateFormatEnd) >0){
errorCount++
data[sheet][rowIndex][this.checkDate.startTimeField] = '开始时间不能大于结束时间'
continue;
}
}
}
}
}
}
}
if (errorCount > 0) {
return false
}
return true
},
validateReplaceColumnDataIsExists (data) {//验证需要替换的数据是否存在
// console.log(this.data.source)
let errorCount = 0
for (let sheet in data) {
for (let rowIndex in data[sheet]) {
let row = data[sheet][rowIndex]
// console.log("data行数据")
// console.log(row)
for (let columnName in row) {
// console.log(columnName)
if (this.replacedHead.indexOf(columnName) > -1) { //<<<< 序列号替换逻辑
let serialNumber = row[columnName]
let sheetMap = this.data.source //excel<serialNumber,row>
if (!sheetMap) {
errorCount++
data[sheet][rowIndex][columnName] = '没有找到对应的sheet'
continue
}
// console.log("模板key的row检查")
// console.log(sheetMap.get(parseInt(serialNumber)))
if (!sheetMap.get(parseInt(serialNumber))) { //没有这个key
errorCount++
data[sheet][rowIndex][columnName] =data[sheet][rowIndex][columnName] + ' 产线基础数据不存在该序列号'
continue
}
// console.log("模板key的row的内容检查")
// console.log(sheetMap.get(parseInt(serialNumber))[columnName])
if (!sheetMap.get(parseInt(serialNumber))[columnName]) { //这个key的value值为空
errorCount++
data[sheet][rowIndex][columnName] = `没有找到对应的${columnName}`
continue
}
break;
}
}
}
}
if (errorCount > 0) {
return false
}
return true
},
transExportErrorTempTable(source,tempErrorList){//导出错误产线模板
for (let sheetName in source) {
for (let rowIndex in source[sheetName]) {
let row = source[sheetName][rowIndex]
for(let tempIndex in tempErrorList){
if(row[this.checkDate.tempId] === tempErrorList[tempIndex]){
row[this.checkDate.tempId] = row[this.checkDate.tempId] + " 产线中不存在该模板内部编号"
}
}
}
}
this.$message.error("上传的产线模板基础数据有错误，详情请看返回的错误excel表！")
excelUtils().exportExcel('errorTemplate.xlsx', source, this.format.source.validate,this.format.source.templateWscols)
this.initExcelData()
this.fileList = [];
this.close()
return;
},
transExportErrorSlineTable(tExcelData,slineConflictList){//导出冲突流水线
for (let slineIndex in slineConflictList){
let slineItemName = slineConflictList[slineIndex]
for (let sheet in tExcelData) {
if (this.checkDate.sheets.indexOf(sheet) > -1) {
for (let rowIndex in tExcelData[sheet]) {
let row = tExcelData[sheet][rowIndex];
if (row[this.checkDate.streamLineNameField] === slineItemName) {
row[this.checkDate.streamLineNameField] = row[this.checkDate.streamLineNameField] + " 和已存在的流水线资源冲突"
break
}
}
}
}
}
this.$message.error("上传的流水线数据导入表数据与已存在的流水线时间段资源冲突，详情请看返回的错误excel表！")
excelUtils().exportExcel('errorConflict.xlsx', tExcelData, this.format.target.validate,this.format.target.slineConflictWscols)
this.initExcelData()
this.fileList = [];
this.close()
return;
},
validateTemplateData(source){
let tempIdList = []
let tempErrorList = []
for (let sheetName in source) {
for (let rowIndex in source[sheetName]) {
let row = source[sheetName][rowIndex]
tempIdList.push(row[this.checkDate.tempId])
}
}
//后端检验
/* let url = `${this.vslm_api}/vslm/streamline/validateTemplates`
this.$axios.post(url, tempIdList).then(response => {
if (SUCCESS == res.data.code && response.data.data) {
tempErrorList.push(response.data.data)
return tempErrorList
} else {
return null
}
})*/
let url = `${this.vslm_api}/vslm/streamline/validateTemplates`
return new Promise((resolve, reject) => {
this.$axios.post(url, tempIdList)
.then(response => {
if (SUCCESS == response.data.code && response.data.data) {
resolve(response.data.data);
} else {
resolve(null);
}
})
.catch(err => {
reject(err);
})
});
},
transExportTableToMap() {//转换导出表的数据结构
let source = this.data.source
//let excel = {}
let excelMap = {}
for (let sheetName in source) {
var map = new Map();
for (let rowIndex in source[sheetName]) {
let row = source[sheetName][rowIndex]
map.set(row.tempSerialNumber, row)
}
excelMap = map
}
//this.data.source = excel
this.data.source = excelMap
},
validateRequiredFieldsData(data, formatSheet) {//验证表格数据是否填写（这里没有验证数据格式）
let errorCount = 0
for (let sheetName in data) {
//获取转换的列数组
let requiredHeadArray = this.getRequiredPron(sheetName, formatSheet)
//获取错误信息数组
let requiredPronErrorMsg = this.getRequiredPronErrorMsg(sheetName, formatSheet)
for (let rowIndex in data[sheetName]) {
let row = data[sheetName][rowIndex]
for (let headIndex in requiredHeadArray) {
if (!row[requiredHeadArray[headIndex]]) {
row[requiredHeadArray[headIndex]] = requiredPronErrorMsg[headIndex]
errorCount++
}
}
}
}
if (errorCount > 0) {
return false
}
return true
},
validateSheetName(format, source) {//验证Excel内的sheet名是否与格式一致
for (let key in format) {
var flag = false
for (let sheetName in source) {
if (key == sheetName) {
flag = true
break
}
}
if (!flag) {
//this.$message.error("Excel内的sheet名与格式规定的不一致！")
return false
}
}
return true
},
validateSheetExistData(data) {//验证Excel内sheet中是否有数据,返回无数据的sheet下标
let sheetIndex = 0
for (let key in data) {
sheetIndex +=1
if (typeof data[key] != 'undefined') {
if (data[key].length == 0) {
//return false;
return sheetIndex;
}
}
}
return null;
},
checkFileName(){
let excelNameList = ["产线模板基础数据.xlsx","流水线数据导入表.xlsx"];
for (let i = 0; i < this.fileList.length; i++) {
if (excelNameList.indexOf(this.fileList[i].name) <= -1){
this.fileList = [];
this.fileTip = "上传文件错误，请上传'产线模板基础数据表'和'流水线数据导入表'"
return false;
}
}
return true;
},
//文件上传,文件变更 回调
handleOnchange(file, fileList) {
this.initExcelData()
if (file.status === 'ready') {
// this.fileList = [];
this.fileList.push(file);
} else if (file.status === 'fail') {
// this.fileList = [];
this.percent = 0;
this.upload.show = false;
this.$message.error("Error")
} else if (file.status === 'success') {
// this.fileList = [];
this.$message.success("Success")
}
if (this.fileList.length == 2){
this.analyzeImportExcelData()
}
},
beforeUpload(file) {
this.fileTip = "";
//判断文件格式
const typeList = ["xls", "xlsx"];
const fileName = file.name;
const extension = fileName.substr(fileName.lastIndexOf(".") + 1);
const isExcel = typeList.includes(extension);
if (!isExcel) {
this.$message.warning("请上传excel文件");
}
this.upload.show = false;
return isExcel;
},
submitUpload() {
//console.log(this.$refs['upload'])
if (this.fileList.length === 0) {
this.$message.error("请上传excel文件");
return;
}
if (this.fileList.length !== 2) {
this.$message.error("文件数量限制，只能是两个文件");
return;
}
if (!this.isPassTheFormatCheck){
this.$message.error("数据校验未通过，请检查后再尝试");
return;
}
//判断校验是否通过
this.upload.show = true;
//this.$refs['upload'].submit();
this.uploadSectionFileFunc()
},
handleRemove(file, fileList) {
this.fileList = [];
},
handleSuccess() {
},
handleProgress(event, file, fileList) {
this.upload.percent = event.percent;
},
close() {
this.fileTip = "";
this.fileList = [];
this.upload.percent = 0;
this.upload.show =false;
//this.$emit('close');
},
},
}
</script>
<style scope>
.sline_excel_upload {
width: 50%;
height: 30%;
}
.el-upload.el-upload--text {
width: auto;
height: auto;
border: none;
overflow: inherit;
}
</style>
前往低代码交流专区
更多推荐
6
0- 0
已为社区贡献11条内容
所有评论(0)