Read/Write Excel File in Node.js using XLSX

Excel sheets are popular in the business world as a de facto standard whether we as developers like it not. Sometimes, clients ask us to upload the excel sheet and all the data should be stored in the database. XLSX is a Node package that solves this problem. In this post, we are going to use busboy to process form data.

This post contains two parts:

1. How to parse the excel sheet into JSON format.

2. How to create an excel sheet using JSON data.

Step 1: Install the XLSX package using npm or bower

npm i --save xlsx
//or
bower install js-xlsx

Step 2: Import multer or busboy

npm install --save multer

Multer is a node.js middleware for handling multipart/form-data, which is primarily used for uploading files. It is written on top of busboy for maximum efficiency.

Busboy is a Node.js module for parsing incoming HTML form data.

Step 2: import XLSX in index.js

const XLSX = require('xlsx')

Parsing Excel Data

req.busboy.on('file', (fieldname, file, fname) => {
 if (fieldname === 'file') {
  const buffers = []
  file.on('data', (data) => {
   buffers.push(data)
  })
  file.on('end', () => {
   buffer = Buffer.concat(buffers)
   workbook = XLSX.read(buffer, {
    type: 'buffer',
   })
  })
 }
})

req.busboy.on('finish', () => {
 try {
  const excelProducts =      XLSX.utils.sheet_to_json(workbook.Sheets[workbook.SheetNames[0]], {
   raw: false,
   header: 1,
   dateNF: 'yyyy-mm-dd',
   blankrows: false,
  })
 } catch (err) {
  console.log(err)
 }
})
req.pipe(req.busboy)

1. Read the excel file using a Buffer. We are using busboy because this file is uploaded by the user. If the file is already downloaded you can use XLSX.readFile(filename: string, opts?: ParsingOptions) by giving file name.

2. XLSX.utils.sheet_to_json() is used to read the worksheet data into array of object. Other options are passed to specify different parameters like Use raw values (true) or formatted strings (false), Include blank lines in the output, default date format, If the header is specified, the first row is considered a data row else the first row is the header row and not considered data.

Some helper functions in XLSX.utils generating different views of the sheets:

• XLSX.utils.sheet_to_csv generates CSV
• XLSX.utils.sheet_to_txt generates UTF16 Formatted Text
• XLSX.utils.sheet_to_html generates HTML
• XLSX.utils.sheet_to_json generates an array of objects
• XLSX.utils.sheet_to_formulae generates a list of formulae

Creating Excel sheet

data = [{
 firstName: 'John',
 lastName: 'Doe'
}, {
 firstName: 'Smith',
 lastName: 'Peters'
}, {
 firstName: 'Alice',
 lastName: 'Lee'
}]

const ws = XLSX.utils.json_to_sheet(data)
const wb = XLSX.utils.book_new()
XLSX.utils.book_append_sheet(wb, ws, 'Responses')
XLSX.writeFile(wb, 'sampleData.export.xlsx')

1. json_to_sheet converts an array of JavaScript objects to a worksheet. There are other methods available to convert data to worksheets like aoa_to_sheet, table_to_sheet. sheet_add_json is used to add an array of JavaScript objects to an existing worksheet.

2. book_new() creates a new workbook in the sheet.

3. book_append_sheet appends worksheet to the workbook with the name ‘Responses’.

4. XLSX.writeFile (wb, ‘sampleData.export.xlsx’) attempts to write wb to ‘sampleData.export.xlsx’.

You can also specify the width of each column & also merge the cells.

Some helper functions XLSX.utils for importing different data into the sheets:

• XLSX.utils.aoa_to_sheet converts an array of arrays of JavaScript data to a worksheet.
• XLSX.utils.json_to_sheet converts an array of JavaScript objects to a worksheet.
• XLSX.utils.table_to_sheet converts a DOM TABLE element to a worksheet.
• XLSX.utils.sheet_add_aoa adds an array of arrays of JavaScript data to a worksheet.
• XLSX.utils.sheet_add_json adds an array of JavaScript objects to a worksheet.

Conclusion:

Using the XLSX package you can read any excel file and also create an excel file.

Thank you for reading.