XLSX从第几行数读取excel内容，去除换行符，两端空格，保持日期格式，加密软件提示，null赋值为空字符串

1.概述

   浏览器读取excel内容，可根据第几行读取，自动去除换行符，单元格的两端空格，保持日期格式，加密软件提示，null赋值为空字符串。

2.代码如下

  readWorkbookFromLocalFile(file, callback) {
    var reader = new FileReader()
    reader.onload = (e) => {
      var data = (e.target as any).result
      try {
        var workbook = XLSX.read(data, {
          type: 'binary',
          cellDates: true,
          // cellStyles: false
        })
      } catch (error) {
        console.log('error: ', error)
        this.$message.error('格式错误：不是一个Excel文件或文件已损坏。')
        return
      }
      if (callback) {
        const result = this.readWorkbook(workbook)
        callback(result)
      }
    }
    reader.readAsBinaryString(file)
  }
  readWorkbook(workbook) {
    var sheetNames = workbook.SheetNames // 工作表名称集合
    var worksheet = workbook.Sheets[sheetNames[0]] // 这里我们只读取第一张sheet
    let val = $(this.$el).find('#uploadfile79953769070592').val()
    for (let index = 0; index < val; index++) {
      this.deleteRow(worksheet, 0)
    }
    const sheet2JSONOpts = {
      /** Default value for null/undefined values */
      defval: '', //给defval赋值为空的字符串
    }
    var csv = XLSX.utils.sheet_to_json(worksheet, sheet2JSONOpts)
    return csv
  }
  encodeCell(r, c) {
    return XLSX.utils.encode_cell({ r, c })
  }

  deleteRow(ws, index) {
    const range = XLSX.utils.decode_range(ws['!ref'])
    for (let row = index; row < range.e.r; row++) {
      for (let col = range.s.c; col <= range.e.c; col++) {
        ws[this.encodeCell(row, col)] = ws[this.encodeCell(row + 1, col)]
      }
    }
    range.e.r--
    ws['!ref'] = XLSX.utils.encode_range(range.s, range.e)
  }

  deleteCol(ws, index) {
    const range = XLSX.utils.decode_range(ws['!ref'])
    for (let col = index; col < range.e.c; col++) {
      for (let row = range.s.r; row <= range.e.r; row++) {
        ws[this.encodeCell(row, col)] = ws[this.encodeCell(row, col + 1)]
      }
    }
    range.e.c--
    ws['!ref'] = XLSX.utils.encode_range(range.s, range.e)
  }
  //去除换行
  clearBr(key) {
    key = key.replace(/<\/?.+?>/g, '')
    key = key.replace(/[\r\n]/g, '')
    return key
  }
  //去掉字符串两端的空格
  trim(str) {
    return str.replace(/(^\s*)|(\s*$)/g, '')
  }

调用

   this.readWorkbookFromLocalFile(file, (data) => {
      window.console.log('data: ', data)
      let result = []
      for (let index = 0; index < data.length; index++) {
        const element = data[index]
        console.log('element: ', element)
        let arr = []
        for (let prop in element) {
          arr.push({
            name: prop,
            ct: typeof element[prop] === 'string' ? this.trim(this.clearBr(element[prop])) : element[prop],
          })
        }
        let obj = {}
        arr.forEach((item) => {
          obj[item.name] = item.ct
        })
        result.push(obj)
      }
      console.log('result', result)
      return
    })

3.总结

1. 调用第三方库

 import XLSX from 'xlsx'

2. 第几行数读取excel内容

 let val = $(this.$el).find('#uploadfile79953769070592').val()
    for (let index = 0; index < val; index++) {
      this.deleteRow(worksheet, 0)
    }

3. 去除换行符，两端空格

 let result = []
      for (let index = 0; index < data.length; index++) {
        const element = data[index]
        console.log('element: ', element)
        let arr = []
        for (let prop in element) {
          arr.push({
            name: prop,
            ct: typeof element[prop] === 'string' ? this.trim(this.clearBr(element[prop])) : element[prop],
          })
        }
        let obj = {}
        arr.forEach((item) => {
          obj[item.name] = item.ct
        })
        result.push(obj)
      }

5. 保持日期格式

         var workbook = XLSX.read(data, {
          type: 'binary',
          cellDates: true,
          // cellStyles: false
        })

6.null赋值为空字符串

  const sheet2JSONOpts = {
      /** Default value for null/undefined values */
      defval: '', //给defval赋值为空的字符串
    }
    var csv = XLSX.utils.sheet_to_json(worksheet, sheet2JSONOpts)