<template>
<div>
<el-steps :active="active" simple>
<el-step v-for="(item,index) in stepList" :key="index" :title="item.title" :icon="item.icon" @click.native="stepClick(index)"></el-step>
</el-steps>
<!-- 步骤一 -->
<header v-if="active===0">
<a :href="href">模板下载</a>
<div class="upload-box">
<el-upload class="upload-demo" drag :action="action" :headers="headers" :file-list="fileList" :show-file-list="false" :on-change="handleChange" :on-success="handleSuccess" :multiple="false">
<i class="el-icon-upload"></i>
<div class="el-upload__text">将Excel文件拖到此处,或<em>点击上传</em></div>
</el-upload>
</div>
</header>
<!-- 步骤二 -->
<div class="button-tip" v-if="active===1">
<label>映射方式:</label>
<el-radio-group v-model="stepValue" size="small" @change="selectMappedWay">
<el-radio-button v-for="(item,index) in ['原始模板导入','自定义列映射','现有列映射模板']" :key="index" :label="item"></el-radio-button>
</el-radio-group>
</div>
<!-- 步骤三 -->
<div v-if="active===2">
<div style="margin:20px 0">
<el-button type="primary" size="mini" @click="importExcelToSql">导入Excel表到数据库</el-button>
<el-button type="primary" size="mini" @click="openDialog" v-if="stepValue==='自定义列映射'">保存模板</el-button>
</div>
<!-- 获取的Excel模板列 -->
<div class="table-box clearfix" v-if="stepValue==='自定义列映射' || stepValue==='现有列映射模板'">
<el-table height="500" v-if="stepValue==='自定义列映射'" class="fl" :data="templateColumnArray" border highlight-current-row @current-change="templateColumnHandleCurrentChange">
<el-table-column prop="columnName" label="Excel模板列"></el-table-column>
</el-table>
<!-- 导入的Excel列 -->
<el-table height="500" v-if="stepValue==='自定义列映射'" class="fl" :data="importColumnArray" border highlight-current-row @current-change="importColumnHandleCurrentChange">
<el-table-column prop="name" label="导入的Excel列"></el-table-column>
</el-table>
<!-- 操作按钮 -->
<div v-if="stepValue==='自定义列映射'" class="fl edit-btn">
<el-button size="mini" type="primary" @click="mapClick">></el-button>
<el-button size="mini" type="primary" @click="mapRemoveClick"><</el-button>
<el-button size="mini" type="primary" @click="mapAllClick">>></el-button>
<el-button size="mini" type="primary" @click="mapAllRemoveClick"><<</el-button>
</div>
<!-- 映射模板 -->
<el-table height="500" class="fl" :data="mappedColumnArray" border highlight-current-row @current-change="mappedColumnHandleCurrentChange">
<el-table-column label="映射的列" v-if="stepValue==='自定义列映射'">
<template slot-scope="scope" v-if="scope.row.templateColumn">{{scope.row.templateColumn.columnName+" - "+scope.row.importColumn.name}}</template>
</el-table-column>
<el-table-column label="映射的列" v-if="stepValue==='现有列映射模板'">
<template slot-scope="scope">{{scope.row.columnName+" - "+scope.row.excelColumnName}}</template>
</el-table-column>
</el-table>
<!-- 映射模板列表 -->
<el-table height="500" v-if="stepValue==='现有列映射模板'" class="fl" :data="templateData" border highlight-current-row @current-change="selectTemplateRow">
<el-table-column prop="templateName" label="模板名称"></el-table-column>
<el-table-column prop="createTimeStr" label="创建时间"></el-table-column>
<el-table-column label="操作" width="100">
<template slot-scope="scope">
<el-link :underline="false" type="danger" @click="deleteTemplateData(scope.row.id)">删除</el-link>
</template>
</el-table-column>
</el-table>
</div>
<!-- 导入的Excel表 -->
<el-table height="500" :data="importExcelData" border style="margin:20px 0">
<el-table-column show-overflow-tooltip v-for="(item,index) in importExcelColumnArray" :key="index" :prop="item.name" :label="item.name"></el-table-column>
</el-table>
</div>
<!-- 步骤四 -->
<div v-if="active===3">
<!--Excel导入数据-->
<el-table height="650" :data="excelImportList" highlight-current-row v-loading="listLoading" border>
<el-table-column show-overflow-tooltip type="index" label="序号" width="50"></el-table-column>
<el-table-column show-overflow-tooltip prop="country" label="国家" width="50"></el-table-column>
<el-table-column show-overflow-tooltip prop="state" label="州/省" min-width="100"></el-table-column>
<el-table-column show-overflow-tooltip prop="city" label="城市" min-width="120"></el-table-column>
<el-table-column show-overflow-tooltip prop="street" label="街道" width="120"></el-table-column>
<el-table-column show-overflow-tooltip prop="houseNumber" label="门牌号" width="100"></el-table-column>
<el-table-column show-overflow-tooltip prop="zipCode" label="邮编" width="100"></el-table-column>
<el-table-column show-overflow-tooltip prop="recipient" label="收件人" width="80"></el-table-column>
<el-table-column show-overflow-tooltip prop="phone" label="联系电话" width="100"></el-table-column>
<el-table-column show-overflow-tooltip prop="email" label="电子邮箱" width="100"></el-table-column>
<el-table-column show-overflow-tooltip prop="expressCompany" label="快递公司" width="100"></el-table-column>
<el-table-column show-overflow-tooltip prop="sku" label="SKU" width="100"></el-table-column>
<el-table-column show-overflow-tooltip prop="trackNo" label="跟踪号" width="100"></el-table-column>
<el-table-column show-overflow-tooltip prop="referenceNo" label="参考号" width="100"></el-table-column>
<el-table-column show-overflow-tooltip prop="serviceType" label="服务类型" width="100"></el-table-column>
<el-table-column show-overflow-tooltip prop="deliveryMethod" label="派送方式" min-width="180"></el-table-column>
<el-table-column show-overflow-tooltip prop="storageMode" label="仓储模式" width="100"></el-table-column>
<el-table-column show-overflow-tooltip prop="quantity" label="数量" width="60"></el-table-column>
<el-table-column show-overflow-tooltip prop="remark" label="说明" width="100"></el-table-column>
</el-table>
<!--工具条-->
<Pagination v-show="total>0" :total="total" :page.sync="listQuery.page" :limit.sync="listQuery.intPageSize" @pagination="getExcelImportData" />
</div>
<!-- 保存模板弹窗 -->
<el-dialog title="模板名称" :visible.sync="dialogFormVisible" width="25%">
<el-form :model="form" ref="form" :rules="rules">
<el-form-item label="模板名称" label-width="100px" prop="name">
<el-input v-model="form.name" autocomplete="off"></el-input>
</el-form-item>
</el-form>
<div slot="footer" class="dialog-footer">
<el-button @click="dialogFormVisible = false,form.name=''">取 消</el-button>
<el-button type="primary" @click="saveTemplate">确 定</el-button>
</div>
</el-dialog>
</div>
</template>
<script>
import { getOriginalExcelTemplate, importExcelToSql,createTemplate,getTemplateList,inIdGetTemplate,getExcelImportData,deleteTemplate } from "@/api/importExcel"
import { base } from '@/utils/config';
import Pagination from '@/components/Pagination'
export default {
components:{Pagination},
data() {
return {
excelImportList:[],
listLoading:false,
listQuery:{
key: "",
page:1,
intPageSize:20
},
total:0,
templateColumnArray: [],
excelData: [],
addFormVisible: false,
token: "",
fileName: "",
fileList: [],//上传的文件列表, 例如: [{name: 'food.jpg', url: 'https://xxx.cdn.com/xxx.jpg'}]
fileId: 0,
file:null,
action: base + "/api/ImportExcel/ReadExcel",
headers: { Authorization: 'Bearer ' + localStorage.getItem("Token") },
href: base + "/excel/%E6%A8%A1%E6%9D%BF.xlsx",
importExcelData: [],
importExcelColumnArray: [],
importColumnArray: [],
mappedColumnArray: [],
templateColumnCurrentRow: null,
importColumnCurrentRow: null,
mappedColumnCurrentRow: null,
active: 0,
stepValue:"",
form:{
name:""
},
rules:{
name:{required: true, message: '请填写模板名称', trigger: 'blur'}
},
dialogFormVisible:false,
templateData:[],
stepList:[
{title:"上传Excel",icon:"el-icon-upload"},
{title:"选择映射方式",icon:"el-icon-edit"},
{title:"映射",icon:"el-icon-document-copy"},
{title:"查看导入的数据",icon:"el-icon-view"}
]
}
},
created() {
this.$nextTick(() => {
})
},
mounted() {
// this.importColumnArray = JSON.parse(localStorage.getItem("importColumnArray"))
// this.importExcelData = JSON.parse(localStorage.getItem("importExcelData"))
},
methods: {
//删除映射模板
deleteTemplateData(id){
let data={id}
deleteTemplate(data).then(res=>{
if(res.success){
this.hintMessage("success","删除成功!")
}
this.getTemplateList()
})
},
//选择步骤条
stepClick (val) {
if(val===1){
if(this.fileList.length<1){
this.hintMessage("error","请在第一步上传Excel文件!")
return false
}
}else if(val===2){
if(this.stepValue===""){
this.hintMessage("error","请在第二步选择映射方式!")
return false
}else{
this.selectMappedWay(this.stepValue)
}
}else if(val===3){
if(this.listQuery.key===""){
this.hintMessage("error","请在第三步导入Excel表到数据库!")
return false
}else{
this.getExcelImportData()
}
}
this.active = val
},
//提示消息
hintMessage(type,message){
return this.$message({type,message})
},
//一键映射
mapAllClick(){
for(let i = 0; i < this.templateColumnArray.length; i++){
var templateColumn= this.templateColumnArray[i];
for(let j = 0; j < this.importColumnArray.length; j++){
var importColumn= this.importColumnArray[j];
if(templateColumn.columnName==importColumn.name){
i--
j--
var mappedColumn = {
templateColumn: templateColumn,
importColumn: importColumn,
}
//添加映射
this.addMappedColumn(mappedColumn);
//删除模板
this.removeTemplateColumn(templateColumn);
//删除导入的表
this.removeImportColumn(importColumn);
}
}
}
},
//一键删除映射
mapAllRemoveClick(){
this.$confirm('确定要删除所有映射表数据吗?', '提示', {
confirmButtonText: '确定',
cancelButtonText: '取消',
type: 'warning'
}).then(() => {
for(let i = 0; i < this.mappedColumnArray.length; i++){
let item=this.mappedColumnArray[i]
//添加模板
this.addTemplateColumn(item.templateColumn);
//添加导入的表
this.addImportColumn(item.importColumn);
//删除映射的表
this.removeMappedColumn(item);
i--
}
this.hintMessage("success","删除成功!")
})
},
//获取模板
getExcelTemplate() {
getOriginalExcelTemplate().then(res => {
if (res.success) {
this.templateColumnArray = res.response.excelTemplateColumns
}
})
},
//上传文件成功
handleSuccess(result, file) {
this.importColumnArray = []
this.importExcelColumnArray = [];
this.importExcelData = []
this.excelData = result.response.excelColumns
this.fileId = result.response.fileId
this.excelData.forEach(v => {
this.importColumnArray.push({ name: v.name, orderSort: v.orderSort })
this.importExcelColumnArray.push({ name: v.name, orderSort: v.orderSort })
})
for (let i = 0; i < this.excelData[0].excelValues.length; i++) {
var excelDataRow = {};
for (let j = 0; j < this.excelData.length; j++) {
this.$set(excelDataRow, this.excelData[j].name, this.excelData[j].excelValues[i].value);
}
this.importExcelData.push(excelDataRow);
}
// localStorage.setItem("importColumnArray", JSON.stringify(this.importColumnArray))
// localStorage.setItem("importExcelData", JSON.stringify(this.importExcelData))
this.hintMessage("success","上传成功!")
this.active = 1
},
//上传文件更改
handleChange(file, fileList) {
this.file = file;
if (fileList.length > 0) {
this.fileList = [fileList[fileList.length - 1]]
}
},
//模板点击
templateColumnHandleCurrentChange(val) {
this.templateColumnCurrentRow = val;
},
//导入的表点击
importColumnHandleCurrentChange(val) {
this.importColumnCurrentRow = val;
},
//映射的表点击
mappedColumnHandleCurrentChange(val) {
this.mappedColumnCurrentRow = val;
},
//往右
mapClick() {
if (!this.templateColumnCurrentRow || !this.importColumnCurrentRow) {
this.hintMessage("warning","请选择要映射的列!")
return;
}
var mappedColumn = {
templateColumn: this.templateColumnCurrentRow,
importColumn: this.importColumnCurrentRow,
}
//添加映射
this.addMappedColumn(mappedColumn);
//删除模板
this.removeTemplateColumn(this.templateColumnCurrentRow);
//删除导入的表
this.removeImportColumn(this.importColumnCurrentRow);
},
//往左
mapRemoveClick() {
if (!this.mappedColumnCurrentRow) {
this.hintMessage("warning","请选择已映射的列!")
return;
}
//添加模板
this.addTemplateColumn(this.mappedColumnCurrentRow.templateColumn);
//添加导入的表
this.addImportColumn(this.mappedColumnCurrentRow.importColumn);
//删除映射的表
this.removeMappedColumn(this.mappedColumnCurrentRow);
},
//删除模板
removeTemplateColumn(templateColumn) {
for (let index = 0; index < this.templateColumnArray.length; index++) {
const element = this.templateColumnArray[index];
if (templateColumn.orderSort === element.orderSort) {
this.templateColumnArray.splice(index, 1);
}
}
},
//添加模板
addTemplateColumn(templateColumn) {
if (this.templateColumnArray.length <= 0) {
this.templateColumnArray.push(templateColumn);
return;
}
if (templateColumn.orderSort > this.templateColumnArray[this.templateColumnArray.length-1].orderSort) {
this.templateColumnArray.push(templateColumn);
return;
}
for (let index = 0; index < this.templateColumnArray.length; index++) {
const element = this.templateColumnArray[index];
if (templateColumn.orderSort < element.orderSort) {
this.templateColumnArray.splice(index, 0, templateColumn);
break;
}
}
},
//删除导入的表
removeImportColumn(importColumn) {
for (let index = 0; index < this.importColumnArray.length; index++) {
const element = this.importColumnArray[index];
if (importColumn.orderSort === element.orderSort) {
this.importColumnArray.splice(index, 1);
}
}
},
//添加导入的表
addImportColumn(importColumn) {
if (this.importColumnArray.length <= 0) {
this.importColumnArray.push(importColumn);
return;
}
if (importColumn.orderSort > this.importColumnArray[this.importColumnArray.length-1].orderSort) {
this.importColumnArray.push(importColumn)
return;
}
for (let index = 0; index < this.importColumnArray.length; index++) {
const element = this.importColumnArray[index];
if (importColumn.orderSort < element.orderSort) {
this.importColumnArray.splice(index, 0, importColumn);
break;
}
}
},
//删除映射
removeMappedColumn(mappedColumn) {
for (let index = 0; index < this.mappedColumnArray.length; index++) {
const element = this.mappedColumnArray[index];
if (mappedColumn.templateColumn.orderSort == element.templateColumn.orderSort) {
this.mappedColumnArray.splice(index, 1);
}
}
},
//添加映射
addMappedColumn(mappedColumn) {
//如果是第一次映射
if (this.mappedColumnArray.length <= 0) {
this.mappedColumnArray.push(mappedColumn);
return;
}
//如果映射的数据顺序比存在的顺序更后
if (mappedColumn.templateColumn.orderSort > this.mappedColumnArray[this.mappedColumnArray.length-1].templateColumn.orderSort) {
this.mappedColumnArray.push(mappedColumn)
return;
}
//如果映射的数据顺序比存在的顺序更前
for (let index = 0; index < this.mappedColumnArray.length; index++) {
const element = this.mappedColumnArray[index];
if (mappedColumn.templateColumn.orderSort < element.templateColumn.orderSort) {
this.mappedColumnArray.splice(index, 0, mappedColumn);
break;
}
}
},
//导入Excel表到数据库
importExcelToSql() {
let excelTemplateColumns= this.fillData()
if(excelTemplateColumns.length<1){
this.hintMessage("warning","请选择要导入的映射列!")
return false
}
let obj = {
excelTemplateColumns,
}
var fileReader = new FileReader();
fileReader.readAsDataURL(this.file.raw);
fileReader.onloadend = e => {
var formData = new FormData();
formData.append("file", e.target.result);
formData.append("json", JSON.stringify(obj));
importExcelToSql(formData).then(res => {
if (res.success) {
this.hintMessage("success","导入成功!")
this.listQuery.key=res.response
this.mappedColumnArray=[]
this.active=3
this.getExcelImportData()
}
})
};
},
//打开弹窗
openDialog(){
let excelTemplateColumns= this.fillData()
if(excelTemplateColumns.length<1){
this.hintMessage("error","映射列为空!")
}else{
this.dialogFormVisible=true
this.form.name=""
}
},
//保存模板
saveTemplate(){
this.$refs["form"].validate((valid) => {
if (valid) {
let excelTemplateColumns= this.fillData()
let obj={
templateName:this.form.name,
excelTemplateColumns,
}
createTemplate(obj).then(res=>{
if(res.success){
this.hintMessage("success","创建成功!")
this.dialogFormVisible=false
}
})
}
})
},
//填充数据
fillData(){
let excelTemplateColumns = []
this.mappedColumnArray.forEach((item, index) => {
if(item.templateColumn&&item.importColumn){
item.templateColumn.excelColumnName = item.importColumn.name
excelTemplateColumns.push(item.templateColumn)
}else{
excelTemplateColumns.push(item)
}
})
return excelTemplateColumns
},
//获取映射模板列表
getTemplateList(){
getTemplateList().then(res=>{
if(res.success){
this.templateData=res.response
this.mappedColumnArray=[]
}
})
},
//模板列表行选择
selectTemplateRow(row){
this.getTemplate(row.id)
},
//获取映射模板
getTemplate(id){
let data={id}
inIdGetTemplate(data).then(res=>{
if(res.success){
this.mappedColumnArray= res.response
}
})
},
//选择映射方式
selectMappedWay(val){
this.active=2
this.getExcelTemplate()
if(val==="原始模板导入"){
this.mappedColumnArray=this.templateColumnArray
}else if(val==="现有列映射模板"){
this.getTemplateList()
}
},
//查询Excel导入数据
getExcelImportData(){
this.listLoading=true
getExcelImportData(this.listQuery).then(res=>{
if(res.success){
this.listLoading=false
this.excelImportList=res.response.data
this.total=res.response.dataCount
}
})
}
}
}
</script>
<style lang="less" scoped>
header {
text-align: center;
a {
display: inline-block;
margin-bottom: 20px;
color: #409eff;
}
.upload-box {
display: flex;
align-items: center;
justify-content: center;
}
}
.button-tip{
margin: 50px 0;
text-align: center;
display: flex;
justify-content: center;
align-items: center;
}
.table-box {
display: flex;
.edit-btn{
margin: 0 30px;
display: flex;
flex-direction: column;
justify-content: center;
align-items: center;
>*{
width: 50px;
margin:0 0 30px 0;
&:last-child{
margin-bottom: 0;
}
}
}
}
/deep/.el-steps.el-steps--simple {
width: 50%;
margin: 0 auto 20px auto;
background-color: #cfe8fc;
}
.el-steps--simple {
cursor: pointer;
}
</style>
excel映射
最新推荐文章于 2024-08-28 09:41:55 发布