excel表格的上传和下载

excel表格的上传和下载

excel的表格用到了xlsx插件，

下载npm install xlsx.js

引入 import xlsx from ‘xlsx’

Utils.js写的公共方法

//把文件按照二进制进行读取
export function readFile(file) {
    return new Promise(resolve => {
        let reader=new FileReader()
        reader.readAsBinaryString(file)
        reader.onload= ev => {
            resolve(ev.target.result)
        }
    })
}

// 字段对应表
export let character = {
    Ticker: {
        text: 'Ticker',
        type: 'string'
    },
    Platform: {
        text: 'DataSource',
        type: 'string'
    }
}

 //上传的template模板

<el-dialog lock-scroll :close-on-click-modal="false" title="File Upload" :visible.sync="dialogVisible" width="30%">
      <div class="upload">
        <el-upload
          ref="upload"
          action
          class="upload-demo"
          :drag="true"
          multiple
          accept=".xlsx,.xls"
          :limit="limit"
         :auto-upload="false"
         :on-change="handle">
          <i class="el-icon-upload"></i>
          <div class="el-upload_text">将文件拖到此处，或<em>点击上传</em></div>  
        </el-upload>
        <div style="text-align:center;margin-top:10px">
        <el-button type="success" size="small" @click="submit" :disabled="disable" >提交excel表格</el-button>
        </div>
      </div>
    </el-dialog>

上传的方法

import { readFile,delay,character,deepCopy } from '@/utils/utils.js'

上传excel，转为数组对象格式，字段转为后端需要的字段

 //excel表格相关
   async handle(ev) {
    let file=ev.raw
     if(!file) return
     
    const loading = this.$loading({
            lock: true,
            text: 'Loading',
            spinner: 'el-icon-loading',
            background: 'rgba(0, 0, 0, 0.7)',
          })
    // await delay(300)

    // 读取file中的数据
    let data=await readFile(file) //二进制数据
    let workbook=xlsx.read(data, { type: 'binary' } ),
        worksheet=workbook.Sheets[workbook.SheetNames[0]]
        data=xlsx.utils.sheet_to_json(worksheet)
    

    //把读取出来的数据变为最后可以传递给服务器的数据
    let arr=[]
    data.forEach(item => {
    let obj={}
    for( let key in character) {
      if(!Object.prototype.hasOwnProperty.call(character,key)) break
    let v=character[key],
      text=v.text,
      type=v.type
      v=item[text] || '' 
      type === 'string' ? (v=String(v)) : null
      type === 'number' ? (v=Number(v)) : null
      obj[key]=v
    }
     arr.push(obj)
  })

    await delay(1000)
    this.ImporttableData=arr
     loading.close()
  },

提交

async submit() {
    if(this.ImporttableData.length<=0) {
      this.$message({
        message:'请您先选择excel文件!',
        showClose:true
      })
      return
    } 
    const loading = this.$loading({
            lock: true,
            text: 'Loading',
            spinner: 'el-icon-loading',
            background: 'rgba(0, 0, 0, 0.7)',
          })
      this.disable=true
    // 完成后处理的事情
    let complate=() => {
      this.$message({
        message:`已经帮您传完${this.IndexNumber}条数据了!`,
        type:'success',
        showClose:true
      })
    } 
     let errorMsg=() => {
      this.$message({
        message:`${this.errMsg}`,
        type:'error',
        showClose:true,
        duration:0
      })
    } 
      
    this.$basisApi.basisRate.ImportRows(this.ImporttableData).then( async(res) => {
       let { OperatorNumber,ResultMessage }=res.result
       let { status }=res
      try {
       if(status===200 || OperatorNumber>-1) {
         this.IndexNumber=OperatorNumber 
         complate()
        //  await delay(1000)
         loading.close()
         let that=this
         setTimeout(() => {
              that.getData()
         },1000)
       } else{
         this.errMsg=ResultMessage
        //  await delay(1000)
        loading.close()
         errorMsg()
       } 
     } catch (error) {
       this.$message(error)
     }
      // await delay(2000)
      this.$refs.upload.clearFiles()
      this.ImporttableData=[]
      this.disable=false
      this.dialogVisible=false
    })
  },

下载

//下载模板
 <el-button
        type=""
        icon="el-icon-download"
        size="small"
        style="font-size: 14px;margin-left:10px;"
        @click="handleExport"
        :disabled="disable"
      ></el-button>
      <el-button
        type=""
        icon="el-icon-upload"
        size="small"
        style="font-size: 14px;"
         @click="handleUploads"
      ></el-button>
      <el-button
        type=""
        icon="el-icon-download"
        size="small"
        style="font-size: 14px;"
         @click="handleExportAll"
         :disabled="disable"
        >All</el-button
      >
          
             
      //table表格
    <div class="tables">
          <el-table
            :data="tableData"
            style="width: 100%;color:#fff"
            border
            :default-sort = "{prop: 'Platform', order: 'descending'}"
            :header-cell-style="tableHeader"
            @selection-change="selectionchange"
          >
          <el-table-column fixed type="selection" width="55" align="center"></el-table-             column>
          <el-table-column
          sortable
          v-for="(column, index) in columns"
          :show-overflow-tooltip="true"
          :key="column.prop"
          :prop="dropColumns[index].prop"
          :label="column.label"
          :width="column.width"
          :align="column.align ? column.align : 'center'">
           </el-table-column>
            <el-table-column  fixed="right" label="Operation">
              <template slot-scope="scope">
                <el-button
                  size="mini"
                  type="text"
                  @click="handleEdit(scope.$index, scope.row)"
                  style="color:green"
                  >Modify</el-button
                >
                <el-button
                  size="mini"
                  type="text"
                  @click="handleDelete(scope.$index, scope.row)"
                   style="color:green"
                  >Delete</el-button
                > 
              </template>
            </el-table-column>
          </el-table>
        </div>

选中
selectionchange(val) {
      this.selecttionList=val
},

导出选中的

npm install xlsx.js插件
import xlsx from 'xlsx'

 //导出相关
    handleExport() {
      if(this.selecttionList.length<=0) {
        this.$message({
          message:'请您先选择要导出的数据哦！' ,
          type:'warning',
          showClose:true
        })
        return
      }

      this.disable=true
      const loading = this.$loading({
            lock: true,
            text: 'Loading',
            spinner: 'el-icon-loading',
            background: 'rgba(0, 0, 0, 0.7)',
          })

      let arr=this.selecttionList.map( item => {
        return {
          Ticker:item.Ticker,
          DataSource:item.Platform,
          Basis:item.Basis,
          Term:item.BasisFrequencyUnit,
          BasisFrequency:item.BasisFrequencyValue,
          CCY:item.CCY,
          Tag:item.Tag,
          RateName:item.RateName,
        }
      })

      let sheet=xlsx.utils.json_to_sheet(arr),
         book=xlsx.utils.book_new()
      xlsx.utils.book_append_sheet(book,sheet,'sheet1')
      xlsx.writeFile(book,`user${new Date().getTime()}.xlsx`)
      loading.close()
      this.disable=false
    },

导出全部的

handleExportAll() {
       if(this.resData.length<=0) {
        this.$message({
          message:'请您先选择要导出的数据哦！' ,
          type:'warning',
          showClose:true
        })
        return
      }

      this.disable=true
       const loading = this.$loading({
            lock: true,
            text: 'Loading',
            spinner: 'el-icon-loading',
            background: 'rgba(0, 0, 0, 0.7)',
          })

      let arr=this.resData.map( item => {
        return {
          Ticker:item.Ticker,
          DataSource:item.Platform,
          Basis:item.Basis,
          Term:item.BasisFrequencyUnit,
          BasisFrequency:item.BasisFrequencyValue,
          CCY:item.CCY,
          Tag:item.Tag,
          RateName:item.RateName,
        }
      })

      let sheet=xlsx.utils.json_to_sheet(arr),
         book=xlsx.utils.book_new()
      xlsx.utils.book_append_sheet(book,sheet,'sheet1')
      xlsx.writeFile(book,`exportall${new Date().getTime()}.xlsx`)
      loading.close()
      this.disable=false
    },