使用 vue 实现 导excel 表格功能

vue项目里使用导入导出表格功能 记录自己的学习

演示用的页面是从网上找来的 由于个人觉得这位网友写的并不清晰 于是改写并记录
使用element-ui

效果图

这里演示了导入表格 ，导出表格时浏览器会下载文件

引入 npm 包

yarn add file-saver
yarn add xlsx
yarn add script-loader --dev

引入两个 js 文件

从该网友从下载
放在合适的 目录下
fileReader相关文档

演示用的 vue单文件

<template>
  <div id="js003">
    <div class="main-content">
      <div style="display: flex; width: 340px; justify-content: space-between;">
        <el-button type="primary" plain size="small" @click="doDown">下载 Excel</el-button>
        <el-upload
          action=""
          :on-change="uploadFile"
          :file-list="fileListUpload"
          :show-file-list="false"
          accept=".xls,.xlsx"
          :auto-upload="false">
          <el-button :loading="loading" size="small" type="primary">上传 Excel</el-button>
        </el-upload>
        <el-button  type="success" plain :loading="loading" size="small"  @click="exportExcel">导出 Excel</el-button>
      </div>
      <el-table
        :data="list"
        stripe
        style="margin-top: 10px; width: 500px;"
        border>
        <el-table-column
          v-for="(item,index) in tableColumns"
          :key="index"
          :prop="item.prop"
          :label="item.label"
          align="center">
        </el-table-column>
        
      </el-table>
    </div>
  </div>
</template>

<script>
import XLSX from 'xlsx'
export default {
  data() {
    return {
      list: [],
      tableColumns:[
        {prop:'name',label:'姓名'},
        {prop:'age',label:'年龄'},
        {prop:'sex',label:'性别'}
      ],
      fileListUpload: [],
      fileTemp: [],
      loading: false,
    }
  },

  methods: {
    doDown(){
      let url = 'https://raw.githubusercontent.com/xrkffgg/Kvue/master/src/assets/excel/demo.xlsx'
      window.location.href = url
    },

    /**
     * 上传文件
     * @param {Object} file 
     * @param {Array} fileList
     */
    async uploadFile(file,fileList){
      if ((file.raw.type == 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') || (file.raw.type == 'application/vnd.ms-excel')) {
        this.loading = true
        try {
          let list = await this.importExcel(file.raw)
          this.list = this.excel2Table(list)
        } catch (error) {
          console.log(error)
        }
        this.loading = false
      } else {
        this.$message({
          type: 'warning',
          message: '附件格式错误，请删除后重新上传！'
        })
      }
    },
    /**
     * 处理上传excel文件 数据 转数组
     * @param {Object} raw
     * @return: {Array}
     */
    importExcel (raw) {
      return new Promise((resolve,reject)=>{
        let reader = new FileReader()
        reader.onloadend = function(){
          let binary = ''
          let bytes = new Uint8Array(this.result)
          let length = bytes.byteLength
          for (var i = 0; i < length; i++) {
            binary += String.fromCharCode(bytes[i])
          }
          // let XLSX = require('xlsx')
          let data = XLSX.read(binary, {
            type: 'binary'
          })
          resolve(XLSX.utils.sheet_to_json(data.Sheets[data.SheetNames[0]]))
        }
        reader.readAsArrayBuffer(raw)
      })
    },
    /**
     * 导入表格数据 转 table 显示
     * @param {Array} arr 
     * @return: {Array}
     */
    excel2Table(arr){
      return arr.map(v=>{
        return this.tableColumns.reduce((obj,c)=>{
          obj[c.prop] = v[c.label] || ''
          return obj
        },{})
      })
    },
    /**
     * 导出excel表格
     */
    exportExcel() { 　　　　　　
      require.ensure([], () => {
        // 这里 require 的地址指向你项目中 Export2Excel.js 文件存放地址
        const { export_json_to_excel } = require('../../js/Export2Excel.js') 
        const tHeader = [] , filterVal = []
        this.tableColumns.map( v => {
          tHeader.push(v.label)
          filterVal.push(v.prop)
        })
        const data = this.formatJson(filterVal, this.list)
        if(!data.length) return this.loading = false
        export_json_to_excel(tHeader, data)
        this.loading = false
      })
    },
    formatJson(filterVal, jsonData) {
      return jsonData.map(v => filterVal.map(j => v[j]))
    },
  },
}
</script>

由于使用了插件 导入导出 一键操作 只需要 对应转化 table 数据显示格式

抽个空 把分页多选导出表格功能也完成

效果图

更新部分js代码

<script>
import XLSX from 'xlsx'
export default {
  data() {
    return {
      list: [],
      tableList:[],
      tableColumns:[
        {prop:'name',label:'姓名'},
        {prop:'age',label:'年龄'},
        {prop:'sex',label:'性别'}
      ],
      fileListUpload: [],
      fileTemp: [],
      loading: false,
      multipleSelection:{},
      page:1,
      total:0,
      pageSize:40
    }
  },

  methods: {
    doDown(){
      let url = 'https://raw.githubusercontent.com/xrkffgg/Kvue/master/src/assets/excel/demo.xlsx'
      window.location.href = url
    },
    // 模拟分页请求数据
    onPageChange(val){
      setTimeout(() => {
        this.page = +val
        this.tableList = this.list[val-1]
        this.$nextTick(()=>{
        let rows = this.multipleSelection[val] || []
          if (rows) {
            rows.forEach(row => {
              this.$refs.multiple.toggleRowSelection(row);
            });
          } else {
            this.$refs.multiple.clearSelection();
          }
        })
      }, 500);
    },
    // 记录 表格 多选
    onSelectionChange(val){
      // 使用防抖 
      clearTimeout(this.selectionTimer)
      this.selectionTimer = setTimeout(() => {
        clearTimeout(this.selectionTimer)
        this.multipleSelection[this.page] = val
      }, 200);
    },
    /**
     * 上传文件
     * @param {Object} file 
     * @param {Array} fileList
     */
    async uploadFile(file,fileList){
      if ((file.raw.type == 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') || (file.raw.type == 'application/vnd.ms-excel')) {
        this.loading = true
        try {
          let list = await this.importExcel(file.raw)
          list = this.excel2Table(list)
          this.total = list.length
          let temp = []
          // 模拟 分页
          this.list = list.reduce((list,item,index)=>{
            if(index% this.pageSize === 0){
              temp = []
              temp.push(item)
              list.push(temp)
            }else{
              temp.push(item)
            }
            return list
          },[])
          this.tableList = this.list[0]
        } catch (error) {
          console.log(error)
        }
        this.loading = false
      } else {
        this.$message({
          type: 'warning',
          message: '附件格式错误，请删除后重新上传！'
        })
      }
    },
    /**
     * 处理上传excel文件 数据 转数组
     * @param {Object} raw
     * @return: {Array}
     */
    importExcel (raw) {
      return new Promise((resolve,reject)=>{
        let reader = new FileReader()
        reader.onloadend = function(){
          let binary = ''
          let bytes = new Uint8Array(this.result)
          let length = bytes.byteLength
          for (var i = 0; i < length; i++) {
            binary += String.fromCharCode(bytes[i])
          }
          // let XLSX = require('xlsx')
          let data = XLSX.read(binary, {
            type: 'binary'
          })
          resolve(XLSX.utils.sheet_to_json(data.Sheets[data.SheetNames[0]]))
        }
        reader.readAsArrayBuffer(raw)
      })
    },
    /**
     * 导入表格数据 转 table 显示
     * @param {Array} arr 
     * @return: {Array}
     */
    excel2Table(arr){
      return arr.map(v=>{
        return this.tableColumns.reduce((obj,c)=>{
          obj[c.prop] = v[c.label] || ''
          return obj
        },{})
      })
    },
    /**
     * 导出excel表格
     */
    exportExcel() {
      const multiple = this.multipleSelection
      let list = []
      for(let key in multiple){
        if(multiple[key].length){
          list = list.concat(multiple[key])
        }
      }
      if(!list.length) return this.$message.warning('请选择需要导出的表格')　　　　
      require.ensure([], () => {
        // 这里 require 的地址指向你项目中 Export2Excel.js 文件存放地址
        const { export_json_to_excel } = require('../../js/Export2Excel.js') 
        const tHeader = [] , filterVal = []
        this.tableColumns.map( v => {
          tHeader.push(v.label)
          filterVal.push(v.prop)
        })
        const data = this.formatJson(filterVal, list)
        if(!data.length) return this.loading = false
        export_json_to_excel(tHeader, data)
        this.loading = false
      })
    },
    formatJson(filterVal, jsonData) {
      return jsonData.map(v => filterVal.map(j => v[j]))
    },
  }
  
}
</script>