前端excl导入 导出

下载 xlsx 第三方包 npm i xlsx --save

<template>
  <div class="staff">
    <div class="header">
      <el-card>
        <el-button
          type="primary"
          icon="el-icon-plus"
          size="mini"
          class="add-staff"
          @click="toUPdownXml"
        >导出</el-button>
        <el-button
          type="primary"
          icon="el-icon-upload"
          size="mini"
          @click="toUplocationXml"
        >导入</el-button>
      </el-card>
      <input id="imFile" ref="input" type="file" @change="importFile">
    </div>
    <div class="centent">
      <el-card>
        <staffTable />
      </el-card>
    </div>
  </div>
</template>

<script>
import XLSX from 'xlsx'
import staffTable from './table'
export default {
  components: {
    staffTable
  },
  data() {
    return {
      exclData: []
    }
  },
  methods: {
    importFile() {
      const inputRef = this.$refs.input.files[0]
      this.toUplocationXml(inputRef)
    },

    // 导入excl文件
    toUplocationXml(file) {
      const that = this
      var reader = new FileReader()
      reader.onload = function(e) {
        var data = e.target.result
        // 读取二进制的excel
        var workbook = XLSX.read(data, { type: 'binary' })
        const json = XLSX.utils.sheet_to_json(
          workbook.Sheets[workbook.SheetNames[0]]
        )
        that.exclData = json
      }
      reader.readAsBinaryString(file)
    },
    toUPdownXml() {
      this.download(this.exclData, '菜单.xlsx')
    },
    download(json, fileName) {
      const type = 'xlsx' // 定义导出文件的格式
      var tmpDown // 导出的内容
      var tmpdata = json[0]
      json.unshift({})
      var keyMap = [] // 获取keys
      for (var k in tmpdata) {
        keyMap.push(k)
        json[0][k] = k
      }
      var tmpdata = [] // 用来保存转换好的json

      json
        .map((v, i) =>
          keyMap.map((k, j) =>
            Object.assign(
              {},
              {
                v: v[k],
                position:
                  (j > 25 ? this.getCharCol(j) : String.fromCharCode(65 + j)) +
                  (i + 1)
              }
            )
          )
        )
        .reduce((prev, next) => prev.concat(next))
        .forEach(
          (v, i) =>
            (tmpdata[v.position] = {
              v: v.v
            })
        )
      var outputPos = Object.keys(tmpdata) // 设置区域,比如表格从A1到D10
      var tmpWB = {
        SheetNames: ['mySheet'], // 保存的表标题
        Sheets: {
          mySheet: Object.assign(
            {},
            tmpdata, // 内容
            {
              '!ref': outputPos[0] + ':' + outputPos[outputPos.length - 1] // 设置填充区域
            }
          )
        }
      }
      tmpDown = new Blob(
        [
          this.s2ab(
            XLSX.write(
              tmpWB,
              {
                bookType: 'xlsx',
                bookSST: false,
                type: 'binary'
              } // 这里的数据是用来定义导出的格式类型
            )
          )
        ],
        {
          type: ''
        }
      ) // 创建二进制对象写入转换好的字节流
      this.saveAs(tmpDown, fileName)
    },
    saveAs(obj, fileName) {
      // 导出功能实现
      var tmpa = document.createElement('a')
      tmpa.download = fileName || '下载'
      tmpa.href = URL.createObjectURL(obj) // 绑定a标签
      tmpa.click() // 模拟点击实现下载
      setTimeout(function() {
        // 延时释放
        URL.revokeObjectURL(obj) // 用URL.revokeObjectURL()来释放这个object URL
      }, 100)
    },
    s2ab(s) {
      // 字符串转字符流
      var buf = new ArrayBuffer(s.length)
      var view = new Uint8Array(buf)
      for (var i = 0; i !== s.length; ++i) view[i] = s.charCodeAt(i) & 0xff
      return buf
    },
    getCharCol(n) {
      const temCol = ''
      let s = ''
      let m = 0
      while (n > 0) {
        m = (n % 26) + 1
        s = String.fromCharCode(m + 64) + s
        n = (n - m) / 26
      }
      return s
    }
  }
}
</script>

<style scoped lang="scss">
.staff {
  height: 100%;
  padding: 20px;
  .header {
    margin-bottom: 20px;
    ::v-deep .el-card__body {
      display: flex;
      flex-direction: row-reverse;
    }
  }

  .add-staff {
    margin-left: 20px;
  }
}
</style>