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:
-
How to parse the excel sheet into JSON format.
-
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)
-
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.
-
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 CSVXLSX.utils.sheet_to_txt
Ā generates UTF16 Formatted TextXLSX.utils.sheet_to_html
Ā generates HTMLXLSX.utils.sheet_to_json
Ā generates an array of objectsXLSX.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')
-
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.
-
book_new() creates a new workbook in the sheet.
-
book_append_sheet appends worksheet to the workbook with the name āResponsesā.
-
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.