Geek Culture
Published in

Geek Culture

Exporting data in excel file in Node.js

→ Many times when we have some reporting or an admin panel application we need to download data in an excel file.

→ This can be achieved in any JavaScript framework. For this article, we will use node with express.js.

→ We will use the exceljs package for the same.

→ We need to install the basic express setup using the below command

express <your_app_name> --view=ejs

→ Open the application in your favourite code editor. The directory structure will look like below:

→ Let us start the node server using the below command

npm start

→ Open localhost:3000 in the browser. The default home route opens.

→ Once the project is set up, we will install exceljs package

npm install exceljs

STEP1: We will create a route for which download will happen. Open route/index.js file.

Here exportUser is a controller which we will create in STEP3

router.get("/downloadExcel", exportUser);

STEP2: We will create a model. This page will hold the data which we want to export. In real scenarios, we will connect with either relational or non-relational databases and get data.

→ Here, for simplicity, we have written just the output of data from any database.

Create Models/User.js

// GET DATA FROM DATABASE// Sample dataconst User = [{ 
fname: "Amir",
lname: "Mustafa",
email: "amir@gmail.com",
gender: "Male"
},
{
fname: "Ashwani",
lname: "Kumar",
email: "ashwani@gmail.com",
gender: "Male",
},
{
fname: "Nupur",
lname: "Shah",
email: "nupur@gmail.com",
gender: "Female"
},
{
fname: "Himanshu",
lname: "Mewari",
email: "himanshu@gmail.com",
gender: "Male",
},
{
fname: "Vankayala",
lname: "Sirisha",
email: "sirisha@gmail.com",
gender: "Female",
},];module.exports = User;

STEP3: We will create a controller which will link to our route

Create Controller/User.js

const User = require("../Models/User");const excelJS = require("exceljs");const exportUser = async (req, res) => {   // WRITE DOWNLOAD EXCEL LOGIC};module.exports = exportUser;

In the route, we will import this controller and configure to our route

route/index.js

var express = require("express");const exportUser = require("../Controller/User"); // CONTROLLERvar router = express.Router();
router.get("/downloadExcel", exportUser); // DOWNLOAD ROUTE
module.exports = router;

→ STEP5: Here, we will write the logic for downloading excel in the User controller:

Controller/User.js

const User = require("../Models/User"); // This has data to be used
const excelJS = require("exceljs");
const exportUser = async (req, res) => { const workbook = new excelJS.Workbook(); // Create a new workbook const worksheet = workbook.addWorksheet("My Users"); // New Worksheet const path = "./files"; // Path to download excel // Column for data in excel. key must match data key
worksheet.columns = [
{ header: "S no.", key: "s_no", width: 10 },
{ header: "First Name", key: "fname", width: 10 },
{ header: "Last Name", key: "lname", width: 10 },
{ header: "Email Id", key: "email", width: 10 },
{ header: "Gender", key: "gender", width: 10 },
];// Looping through User data
let counter = 1;
User.forEach((user) => { user.s_no = counter; worksheet.addRow(user); // Add data in worksheet counter++;});// Making first line in excel bold
worksheet.getRow(1).eachCell((cell) => {
cell.font = { bold: true };});try { const data = await workbook.xlsx.writeFile(`${path}/users.xlsx`) .then(() => { res.send({ status: "success", message: "file successfully downloaded", path: `${path}/users.xlsx`, });
});
} catch (err) { res.send({ status: "error", message: "Something went wrong", }); }};module.exports = exportUser;

→ Here we create a workbook. Inside the workbook, we create a worksheet.

→ Every sheet holds a different set of data. For this article, we have only one file to download i.e. user worksheet

→ In the next step, we define the columns to print. The key of every entry should match the User data that is there in the User model.

→ We download using the below command. This will download in xlsx format. exceljs also provide a facility to download in CSV format

await workbook.xlsx.writeFile(`${path}/users.xlsx`)

→Keeping node server should be running. Let us run the route in browser

localhost:3000/downloadExcel

→ File is now downloaded in the path mentioned in response

Video:

https://secure.vidyard.com/organizations/1904214/players/Qr56rBG9FMtqB1SrETx2ut?edit=true&npsRecordControl=1

Repository:

https://github.com/AmirMustafa/export-to-excel

Closing Thoughts:

We have seen how simply we can export data in an excel file. Try reading exceljs documentation. There we can do many formatting and download options in excel files.

Thank you for being till the end 🙌 . If you enjoyed this article or learned something new, support me by clicking the share button below to reach more people and/or give me a follow on Twitter to see some other tips, articles, and things I learn and share there.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store