main.vue

import { exportExcel } from '@/utils/ExcelUtils'
methods: {
    exportExcel () {
      this.axios.get(url, {params: this.params}).then((data) => {
        let fileName = this.$route.meta.title + this.$DateUtil.dateFormat('yyyy_MM_dd_HH_mm_ss', new Date())
        let columns = {
          name: {name: '姓名'},
          age: {name: '年龄'},
          gender: {name: '性别'}
        }
        exportExcel(fileName, columns, data)
      })
    }
}

ExcelUtils.js

import XLSX from 'xlsx-js-style'
// fileName：表格名，columns：数据格式，data：接口返回的数据
export const exportExcel = (fileName = '表格', columns = {}, data = []) => {
  let columnKeyData = []
  if (columns) {
    for (let key in columns) {
      columnKeyData.push(key)
    }
  } else {
    throw new Error('导出列不能为空')
  }
  if (columnKeyData.length <= 0) {
    throw new Error('导出列不能为空')
  }
  let excelData = []
  let columnData = [] // 表头中文名
  let columnColWidth = [] // 表头宽度
  // 组织表头数据
  for (let i = 0; i < columnKeyData.length; i++) {
    columnData.push(columns[columnKeyData[i]].name)
    columnColWidth.push(columns[columnKeyData[i]].width ? columns[columnKeyData[i]].width : 20)
  }
  excelData.push(columnData) // [[姓名, 年龄, 性别]]
  // 组织数据
  for (let i = 0; i < data.length; i++) {
    let row = data[i] // 接口返回的第i条数据
    let rowData = []
    for (let j = 0; j < columnKeyData.length; j++) { // 循环传入的导出表头
      if (row[columnKeyData[j]]) {
        if (columns[columnKeyData[j]].formatter instanceof Function) { // 判断传入的数据格式内有没有封装的函数
          rowData.push(columns[columnKeyData[j]].formatter(row)) // 放入封装函数的return结果
        } else {
          rowData.push(row[columnKeyData[j]])
        }
      } else {
        rowData.push('')
      }
    }
    excelData.push(rowData)
    // [[姓名, 年龄, 性别], [name, age, gender], [name2, age2, gender2], ...., [namei, agei, genderi]]
  }
  let workbook = XLSX.utils.book_new()
  let worksheet = XLSX.utils.aoa_to_sheet(excelData)

  let cols = []
  // wpx 字段表示以像素为单位，wch 字段表示以字符为单位
  for (let i = 0; i <= excelData[0].length; i++) {
    let col = {}
    col.wch = columnColWidth[i]
    cols.push(col)
  }
  worksheet['!cols'] = cols

  // 以下是样式设置，样式设置放在组织完数据之后，xlsx-js-style的核心API就是SheetJS的
  Object.keys(worksheet).forEach(key => {
    // 非!开头的属性都是单元格
    if (!key.startsWith('!')) { // 查看key是否以“!”开头(key:A1,B1,C1.....A2,B2,C2单元格的序列)
      let patten = /[0-9]+/g
      let rowNum = key.match(patten)
      let fill
      if (rowNum[0] === '1') {
        fill = {
          fgColor: {rgb: '00B050'}
        }
      }
      worksheet[key].s = {
        font: {
          sz: '12'
        },
        alignment: {
          horizontal: 'left',
          vertical: 'center',
          wrapText: false
        },
        fill: fill,
        border: {
          top: { style: 'thin' },
          right: { style: 'thin' },
          bottom: { style: 'thin' },
          left: { style: 'thin' }
        }
      }
    }
  })
  XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1')
  XLSX.writeFile(workbook, fileName + '.xlsx')
}