Introduce XLSX

Js-xlsx from SheetJS is a very convenient tool library for reading and exporting Excel in pure JS. It is powerful and supports a wide range of formats, including XLS, XLSX, ODS (a proprietary form file format for OpenOffice) and more. You can also set the style and so on.

Export excel implementation

  • Download XLSX and introduce it

Use NPM to download

npm install xlsx
Copy the code
import XLSX from 'xlsx'
Copy the code
  • The export file
/ * * * *@param SheetData sheetData *@param FileName indicates the fileName */
function exportFile(sheetData, fileName) {
    // Convert an array of objects into a sheet
    const sheet = XLSX.utils.json_to_sheet(sheetData)
    // Percentages and numbers are changed to numeric types
    Object.keys(sheet).forEach((key) = > {
        if (sheet[key].v) {
            const val = sheet[key].v
            if (!isNaN(val)) {
                sheet[key].t = 'n'
            }
            if (val.lastIndexOf(The '%') === val.length - 1) {
                sheet[key].t = 'n'
                sheet[key].z = '0.00%'
                sheet[key].v = Number(val.substring(0, val.length - 1)) / 100}}})// Create a virtual workbook
    const wb = XLSX.utils.book_new()
    // Add sheet to workbook
    XLSX.utils.book_append_sheet(wb, sheet, fileName)
    const workbookBlob = workbook2blob(wb)
    openDownload(workbookBlob, `${fileName}.xls`)}Copy the code
  • Create blobUrl for bloB object. Download the blob object by a tag and createObjectURL
function openDownload(blob, fileName) {
    if (typeof blob === 'object' && blob instanceof Blob) {
        blob = URL.createObjectURL(blob) // Create bloB address
    }
    const aLink = document.createElement('a')
    aLink.href = blob
    // a new attribute added to HTML5 that specifies the name of the file to be saved. It can be saved without a suffix. Note that sometimes this does not work in file:/// / mode
    aLink.download = fileName || ' '
    let event
    if (window.MouseEvent) event = new MouseEvent('click')
    / / move
    else {
        event = document.createEvent('MouseEvents')
        event.initMouseEvent('click'.true.false.window.0.0.0.0.0.false.false.false.false.0.null)
    }
    aLink.dispatchEvent(event)
}
Copy the code
  • Install the workbook as a BLOB object
function workbook2blob(workbook) {
    // Generate excel configuration items
    const wopts = {
        // The type of file to generate
        bookType: 'xlsx'.// // Whether to generate a Shared String Table. If this function is enabled, the speed will decrease, but it is more compatible with earlier versions of IOS devices
        bookSST: false.type: 'binary',}const wbout = XLSX.write(workbook, wopts)
    // Turn the string into ArrayBuffer
    function s2ab(s: string) {
        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 blob = new Blob([s2ab(wbout)], {
        type: 'application/octet-stream',})return blob
}
Copy the code
  • How to use
let sheetData = [
    {
        name: 'april'.age: '24'}, {name: 'bran'.age: '26',
    },
]
exportFile(sheetData, 'Personnel Statistics')
Copy the code

Problems encountered

Numbers and percentages cannot be selected for calculation in Excel after they are exported. Double-click the cell and they are available again. Viewing the document shows that you can set the type of the cell object:Solution:

  • For things that can be converted to numbers it’s of type n(number)
  • For a percentage, set the cell like this
sheet[key].t = 'n'
sheet[key].z = '0.00%'
sheet[key].v = Number(val.substring(0, val.length - 1)) / 100
Copy the code

Other export

  • The back end of the callback interface returns the file stream implementation

A previous post

Refer to the article

  • Lot of XLXS
  • XLSX Document Chinese version