Front-end export Excel online guide north

Hello, brave friends, hello everyone, I am your little five, the king of the mouth, I am in good health, and my brain is not sick.

I have a wealth of hair loss skills, which can make you a veteran celebrity.

It is my main theme that I will write it when I look at it, and it is my characteristic to pick my feet. There is a trace of strength in the humble. The stupid blessing of a fool is the greatest comfort to me.

Welcome to fifth of essay series front-end export Excel-line refers to the North.

Write in front

Both hands offer code link Portal-ajun568

final rendering of the feet

reminder before

👺 The final effect of this article is as shown in the figure above. The specific function is: export Excel + multiple Sheets + mergeable multi-line header. The code part uses React+TS as a tool for writing.

Ready to work

👺 install xlsx.js npm install xlsx

👺 write to Excel file: XLSX.write(workbook, writeOpts)

workbook 👇

• SheetNames @types string[] : The name of the current Sheet
• Sheets: The object of the current sheet, the format is as follows

[SheetNames]: {
  "!refs": "A1:G7", // 表示从 第1行第A列 到 第7行第G列
  "!cols": [{wpx: 80} ... ], // 表示 列宽 80px
  "!rows": [{hpx: 20} ... ], // 表示 行高 20px
  "!merges": [{s: {r: 0, c: 2}, e: {r: 0, c: 3}} ... ], // 表示 将 第0行第2列 和 第0行第3列 进行合并 (s: start, e: end, c: column, r: row)
  "A1": {v: "姓名"}, // 表示第1行第A列 显示数据为 "姓名", 以此类推 ...
  ...
}

writeOpts 👇

{
  type, // 数据编码, 本文采用 binary 二进制格式
  bookType, // 导出类型, 本文采用 xlsx 类型
  compression, // 是否使用 Gzip 压缩
}

download file

Want to download a file, my little A is the first to express dissatisfaction and apply to play < a tag’s download attribute >

Create the URL required for download through URL.createObjectURL(Object) . Because each call will generate a new URL object, so remember to release after use, release method URL.revokeObjectURL(FileUrl)

Trigger a tag by simulating the click event to achieve download

const saveAs = (obj: Blob, fileName?: string): void => {
  const temp = document.createElement('a')
  temp.download = fileName || 'download'
  temp.href = URL.createObjectURL(obj)
  temp.click()
  setTimeout(() =>  { URL.revokeObjectURL(temp.href) }, 100)
}

Head treatment

Mock data : For detailed data, please go to Github , view mock.ts

Header data format

[
  ...
  {
    key: 'animal',
    value: '动物',
    child: [
      {
        key: 'dog',
        value: '狗',
        child: [
          {
            key: 'corgi',
            value: '柯基',
          },
          {
            key: 'husky',
            value: '哈士奇',
          },
        ],
      },
      {
        key: 'tiger',
        value: '老虎',
      },
    ],
  },
  ...
]

Data part of the data format

[
  {
    name: '黄刀小五',
    desc: '基于搜索引擎的复制粘贴攻城狮',
    watermelon: '喜欢',
    banana: '不喜欢',
    corgi: '喜欢',
    husky: '喜欢',
    tiger: '不喜欢',
  },
  ...
]

Head data processing

👺 analysis

• Header data is a tree structure, and its depth is the number of rows occupied by the head
• Header data should be converted into Data data format, and combined with Data array, and processed together into the format required for export
• Conversion target Key should be a minimum of leaf nodes Key
• Conversion target value should the current level value (i.e., after the current line derived displayed value )
• Since it's a tree, decisively recursively, sure enough

🧟‍♂️ Code

🧟‍♂️ Image

Merged data

{
  s: { // start
    r: x, // row
    c: y, // column
  },
  e: { ... } // end
}

👺 analysis

• Treat the processed header data as a matrix
• In a row or column, if adjacent elements are the same, they are merged

tips: This article uses to judge whether adjacent value values are equal to merge. If there is a need, it is recommended to rewrite it as an object form to improve it.

🧟‍♂️ Code

🧟‍♂️ Image

Generate sheet data

• Use Object.assign to merge objects
• Use String.fromCharCode(65 + i) to convert columns to uppercase letters

🧟‍♂️ Code

🧟‍♂️ Image

Convert byte stream

Use new ArrayBuffer(str) create a buffer, use new Uint8Array(buf) reference

Because Unicode coding is 0 ~ 65535 , and Uint8Array range 0 ~ 255 , and therefore requires bit 0xFF , in order to maintain a consistent number of bits

const s2ab = (str: string): ArrayBuffer => {
  let buf = new ArrayBuffer(str.length)
  let view = new Uint8Array(buf)

  for (let i = 0; i !== str.length; ++i) {
    view[i] = str.charCodeAt(i) & 0xFF
  }

  return buf
}

Export file

Combined with the preparatory work part of the previous article, the exported code logic comes out, and the code directly

Concluding remarks

The open source version does not support setting styles. If you need it, you can use the paid version or use xlsx-style . The usage is consistent with this article. You can refer to the document add the style part yourself.

Reference 🔗 link

【Github】 SheetJS ~ js-xlsx

[mySoul] Elegant | Elegant import and export of front and back ends Excel

[Seefly] The front end uses xlsx.js to export excel with complex header