exceljs xlsx 前端生成excel 和解析excel (一)

    "exceljs": "^4.2.0",

"xlsx": "^0.16.9"

一、

1、生成 

 

   exportFileFunc () {
      let records = []
      for (let i = 0; i < this.fileData.length; i++) {
        records = records.concat(this.fileData[i])
      }

      const exportData = []
      const excludeEmt = ['nickname', 'mtime', 'action']
      const newColumns = this.columns.filter(item => excludeEmt.indexOf(item.dataIndex) === -1)
    
      exportData[0] = newColumns.map((item) => item.title)
      for (let j = 0; j < records.length; j++) {
        const tmpArr = newColumns.map((item) => {
          const crtKey = item.dataIndex
          let crtValue = records[j][crtKey]
          switch (crtKey) {
           
            case 'updated': crtValue = moment.unix(crtValue).format('YYYY-MM-DD HH:mm:ss')
          }

          return crtValue
        })
        exportData.push(tmpArr)
      }
      console.log('this.fileData', records, this.fileData)
      var workbook = new Excel.Workbook()
      var sheet = workbook.addWorksheet('Worksheet')
      sheet.addRows(exportData)
      workbook.xlsx.writeBuffer({ base64: true })
        .then(buffer => {
          var blob = new Blob([buffer], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' })
          var a = document.createElement('a')
          a.href = URL.createObjectURL(blob)
          a.download = `记录.xlsx`
          a.click()
          var dispose = () => URL.revokeObjectURL(blob)
          setTimeout(dispose, 100)
        })
    },

2、 "xlsx": "^0.16.9"   生成

      getExml({ bt: JSON.stringify(this.columns1), main: JSON.stringify(this.data) }, xmlName)
      // const a = await this.$api.read.getexml({ bt: JSON.stringify(this.columns1), main: JSON.stringify(this.data) }, { responseType: 'blob' })
      // var blob = new Blob([a])
      // const URL = window.URL || window.webkitURL || window.moxURL
      // const url = URL.createObjectURL(blob)
      // const link = document.createElement('a')
      // const filename = xmlName + '.xlsx'
      // link.href = url
      // link.download = filename
      // link.click()
      // URL.revokeObjectURL(url)

export const getExml = (body, xmlName) => {
  let { bt, main } = body
  bt = JSON.parse(bt)
  main = JSON.parse(main)
  const data = []
  const titleData = []
  const dataKey = []
  for (let i = 0; i < bt.length; i++) {
    titleData.push(bt[i].title)
    dataKey.push(bt[i].dataIndex)
  }
  data.push(titleData)
  for (let i = 0; i < main.length; i++) {
    const dataMain = []
    for (let j = 0; j < dataKey.length; j++) {
      dataMain.push(main[i][dataKey[j]])
    }
    data.push(dataMain)
  }

  var sheet = XLSX.utils.aoa_to_sheet(data)

  function sheet2blob (sheet, sheetName) {
    sheetName = sheetName || 'sheet1'
    var workbook = {
      SheetNames: [sheetName],
      Sheets: {}
    }
    workbook.Sheets[sheetName] = sheet
    var wopts = {
      bookType: 'xlsx',
      bookSST: false,
      type: 'binary'
    }
    var wbout = XLSX.write(workbook, wopts)
    var blob = new Blob([s2ab(wbout)], { type: 'application/octet-stream' })

    function 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
    }
    return blob
  }

 

 

二、、解析

 

// some.vue

import XLSX from 'xlsx'

import eventTemplate from '@/excel/pointTmt.xlsx'

handleChangeFile (e) {





          this.readWorkbookFromLocalFile(e.file, function (workbook) {
            _this.readWorkbook(workbook)
          })



}


    // 读取本地excel文件
    readWorkbookFromLocalFile (file, callback) {
      var reader = new FileReader()
      reader.onload = function (e) {
        var data = e.target.result
        var workbook = XLSX.read(data, {
          type: 'binary'
        })
        if (callback) callback(workbook)
      }
      reader.readAsBinaryString(file)
    },


    readWorkbook (workbook) {
      var sheetNames = workbook.SheetNames // 工作表名称集合
      var worksheet = workbook.Sheets[sheetNames[0]] // 这里我们只读取第一张sheet
      const fileDataArray = XLSX.utils.sheet_to_json(worksheet)
      if (this.checkFileData(fileDataArray)) {
        this.hasErrorFile = false
        this.buryData = fileDataArray
        this.getTableData()
        this.currentPage = 1
      } else {
        this.$message.error('文档缺失必填信息')
        this.hasErrorFile = true
        this.buryData = []
      }

      console.log(workbook, this.buryData)
    },