JS-XLSX 导出Elementui表格

导出Elmentui，支持fixed定位以及多级表头等。

先安装

npm install file-saver -S  
npm install xlsx -Snpm install xlsx-style -S

然后引入

import FileSaver from 'file-saver'import XLSX from 'xlsx'import XLSXS from 'xlsx-style'

然后写导出方法

exportExcel(dom, excelName) {
      try {
        new Promise((resolve) => {
          try {
            // 展开全部
            this.showAll()
          } catch (error) {
            // 如果父页面没有这个方法(有些页面是分页的)
            console.log(error)
            // reject()
          } finally {
            resolve()
          }
        }).then(() => {
          // 先找到所在的el表格
          let [el] = this.$children.filter((item) => {
            return item.tableId
          })
          // el实例的Dom
          let $e = el.$el
          if (dom) {
            $e = dom
          }
          // 如果表格加了fixed属性，则导出的文件会生产两份一样的数据，所以可在这里判断一下
          let $table = $e.querySelector('.el-table__fixed')
          if (!$table) {
            $table = $e
          }
          // console.log($table)
          // return
          // 为了返回单元格原始字符串，设置{ raw: true }
          const wb = XLSX.utils.table_to_book($table, { raw: true })
          // console.log('[wb]', $table, wb)
          // Sheet1 如果取不到可以 log 下 wb 查找
          setExlStyle(wb['Sheets']['Sheet1']) // 设置列宽 字号等
          addRangeBorder(wb['Sheets']['Sheet1']['!merges'], wb['Sheets']['Sheet1'])
          const wbout = XLSXS.write(wb, {
            type: 'buffer'
          })
          console.log(wb)
          // return
          FileSaver.saveAs(new Blob([wbout], { type: 'application/octet-stream' }), `${excelName || el.$parent.titleName || '新建Excel表格'}.xls`)
          try {
            // 收起来
            this.hideAll()
          } catch (error) {
            console.log(error)
          }
        })
      } catch (e) {
        if (typeof console !== 'undefined') console.error(e)
      }
    }

function setExlStyle(data) {
  // console.log('data:', data)
  data['!cols'] = []
  for (let key in data) {
    if (data[key] instanceof Object) {
      // 其余配置可以去 源文档查找 Cell Styles
      data[key].s = {
        //边框线
        border: {
          top: {
            style: 'thin'
          },
          bottom: {
            style: 'thin'
          },
          left: {
            style: 'thin'
          },
          right: {
            style: 'thin'
          }
        },
        font: {
          // 头部样式 (判断是否头部) 最后第二个不是数字
          sz: key[key.length - 1] == 1 && !['1', '2', '3', '4', '5', '6', '7', '8', '9'].includes(key[key.length - 2]) ? 16 : 12
        },
        //水平居中对齐
        alignment: {
          horizontal: 'center',
          vertical: 'center'
        }
      }
      // 列宽
      data['!cols'].push({ wpx: 100 })
    }
  }
  // console.log(data)
  return data
}

function addRangeBorder(range, ws) {
  const arr = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z']
  // console.log('range:', range)
  console.log('ws:', ws)
  if (!range) {
    return ws
  }
  range.forEach((item) => {
    let startRow = Number(item.s.c),
      endRow = Number(item.e.c)

    for (let i = startRow + 1; i <= endRow; i++) {
      ws[arr[i] + (Number(item.e.r) + 1)] = { s: { border: { top: { style: 'thin' }, left: { style: 'thin' }, bottom: { style: 'thin' }, right: { style: 'thin' } } } }
    }
  })

  // -----------------------------------------------------------------
  try {
    // const arr = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z']
    let end = ws['!ref'].split(':')[1]
    // console.log(end)
    let num = 0
    for (const i in end) {
      if (Object.hasOwnProperty.call(end, i)) {
        if (!arr.includes(end[i])) {
          num = i
          break
        }
      }
    }
    let line = end.slice(0, num),
      lineNumber = end.slice(num, end.length)
    console.log(lineNumber)
    let sum = 0
    // 取到一共有多少个列
    for (let i = 0; i < line.length; i++) {
      sum +=
        (arr.findIndex((s) => {
          return s == line[line.length - 1 - i]
        }) +
          1) *
        Math.pow(26, i)
    }

    if (sum > 701) {
      throw '数据量太多，请做后端导出!'
    }
    let code = ''
    // console.log(sum)
    for (let i = 1; i < sum + 1; i++) {
      // 列还原特殊字段如:'AA' 来检查缺失的单元格
      code = get26Code(i)
      // 补齐单元格
      for (let j = 1; j < lineNumber - 0 + 1; j++) {
        if (!ws[code + j]) {
          ws[code + j] = { s: { border: { top: { style: 'thin' }, left: { style: 'thin' }, bottom: { style: 'thin' }, right: { style: 'thin' } } } }
        }
      }

      // if (!ws[code + '2']) {
      //   ws[code + '2'] = { s: { border: { top: { style: 'thin' }, left: { style: 'thin' }, bottom: { style: 'thin' }, right: { style: 'thin' } } } }
      // }
    }
    // eslint-disable-next-line
    function get26Code(sum) {
      if (sum < 26) {
        return arr[sum - 1]
      } else if (sum === 26) {
        return 'Z'
      } else {
        let one,
          two = ''
        // console.log(sum % 26 === 0);
        // console.log(arr[parseInt(sum / 26) - 2]);
        if (sum % 26 === 0) {
          ;(two = 'Z'), (one = arr[parseInt(sum / 26) - 2])
        } else {
          ;(two = arr[(sum % 26) - 1]), (one = arr[parseInt(sum / 26) - 1])
        }
        return one + two
      }
      // console.log(arr[parseInt(sum / 26) - 1]);
      // console.log(arr[sum % 26 - 1]);
    }
  } catch (error) {
    console.warn(error)
  }
  // -----------------------------------------------------------------

  return ws
}