Solving the Mystery of Export to Excel in NodeJS

How to create an export to excel service using NodeJS and Express

Prerequisite

It would be expected that the reader has basic knowledge of NodeJS and ExpressJS since this read is more towards targeting a specific use case rather than an overview of Node and Express :)

Use case

It was a couple of months back when I was assigned with a mind-boggling task of creating an export to excel service for one of the applications in banking domain. It took us lot of research, struggle and perseverance to come up with the best library for achieving this, hence we thought of sharing this with the open source community.

Let’s lay down the basic requirements for the export to excel service.

• Exported excel file should contain the list of the customers whose current bank balance is greater than or equal to the specified amount in query parameter.
• Sheet should be password protected for modifications with the password as the username of the user who raised the request for exporting the file. The username will be available in the request header.
• Sheet that has a list of customers should have a filter applied by default.
• First row should freeze when the user scrolls the list of customer.
• A total row should be displayed at the end to show the total balance of all the customers.

Now that we are aware of the problem statement, let’s solve this mystery step by step. The strategy is to create a NodeJS with Express application which uses a library to export the JSON documents from MongoDB to an excel file. As always, in the npm world, choosing the best library for the defined use case takes a lot of trial and error considering there are tons of libraries available out there. For our use case and feature request, we had to choose from these following available options:

NPM export to excel libraries mapped to its supported feature set

Based on the above mentioned technical and feature set analysis we landed on a decision to use exceljs for our use case. ExcelJS supports password protection out of the box which would enable us to protect the generated files using the username as the password. In addition to that, it supports features such as total row at the end and row freeze as well to freeze the first row which contains the header of the grid.

With that in mind, we are all set to get our hands dirty and dive directly into the code. What are we waiting for? Let’s get the ball rolling.

Application Setup

Step 1: Let’s create an npm package using the following command.

npm init

It should ask you couple of questions, answer them as follows:

Answering the questions asked while npm init

Step 2: Install express, http-status-code, mongoose, nodemon and dotenv using the following commands.

npm install express http-status-codes mongoose exceljs --save

npm install nodemon dotenv --save-dev

Step 3: Create the following files and folders in your working directory.

Folder structure to begin the development of export to excel service

Step 4: Next up we will setup the database. We will be using MongoDB Atlas cloud database but you’re free to setup MongoDB locally as well.

MongoDB Atlas with customer database and customers collection

Step 5: Let us now insert sample data in customers collection so that it could be used to generate the report. We have used json-generator tool using the schema mentioned below to generate sample records. Copy the schema and you should be able to generate around 33 records for testing purposes.

[
  '{{repeat(5, 100)}}',
  {
    _id: '{{objectId()}}',
    name: '{{firstName()}} {{surname()}}',
    balance: '{{floating(1000, 40000, 2)}}',
    gender: '{{gender()}}',
    email: '{{email()}}',
    age: '{{integer(18, 90)}}'
  }
]

Step 6: Download the json file from the json generator tool and import it into MongoDB using MongoDB compass tool. This is the last step to up the database.

Customer data imported into MongoDB Compass

Step 7: Add the following content to .env file which we created in step 3. This file will contain all the configurations related to the application.

APPLICATION_ENV=development
APPLICATION_PORT=3000
CUSTOMER_DB_URL=<Your db connection string>

Step 8: In the config/config.js file add the following content. The code in this file is responsible to read the environment variables loaded by NodeJS and provide a single access point.

"use strict";
const APP = Object.freeze({
PORT: process.env.APPLICATION_PORT,
ENVIRONMENT: process.env.APPLICATION_ENV,
});
const MONGODB = Object.freeze({
CUSTOMER_DB_URL: process.env.CUSTOMER_DB_URL,
});
module.exports = {
APP,
MONGODB,
};

Step 9: In common/db.js add the code related to establish database connection as follows.

"use strict";
const { MONGODB } = require("../config/config");
const mongoose = require("mongoose");
module.exports = () => {
const DB_OPTIONS = {
useNewUrlParser: true,
useUnifiedTopology: true,
};
mongoose.connect(MONGODB.CUSTOMER_DB_URL, DB_OPTIONS);
const db = mongoose.connection;
db.on("error", (error) => {
console.log(`Connection to DB failed due to ${error}`);
mongoose.disconnect();
});
db.once("connected", () => {
console.log(`DB was connected`);
});
db.once("open", () => {
console.log(`Connection to DB was opened`);
});
db.on("disconnected", () => {
console.log(`Database was disconnected`);
});
process.on("SIGINT", () => {
db.close(() => {
console.log(`Connection was closed successfully`);
process.exit();
});
});
};

Step 10: In the server.js file available at the root level, add the following content to test the connection to the database and run the express application on port 3000

require("dotenv").config();
const app = require("express")();
const { APP } = require("./config/config");
const connectDb = require("./common/db");
const customers = require("./routes/customer");
connectDb();
app.use("/api", customers);
app.listen(APP.PORT, () => {
console.log(`The export to excel service is listening on port ${APP.PORT}`);
});

Step 11: Add a start command in package.json file available at the root folder. Here we are setting up nodemon to ensure that the application gets reloaded whenever we save changes to the js files. Your package.json should look like this.

{
"name": "export-to-excel",
"version": "0.0.1",
"description": "A service for exporting JSON to excel",
"main": "server.js",
"scripts": {
"start": "nodemon server.js",
"test": "echo \"Error: no test specified\" && exit 1"
},
"author": "Faizal Vasyaa",
"license": "ISC",
"dependencies": {
"exceljs": "^4.2.0",
"express": "^4.17.1",
"http-status-codes": "^2.1.4",
"mongoose": "^5.11.8"
},
"devDependencies": {
"dotenv": "^8.2.0",
"nodemon": "^2.0.6"
}
}

Routes and Validations

Step 1: Let’s create a route that will be invoked by the user to generate an excel file. Add the following content in the routes/customer.js file. Here, we have added couple of validators to check the balance amount received in query parameter and the availability of username in the header (Remember that the report that needs to be generated requires the query parameter and the username in the headers for setting the password)

const customerRouter = require("express").Router();
const customerController = require("../controllers/customer");
const {
balanceValidator,
usernameValidator,
} = require("../validators/customer");
customerRouter.get(
"/customer/extractData",
balanceValidator,
usernameValidator,
customerController.extractData
);
module.exports = customerRouter;

Step 2: The next step is to create the validators in the validators/customer.js file. Add the following line of code to create a balance validator and a username validator.

"use strict";
const { StatusCodes } = require("http-status-codes");
const balanceValidator = (request, response, next) => {
if (!request.query.minBalance) {
return response.status(StatusCodes.UNPROCESSABLE_ENTITY).json({
message: "Minimum balance is required to extract customer data",
});
} else if (isNaN(request.query.minBalance)) {
return response.status(StatusCodes.UNPROCESSABLE_ENTITY).json({
message:
"Minimum balance must be a valid integer or floating point number to extract customer data",
});
} else if (
parseFloat(request.query.minBalance) >=
Number.MAX_SAFE_INTEGER - 1
) {
return response.status(StatusCodes.UNPROCESSABLE_ENTITY).json({
message: "Unsupported number to extract customer data",
});
}
next();
};
const usernameValidator = (request, response, next) => {
const username = request.get("X-username");
if (!username) {
return response.status(StatusCodes.UNPROCESSABLE_ENTITY).json({
message: "Username is required to extract customer data",
});
}
next();
};
module.exports = {
balanceValidator,
usernameValidator,
};

Step 3: Now that we have validated our requests, let’s create the controller which would handover the minBalance and username to the service for further processing. Add the following content in controllers/customer.js file.

"use strict";
const customerService = require("../services/customer");
const extractData = async (request, response, next) => {
try {
const username = request.get("X-username");
const workbook = await customerService.exportToExcel(
parseFloat(request.query.minBalance),
request.get("X-username")
);
response.set({
"Content-disposition": `attachment; filename=${username}.xlsx`,
"Content-Type":
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
});
return workbook.xlsx.write(response).then(() => {
response.status(200).end();
});
} catch (err) {
return response.status(500).json({
message: `Internal server error: ${err.toString()}`,
});
}
};
module.exports = { extractData };

Ready to munch the meat ?

Step 1: Here comes the export to excel service, whose responsibility is to get the minimum balance and username from controller, query the database to fetch the customers who have balance greater than the minimum balance, create an excel of such customers and the send it back to the controller. First, add the following code to models/customer.js.

"use strict";
const mongoose = require("mongoose");
const customerSchema = new mongoose.Schema(
{
name: {
type: String,
},
balance: {
type: Number,
},
gender: {
type: String,
enum: ["male", "female"],
},
email: {
type: String,
},
age: {
type: Number,
},
},
{
collection: "customers",
}
);
module.exports = mongoose.model("Customer", customerSchema);

Step 2: Next up, we will create a simple service that will fetch customer data whose balance is above minimum balance and return it to the controller. Add the following code in services/customer.js

"use strict";
const Customer = require("../models/customer");
const exportToExcelUtility = require("../common/export-to-excel");
const customerProjection = {
_id: 0,
__v: 0,
};
const exportToExcel = async (minBalance, username) => {
try {
const customers = await Customer.find({
balance: { $gte: minBalance },
}).select(customerProjection);
const rows = transformRows(customers);
const columns = [
{ name: "Name", totalsRowLabel: "Total:", filterButton: true },
{ name: "Gender", filterButton: true },
{ name: "Age", filterButton: true },
{ name: "Email", filterButton: false },
{
name: "Balance",
totalsRowFunction: "custom",
totalsRowFormula: `SUBTOTAL(109,E2:E${rows.length + 1})`,
filterButton: false,
},
];
return exportToExcelUtility(rows, username, "Customer", columns);
} catch (err) {
throw err;
}
};
const transformRows = (customers) => {
const transformedRows = customers.map((row) => {
let transformedRow = [];
transformedRow.push(row._doc["name"]);
transformedRow.push(row._doc["gender"]);
transformedRow.push(row._doc["age"]);
transformedRow.push(row._doc["email"]);
transformedRow.push(row._doc["balance"]);
return transformedRow;
});
return transformedRows;
};
module.exports = { exportToExcel };

Step 3: In common/export-to-excel.js and add the following content.

"use strict";
const excel = require("exceljs");
const exportToExcelUtility = async (rows, username, sheetName, columns) => {
// Creating a workbook
let workbook = new excel.Workbook();
workbook.creator = username;
workbook.lastModifiedBy = username;
workbook.created = new Date();
workbook.modified = new Date();
// Adding worksheet to workbook
let worksheet = workbook.addWorksheet(sheetName, {
views: [{ state: "frozen", xSplit: 0, ySplit: 1 }],
});
worksheet.state = "visible";
worksheet.properties.defaultColWidth = 30;
// Adding table to worksheet
worksheet.addTable({
name: sheetName,
ref: "A1",
headerRow: true,
totalsRow: true,
style: {
theme: "TableStyleLight1",
showFirstColumn: true,
showRowStripes: true,
},
columns: columns,
rows: [...rows],
});
// Adding password to worksheet to protect it from editing
await worksheet.protect(username);
return workbook;
};
module.exports = exportToExcelUtility;

Once we are done with all these steps, run npm start which should show the output on the command prompt below

How to test it

• Open postman, type the URL http://localhost:3000/api/customer/extractData?minBalance=10000 in the address bar and hit Send and Download button as shown below.

• A prompt should ask you to save the file at a given location.
• Once, saved open it using MS Excel.

Known issues:

• In case you need to support Libre office, this library is not for you. The file generated by this library does not have same behavior in MS Excel and Libre office.
• In some versions of MS Excel, you might get warnings when you open the file. You may ignore them and proceed ahead.

Hope this was an interesting experiment for you. Feel free to reach out via comments or LinkedIn in case you get issues running the above demonstration :)