preface

Recently, I received a demand to integrate the table data in the page into Excel files for users to download. At the beginning, the idea of this problem was to directly generate a file stream at the back end, but it required the format of the file and the style of part of the text. But the back end feels the pain and leaves it to the poor, helpless front end. I would have preferred to use JS-XLSX provided by sheetJS, but I couldn’t style the sheet. (It can be done, but use the Pro version of JS-XLSX, which costs money, as a poor front-end coder for a poor company this is ok. Nothing can’t be solved by hand. I found an open source library for XLSX-style. This library is actually a branch of JS-XLSX, and it can style exported Excel files, but so far, it can’t edit cell heights. After all, you can edit the width of the cell, the font style, the fill color, the border and so on

The installation

Since our development environment is Electron +vue, which comes with its own Node, we don’t have to worry about the library we can’t use.

yarn

yarn add xlsx-style
Copy the code

In the browser:

<script lang="javascript" src="dist/xlsx.core.min.js"></script>
Copy the code

With bower:

bower install js-xlsx-style#beta
Copy the code
Description of exporting Excel files
Cell Object
cellObject = {c:C,r:R}
Copy the code

Where C represents the column number, R represents the row number, and cell B5 is {c:1,r:5}. If you want to represent the range of cells, you can use {s: s,e: e}, where S represents the first cell (start),e represents the last cell (end), where s and E are cellObject objects; {s:{c:0,r:0},e:{c:1,r:4}}

Sheet object
sheetObject = {
  A1: {
    v: 'cell'.t: 's'.s: {
      font: {},
      fill: {},
      numFmt: {},
      alignment: {},
      border: {}}}}Copy the code

V: represents the value of the cell;

T: indicates the type of the cell value, B: indicates a Boolean value, N indicates an array of numbers, e indicates error information, S indicates a string, and D: indicates the date

S: Represents the style of the cell, which we’ll focus on later

Of course, these are not the only properties of the cell, but they are enough for a simple derivation, and we don’t use much of the rest

Ok, now that we’ve looked at the basic configuration of the information let’s see how to turn an array into an Excel file in three steps: Open the fridge, put the elephant in the fridge, close the fridge

// The raw data shown in the table
const tableArray = [
  {
    name: 'Joe'.age: 14.gender: 'male'
  },
  {
    name: 'bill'.age: 23.gender: 'woman'}]const title = ['name'.'age'.'gender']
Copy the code
Step 1: Open the fridge => Now convert the raw data to the JSON format needed to export Excel
/** * Function to convert data * tableArray data to convert * title table shows the title * excludeKey data not to export * backgroundRed background color is red */
const ProcessingData = ({tableArray,title,excludeKey,backgroundRed}) = > {
  if(! tableArray? .length) {return null
     }
  const excelTable = [title]
  // Get the array of the transforms
  const sheetData = tableArray.map((item, index) = > {
    // The lodash method is referred to as needed
    const copyItem = cloneDeep(item)
    if (excludeKey) {
      delete copyItem[excludeKey]
    }
    const colArray = Object.values(copyItem)
    colArray.unshift(index + 1)
    returncolArray }) sheetData.unshift(... excelTable)const sheet = {}
  sheetData.forEach((item, index) = > {
    item.forEach((sheetItem, key) = > {
      const itemIndex = `${EnLetter[key]}${index + 1}`
      const s = { alignment: { vertical: 'center'.horizontal: 'center'.wrapText: true}}if (backgroundRed && backgroundRed.length && index === 1) {
        if (backgroundRed.some(item= > item === key)) {
          // Make sure the colors are in ARGB format
          s.fill = { fgColor: { rgb: 'FFFF0023' } }
          s.font = { color: { rgb: 'FFFFFFFF' } }
          s.alignment.vertical = 'left'
        }
      }
      sheet[itemIndex] = { v: sheetItem, t: 's', s }
    })
  })
  const sheetKeys = Object.keys(sheet)
  const ref = `${sheetKeys[0]}:${sheetKeys.pop()}`
  sheet['! ref'] = ref
  return { sheet, sheetData }
}
Copy the code

Step 2: Put the elephant in the fridge => Convert the JSON format to a BLOB format file

const base64ToBlob = s= > {
  const bstr = atob(s)
  let n = bstr.length
  const u8Arr = new Uint8Array(n)
  while (n--) {
    u8Arr[n] = bstr.charCodeAt(n)
  }
  return new Blob([u8Arr], { type: 'xlsx'})}/** * convert to file *@param {Object} params
 * @param {Array} Params. tableArray displays the data in the table *@param {Boolean} Params. autoSerial Indicates whether the serial number is automatic *@param {String} Params. fileName specifies the fileName *@param {Array} Params. title The title * shown in the file table@param {Boolean} Params. autoWidth Whether it is an adaptive width *@param {Array} Params. description Description of the file *@param {Array} Params. backgroundRed red serial number usually refers to title */
const JSON2ExcelFile = ({
  tableArray,
  title,
  autoWidth = true,
  errorToRed = true,
  description,
  backgroundRed,
  excludeKey
}) = > {
  const { sheet, sheetData: excelArray } = ProcessingData({
    tableArray,
    title,
    description,
    excludeKey,
    errorToRed,
    backgroundRed
  })
  // Merge the cell description
  sheet['! merges'] = [{s: {
        c: 0.r: 0
      },
      e: {
        c: 7.r: 0}}]// Set the automatic height
  if (autoWidth) {
    /* Sets the maximum width of each column of the worksheet */
    excelArray.shift()
    const colWidth = excelArray.map(row= >
      row.map(val= > {
        /* Check whether the value is null/undefined */
        if (val == null) {
          return {
            wch: 20}}if (val.toString().charCodeAt(0) > 255) {
          /* Check whether it is Chinese */
          return {
            wch: val.toString().length * 2 > 20 ? val.toString().length * 2 : 10}}return {
          wch: val.toString().length > 20 ? val.toString().length * 2 : 20}}))// /* start with the first value */
    const result = colWidth[0]
    for (let i = 1; i < colWidth.length; i += 1) {
      for (let j = 0; j < colWidth[i].length; j += 1) {
        if (result[j].wch < colWidth[i][j].wch) {
          result[j].wch = colWidth[i][j].wch
        }
      }
    }
    sheet['! cols'] = result
  }
  const workbook = { Sheets: {employee information sheet: sheet},SheetNames: ['Staff Information Sheet']}const bouts = XLSXStyle.write(workbook, { type: 'base64'.bookType: 'xlsx'.cellStyles: true })
  const file = base64ToBlob(bouts)
  return file
}
Copy the code

Step 3: Close the fridge => Convert bloB data to a file

/** * Export the error information to an Excel spreadsheet for download@param {Object} params
 * @param {Array} Params. tableArray displays the data in the table *@param {String} Params. fileName specifies the fileName *@param {Array} Params. title The title * shown in the file table@param {Boolean} Params. autoWidth Whether it is an adaptive width *@param {Array} Params. description Description of the file *@param {Array} Params. backgroundRed Red serial number */
const ExcelFile = ({
  tableArray,
  filename,
  title,
  autoWidth = true,
  errorToRed = true,
  description,
  backgroundRed,
  excludeKey
}) = > {
 const file = JSON2ExcelFile({
    tableArray,
    autoWidth,
    title,
    errorToRed,
    description,
    backgroundRed,
    excludeKey
  })
  const homeDir = homedir()
  const options = {
    title: 'save the Excel'.defaultPath: `${homeDir}\\downloads\\${filename}.xlsx`.filters: [{ name: 'excel'.extensions: ['xlsx'] }]
  }
  remote.dialog.showSaveDialog(options).then(({ filePath: filename }) = > {
    if(! filename)return
    const reader = new FileReader()
    reader.readAsArrayBuffer(file)
    reader.onload = () = > {
      const buffer = Buffer.from(reader.result)
      fs.writeFile(filename, buffer, {}, err= > {
        if (err) {
          Message({
            showClose: true.message: 'Download failed'.type: 'error'
          })
          throw err
        }
        const vm = new Vue()
        const h = vm.$createElement
        const fileName = h('p', basename(filename))
        const notifier = Notification.success({
          title: 'Download completed'.duration: 0.dangerouslyUseHTMLString: true.message: h('div', [
            fileName,
            h('span', {
              style: {
                display: 'inline-block'.color: '#409eff'.cursor: 'pointer'.margin: '10px 0 0'
              },
              domProps: {
                innerHTML: 'Display in folder'
              },
              on: {
                click: () = > {
                  fs.access(filename, err= > {
                    if (err) {
                      vm.$message.error({
                        message: 'This file does not exist'.showClose: true})}else {
                      remote.shell.showItemInFolder(filename)
                    }
                    notifier.close()
                  })
                }
              }
            })
          ])
        })
      })
    }
  })
}
Copy the code

The front-end implementation exports Excel files. The basic implementation logic is to convert the table data displayed in the present into JSON data that Excel can recognize, and convert these data into BLOB data required by the file. Write this BLOB data as a file;

@startuml start: Obtain the data to be exported. : Convert data to JSON data that Excel can recognize; Convert JSON to BLOB data; : Write blob files as files; : Export files. stop @endumlCopy the code