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.
