vue 下载xlsx或xls表格

需求:

后台没有提供下载接口,就只能下载插件调用查询接口,把查询出来的数据通过插件生成xlsx或xls表格导出来

对应插件:

npm install --save xlsx-style

npm install file-saver --save

创建一个文件export2Excel.js

/* eslint-disable */
import { saveAs } from 'file-saver'
import XLSX from 'xlsx-style'

function dataNum(v, date1904) {
  if (date1904) v += 1462
  const epoch = Date.parse(v)
  return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000)
}

function sheetFromArrayOfArrays(headers, data) {
  const ws = {}
  const range = {
    s: {
      c: 10000000,
      r: 10000000
    },
    e: {
      c: 0,
      r: 0
    }
  }
  /*设置worksheet每列的最大宽度*/
  for (let R = 0; R !== headers.length; ++R) {
    for (let C = 0; C !== headers[R].length; ++C) {
      if (range.s.r > R) range.s.r = R
      if (range.s.c > C) range.s.c = C
      if (range.e.r < R) range.e.r = R
      if (range.e.c < C) range.e.c = C
      const cell = {
        v: headers[R][C]
      }
      if (cell.v == null) continue
      const cell_ref = XLSX.utils.encode_cell({
        c: C,
        r: R
      })

      if (typeof cell.v === 'number') {
        cell.t = 'n'
      } else if (typeof cell.v === 'boolean') {
        cell.t = 'b'
      } else if (cell.v instanceof Date) {
        cell.t = 'n'
        cell.z = XLSX.SSF._table[14]
        cell.v = dataNum(cell.v)
      } else {
        cell.t = 's'
      }
      cell.s = {
        border:{
          top: {
            style: 'thin'
          },
          bottom: {
            style: 'thin'
          },
          left: {
            style: 'thin'
          },
          right: {
            style: 'thin'
          }
        },
        fill: {
          fgColor: {rgb: 'CDCDCD'},
        },
        font: {
          bold: true
        },
        alignment: {
          horizontal: 'center',
          vertical: 'center'
        }
      }
      ws[cell_ref] = cell
    }
  }
  for (let R = headers.length; R !== data.length + headers.length; ++R) {
    for (let C = 0; C !== data[R - headers.length].length; ++C) {
      if (range.s.r > R) range.s.r = R
      if (range.s.c > C) range.s.c = C
      if (range.e.r < R) range.e.r = R
      if (range.e.c < C) range.e.c = C
      const cell = {
        v: data[R - headers.length][C]
      }
      if (cell.v == null) continue
      const cell_ref = XLSX.utils.encode_cell({
        c: C,
        r: R
      })

      if (typeof cell.v === 'number') {
        cell.t = 'n'
      } else if (typeof cell.v === 'boolean') {
        cell.t = 'b'
      } else if (cell.v instanceof Date) {
        cell.t = 'n'
        cell.z = XLSX.SSF._table[14]
        cell.v = dataNum(cell.v)
      } else {
        cell.t = 's'
      }
      cell.s = {
        border: {
          top: {
            style: 'thin'
          },
          bottom: {
            style: 'thin'
          },
          left: {
            style: 'thin'
          },
          right: {
            style: 'thin'
          }
        },
        alignment: {
          vertical: 'center'
        }
      }
      ws[cell_ref] = cell
    }
  }
  if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range)
  return ws
}

function Workbook() {
  if (!(this instanceof Workbook)) return new Workbook()
  this.SheetNames = []
  this.Sheets = {}
}

function s2ab(s) {
  const buf = new ArrayBuffer(s.length)
  const view = new Uint8Array(buf)
  for (let i = 0; i !== s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF
  return buf
}

export function sheetMaker({
                             headers = [],
                             data,
                             merges = [],
                             autoWidth = true
                           } = {}) {
  data = [...data]
  if (!headers.every(header => header instanceof Array)) {
    headers = [headers]
  }
  const ws = sheetFromArrayOfArrays(headers, data)
  if (merges.length > 0) {
    if (!ws['!merges']) ws['!merges'] = []
    merges.forEach(item => {
      ws['!merges'].push(XLSX.utils.decode_range(item))
    })
  }

  if (autoWidth) {
    /*设置worksheet每列的最大宽度*/
    if (headers.every(header => header instanceof Array)) {
      for (let i = headers.length - 1; i > -1; i--) {
        data.unshift(headers[i])
      }
    } else {
      data.unshift(headers)
    }
    const colWidth = data.map(row => row.map(val => {
      /*先判断是否为null/undefined*/
      if (val == null) {
        return {
          'wch': 10
        }
      }
      /*再判断是否为中文*/
      else if (val.toString().charCodeAt(0) > 255) {
        return {
          'wch': val.toString().length * 2 > 150 ? 150 : val.toString().length * 2
        }
      } else {
        return {
          'wch': val.toString().length > 150 ? 150 : val.toString().length
        }
      }
    }))
    /*以Header最后一行为初始值*/
    let result = colWidth[headers.length - 1]
    for (let i = 1; i < colWidth.length; i++) {
      for (let j = 0; j < colWidth[i].length; j++) {
        if (result[j]['wch'] < colWidth[i][j]['wch']) {
          result[j]['wch'] = colWidth[i][j]['wch']
        }
      }
    }
    ws['!cols'] = result
  }
  return ws
}

export function json2Excel({ headers, data, keys, filter, sheetName = 'Sheet 1', fileName = 'excel-list', merges = [], autoWidth = true, bookType = 'xlsx' }) {
  // 数据处理
  data = data.map(item => keys.map(key => {
    if (filter && filter instanceof Function) {
      const value = filter(key, item[key], item)
      return value == null ? item[key] || '' : value
    } else {
      return item[key] == null ? '' : item[key]
    }
  }))
  data = [...data]
  // 表格对象
  const wb = new Workbook()
  const ws = sheetMaker({ headers, data, merges, autoWidth })
  wb.SheetNames.push(sheetName)
  wb.Sheets[sheetName] = ws
  const wbout = XLSX.write(wb, {
    bookType: bookType,
    bookSST: false,
    type: 'binary'
  })

  saveAs(new Blob([s2ab(wbout)], {
    type: 'application/octet-stream'
  }), `${fileName}.${bookType}`)
}

页面使用:

html:

  <el-button
          :disabled="!params.PAYDETFINDINFO.PAYPLANNO"
          class="sale-button-addon sale-icon-download"
          type="primary"
          round
          plain
          @click="clickExportSeatBtn"
        >导出
        </el-button>

 methods:

import { json2Excel } from '@/utils/export2Excel'

//js mothds


  // 导出
      clickExportSeatBtn() {
        if (+this.totalCount > 1000) {
          this.$message.warning('每次最多导出1000条数据')
        }
        const exportFilter = (key, keyValue, item) => {
          if (key === 'userCode') {
            return item.groupUserCode || item.userCode
          }
          if (key === 'landFlag') {
            return keyValue || '0'
          }
        }
        const fileName = parseTime(new Date(), '{y}{m}{d}{h}{i}{s}')
        const exportFn = (list) => {
          json2Excel({
            data: list,
            keys: ['seatCode', 'userCode', 'lastLogonTime', 'insertTimeForHis'],
            headers: [
              ['坐席编码', '用户代码',  '最后一次登录时间', '插入时间']
            ],
            merges: [],
            filter: exportFilter,
            fileName: 'SeatMain' + fileName.toString()
          })
        }
        const exportParams = {
          'currPage': 1,
          'pageSize': 1000,
          'currUserCode': this.$store.state.user.info.userCode,
          
        }
//调用查询接口
        seatMainFindSeatMainByPage(exportParams).then(res => {
          exportFn(res.result instanceof Array ? res.result : [res.result])
        }).catch(err => {
          this.$message.error(err)
        })
      },