Node-xlsx is a lightweight Excel plug-in that can download and export basic functions of Excel. This article records the steps of using Node-xlsx plug-in to download Excel forms in the Express framework.

Case 1: Read the local file and return the front-end Excel stream file

This applies to the excel template download scenario, because templates are fixed content, we store a fixed Excel template in the code folder, read and return.

  • The first step, of course, is to download and install the pluginnpm i node-xlsx
  • The second step is to introduce the plug-in into the corresponding codeconst xlsx = require('node-xlsx')
  • The third step is to write the corresponding code in the corresponding route URL, the code is as follows:
// Excel export and download template interface
route.get("/exportExcel".(req, res) = > {

  // First, read the local Excel template file and parse it into the data format required by the Node-Xlsx plug-in.
  // (for example, my table file is in the excel folder in the code) to read the fs file module
  const dataByParse = xlsx.parse(fs.readFileSync('./excel/ statistics template.xlsx '));

  /* The printed data is an array, and each entry (each object) in the array is a sheet. The name attribute specifies the name of each sheet. The data attribute is an array, and the array contains the data corresponding to each sheet. It can also be understood as the data in the first row, and each item in the following is the data corresponding to the "table body" of each row. The specific format will be illustrated later. * / 
  console.log("Parse data format",dataByParse);

  // Finally, use the build method of the XLSX plug-in to convert the parsed data into excel tables (streams in the form of buffers).
  // It is returned to the front end as a stream file, which can be analyzed and downloaded by the front end
  res.send(xlsx.build(dataByParse))

})
Copy the code

The data format required by Node-xlsx is given as an example

Let’s take a look at the data structure

let excelData = [
    // The first sheet content
    {
      name:"I am sheet1".// Give the first sheet the name
      data: [// Note that this is a two-dimensional array
        ["Name"."Age"."Home"."Note"]./ / the first line
        ["Sun Wukong"."500"."Flower and Fruit Hill"."A man beats a Buddha with a nickname."]./ / the second line
        ["Pig Eight Quit"."88"."Lao Zhuang Gao"."Marshal Of the Canopy"]./ / the third row]},// The second sheet
    {
      name:"I am sheet2".// Give the second sheet the name
      data:[
        ["City"."Country"."Population"."Economic level"]./ / same as above
        ["Shanghai"."China"."1.4 billion"."Getting better and better"],
        ["London"."British"."70 million"."还行"],
        ["Washington"."The United States." "."340 million"."Live together"]]}]Copy the code

The above data format corresponds to the effect drawing

Obviously, the data structure and the corresponding exported Excel results are the same

Case 2: Query mysql data and return the front-end stream file according to the front-end transfer parameters

This applies to a one-time download of a form file. Does not occupy back-end disk files. Is to take the parameters passed from the front end and concatenate the parameters into SQL statements. Finally, assemble the data structure into the data format required by the Node-XlsX plug-in

// Excel export and download template interface
route.post("/exportExcel".(req, res) = > {
  // Let's say we get excelData from our mysql database
  let excelData = [
    // The first sheet content
    {
      name: "I am sheet1".// Give the first sheet the name
      data: [["Name"."Age"."Home"."Note"]./ / the first line
        ["Sun Wukong"."500"."Flower and Fruit Hill"."A man beats a Buddha with a nickname."]./ / the second line
        ["Pig Eight Quit"."88"."Lao Zhuang Gao"."Marshal Of the Canopy"]./ / the third row]},// The second sheet
    {
      name: "I am sheet2".// Give the second sheet the name
      data: [["City"."Country"."Population"."Economic level"]./ / same as above
        ["Shanghai"."China"."1.4 billion"."Getting better and better"],
        ["London"."British"."70 million"."还行"],
        ["Washington"."The United States." "."340 million"."Live together"]]}]// Excel table content configures cell width
  let optionArr = { 
    // Specify sheet1 width
    ! "" cols": [{wch: 15 },
      { wch: 15 },
      { wch: 10 },
      { wch: 50},].// Specify sheet2 width
    "cols": [{wch: 15 },
      { wch: 15 },
      { wch: 10 },
      { wch: 50},],}The second parameter to the xlsx.build method takes the cell configuration parameter
  res.send(xlsx.build(excelData,optionArr))
})
Copy the code

conclusion

This article records the main back-end side of the code writing, as for the front-end download excel form writing and notes frequently asked questions, you can refer to my other article, the portal is as follows: juejin.cn/post/692688…

Finally the enclosed NPMJS document instance is introduced, the official web site more complete yo: www.npmjs.com/package/nod…