Front end export excel online pointing North

Time:2021-12-4

Hello, all brave friends, Hello, I’m your strong mouth king, Xiao Wu. I’m healthy and my brain is not sick

I have rich hair loss skills, which can make you a senior

It’s my main idea to write as soon as I see it. It’s my characteristic to pick my feet. There’s a trace of strength in the humble. It’s the greatest comfort for me that a fool has a fool’s blessing

Welcome toSmall fiveofEssay seriesofFront end export excel online pointing North.

Write in front

Code link with both handsPortal – ajun568

Final renderings on both feet

Front end export excel online pointing North

Pre observation reminder

The final implementation effect of this paper is shown in the figure above. The specific functions are as follows:Export excel + multiple sheets + multi row header that can be merged. the code part is adoptedReact+TSWrite as a tool

preparation

πŸ‘Ί 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 in the following format
[SheetNames]: {
  "! refs": "A1: G7", // indicates 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}...], // indicates merging row 0, column 2 and row 0, column 3 (s: start, e: end, C: column, R: row)
  "A1": {V: "name"}, // indicates that the data displayed in column a of row 1 is "name", and so on
  ...
}

writeOpts πŸ‘‡

{
  Type, // data encoding. Binary format is adopted in this paper
  BookType, // export type. Xlsx type is adopted in this paper
  Compression, // whether to use gzip compression
}

Download File

If I want to download a file, my little a is the first to express dissatisfaction and apply for battle < download attribute of a tag >

Create the URL required for downloading through url.createobjecturl (object). Since each call will generate a new URL object, remember to release it after use. Release the method url.revokeobjecturl (fileurl)

The a tag is triggered by simulating the click event to realize the 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 dataFor detailed data, please go to GitHub:mock.tsView in

Header part data format

[
  ...
  {
    key: 'animal',
    Value: 'animal',
    child: [
      {
        key: 'dog',
        Value: 'dog',
        child: [
          {
            key: 'corgi',
            Value: 'kerky',
          },
          {
            key: 'husky',
            Value: 'husky',
          },
        ],
      },
      {
        key: 'tiger',
        Value: 'Tiger',
      },
    ],
  },
  ...
]

Data part data format

[
  {
    Name: 'Huang Dao Xiao Wu',
    Desc: 'copy and paste siege lion based on search engine',
    Watermelon: 'like',
    Banana: 'I don't like it',
    Corgi: 'like',
    Husky: 'like',
    Tiger: 'I don't like it',
  },
  ...
]

Header data processing

πŸ‘Ί analysis

  • HeaderThe data is a tree structure, and its depth is the number of rows occupied by the header
  • HeaderData to be converted toDataThe format of the data, andDataThe array is merged and processed together into the format required for export
  • Conversion objectkeyExpected minimum leaf nodekey
  • Conversion objectvalueExpected current levelvalue(i.e. the displayed in the current line after export)value )
  • Since it’s a tree, you must be right

πŸ§Ÿβ€β™‚οΈ Code

Front end export excel online pointing North

πŸ§Ÿβ€β™‚οΈ Image

Front end export excel online pointing North

Merged data

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

πŸ‘Ί analysis

  • The processed header data is regarded as a matrix
  • In rows or columns, if adjacent elements are the same, they are merged

tips:This paper uses to judge whether adjacent value values are equal for merging. If necessary, it is suggested to rewrite it into object form for improvement

πŸ§Ÿβ€β™‚οΈ Code

Front end export excel online pointing North

πŸ§Ÿβ€β™‚οΈ Image

Front end export excel online pointing North

Generate sheet data

  • utilizeObject.assignMerge objects
  • utilizeString.fromCharCode(65 + i)Convert columns to uppercase letters

πŸ§Ÿβ€β™‚οΈ Code

Front end export excel online pointing North

πŸ§Ÿβ€β™‚οΈ Image

Front end export excel online pointing North

Convert byte stream

Create a buffer with new arraybuffer (STR) and usenew Uint8Array(buf)quote

Front end export excel online pointing North

becauseunicodeCode is0~65535, andUint8ArrayRange is0~255, therefore, it is necessary to0xFFTo keep the number of digits 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
}

export file

Combined with the foregoingpreparationAs mentioned in the section, the exported code logic comes out and is directly loaded into the code

Front end export excel online pointing North

Conclusion

The open source version does not support style setting, which can be adopted if requiredPaid versionOr usexlsx-style, the use method is consistent with this article. You can refer to the document to add the style section

Front end export excel online pointing North

Reference link

【Github】 SheetJS ~ js-xlsx

[mysoul] elegant import and export of Excel from front to back

[seefly] the front end uses xlsx.js to export excel with complex header

Recommended Today

Hive built-in function summary

1. Related help operation functions View built-in functions: Show functions; Display function details: desc function ABS; Display function extension information: desc function extended concat; 2. Learn the ultimate mental method of built-in function Step 1: carefully read all the functions of the show functions command to establish an overall understanding and impression Step 2: use […]