基于xlsx封装上传和下载excel

蓝色海岛
244 阅读8分钟

一、安装

npm i xlsx@0.17.0

二、封装模块

fileTools.ts

import * as XLSX from 'xlsx'

export const getXlsxData = (file) => {
  return new Promise((resolve, reject) => {
    const fileReader = new FileReader()
    fileReader.onload = (event) => {
      try {
        let data = [] // 存储获取到的数据
        const result = event.target?.result
        // 以二进制流方式读取得到整份excel表格对象
        const workbook = XLSX.read(result, { type: 'binary' })
        // 遍历每张工作表进行读取（这里默认只读取第一张表）
        for (const sheet in workbook.Sheets) {
          if (workbook.Sheets.hasOwnProperty(sheet)) {
            // 利用 sheet_to_json 方法将 excel 转成 json 数据
            const sheetJson = XLSX.utils.sheet_to_json(workbook.Sheets[sheet], { defval: '' })
            // console.log("sheetJson>>", sheetJson)
            data = data.concat(sheetJson)
            // break; // 如果只取第一张表，就取消注释这行
          }
        }
        resolve(data)
      } catch (e) {
        // 这里可以抛出文件类型错误不正确的相关提示
        console.log('文件类型不正确')
        reject(e)
      }
    }
    // 以二进制方式打开文件
    fileReader.readAsBinaryString(file)
  })
}

export function downloadXlsx(tHeader, filterVal, data, fileName) {
  //tHeader 表头 data文件数据列表 fileName文件名称
  const table = []
  const obj = {}
  tHeader.forEach((el, index) => {
    const str = String.fromCharCode(index + 65)
    obj[str] = el
  })
  table.push(obj)
  const exportData = formatJson(filterVal, data)
  exportData.forEach((arr) => {
    const row = {}
    arr.forEach((el, index) => {
      const str = String.fromCharCode(index + 65)
      row[str] = el
    })
    table.push(row)
  })
  // 创建book
  const wb = XLSX.utils.book_new()
  // json转sheet
  const ws = XLSX.utils.json_to_sheet(table, { header: Object.keys(obj), skipHeader: true })
  // 设置列宽
  ws['!cols'] = new Array(Object.keys(obj).length).fill({ width: 15 })
  // sheet写入book
  XLSX.utils.book_append_sheet(wb, ws, 'file')
  // 输出
  const name = fileName || '文件下载'
  XLSX.writeFile(wb, name + '.xlsx')
}

const formatJson = (filterVal: Array<string>, jsonData: Array<any>) =>
  jsonData.map((v) =>
    v && Object.keys(v).length
      ? filterVal.map((j) => (v[j] === undefined || v[j] === null ? '' : v[j]))
      : []
  )

三、下载excel

import { downloadXlsx } from '@/utils/fileTools'

  const handleExport = async () => {
    setExportLoading(true)
    const tHeader = [
      '部门',
      '归属人',
      '目标学校',
      '客户等级',
      '新签/续签',
      '上学年星级',
      '目标星级',
      '当前星级',
      '合作时星级',
      '本学年合作产值（万元）',
      '上学年合作产值（万元）',
      '本学年总产值（万元）',
      '上学年总产值（万元）'
    ]
    const filterVal = [
      'deptName',
      'realName',
      'customerName',
      'levelName',
      'businessTypeName',
      'lastStar',
      'targetStar',
      'currentStar',
      'cooperateStar',
      'currentCooperateSales',
      'lastCooperateSales',
      'currentSales',
      'lastSales'
    ]
    const { success, data } = await listApi(requestParams)
    if (success) {
      downloadXlsx(tHeader, filterVal, data, '客户星级总表-明细数据')
      message.success('导出成功')
      setExportLoading(false)
    }
  }

data数据结构

点击查看详细内容 
[
    {
        "customerId": 368,
        "customerName": "凌云县高级中学",
        "userId": 138,
        "realName": "测试员工108",
        "deptName": "新模式-川鄂分公司-省区分公司3-区域11",
        "levelName": "S",
        "businessTypeName": "新签",
        "lastStar": 1,
        "targetStar": 0,
        "currentStar": 0,
        "cooperateStar": 0,
        "currentCooperateSales": 0,
        "lastCooperateSales": 0,
        "currentSales": 4.75,
        "lastSales": 0
    },
    {
        "customerId": 372,
        "customerName": "平果高中",
        "userId": 140,
        "realName": "测试员工110",
        "deptName": "新模式-川鄂分公司-省区分公司3-区域11",
        "levelName": "S",
        "businessTypeName": "新签",
        "lastStar": 4,
        "targetStar": 0,
        "currentStar": 0,
        "cooperateStar": 0,
        "currentCooperateSales": 0,
        "lastCooperateSales": 0,
        "currentSales": 2.82,
        "lastSales": 0
    },
    {
        "customerId": 694,
        "customerName": "金沙一中",
        "userId": 247,
        "realName": "测试员工217",
        "deptName": "新模式-分公司2-省区分公司6-区域22",
        "levelName": "S",
        "businessTypeName": "新签",
        "lastStar": 5,
        "targetStar": 0,
        "currentStar": 0,
        "cooperateStar": 0,
        "currentCooperateSales": 0,
        "lastCooperateSales": 0,
        "currentSales": 0.91,
        "lastSales": 0
    },
    {
        "customerId": 785,
        "customerName": "沧州市第二中学",
        "userId": 277,
        "realName": "测试员工247",
        "deptName": "新模式-分公司2-省区分公司7-区域25",
        "levelName": "S",
        "businessTypeName": "新签",
        "lastStar": 5,
        "targetStar": 0,
        "currentStar": 0,
        "cooperateStar": 0,
        "currentCooperateSales": 0,
        "lastCooperateSales": 0,
        "currentSales": 1.79,
        "lastSales": 0
    },
    {
        "customerId": 789,
        "customerName": "沧州市第十一中学",
        "userId": 1812,
        "realName": "华中客户经理6",
        "deptName": "新模式-东北大区-东北省区1-东北区域2",
        "levelName": "S",
        "businessTypeName": "新签",
        "lastStar": 2,
        "targetStar": 5,
        "currentStar": 4,
        "cooperateStar": 3,
        "currentCooperateSales": 0,
        "lastCooperateSales": 0,
        "currentSales": 1.69,
        "lastSales": 0
    },
    {
        "customerId": 1198,
        "customerName": "北京师范大学成都实验中学",
        "userId": 420,
        "realName": "测试员工2383",
        "deptName": "新模式-分公司3-省区分公司10-区域39",
        "levelName": "S",
        "businessTypeName": "新签",
        "lastStar": 2,
        "targetStar": 0,
        "currentStar": 0,
        "cooperateStar": 0,
        "currentCooperateSales": 0,
        "lastCooperateSales": 0,
        "currentSales": 1.68,
        "lastSales": 0
    },
    {
        "customerId": 1205,
        "customerName": "成都七中",
        "userId": 422,
        "realName": "测试员工2385",
        "deptName": "新模式-分公司3-省区分公司10-区域39",
        "levelName": "S",
        "businessTypeName": "新签",
        "lastStar": 1,
        "targetStar": 0,
        "currentStar": 0,
        "cooperateStar": 0,
        "currentCooperateSales": 0,
        "lastCooperateSales": 0,
        "currentSales": 1.85,
        "lastSales": 0
    },
    {
        "customerId": 1585,
        "customerName": "大连市第十三中学",
        "userId": 549,
        "realName": "测试员工2512",
        "deptName": "新模式-分公司4-省区分公司13-区域52",
        "levelName": "S",
        "businessTypeName": "新签",
        "lastStar": 1,
        "targetStar": 4,
        "currentStar": 4,
        "cooperateStar": 3,
        "currentCooperateSales": 0,
        "lastCooperateSales": 21.87,
        "currentSales": 3.84,
        "lastSales": 21.87
    },
    {
        "customerId": 1695,
        "customerName": "广灵二中",
        "userId": 586,
        "realName": "测试员工2549",
        "deptName": "新模式-分公司4-省区分公司14-区域55",
        "levelName": "S",
        "businessTypeName": "新签",
        "lastStar": 4,
        "targetStar": 0,
        "currentStar": 0,
        "cooperateStar": 0,
        "currentCooperateSales": 0,
        "lastCooperateSales": 0,
        "currentSales": 1.46,
        "lastSales": 0
    },
    {
        "customerId": 1702,
        "customerName": "灵丘一中",
        "userId": 588,
        "realName": "测试员工2551",
        "deptName": "新模式-分公司4-省区分公司14-区域56",
        "levelName": "S",
        "businessTypeName": "新签",
        "lastStar": 3,
        "targetStar": 0,
        "currentStar": 0,
        "cooperateStar": 0,
        "currentCooperateSales": 0,
        "lastCooperateSales": 0,
        "currentSales": 2.23,
        "lastSales": 0
    },
    {
        "customerId": 1709,
        "customerName": "同煤集团第四中学",
        "userId": 590,
        "realName": "测试员工2553",
        "deptName": "新模式-分公司4-省区分公司14-区域56",
        "levelName": "S",
        "businessTypeName": "新签",
        "lastStar": 5,
        "targetStar": 0,
        "currentStar": 0,
        "cooperateStar": 0,
        "currentCooperateSales": 0,
        "lastCooperateSales": 0,
        "currentSales": 2.05,
        "lastSales": 0
    },
    {
        "customerId": 2192,
        "customerName": "阜阳市第三中学",
        "userId": 752,
        "realName": "测试员工2715",
        "deptName": "新模式-分公司5-省区分公司18-区域72",
        "levelName": "S",
        "businessTypeName": "新签",
        "lastStar": 2,
        "targetStar": 0,
        "currentStar": 0,
        "cooperateStar": 0,
        "currentCooperateSales": 0,
        "lastCooperateSales": 0,
        "currentSales": 0.51,
        "lastSales": 0
    },
    {
        "customerId": 2199,
        "customerName": "界首市第一中学",
        "userId": 755,
        "realName": "测试员工2718",
        "deptName": "新模式-分公司5-省区分公司18-区域72",
        "levelName": "S",
        "businessTypeName": "新签",
        "lastStar": 3,
        "targetStar": 0,
        "currentStar": 0,
        "cooperateStar": 0,
        "currentCooperateSales": 0,
        "lastCooperateSales": 0,
        "currentSales": 0.78,
        "lastSales": 0
    },
    {
        "customerId": 2283,
        "customerName": "宁都中学",
        "userId": 783,
        "realName": "测试员工2746",
        "deptName": "新模式-分公司5-省区分公司19-区域75",
        "levelName": "S",
        "businessTypeName": "新签",
        "lastStar": 1,
        "targetStar": 0,
        "currentStar": 0,
        "cooperateStar": 0,
        "currentCooperateSales": 0,
        "lastCooperateSales": 0,
        "currentSales": 0.77,
        "lastSales": 0
    },
    {
        "customerId": 2703,
        "customerName": "阿城市第二中学",
        "userId": 923,
        "realName": "测试员工2886",
        "deptName": null,
        "levelName": "S",
        "businessTypeName": "新签",
        "lastStar": 1,
        "targetStar": 0,
        "currentStar": 0,
        "cooperateStar": 0,
        "currentCooperateSales": 0,
        "lastCooperateSales": 0,
        "currentSales": 0.91,
        "lastSales": 0
    },
    {
        "customerId": 2787,
        "customerName": "木兰县东兴镇中学",
        "userId": 951,
        "realName": "测试员工2914",
        "deptName": null,
        "levelName": "S",
        "businessTypeName": "新签",
        "lastStar": 2,
        "targetStar": 5,
        "currentStar": 3,
        "cooperateStar": 3,
        "currentCooperateSales": 0,
        "lastCooperateSales": 0,
        "currentSales": 4.88,
        "lastSales": 0
    },
    {
        "customerId": 3200,
        "customerName": "河源市东源中学",
        "userId": 1088,
        "realName": "测试员工3051",
        "deptName": null,
        "levelName": "S",
        "businessTypeName": "新签",
        "lastStar": 5,
        "targetStar": 0,
        "currentStar": 0,
        "cooperateStar": 0,
        "currentCooperateSales": 0,
        "lastCooperateSales": 0,
        "currentSales": 1.9,
        "lastSales": 0
    },
    {
        "customerId": 3207,
        "customerName": "连平县实验中学",
        "userId": 1091,
        "realName": "测试员工3054",
        "deptName": null,
        "levelName": "S",
        "businessTypeName": "新签",
        "lastStar": 5,
        "targetStar": 0,
        "currentStar": 0,
        "cooperateStar": 0,
        "currentCooperateSales": 0,
        "lastCooperateSales": 0,
        "currentSales": 1.13,
        "lastSales": 0
    },
    {
        "customerId": 3284,
        "customerName": "八步区信都中学",
        "userId": 1116,
        "realName": "测试员工3079",
        "deptName": null,
        "levelName": "S",
        "businessTypeName": "新签",
        "lastStar": 3,
        "targetStar": 0,
        "currentStar": 0,
        "cooperateStar": 0,
        "currentCooperateSales": 0,
        "lastCooperateSales": 0,
        "currentSales": 0.74,
        "lastSales": 0
    },
    {
        "customerId": 3690,
        "customerName": "涟水县第二中学",
        "userId": 1252,
        "realName": "测试员工3215",
        "deptName": null,
        "levelName": "S",
        "businessTypeName": "新签",
        "lastStar": 2,
        "targetStar": 0,
        "currentStar": 0,
        "cooperateStar": 0,
        "currentCooperateSales": 0,
        "lastCooperateSales": 0,
        "currentSales": 1.64,
        "lastSales": 0
    },
    {
        "customerId": 3704,
        "customerName": "淮北市第二中学",
        "userId": 1256,
        "realName": "测试员工3219",
        "deptName": null,
        "levelName": "S",
        "businessTypeName": "新签",
        "lastStar": 5,
        "targetStar": 0,
        "currentStar": 0,
        "cooperateStar": 0,
        "currentCooperateSales": 0,
        "lastCooperateSales": 0,
        "currentSales": 1,
        "lastSales": 0
    },
    {
        "customerId": 3788,
        "customerName": "龙感湖一中",
        "userId": 1284,
        "realName": "测试员工3247",
        "deptName": null,
        "levelName": "S",
        "businessTypeName": "新签",
        "lastStar": 5,
        "targetStar": 0,
        "currentStar": 0,
        "cooperateStar": 0,
        "currentCooperateSales": 0,
        "lastCooperateSales": 0,
        "currentSales": 3.99,
        "lastSales": 0
    },
    {
        "customerId": 4194,
        "customerName": "台山市第一中学",
        "userId": 1420,
        "realName": "测试员工3383",
        "deptName": null,
        "levelName": "S",
        "businessTypeName": "新签",
        "lastStar": 1,
        "targetStar": 0,
        "currentStar": 0,
        "cooperateStar": 0,
        "currentCooperateSales": 0,
        "lastCooperateSales": 0,
        "currentSales": 1.46,
        "lastSales": 0
    },
    {
        "customerId": 4208,
        "customerName": "新会华侨中学",
        "userId": 1424,
        "realName": "测试员工3387",
        "deptName": null,
        "levelName": "S",
        "businessTypeName": "新签",
        "lastStar": 3,
        "targetStar": 0,
        "currentStar": 0,
        "cooperateStar": 0,
        "currentCooperateSales": 0,
        "lastCooperateSales": 0,
        "currentSales": 0.87,
        "lastSales": 0
    },
    {
        "customerId": 4698,
        "customerName": "通许县丽星中学",
        "userId": 1588,
        "realName": "测试员工3551",
        "deptName": null,
        "levelName": "S",
        "businessTypeName": "新签",
        "lastStar": 3,
        "targetStar": 0,
        "currentStar": 0,
        "cooperateStar": 0,
        "currentCooperateSales": 0,
        "lastCooperateSales": 0,
        "currentSales": 1.45,
        "lastSales": 0
    },
    {
        "customerId": 4712,
        "customerName": "北大附中云南实验学校",
        "userId": 1592,
        "realName": "测试员工3555",
        "deptName": null,
        "levelName": "S",
        "businessTypeName": "新签",
        "lastStar": 2,
        "targetStar": 0,
        "currentStar": 0,
        "cooperateStar": 0,
        "currentCooperateSales": 0,
        "lastCooperateSales": 0,
        "currentSales": 1.86,
        "lastSales": 0
    },
    {
        "customerId": 5195,
        "customerName": "东乡二中",
        "userId": 7,
        "realName": "首执测试",
        "deptName": null,
        "levelName": "S",
        "businessTypeName": "新签",
        "lastStar": 4,
        "targetStar": 0,
        "currentStar": 0,
        "cooperateStar": 0,
        "currentCooperateSales": 0,
        "lastCooperateSales": 0,
        "currentSales": 0.64,
        "lastSales": 0
    },
    {
        "customerId": 5202,
        "customerName": "康乐一中",
        "userId": 16,
        "realName": "小张2",
        "deptName": null,
        "levelName": "S",
        "businessTypeName": "新签",
        "lastStar": 3,
        "targetStar": 0,
        "currentStar": 0,
        "cooperateStar": 0,
        "currentCooperateSales": 0,
        "lastCooperateSales": 0,
        "currentSales": 1.1,
        "lastSales": 0
    },
    {
        "customerId": 13599,
        "customerName": "玉龙专用学校",
        "userId": 1776,
        "realName": "yyl1",
        "deptName": "新模式-分公司5，新模式-分公司2，新模式-东北大区，新模式",
        "levelName": "S",
        "businessTypeName": "新签",
        "lastStar": 1,
        "targetStar": 5,
        "currentStar": 5,
        "cooperateStar": 3,
        "currentCooperateSales": 0,
        "lastCooperateSales": 0,
        "currentSales": 0,
        "lastSales": 0
    }
]

四、上传

导入

import { getXlsxData } from '@/utils/fileTools'

DOM：上传按钮

          <Upload
            accept=".xls, .xlsx"
            beforeUpload={(file) => handleImport(file)}
          >
            <Button type="link">模板导入</Button>
          </Upload>

上传事件回调

  const handleImport = (file) => {
    getXlsxData(file).then(async (data) => {
      setLoading(true)
      const { success } = await importContactTagApi({ json: JSON.stringify(data) })
      setLoading(false)
      if (success) {
        tableRef?.current.reload()
        message.success('上传成功')
      }
    })
  }

五、导入导出时，excel头部有一行提示

  • 导出时，在第一行加上这句提示；
  • 导入时，将第一行过滤掉 image.png
  // 导入
  const getXlsxData = (file: any) => {
    return new Promise((resolve, reject) => {
      const fileReader = new FileReader()
      fileReader.onload = (event) => {
        try {
          let data: any = [] // 存储获取到的数据
          const result = event.target?.result
          // 以二进制流方式读取得到整份excel表格对象
          const workbook = XLSX.read(result, { type: 'binary' })
          // 遍历每张工作表进行读取（这里默认只读取第一张表）
          for (const sheet in workbook.Sheets) {
            if (workbook.Sheets.hasOwnProperty(sheet)) {
              // 利用 sheet_to_json 方法将 excel 转成 json 数据
              const sheetJson = XLSX.utils.sheet_to_json(workbook.Sheets[sheet], {
                defval: '', // 空数据默认值
                range: 1 // 从第几行开始读取
              })
              data = data.concat(sheetJson)
              // break; // 如果只取第一张表，就取消注释这行
            }
          }
          resolve(data)
        } catch (e) {
          // 这里可以抛出文件类型错误不正确的相关提示
          console.log('文件类型不正确')
          reject(e)
        }
      }
      // 以二进制方式打开文件
      fileReader.readAsBinaryString(file)
    })
  }

  // 导出
  const downloadXlsx = (columns: Array<string>, data: Array<any>, fileName: string) => {
    // columns 表头 data文件数据列表 fileName文件名称
    const table = []
    const obj = {}
    columns.forEach((el, index) => {
      const str = String.fromCharCode(index + 65)
      obj[str] = el
    })
    table.push(obj)
    data.forEach((arr) => {
      const row = {}
      arr.forEach((el: any, index: number) => {
        let str = String.fromCharCode(index + 65)
        row[str] = el
      })
      table.push(row)
    })
    const wb = XLSX.utils.book_new() // 创建book
    const ws = XLSX.utils.json_to_sheet(table, { header: Object.keys(obj), skipHeader: true }) // json转sheet
    ws['!cols'] = new Array(Object.keys(obj).length).fill({ width: 15 }) // 设置列宽
    /**
     * 合并列：
     *    s 意为 start ，即开始的单元格
     *    r 是 row ，表示行号，从 0 计起
     *    c 是 col ，表示列号，从 0 计起
     */
    ws['!merges'] = [{ s: { c: 0, r: 0 }, e: { c: 5, r: 0 } }]
    XLSX.utils.book_append_sheet(wb, ws, 'file') // sheet写入book
    const name = fileName || '文件下载'
    XLSX.writeFile(wb, name + '.xlsx') // 输出
  }
avatar
文章
阅读
粉丝