sheetjs xlsx 复杂表格导出丨技术开发分享录

官网：https://docs.sheetjs.com/
npm / yarn 官网下载地址：https://docs.sheetjs.com/docs/getting-started/installation/frameworks

npm i --save https://cdn.sheetjs.com/xlsx-0.20.1/xlsx-0.20.1.tgz
yarn add https://cdn.sheetjs.com/xlsx-0.20.1/xlsx-0.20.1.tgz

import { utils, write } from "XLSX";

浏览器示例：

代码：

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>复杂表格导出</title>
  <script src="https://cdn.sheetjs.com/xlsx-0.18.12/package/dist/xlsx.full.min.js"></script>
</head>
<body>
  <script>
    // 普通导出 mergeRules 为空数组即可
    const mergeRules = [
      { s: { r: 0, c: 0 }, e: { r: 1, c: 0 } }, // s: start, r: row, c: col, e: end；c:0 到 c:0表示第一列跨二行
      { s: { r: 0, c: 1 }, e: { r: 0, c: 3 } }, // c:1 到 c:3 表示第一行，合并1到3列
      { s: { r: 0, c: 4 }, e: { r: 0, c: 6 } },
      { s: { r: 0, c: 7 }, e: { r: 0, c: 11 } },
      { s: { r: 0, c: 12 }, e: { r: 0, c: 16 } },
    ]

    let data = [
      ['日期','a',null,null,'b',null,null,'c',null,null,null,null,'d'], // null 用来占位
      [null,'a1','a2','a3','b1','b2','b3','c1','c2','c3','c4','c5','d1','d2','d3','d4','d5'],
      [new Date().toLocaleString(),2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17],
    ]

    const sheet = XLSX.utils.aoa_to_sheet(data)

    sheet['!merges'] = mergeRules

    const sheetName = 'sheet1'

    let workbook = {
      SheetNames: [sheetName],
      Sheets: {},
    }
    workbook.Sheets[sheetName] = sheet

    // 生成excel的配置项
    const wopts = {
      bookType: 'xlsx', // 要生成的文件类型
      bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
      type: 'binary',
    }
    const wbout = XLSX.write(workbook, wopts)
    const blob = new Blob([s2ab(wbout)], {
      type: 'application/octet-stream',
    })

    // 字符串转ArrayBuffer
    function s2ab(s) {
      const buf = new ArrayBuffer(s.length)
      const view = new Uint8Array(buf)
      for (let i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xff
      return buf
    }

    // 触发下载
    const url = URL.createObjectURL(blob)
    const aLink = document.createElement('a')
    aLink.href = url
    aLink.download = 'aa.xlsx'
    aLink.dispatchEvent(new MouseEvent('click')) // 或 aLink.click()

    window.URL.revokeObjectURL(url)
  </script>
</body>
</html>

单元格换行使用 \n，示例：

'111/222'.replace('/', '\n')

不过最终导出的还是一行，点进去才会变成多行

sheetjs xlsx 复杂表格导出

猜你想看