一个导出表格的轮子（可多级表头）

<template>
  <div />
</template>

<script>
// https://blog.csdn.net/seeflyliu/article/details/109476804
import XLSX from 'xlsx'
export default {
  name: 'Export',
  components: {

  },
  props: {
    // 数据表头，支持多级表头
    columns: {
      type: Array
    },
    // 表格数据
    dataSource: {
      type: Array
    },
    // 导出文件名称
    name: {
      type: String
    }
  },
  data() {
    return {}
  },
  computed: {},
  watch: {},
  created() {},
  methods: {
    // 导出方法
    exportData() {
      const regex = /[:\/\?\*\\|<>\[\]]+/g
      let name = this.name
      if (name.length > 31) {
        name = name.substring(0, 31)
      }
      let sheetName = name.replace(regex, '_')
      // excel表头
      let excelHeader = this.buildHeader(this.columns)
      // 头部行数，用来固定表头
      let headerRows = excelHeader.length
      // 提取数据
      let dataList = this.extractData(this.dataSource, this.columns)
      excelHeader.push(...dataList, [])
      // 计算合并
      let merges = this.doMerges(excelHeader)
      // 新建工作表
      let ws = XLSX.utils.aoa_to_sheet(excelHeader)
      // 将数据添加到工作表
      XLSX.utils.sheet_add_aoa(ws, dataList, { origin: headerRows })
      // 单元格合并
      ws['!merges'] = merges
      // 头部冻结
      ws['!freeze'] = {
        xSplit: '1',
        ySplit: '' + headerRows,
        topLeftCell: 'B' + (headerRows + 1),
        activePane: 'bottomRight',
        state: 'frozen'
      }
      // 列宽
      ws['!cols'] = [{ wpx: 165 }]
      let workbook = {
        SheetNames: [sheetName],
        Sheets: {}
      }
      workbook.Sheets[sheetName] = ws
      // excel样式
      let wopts = {
        bookType: 'xlsx',
        bookSST: false,
        type: 'binary',
        cellStyles: true
      }
      let wbout = XLSX.write(workbook, wopts)
      let blob = new Blob([this.s2ab(wbout)], {
        type: 'application/octet-stream'
      })
      this.openDownloadXLSXDialog(blob, sheetName + '.xlsx')
    },
    /**
     * 构建excel表头
     * @param columns 列表页面展示的表头
     * @returns {[]} excel表格展示的表头
     */
    buildHeader(columns) {
      let excelHeader = []
      // 构建生成excel表头需要的数据结构
      this.getHeader(columns, excelHeader, 0, 0)
      // 多行表头长短不一，短的向长的看齐，不够的补上行合并占位符
      let max = Math.max(...excelHeader.map(a => a.length))
      excelHeader
        .filter(e => e.length < max)
        .forEach(e => this.pushRowSpanPlaceHolder(e, max - e.length))
      return excelHeader
    },
    /**
     * 生成头部
     * @param headers 展示的头部
     * @param excelHeader excel头部
     * @param deep 深度
     * @param perOffset 前置偏移量
     * @returns {number}  后置偏移量
     */
    getHeader(headers, excelHeader, deep, perOffset) {
      let offset = 0
      let cur = excelHeader[deep]
      if (!cur) {
        cur = excelHeader[deep] = []
      }
      // 填充行合并占位符
      this.pushRowSpanPlaceHolder(cur, perOffset - cur.length)
      for (let i = 0; i < headers.length; i++) {
        let head = headers[i]
        cur.push(head.name)
        if (
          head.hasOwnProperty('children') &&
          Array.isArray(head.children) &&
          head.children.length > 0
        ) {
          let childOffset = this.getHeader(
            head.children,
            excelHeader,
            deep + 1,
            cur.length - 1
          )
          // 填充列合并占位符
          this.pushColSpanPlaceHolder(cur, childOffset - 1)
          offset += childOffset
        } else {
          offset++
        }
      }
      return offset
    },
    /**
     * 根据选中的数据和展示的列，生成结果
     * @param dataSource
     * @param columns
     */
    // extractData(dataSource, columns) {
    //   // 列
    //   let headerList = this.flat(columns)
    //   console.log('headerList', headerList)
    //   // 导出的结果集
    //   let excelRows = []
    //   if (Array.isArray(dataSource) && dataSource.length > 0) {
    //     // 如果有children集合的话会用到
    //     let dataKeys = new Set(Object.keys(dataSource[0]))
    //     dataSource.some(e => {
    //       if (e.children && e.children.length > 0) {
    //         let childKeys = Object.keys(e.children[0])
    //         for (let i = 0; i < childKeys.length; i++) {
    //           dataKeys.delete(childKeys[i])
    //         }
    //         return true
    //       }
    //     })
    //     this.flatData(dataSource, list => {
    //       excelRows.push(...this.buildExcelRow(dataKeys, headerList, list))
    //     })
    //   }
    //   console.log('excelRows', excelRows)
    //   return excelRows
    // },
    extractData(dataSource, columns) {
      // 列
      let headerList = this.flat(columns)
      console.log('headerList', headerList)
      // 导出的结果集
      let excelRows = []
      if (Array.isArray(dataSource) && dataSource.length > 0) {
        // 如果有children集合的话会用到
        let dataKeys = new Set(Object.keys(dataSource[0]))
        dataSource.some(e => {
          if (e.children && e.children.length > 0) {
            let childKeys = Object.keys(e.children[0])
            for (let i = 0; i < childKeys.length; i++) {
              dataKeys.delete(childKeys[i])
            }
            return true
          }
        })
        this.flatData(dataSource, list => {
          excelRows.push(...this.buildExcelRow(dataKeys, headerList, list))
        })
      }
      // 对每个单元格的内容进行截取
      excelRows = excelRows.map(row => {
        return row.map(cell => {
          if (typeof cell === 'string' && cell.length > 3000) {
            return cell.substring(0, 3000)
          } else {
            return cell
          }
        })
      })
      console.log('excelRows', excelRows)
      return excelRows
    },
    /**
     * @param mainKeys
     * @param headers
     * @param rawDataList
     * */
    buildExcelRow(mainKeys, headers, rawDataList) {
      // 合计行
      let sumCols = []
      // 数据行
      let rows = []
      for (let i = 0; i < rawDataList.length; i++) {
        let cols = []
        let rawData = rawDataList[i]
        // 提取数据
        for (let j = 0; j < headers.length; j++) {
          let header = headers[j]
          // 父元素键需要行合并
          if (rawData['rowSpan'] === 0 && mainKeys.has(header.prop)) {
            cols.push('!$ROW_SPAN_PLACEHOLDER')
          } else {
            let value
            if (typeof header.exeFun === 'function') {
              value = header.exeFun(rawData)
            } else {
              value = rawData[header.prop]
            }
            cols.push(value)
            // 如果该列需要合计,并且是数字类型
            if (header['summable'] && typeof value === 'number') {
              sumCols[j] = (sumCols[j] ? sumCols[j] : 0) + value
            }
          }
        }
        rows.push(cols)
      }
      // 如果有合计行
      if (sumCols.length > 0) {
        rows.push(...this.sumRowHandle(sumCols))
      }
      return rows
    },
    sumRowHandle(sumCols) {
      // TODO
      return []
    },
    /**
     * 合并头部单元格
     **/
    doMerges(arr) {
      // 要么横向合并 要么纵向合并
      let deep = arr.length
      let merges = []
      for (let y = 0; y < deep; y++) {
        // 先处理横向合并
        let row = arr[y]
        let colSpan = 0
        for (let x = 0; x < row.length; x++) {
          if (row[x] === '!$COL_SPAN_PLACEHOLDER') {
            row[x] = undefined
            if (x + 1 === row.length) {
              merges.push({
                s: { r: y, c: x - colSpan - 1 },
                e: { r: y, c: x }
              })
            }
            colSpan++
          } else if (colSpan > 0 && x > colSpan) {
            merges.push({
              s: { r: y, c: x - colSpan - 1 },
              e: { r: y, c: x - 1 }
            })
            colSpan = 0
          } else {
            colSpan = 0
          }
        }
      }
      // 再处理纵向合并
      let colLength = arr[0].length
      for (let x = 0; x < colLength; x++) {
        let rowSpan = 0
        for (let y = 0; y < deep; y++) {
          if (arr[y][x] === '!$ROW_SPAN_PLACEHOLDER') {
            arr[y][x] = undefined
            if (y + 1 === deep) {
              merges.push({ s: { r: y - rowSpan, c: x }, e: { r: y, c: x } })
            }
            rowSpan++
          } else if (rowSpan > 0 && y > rowSpan) {
            merges.push({
              s: { r: y - rowSpan - 1, c: x },
              e: { r: y - 1, c: x }
            })
            rowSpan = 0
          } else {
            rowSpan = 0
          }
        }
      }
      return merges
    },
    /**
     * data: 数据
     * headerRows：对象
     */
    aoa_to_sheet(data, headerRows) {
      const ws = {}
      const range = { s: { c: 10000000, r: 10000000 }, e: { c: 0, r: 0 } }
      for (let R = 0; R !== data.length; ++R) {
        for (let C = 0; C !== data[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
          }
          // / 这里生成cell的时候，使用上面定义的默认样式
          const cell = {
            v: data[R][C] || '',
            s: {
              font: { name: '宋体', sz: 11, color: { auto: 1 } },
              alignment: {
                // / 自动换行
                wrapText: 1,
                // 居中
                horizontal: 'center',
                vertical: 'center',
                indent: 0
              }
            }
          }
          // 头部列表加边框
          if (R < headerRows) {
            cell.s.border = {
              top: { style: 'thin', color: { rgb: '000000' } },
              left: { style: 'thin', color: { rgb: '000000' } },
              bottom: { style: 'thin', color: { rgb: '000000' } },
              right: { style: 'thin', color: { rgb: '000000' } }
            }
            cell.s.fill = {
              patternType: 'solid',
              fgColor: { theme: 3, tint: 0.3999755851924192, rgb: 'DDD9C4' },
              bgColor: { theme: 7, tint: 0.3999755851924192, rgb: '8064A2' }
            }
          }
          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 {
            cell.t = 's'
          }
          ws[cell_ref] = cell
        }
      }
      if (range.s.c < 10000000) {
        ws['!ref'] = XLSX.utils.encode_range(range)
      }
      return ws
    },
    /**
     * 填充行合并占位符
     * */
    pushRowSpanPlaceHolder(arr, count) {
      for (let i = 0; i < count; i++) {
        arr.push('!$ROW_SPAN_PLACEHOLDER')
      }
    },
    // 填充列合并占位符
    pushColSpanPlaceHolder(arr, count) {
      for (let i = 0; i < count; i++) {
        arr.push('!$COL_SPAN_PLACEHOLDER')
      }
    },
    /**
     * 展开数据，为了实现父子关系的数据进行行合并
     * @param list
     * @param eachDataCallBack
     */
    flatData(list, eachDataCallBack) {
      let resultList = []
      for (let i = 0; i < list.length; i++) {
        let data = list[i]
        let rawDataList = []
        // 每个子元素都和父元素合并成一条数据
        if (data.children && data.children.length > 0) {
          for (let j = 0; j < data.children.length; j++) {
            delete data.children[j].bsm
            let copy = Object.assign({}, data, data.children[j])
            rawDataList.push(copy)
            copy['rowSpan'] = j > 0 ? 0 : data.children.length
          }
        } else {
          data['rowSpan'] = 1
          rawDataList.push(data)
        }
        resultList.push(...rawDataList)
        if (typeof eachDataCallBack === 'function') {
          eachDataCallBack(rawDataList)
        }
      }
      return resultList
    },
    // 扁平头部
    flat(columns) {
      let result = []
      columns && columns.forEach(e => {
        if (e.hasOwnProperty('children')) {
          if (e.children && e.children.length > 0) {
            result.push(...this.flat(e.children))
          } else {
            result.push(e)
          }
        } else if (e.hasOwnProperty('prop')) {
          result.push(e)
        }
        // else if (e.hasOwnProperty('exeFun')) {
        //   result.push(e)
        // } else if (e.hasOwnProperty('prop')) {
        //   result.push(e)
        // }
      })
      return result
    },
    s2ab(s) {
      let buf = new ArrayBuffer(s.length)
      let view = new Uint8Array(buf)
      for (let i = 0; i !== s.length; ++i) {
        view[i] = s.charCodeAt(i) & 0xff
      }
      return buf
    },
    openDownloadXLSXDialog(url, saveName) {
      if (typeof url == 'object' && url instanceof Blob) {
        url = URL.createObjectURL(url) // 创建blob地址
      }
      var aLink = document.createElement('a')
      aLink.href = url
      aLink.download = saveName || '' // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，file:///模式下不会生效
      var event
      if (window.MouseEvent) {
        event = new MouseEvent('click')
      } else {
        event = document.createEvent('MouseEvents')
        event.initMouseEvent(
          'click',
          true,
          false,
          window,
          0,
          0,
          0,
          0,
          0,
          false,
          false,
          false,
          false,
          0,
          null
        )
      }
      aLink.dispatchEvent(event)
    }
  }
}
</script>

在组件中使用

<Export
        ref="Export"
        :name="测试的数据"
        :columns="columns"
        :data-source="ReportDataList"
      />

// columns的使用方法

 [
      {
        name: 'xxxx',
        prop: 'XXXX'
      },
      {
        name: 'xxxx',
        prop: 'XXXX'
      }
    ]
// data-source为导出的数据