Vue xlsx插件导入导出excel 前端解析excel el-table导出excel

一、引入xlsx插件

npm i xlsx
npm i file-saver

import XLSX from 'xlsx' // 导入导出都要用到的插件
import FileSaver from 'file-saver' // 导出需要用到的插件

二、导出

这里示例el-table导出为excel：

<el-table id="exportTable" /> // 需要导出的el-table，需标注id
<el-button @click="exportExcel('elTable导出','exportTable')"> elTable导出 </el-button>

import XLSX from 'xlsx' // 导入导出都要用到的插件
import FileSaver from 'file-saver' // 导出需要用到的插件

/**
 * @description: el-table 导出数据
 * @param fileName 导出的文件命名
 * @param id id
 * @returns void
 */
exportExcel(fileName, id) {
  const _table = document.querySelector(`#${id}`)
  if (_table) {
    var wb = XLSX.utils.table_to_book?.(_table)
    var wbout = XLSX.write(wb, {
      bookType: 'xlsx',
      bookSST: true,
      type: 'array',
    })
    try {
      FileSaver.saveAs(
        new Blob([wbout], {
          type: 'application/octet-stream',
        }),
        `${fileName}.xlsx`
      )
      this[`download_${fileName}`] = this.$notify({
        title: '导出Excel',
        message: `${fileName} 导出成功`,
        type: 'success',
        duration: 0,
      })
    } catch (e) {
      this[`download_${fileName}`] = this.$notify({
        title: '导出Excel',
        message: '导出失败',
        type: 'error',
        duration: 0,
      })
    }
    return wbout
  } else {
    this[`download_${fileName}`] = this.$notify({
      title: '导出Excel',
      message: '导出失败',
      type: 'error',
      duration: 0,
    })
  }
},

 

三、导入

<el-upload
  multiple
  class=""
  ref="upload"
  action=""
  :auto-upload="false"
  :file-list="fileList"
  :on-change="handleChange"
  :show-file-list="false"
>
  <el-button type="primary">点击上传</el-button>
</el-upload>

import XLSX from 'xlsx'

export default {
  data() {
    return {
      fileList: [],
      outData: [],
    }
  },
  methods: {
    /**
     * @description: 上传文件
     * @param file
     * @param fileList
     * @return void
     */
    handleChange(file, fileList) {
      this.fileList = [fileList[fileList.length - 1]] // 读最后一个上传的数据
      let reader = new FileReader()
      reader.readAsArrayBuffer(file.raw)

      reader.onload = () => {
        let buffer = reader.result
        let bytes = new Uint8Array(buffer)
        let length = bytes.byteLength
        let binary = ''
        for (let i = 0; i < length; i++) {
          binary += String.fromCharCode(bytes[i])
        }
        let wb = XLSX.read(binary, {
          type: 'binary',
          cellDates: true, //设为true，将天数的时间戳转为时间格式
        })
        this.outData = XLSX.utils.sheet_to_json?.(
          wb.Sheets?.[wb.SheetNames?.[0]]
        ) // 这里只读取了第一个Sheets页
        this.$message.success('导入成功')
      }
    },
  },
}