Downloading data in excel format with Nest.js

In data organization , excel was and is a most acclaimed format . Even to this date when we have lots of database services still excel stands in between for storing or representing data in easy and facile way .

In Nest.js for writing the data in excel format we use a library named “exceljs”. Its a light-weight library used to manipulate , read or write the data into XLSX format. For more detail you can dive into documentation.

Prerequisite

Install Nest.js (node.js framework) globally using npm .

STEP 1:

Open your nest project in vs code and install following depedencies:

tmp library is used for creating temporary files and directories in nodejs environment.

STEP 2:

Head to your excel.service.ts file , write the following code

Import the following:

Using the sample data placed in data.ts file ,imported on line 2. The data format is :

Moving back to excel.service.ts , which contains the downloadExcel() function

On line 17 , it is structuring the data as Array of arrays because it accept multiple rows as :

FORMAT:

[

[‘karachi’ , ’Pakistan’],

[‘lahore’ , ’Pakistan’]

]

On line 22 , Workbook() will create a new workbook . Then by calling its addWorksheet([‘name’]) method you can add a new worksheet to it. We can add multiple worksheets by different names of worksheet.

Now we’ll write this book on temporary file and return its path .

On line 35 , tmp.file() will create a temporary file . Option mode in this method will identify the permissions towards the file .

0600: user can write , can read but cant execute.

STEP 3:

Head to excel.controller.ts file , we’ll define the route and call the serivce for downloading the excel file.

Make a get request on the specified route, for me its : http://localhost:3000/excel/download.

Boom ! the file will download and look like

But this looks too dull and unattractive, So if you need to style the sheet little bit , then add this to your excel.service.ts file

Call this after adding the rows in sheet

Now the downloaded file will look like,

Conclusion:

This is the basic code for writing the data into excel file and downloading it using Exceljs ,tmp and Nest.js. You can also merge cells , apply formulas or add multiple sheets etc with exceljs . For more explore exceljs documentation.