Hello, brave friends, I am your strong mouth king xiao Wu, healthy body, not brain disease.

I have a wealth of hair loss techniques that will catapult you to the top.

A look will be written waste is my theme, food to pick the foot is my characteristics, humble in showing a trace of strong, stupid people have stupid blessing is the greatest comfort to me.

Welcome to xiao wu’s essay series.

Writing in the front

Hand the code to the portal – Ajun568

The feet are presented with final renderings

View before remind

👺 The final effect of this paper is shown in the figure above. The specific functions are as follows: Export Excel + multiple sheets + mergable multi-line table headers. The code part uses React+TS as a tool to write.

The preparatory work

👺 Install xlsx.js NPM install XLSX

👺 Write to an Excel file: xlsx. write(workbook, writeOpts)

Workbook 👇

  • SheetNames @types String []: indicates the name of the current Sheet

  • Sheets: object of the current sheet. The format is as follows

[SheetNames]: {
  ! "" refs": "A1:G7".From row 1, column A, to row 7, column G
  ! "" cols": [{wpx: 80}... ] .// indicates a column width of 80px
  ! "" rows": [{hpx: 20}... ] .// Indicates a row height of 20px
  ! "" merges": [{s: {r: 0.c: 2}, e: {r: 0.c: 3}}... ] .(s: start, e: end, c: column, r: row)
  "A1": {v: "Name"}, // row 1 and column A show data as "name ", and so on.... }Copy the code

WriteOpts 👇

{
  type, // The data is encoded in binary format
  bookType, // Export type, this article uses the XLSX type
  compression, // Whether to use Gzip compression
}
Copy the code

The download file

< A tag download attribute >

Use url.createObjECturl (Object) to create the required URL for the download. RevokeObjectURL (FileUrl) ¶ Since each call generates a new URL object, remember to release it after use. Release method url.revokeobjecturl (FileUrl).

A tag is triggered by simulating click event to achieve download

constsaveAs = (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)}Copy the code

The head to deal with

Mock data: For details, go to Github and see mocking. ts

Header Data format

{[...key: 'animal'.value: 'animals'.child: [{key: 'dog'.value: 'dog'.child: [{key: 'corgi'.value: 'corgi'}, {key: 'husky'.value: 'Husky',},],}, {key: 'tiger'.value: 'the tiger'},],}... ]Copy the code

Data Format of partial Data

[{name: Huang Dao Xiao Wu.desc: 'Search engine based copy and paste siege Lion'.watermelon: 'like'.banana: 'Don't like'.corgi: 'like'.husky: 'like'.tiger: 'Don't like',},... ]Copy the code

Header data processing

👺 analysis

  • Header data is a tree structure with a depth of the number of Header rows

  • The Header Data is converted to the format of the Data Data and merged with the Data array for processing together into the desired export format

  • The key of the transformation object should be the key of the smallest leaf node

  • The value of the transformed object should be the value of the current hierarchy (that is, the value shown in the current row after export)

  • And since it’s a tree, you can definitely recurse

🧟 came ️ Code

🧟 came ️ Image

The Merged data

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

👺 analysis

  • Treat the processed header data as a matrix

  • Row or column, adjacent elements are merged if they are the same

Tips: This article is based on the judgment of adjacent value values are equal to merge, if necessary, it is recommended to rewrite the object form to improve.

🧟 came ️ Code

🧟 came ️ Image

Generate sheet data

  • Use object.assign to merge objects

  • Convert columns to uppercase using string.fromCharCode (65 + I)

🧟 came ️ Code

🧟 came ️ Image

Conversion byte stream

Create a buffer using the New Uint8Array(buf) reference

Because the Unicode encoding ranges from 0 to 65535 and the Uint8Array ranges from 0 to 255, it needs to be bit-sized and 0xFF to keep the number of bits consistent

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
}
Copy the code

The export file

Combined with the previous preparatory work, the exported code logic is out, directly on the code

conclusion

The open source version does not support styling. If required, use the paid version or use xLSX-style. Use the same method as in this article. You can refer to the document to add your own style section.

Refer to 🔗

[Github] SheetJS ~ js-xlsx

[mySoul] before and after the elegant | elegant import and export Excel

[Seefly] front-end uses xlsx.js to export Excel with complex table headers