fs-admin/src/hooks/useExcel.ts

import ExcelJS from 'exceljs'

/* 导出的参数 */
export interface IExcel {
  // 文件名称
  fileName?: string
  // 表头数据
  rows: Array<Array<string | number>>
  // 表格数据
  data: Array<Array<any>>
  // 表格列宽
  width?: Array<number>
  // 需要合并的表格
  merge?: Array<any>
}

export const useExcel = () => {
  /**
   * 导入excel
   * @param file 传入文件
   * @param fileMaxSize number 最大文件大小 单位 MB
   */
  const importExcel = (file: File, fileMaxSize: number = 20) => {
    return new Promise((resolve, reject) => {
      if (
        !['application/vnd.ms-excel', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'].includes(
          file.type
        )
      ) {
        reject(new Error('只能选择 excel 文件'))
        return
      }
      if (file.size / 1024 / 1024 > fileMaxSize) {
        reject(new Error('大小不能超过 20MB'))
        return
      }
      const reader = new FileReader()
      reader.onload = e => {
        const workbook = new ExcelJS.Workbook()
        workbook.xlsx.load(e.target?.result as any).then(() => {
          const title: string[] = []
          const list: Record<string, any>[] = []
          workbook.eachSheet(sheet => {
            list.splice(0, list.length)
            sheet.eachRow({ includeEmpty: true }, row => {
              title.splice(0, title.length)
              const item: Record<string, any> = {}
              row.eachCell({ includeEmpty: true }, cell => {
                const key = cell.address.slice(0, -String(cell.row).length)
                title.push(key)
                const isMerged = cell.isMerged && cell.address !== cell.master.address
                item[key] = {
                  value: cell.value,
                  colspan: isMerged ? 0 : 1,
                  rowspan: isMerged ? 0 : 1
                }
              })
              list.push(item)
            })
            // 生成表格的跨行跨列
            sheet.eachRow({ includeEmpty: true }, row => {
              row.eachCell({ includeEmpty: true }, cell => {
                const master = cell.master
                if (cell.isMerged && cell.address !== master.address) {
                  const mk = master.address.slice(0, -String(master.row).length)
                  const mItem = list[Number(master.row) - 1][mk]
                  const rs = Math.abs(Number(cell.row) - Number(master.row)) + 1
                  rs > mItem.rowspan && (mItem.rowspan = rs)
                  const cs = Math.abs(Number(cell.col) - Number(master.col)) + 1
                  cs > mItem.colspan && (mItem.colspan = cs)
                }
              })
            })
          })
          resolve({ title, list })
        })
      }
      reader.readAsArrayBuffer(file)
    })
  }

  /**
   * 导出excel
   */
  const exportExcel = (options: IExcel) => {
    return new Promise((resolve, reject) => {
      options = Object.assign(
        {
          fileName: options.fileName || Date.now()
        },
        options
      )
      const workbook = new ExcelJS.Workbook()
      const sheet = workbook.addWorksheet('Sheet1')
      if (!(Array.isArray(options.rows) && Array.isArray(options.rows[0]))) {
        reject(new Error('表头数据必须为二维数组'))
        return
      }
      if (!Array.isArray(options.data)) {
        reject(new Error('表格数据必须为数组'))
        return
      }
      // 设置表格宽度
      const widths =
        Array.isArray(options.width) && options.width.length
          ? options.width
          : Array.from(Array(options.rows[0].length).keys()).map(() => 16)

      // 设置表头
      options.rows.forEach(row => {
        sheet.addRow(row)
      })

      // 设置列宽
      options.data.forEach(item => {
        sheet.addRow(item)
      })

      // 设置列宽
      widths.forEach((width, index) => {
        sheet.getColumn(index + 1).width = width
      })

      // 设置样式
      sheet.eachRow({ includeEmpty: true }, (row, rowIndex) => {
        row.height = 20
        row.eachCell({ includeEmpty: true }, cell => {
          cell.border = {
            top: { style: 'thin' },
            left: { style: 'thin' },
            bottom: { style: 'thin' },
            right: { style: 'thin' }
          }
          cell.alignment = {
            vertical: 'middle',
            horizontal: 'center'
          }
          cell.font = { size: 12, bold: rowIndex < options.rows.length }
        })
      })

      // 合并单元格
      if (Array.isArray(options.merge)) {
        options.merge.forEach(item => {
          sheet.mergeCells(item)
        })
      }
      // 下载文件
      workbook.xlsx.writeBuffer().then(data => {
        download(data, `${options.fileName}.xlsx`)
      })
    })
  }

  /* 转为二维数组 */
  const toTwoArray = (list: Record<string, any>[]) => {
    const arr: Array<Array<string | number | null | undefined>> = []
    list.forEach((item: any) => {
      const brr = Object.values(item).map((ite: any) => ite.value)
      arr.push(brr)
    })
    return arr
  }

  /**
   * 下载文件
   * @param data 二进制数据,如果传入是http地址，则直接下载
   * @param name 文件名
   * @param type 文件类型
   */
  const download = (data: Blob | ArrayBuffer | string, name: string, type?: string) => {
    let url = ''
    if (typeof data === 'string') {
      url = data
    } else {
      const blob = new Blob([data], { type: type || 'application/octet-stream' })
      url = window.URL.createObjectURL(blob)
    }
    const a = document.createElement('a')
    a.href = url
    a.download = name
    a.style.display = 'none'
    document.body.appendChild(a)
    a.click()
    document.body.removeChild(a)
    URL.revokeObjectURL(url)
  }

  return { importExcel, toTwoArray, exportExcel, download }
}