vue获取excel文件数据

vue获取excel文件数据并用表格展示

1、安装xlsx paparse及handsontable

npm install xlsx
npm install papaparse
npm install handsontable @handsontable/vue

2、页面引入

import xlsx from 'xlsx'
import Papa from 'papaparse'

import {HotTable} from '@handsontable/vue'

3、判断文件类型

getFileType(fileurl){
  var suffix = ""  //获取文件后缀
  var result = ""  //获取结果类型
  try {
    var fileArr = filename.split('.')
    suffix = fileArr[fileArr.length-1]
  } catch (err) {
    suffix = ""
  }
  //filename无后缀时，返回false
  if(!suffix){
    result = false
    return result
  }
  // 图片
  var imglist = ['png', 'jpg', 'jpeg', 'bmp', 'gif']
  result = imglist.some(function(item) {
    return item === suffix
  })
  if(result) {
    result = 'image'
    return result
  }
  // txt
  var txtlist = ['txt']
  result = txtlist.some(function(item) {
    return item === suffix
  })
  if(result) {
    result = 'txt'
    return result
  }
  // excel
  var excellist = ['xls', 'xlsx']
  result = excellist.some(function(item) {
    return item === suffix
  })
  if(result) {
    result = 'excel'
    return result
  }
  // csv
  var csvlist = ['csv']
  result = csvlist.some(function(item) {
    return item === suffix
  })
  if(result) {
    result = 'csv'
    return result
  }
  // 以此类推
}

4、解析excel内容

const type = this.getFileTypr(fileurl)

1、excel文件

if(type==="excel") {
  const xhr = new XMLHttpRequest()
  xhr.open('GET', fileurl, true)
  xhr.responseType = 'blob'
  const rhis = this
  xhr.onload = function() {
    const blob = this.response
    const reader = new FileReader()
    reader.onload = function(e) {
      const wb = xlsx.read(e.target.result, { type: 'binary' })
      rhis.outputExcel(wb)  //处理数据
    }
    reader.readAsBinaryString(blob)
  }
  xhr.send()
}

outputExcel(wb) {
  this.sheetData = []
  var sheetNames = wb.SheetNames  //工作表名称集合
  sheetNames.foreach(name => {
    var worksheet = wb.Sheets[name]  //只能通过工作表名称来获取指定工作表
    var data = xlsx.utils.sheet_to_csv(worksheet)

    Papa.parse(data, {  //使用papaparse解析csv数据
      complete: res => {
        const arrs = res.data
        //去除最后的空行
        const lastItem = arrs[arrs.length - 1].every(val => val === "")
        lastItem && arrs.pop()
        this.sheetData = arrs
      }
    })
  })
}

2、csv文件

if(type === "csv") {
  this.sheetData = []
  Papa.parse(fileurl, 
    {
      download: true,
      complete: res => {
	    const arrs = res.data
	    //去除最后的空行
	    const lastItem = arrs[arrs.length - 1].every(val => val === "")
	    lastItem && arrs.pop()
	    this.sheetData = arrs
      }
   })
}

5、页面展示

<sheet :data="sheetData"/>

import sheet from './commonSheet'

commonSheet组件如下：

<template>
  <div class="overf">
    <div id="table" class="sheet">
      <hot-table ref="hot" :data="data" :settings="hotSetting" />
    </div>
  </div>
</template>

<script>
import { HotTable } from '@handsontable/vue
import 'handsontable/dist/handsontable.full.css
export default {
  components: { HotTable },
  props: {
    data: {
      type: Array,
      default() {
        return []
      }
    }
  },
  data() {
    return {
      hot: null,
      hotSettings: {
        readOnly: true,  // 表格只读
      }
    }
  },
  watch: {
    data(newValue) {
      this.$refs.hot.hotInstance.loadData(newValue)
    }
  }
}
</script>

<style lang="scss" scope>
.overf{
  height: 300px; overflow: hidden;
}
.sheet{
  height: 100%; overflow: auto;
  &>>>#hot-display-license-info{
    display: none;
  }
}
</style>