excel映射

<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">&lt;</el-button>
          <el-button size="mini" type="primary" @click="mapAllClick">>></el-button>
          <el-button size="mini" type="primary" @click="mapAllRemoveClick">&lt;&lt;</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>