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.
Install Nest.js (node.js framework) globally using npm .
Open your nest project in vs code and install following depedencies:
tmp library is used for creating temporary files and directories in nodejs environment.
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 :
[‘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.
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,
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.