前端文件导出

分享一下 xlsx 这个库的常见使用场景，仅代表个人使用习惯，不喜勿喷

一、useExcel Hook封装

// src/hooks/useExcel.ts
import { getTypeOf, isAvailableArr } from '@/utils'
import { message } from 'antd'
import type { BookType, ColInfo, RowInfo, WorkBook, Range } from 'xlsx'
import * as XLSX from 'xlsx'

// 导出文件配置
export type IExportConfig = {
  name?: string // 导出文件名称
  bookType?: BookType // 导出文件类型
  sheetName?: string // sheet名称
  errorMsg?: string // 错误提示
  headers?: Record<string, string> // 自定义表头，导出的文件里面只有在定义中的字段，并且如果数据为空的话，只生成一个表头。示例：{id: 'ID', name: '链接名称', site_type: '官网类型'}
  merges?: Range[] // 单元格合并
  colInfo?: ColInfo[] // 列属性
  rowInfo?: RowInfo[] // 行属性
}

// 多sheet导出
export type IExtraSheetConfig = {
  name?: string // 导出文件名称
  bookType?: BookType // 导出文件类型
  sheets: ({ json: any[] } & Omit<
    IExportConfig,
    'name' | 'bookType'
  >)[]
}

/**
 * @desc 自定义导出文件hook
 */
const useExcel = () => {
  // 一维数组导出
  function exportJson2Excel<T = any>(json: T[], config?: IExportConfig) {
    const {
      name = '导出',
      sheetName = 'Sheet1',
      bookType = 'xlsx',
      headers,
      merges,
      colInfo,
      rowInfo,
    } = config || {}
    let lists = [...json]
    if (
      headers &&
      getTypeOf(headers) === 'Object' &&
      isAvailableArr(Object.keys(headers))
    ) {
      // 没有数据的时候用header去生成一个空表头
      if (!isAvailableArr(lists)) {
        const headersField = Object.values(headers)
        const headerObj: Record<string, any> = {}
        headersField.forEach((f) => {
          headerObj[f] = null
        })
        lists = [headerObj as T]
      } else {
        // 有数据的时候根据header去生成
        const headerFields = Object.entries(headers)
        lists = json.map((j) => {
          const obj: Record<string, any> = {}
          for (const [key, value] of headerFields) {
            obj[value] = j[key as keyof T] ?? null
          }
          return obj
        }) as T[]
      }
    }

    const wb = XLSX.utils.book_new()
    const ws = XLSX.utils.json_to_sheet(lists)
    if (merges) {
      ws['!merges'] = merges
    }
    if (colInfo) {
      ws['!cols'] = colInfo
    }
    if (rowInfo) {
      ws['!rows'] = rowInfo
    }
    XLSX.utils.book_append_sheet(wb, ws, sheetName)
    XLSX.writeFile(wb, `${name}.${bookType}`, { bookType })
  }

  // 一维数组多sheet导出
  function exportJson2ExcelSheets<T = any>(params: IExtraSheetConfig) {
    const { name = '导出', bookType = 'xlsx', sheets } = params || {}
    const wb = XLSX.utils.book_new()
    if (isAvailableArr(sheets)) {
      sheets?.forEach((s) => {
        const { json, headers, merges, colInfo, rowInfo, sheetName } = s || {}
        let lists = [...json]
        if (
          headers &&
          getTypeOf(headers) === 'Object' &&
          isAvailableArr(Object.keys(headers))
        ) {
          // 没有数据的时候用header去生成一个空表头
          if (!isAvailableArr(lists)) {
            const headersField = Object.values(headers)
            const headerObj: Record<string, any> = {}
            headersField.forEach((f) => {
              headerObj[f] = null
            })
            lists = [headerObj as T]
          } else {
            // 有数据的时候根据header去生成
            const headerFields = Object.entries(headers)
            lists = json.map((j) => {
              const obj: Record<string, any> = {}
              for (const [key, value] of headerFields) {
                obj[value] = j[key] ?? null
              }
              return obj
            }) as T[]
          }
        }

        const ws = XLSX.utils.json_to_sheet(lists)
        if (merges) {
          ws['!merges'] = merges
        }
        if (colInfo) {
          ws['!cols'] = colInfo
        }
        if (rowInfo) {
          ws['!rows'] = rowInfo
        }
        XLSX.utils.book_append_sheet(wb, ws, sheetName)
      })
    } else {
      const ws = XLSX.utils.json_to_sheet([])
      XLSX.utils.book_append_sheet(wb, ws)
    }

    XLSX.writeFile(wb, `${name}.${bookType}`, { bookType })
  }

  // 二维数组导出
  function exportAoa2Excel<T = any>(aoas: T[][], config?: IExportConfig) {
    const {
      name = '导出',
      sheetName = 'Sheet1',
      bookType = 'xlsx',
      merges,
      colInfo,
      rowInfo,
    } = config || {}

    const wb = XLSX.utils.book_new()
    const ws = XLSX.utils.aoa_to_sheet(aoas)
    if (merges) {
      ws['!merges'] = merges
    }
    if (colInfo) {
      ws['!cols'] = colInfo
    }
    if (rowInfo) {
      ws['!rows'] = rowInfo
    }

    XLSX.utils.book_append_sheet(wb, ws, sheetName)
    XLSX.writeFile(wb, `${name}.${bookType}`, { bookType })
  }

  // 读取本地excel文件
  function readWorkbookFromLocalFile(
    file: File | Blob,
  ): Promise<WorkBook | false> {
    return new Promise((resolve) => {
      const reader = new FileReader()
      reader.readAsBinaryString(file)
      reader.onload = function (e) {
        const data = (e.target as any)?.result
        const workbook = XLSX.read(data, {
          type: 'binary',
          raw: true,
          cellNF: true,
        })
        resolve(workbook)
      }
      reader.onerror = function () {
        resolve(false)
      }
    })
  }

  // 文件流导出
  async function exportBuffer2Excel(file: File | Blob, config?: IExportConfig) {
    const {
      name = '导出',
      bookType = 'xlsx',
      errorMsg = '下载失败',
    } = config || {}
    const wb: WorkBook | false = await readWorkbookFromLocalFile(file)
    if (wb) {
      XLSX.writeFile(wb, `${name}.${bookType}`, { bookType })
    } else {
      message.error(errorMsg)
    }
  }

  // 文件完整路径导出
  async function exportUrl2Excel(url: string, config?: IExportConfig) {
    fetch(url)
      .then((response) => response.blob())
      .then((blob) => {
        exportBuffer2Excel(new Blob([blob]), config)
      })
      .catch((error) => {
        throw error
      })
  }

  return {
    exportJson2Excel,
    exportAoa2Excel,
    exportBuffer2Excel,
    exportJson2ExcelSheets,
    exportUrl2Excel,
  }
}

export default useExcel

二、使用场景

以下使用示例都是基于上面的 useExcel

模拟的一条导出数据

const exportDatas = [
  {
    id: 6,
    name: '假期/周末',
    uniq_key: 2,
    status: 1,
    admin_name: '王光环',
    last_update_time: 1733900693,
  },
  {
    id: 52,
    name: '12345678901234567890',
    uniq_key: 32141,
    status: 1,
    admin_name: '王光环',
    last_update_time: 1723458626,
  },
  {
    id: 31,
    name: '王林1',
    uniq_key: 43,
    status: 0,
    admin_name: '王光环',
    last_update_time: 1723458305,
  },
  {
    id: 24,
    name: '王林',
    uniq_key: 44,
    status: 0,
    admin_name: '王光环',
    last_update_time: 1723458246,
  },
  {
    id: 50,
    name: 'dsfgj',
    uniq_key: 23,
    status: 0,
    admin_name: '朱宇航',
    last_update_time: 1723457090,
  },
  {
    id: 45,
    name: '泥瓦工个',
    uniq_key: 32,
    status: 0,
    admin_name: '王光环',
    last_update_time: 1723451897,
  },
  {
    id: 44,
    name: '生产制造隔热',
    uniq_key: 123421,
    status: 0,
    admin_name: '王光环',
    last_update_time: 1723451890,
  },
  {
    id: 42,
    name: '哥人',
    uniq_key: 121,
    status: 0,
    admin_name: '王光环',
    last_update_time: 1723451876,
  },
  {
    id: 40,
    name: '亮反而',
    uniq_key: 22,
    status: 0,
    admin_name: '王光环',
    last_update_time: 1723451857,
  },
  {
    id: 39,
    name: '生产制造热',
    uniq_key: 156,
    status: 0,
    admin_name: '王光环',
    last_update_time: 1723451849,
  },
  {
    id: 38,
    name: 'v个人头',
    uniq_key: 21,
    status: 0,
    admin_name: '王光环',
    last_update_time: 1723451840,
  },
  {
    id: 37,
    name: '而非',
    uniq_key: 1232,
    status: 0,
    admin_name: '王光环',
    last_update_time: 1723451832,
  },
  {
    id: 36,
    name: '道路工/公路工/铁路工反而',
    uniq_key: 12343,
    status: 0,
    admin_name: '王光环',
    last_update_time: 1723451826,
  },
  {
    id: 35,
    name: '泥瓦工非',
    uniq_key: 15,
    status: 0,
    admin_name: '王光环',
    last_update_time: 1723451818,
  },
  {
    id: 34,
    name: '道路工/公路工/铁路工',
    uniq_key: 14,
    status: 0,
    admin_name: '王光环',
    last_update_time: 1723451811,
  },
  {
    id: 33,
    name: '淡而无味',
    uniq_key: 11,
    status: 0,
    admin_name: '王光环',
    last_update_time: 1723451802,
  },
  {
    id: 4,
    name: '一日一结',
    uniq_key: 1,
    status: 1,
    admin_name: '王光环',
    last_update_time: 1723449520,
  },
  {
    id: 12,
    name: '在线兼职',
    uniq_key: 8,
    status: 1,
    admin_name: '朱宇航',
    last_update_time: 1723445457,
  },
  {
    id: 7,
    name: '手艺活',
    uniq_key: 3,
    status: 1,
    admin_name: '朱宇航',
    last_update_time: 1723432946,
  },
  {
    id: 10,
    name: '在家兼职',
    uniq_key: 6,
    status: 1,
    admin_name: '朱宇航',
    last_update_time: 1723172619,
  },
]

const headers = {
  id: 'ID',
  name: '链接名称',
  uniq_key: '唯一标识',
  status: '状态',
  admin_name: '编辑人',
  last_update_time: '最新编辑时间',
}

1、一维数组导出

/**
   * @name: exportJson2ExcelHandler
   * @description: 一维数组导出
   */
  const exportJson2ExcelHandler = () => {
    exportJson2Excel(exportDatas, {
      headers,
      name: '一维数组导出数据',
      sheetName: '一维数组导出数据',
    })
  }

2、一维数组多sheet导出

/**
* @name: exportJson2ExcelSheetsHandler
* @description: 一维数组多Sheet导出
*/
const exportJson2ExcelSheetsHandler = () => {
const tip = `模板说明：\n
1、“企业id”填写说明：必填，需填写正确\n
2、“企业名称”填写说明：选填\n
3、“第三方平台”&“该企业的第三方id”填写说明：选填，目前第三方平台限填纷享销客`
const template = [
  {
    企业名称: null,
    企业id: null,
    第三方平台: null,
    该企业的第三方id: null,
    该企业的第三方名称: null,
    模板说明: tip,
  },
]
const merges = [{ s: { r: 1, c: 5 }, e: { r: 9, c: 5 } }]
const colInfo = [
  { width: 20 },
  { width: 40 },
  { width: 20 },
  { width: 20 },
  { width: 20 },
  { width: 40 },
]

exportJson2ExcelSheets({
  name: '一维数组多Sheet导出数据',
  sheets: [
    { json: template, sheetName: '模板', merges, colInfo },
    {
      headers,
      json: exportDatas,
      sheetName: '数据',
    },
  ],
})
}

3、简单的模板下载

/**
* @name: downloadTemplate1
* @description: 简单的模板下载
*/
const downloadTemplate1 = async () => {
const name = '切词导入模板'
const sheetName = '导入模板'
const bookType = 'xlsx'
const merges = [{ s: { r: 1, c: 3 }, e: { r: 7, c: 3 } }]
const colInfo = [{ width: 20 }, { width: 20 }, { width: 20 }, { width: 50 }]
const tip = `1.“所属词包”填写说明：词包名称需与“词表词包管理”模块中的词包名称一致；\n
2. “状态”中，选项有待启用，已启用 \n（1）“待启用”状态的切词，将不被用户端取用 \n（2）“已启用”状态的切词，将被用户端取用`
const template = [
  {
    '切词（必填）': null,
    '所属词包（选填）': null,
    '状态（必填）': null,
    模板使用说明: tip,
  },
]
exportJson2Excel(template, { name, sheetName, merges, colInfo, bookType })
}

4、复杂的模板下载

// 工种没获取到的时候
  const withoutWorksTemplate = () => {
    const name = '搜索热词导入模板'
    const sheetName = '导入模板'
    const bookType = 'xlsx'
    const merges = [{ s: { r: 1, c: 2 }, e: { r: 7, c: 2 } }]
    const colInfo = [{ width: 20 }, { width: 20 }, { width: 40 }, { width: 30 }]
    const tip = `1.“适用工种”填写说明：\n
（1）最右侧为最新的二级工种名称，请以此在“适用工种”中，录入正确的二级工种名称。\n
（2）录入多个工种时，需使用英文逗号隔开`
    const template = [
      {
        '工种定向热词（必填）': '测试',
        '适用工种（必填）': '二级工种1,二级工种2',
        模板使用说明: tip,
      },
    ]
    exportJson2Excel(template, { name, sheetName, merges, colInfo, bookType })
  }

  /**
   * @name: downloadTemplate
   * @description: 复杂的模板下载
   */
  const downloadTemplate = () => {
    const name = '搜索热词导入模板'
    const sheetName = '导入模板'
    const bookType = 'xlsx'
    const merges = [{ s: { r: 1, c: 2 }, e: { r: 7, c: 2 } }]
    const colInfo = [{ width: 20 }, { width: 20 }, { width: 40 }, { width: 30 }]
    const tip = `1.“适用工种”填写说明：\n
（1）最右侧为最新的二级工种名称，请以此在“适用工种”中，录入正确的二级工种名称。\n
（2）录入多个工种时，需使用英文逗号隔开`

    toggleLoading()

    getAllWorks({ level: 2, online: 1 })
      .then((res) => {
        const { code, result } = res || {}
        if (code !== 0) return

        if (isAvailableArr(result.list)) {
          const header = [
            '工种定向热词（必填）',
            '适用工种（必填）',
            '模板使用说明',
            '最新二级工种列表',
          ]
          const workNames = result.list.map((l: any) => l.name)
          const contents = workNames.map((w: string, index: number) => {
            if (index === 0) {
              return ['测试', '二级工种1,二级工种2', tip, w]
            }
            return [null, null, null, w]
          })
          const template = [header, ...contents]
          exportAoa2Excel(template, {
            name,
            sheetName,
            merges,
            colInfo,
            bookType,
          })
        } else {
          withoutWorksTemplate()
        }
      })
      .catch(() => {
        withoutWorksTemplate()
      })
      .finally(toggleLoading)
  }
  
  // getAllWorks为一个接口，不用在意，我们最终只是想取到里面的值而已，没有该接口我们也可以模拟一个对象数组 ，只需要里面有name属性就行

5、文件流导出

/**
   * @name: exportCardData
   * @description: 导出统计明细(文件流导出)
   */
  const exportCardData = async () => {
    toggleLoading()
    try {
      const res = await exportCardDatas({ pg: 1, pagesize: 100, export: 1 })
      if (!res.code) {
        exportBuffer2Excel(new Blob(['\ufeff', res as any]), {
          name: '卡片统计明细',
          bookType: 'xlsx',
        })
      } else {
        message.error('导出统计明细失败')
      }
    } catch {
      //
    } finally {
      toggleLoading()
    }
  }
  
  // exportCardDatas是一个返回值为文件流的接口，可以自己模拟

6、文件地址下载

/**
   * @name: downloadTempUrl
   * @description: 文件地址下载(网络请求导出)
   */
  const downloadTempUrl = () => {
    const temp_url =
      'https://jgj-store.oss-cn-beijing.aliyuncs.com/operation/manual_rule/demo.xlsx'
    exportUrl2Excel(temp_url, { name: '手动触达推送人群导入模板' })
  }