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
- 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.