Export Data in Excel Using Node JS

When we have some reporting or admin panel application, we often need to download data in Excel. In this article, we will learn how to export data in excel using node js. There are different packages available in the market to export the data in node js. We will be using exceljs package to export data in an excel file.

ExcelJS is a data manipulation package available in node js to read manipulate and write data and styles to XLSX. ExcelJS is one of the best packages for data manipulation freely available and frequently updated from time to time.

Prerequisites

1. A basic understanding of Node js

How to export data in excel using node js?

Project Setup

Create a directory named  node-export, and change it into that directory using the following command.

mkdir node-export
cd node-export

Inside the node-export directory, initialize a node js project using

npm init

After the command, a new package.json file will be created in the node-export folder.

Now, let’s install the required dependencies required to export the data in an excel file.

npm install express exceljs

This will install express and exceljs modules which will be used for routing and exporting data to excel files respectively.

Next, let’s create a index.js file where we will create a route through which download will happen.

The above code will initialize the express framework, set up the server at port 3000, and create a route downloadExcel which is used to export data to an excel file.

Create sample data for exporting

Let’s create a model file named user.js inside the Model folder which contains the sample data for exporting to the excel file. Typically, we will connect to relational or non-relational databases and get data. You can follow this article to learn how to add, delete and fetch data from the database in node js.

// Sample data
const User = [
  { 
   name: "John",
   email: "john@gmail.com", 
   gender: "Male" 
  },
  {
   name: "Harry",
   email: "harry@gmail.com",
   gender: "Male",
  },
  {
  name: "Olivia",
  email: "olivia@gmail.com",
  gender: "Female",
  },
  ];
  module.exports = User;

Export data to an excel file

In this step, let’s create a file where we will write a logic to store the sample data provided above to the excel file using the package exceljs. Let the name of the file be user.js which will be located inside Controller folder.

Now, let’s update our index.js file, so that when downloadExcel the route is called out controller logic will be called.

const express = require('express')
const exportUser = require('./Controller/user');

const app = express();

app.use(express.json());
app.use(express.urlencoded({ extended: true }));

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

app.listen(3000, function () {
  console.log('App listening on port 3000!');
});

Finally, let’s write a logic to export the sample data into the excel file in our user.js file inside the Controller folder.

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: "Name", key: "name", 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) {
    console.log(err);
    res.send({
      status: "error",
      message: err,
    });
  }
};

module.exports = exportUser;

Here, we create an exceljs workbook object inside which we create a worksheet named My Users. In this object, we can create multiple worksheets but for this, we have only created a single sheet that will contain exported data. After that, we create a column to be displayed in the excel file. While defining the worksheet column, the key index should match with the data key returned from the Model/user.js file.

After, creating the columns we have looped through the model data and add it to the worksheet. Then, we modify the heading of the excel file so that we can distinguish between data and heading. This command cell.font = { bold: true } is used to make the heading bold. Then, the following command will export the model data into an excel file.

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

The exported excel file will be stored in the files/users.xlsx location. The exported file will look like the following:

Conclusion

By following the above process, we can export data in excel file in node js using the exceljs package. If you are searching for exporting files in other languages like Laravel you can follow this link.