一、介绍

exceljs 是一款可导出，可读取的 Excel 操作工具，可以实现样式的修改以及 Excel 的高级功能。 本代码基于antDesign.table实现。

解决：

1.加了表头的问题(177)

{
    title: 'parent',
    children: [
      {
          title: 'a',
          dataIndex: 'a',
          key: 'a',
          ...
      }
    ]
}

2.列数多的问题(55) excel A-Z后面的列为AA-AZ再到BA-BZ,CA-CZ...需要再次遍历。

二、代码

<!--单个表格下载功能-->

<template>
  <a-button :loading="loading"
            @click="downLoad"
            style="font-size: 12px"
            :style="{height : `${data[3]?data[3]:24}px`}"
  >
    {{ data[1] }}
  </a-button>
</template>

<script>
import ExcelJS from 'exceljs'
import FileSaver from 'file-saver'

export default {
  name: 'down-load',
  props: {
    //data:[table数据，标题，excel标题，height]
    //excel格式:[{ rowNumber: 1,rowFmt: '0.00%'}]  rowNumFmtX:行数 rowNumFmtY:列数
    data: {
      type: Array,
      default: () => {
        return [
          {
            columns: [],
            dataSource: [],
          },
          '下载',
          '明细',
          24,
        ]
      }
    },
    rowNumFmtX: {
      type: Array,
      default: () => {
        return []
      }
    },
    rowNumFmtY: {
      type: Array,
      default: () => {
        return []
      }
    },
  },
  data () {
    return {
      loading: false,
    }
  },
  methods: {
    changeStr (str) {
      // 数字转字母、字母转数字  A-Z后处理成 AA-AZ BA-BZ。。。
      if (isNaN(str * 1)) {
        if (str.length === 1) return str.charCodeAt(0) - 64
        else if (/^[A-Z]+$/.test(str)) {
          let result = 0
          for (let char of str) {
            result = result * 26 + (char.charCodeAt(0) - 64)
          }
          return result
        }
      } else {
        let num = parseInt(str * 1)
        let result = ''
        while (num > 0) {
          let remainder = (num - 1) % 26 // 计算余数并调整以使1对应'A'
          result = String.fromCharCode(65 + remainder) + result // 将余数转换为对应的大写字母，并添加到结果字符串的前面
          num = Math.floor((num - remainder) / 26) // 更新num以处理下一轮的计算
        }
        return result || 'A' // 如果num为0，则默认返回'A'
      }
    },
    //判断是否有children
    hasChildren (obj) {
      return Object.prototype.hasOwnProperty.call(obj, 'children')
    },
    //获取层数
    depthFun (col) {
      if (this.hasChildren(col)) {
        return 1 + Math.max(...col.children.map(cIt => this.depthFun(cIt)))
      }
      return 1
    },
    //设置层数
    setRowIndex(columns,depth){
      // 设置rowSpan
      const setRowSpan = (obj, i = 1) => {
        if (this.hasChildren(obj)) {
          obj.children.forEach(item => {
            setRowSpan(item, i + 1)
          })
          obj.rowSpan = 1
        } else {
          obj.rowSpan = depth - i + 1
        }
        obj.rowIndex = i
      }
      // 设置RowSpan
      const setColSpan = (obj) => {
        let fun = (o) => {
          if (this.hasChildren(o)) {
            return o.children.map(item => fun(item)).reduce((a, b) => a + b)
          } else return 1
        }
        if (this.hasChildren(obj)) {
          obj.colSpan = fun(obj)
          obj.children.forEach(item => setColSpan(item))
        } else {
          obj.colSpan = 1
        }
      }

      columns.forEach(it => setRowSpan(it))
      columns.forEach(it => setColSpan(it))

      const setStart = (it, i, arr, parentStart = 0) => {
        const { changeStr } = this
        const start = changeStr(arr.slice(0, i).map(_ => _.colSpan).reduce((a, b) => a + b, 1 + parentStart))
        const end = changeStr(changeStr(start) + it.colSpan - 1)
        it.start = start
        it.end = end
        it.startIndex = start + it.rowIndex
        it.endIndex = end + (it.rowIndex + it.rowSpan - 1)
        if (this.hasChildren(it)) {
          it.children.forEach((cit, ci) => setStart(cit, ci, it.children, changeStr(start) - 1))
        }
      }
      columns.forEach((it, i) => setStart(it, i, columns))
      return columns
    },
    downLoad () {
      this.loading = true
      let tables = [].concat(this.data[0])
      const _workbook = new ExcelJS.Workbook()

      // 添加工作表
      const _sheet1 = _workbook.addWorksheet('sheet1')

      //展开column children 获取所有column
      let allColumns = []
      function expandColumns (columnsToExpand) {
        for (let column of columnsToExpand) {
          if (Array.isArray(column.children)) {
            expandColumns(column.children)
          } else {
            allColumns.push(column)
          }
        }
      }
      expandColumns(tables[0].columns)

      // columns
      let _sheet1Columns = []
      allColumns.forEach((item, index) => {
        _sheet1Columns.push({
          header: item.title,
          key: 'a' + index,
          width: 15,
          style: { alignment: { vertical: 'middle', horizontal: item?.align || 'left' } }
        })
      })
      _sheet1.columns = _sheet1Columns

      // 删除第一行
      _sheet1.spliceRows(0, 1)

      // 获取层数
      const depth = Math.max(...tables[0].columns.map(item => this.depthFun(item)))

      // 设置层数 行数从1开始 列数从A开始
      const newColumns = this.setRowIndex(tables[0].columns,depth)

      // 设置空白行 根据newColumns填入表头
      _sheet1.addRows(Array(depth).fill({}).map(() => {}))
      const setHeader = (obj) => {
        if (this.hasChildren(obj)) {
          obj.children.forEach(item => setHeader(item))
        }
        const row = _sheet1.getRow(obj.rowIndex)
        const cell = row.getCell(obj.start)
        cell.value = obj.title
        cell.style = {alignment: {vertical: 'middle', horizontal: 'center'}}
        if (obj.startIndex !== obj.endIndex) {
          _sheet1.mergeCells(`${obj.startIndex}:${obj.endIndex}`);
        }
      }
      newColumns.forEach(item => setHeader(item))

      // 数据
      tables.forEach(item => {
        //prepend为合计行
        [...(item.prepend || []), ...item.dataSource].forEach(data => {
          let row = {}
          allColumns.forEach((column, di) => {
            row['a' + (di)] = data[column.dataIndex]
          })
          _sheet1.addRow(row)
        })
      })

      // 设置表格单位样式
      if (this.rowNumFmtX.length >= 1) {
        this.rowNumFmtX.forEach(item => {
          _sheet1.getRow(item?.rowNumber).numFmt = item?.rowFmt
        })
      }
      if (this.rowNumFmtY.length >= 1) {
        this.rowNumFmtY.forEach(item => {
          _sheet1.getColumn(item?.rowNumber).numFmt = item?.rowFmt
        })
      }
      // 导出表格
      _workbook.xlsx.writeBuffer().then((buffer) => {
        let _file = new Blob([buffer], {
          type: 'application/octet-stream',
        })
        FileSaver.saveAs(_file, `${this.data[2]}.xlsx`)
      })
      this.loading = false
    }
  }
}
</script>

<style scoped>

</style>