Node.js Download Excel file example with exceljs

The Excel file is a spreadsheet file format created by Microsoft for use with Microsoft Excel. You can use the file to create, view, edit, analyse data, charts, budgets and more. In this tutorial, I will show you how to use Node.js Express Rest API to download Excel file from a table in MySQL Database using exceljs.

Related Posts:
– Node.js: Upload/Import Excel file data into Database
– Export MySQL data to CSV file using Node.js
– Export PostgresSQL data to CSV file using Node.js
– Export MongoDB collection to CSV file using Node.js

Download CSV File instead:
Node.js Download CSV file example

Node.js Download/Export Excel File overview

Assume that we have a tutorials table in MySQL database like this:

We’re gonna create a Node.js Application that provides APIs for downloading MySQL database table data as Excel file with following content:

If you send request to /api/excel/download, the server will return a response with an Excel file tutorials.xlsx that contains data in MySQL table:

How to do this?
You need to set the HTTP header:

"Content-disposition" : "attachment; filename=[yourFileName]" 
"Content-Type" : "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

We’re gonna apply the information above later in this tutorial.

You can also find how to upload Excel File to the Node.js Express Server & store data in MySQL Database in the post:
Node.js: Upload/Import Excel file data into Database

exceljs to create Excel file

We’re gonna use exceljs to create and add data (tutorials array) into Excel file.
Let me summarize the steps for writing to Excel file:

• create a new Workbook
• add a new WorkSheet using Workbook.addWorksheet()
• configure WorkSheet columns with header, key, width
• use WorkSheet.addRows() with array of objects you want to add as parameter
• use Workbook.xlsx.write() to write out Stream as response

const excel = require("exceljs");

let workbook = new excel.Workbook();
let worksheet = workbook.addWorksheet("Tutorials");

worksheet.columns = [
  { header: "Id", key: "id", width: 5 },
  { header: "Title", key: "title", width: 25 },
  { header: "Description", key: "description", width: 25 },
  { header: "Published", key: "published", width: 10 },
];

// Add Array Rows
worksheet.addRows(tutorials);

// res is a Stream object
res.setHeader(
  "Content-Type",
  "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
);
res.setHeader(
  "Content-Disposition",
  "attachment; filename=" + "tutorials.xlsx"
);

return workbook.xlsx.write(res).then(function () {
  res.status(200).end();
});

Technology

• express 4.17.1
• mysql2 2.1.0
• exceljs 4.0.1
• sequelize 5.21.13

Project Structure

This is the project directory that we’re gonna build:

– db.config.js exports configuring parameters for MySQL connection & Sequelize.
– models/index.js: uses configuration above to initialize Sequelize, models/tutorial.model.js for Sequelize Tutorial data model.
– excel.controllers.js: get data from database table, then uses exceljs to write out and return Excel file.
– routes/tutorial.routes.js: defines routes for endpoints that is called from HTTP Client, use controllers to handle requests.
– server.js: initializes routes, runs Express app.

Setup Node.js Excel File Download project

Open command prompt, change current directory to the root folder of our project.
Install Express, Multer, Sequelize, Mysql2 with the following command:

npm install express sequelize mysql2 exceljs

The package.json file will look like this:

{
  "name": "node-js-download-excel-files",
  "version": "1.0.0",
  "description": "Node.js Download/Export Excel file from MySQL database",
  "main": "src/server.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "node js",
    "download",
    "export",
    "excel",
    "file",
    "database",
    "mysql"
  ],
  "author": "bezkoder",
  "license": "ISC",
  "dependencies": {
    "exceljs": "^4.1.0",
    "express": "^4.17.1",
    "mysql2": "^2.1.0",
    "sequelize": "^6.3.0"
  }
}

Configure MySQL database & Sequelize

In the src folder, we create a separate config folder for configuration with db.config.js file like this:

module.exports = {
  HOST: "localhost",
  USER: "root",
  PASSWORD: "123456",
  DB: "testdb",
  dialect: "mysql",
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  }
};

First five parameters are for MySQL connection.
pool is optional, it will be used for Sequelize connection pool configuration:

• max: maximum number of connection in pool
• min: minimum number of connection in pool
• idle: maximum time, in milliseconds, that a connection can be idle before being released
• acquire: maximum time, in milliseconds, that pool will try to get connection before throwing error

For more details, please visit API Reference for the Sequelize constructor.

Initialize Sequelize

Now we initialize Sequelize in src/models folder.

Create src/models/index.js with the following code:

const dbConfig = require("../config/db.config.js");

const Sequelize = require("sequelize");
const sequelize = new Sequelize(dbConfig.DB, dbConfig.USER, dbConfig.PASSWORD, {
  host: dbConfig.HOST,
  dialect: dbConfig.dialect,
  operatorsAliases: false,

  pool: {
    max: dbConfig.pool.max,
    min: dbConfig.pool.min,
    acquire: dbConfig.pool.acquire,
    idle: dbConfig.pool.idle
  }
});

const db = {};

db.Sequelize = Sequelize;
db.sequelize = sequelize;

db.tutorials = require("./tutorial.model.js")(sequelize, Sequelize);

module.exports = db;

We’re gonna define Tutorial model in the next step.

Define the Sequelize Model

In models folder, create tutorial.model.js file like this:

module.exports = (sequelize, Sequelize) => {
  const Tutorial = sequelize.define("tutorial", {
    title: {
      type: Sequelize.STRING
    },
    description: {
      type: Sequelize.STRING
    },
    published: {
      type: Sequelize.BOOLEAN
    }
  });

  return Tutorial;
};

This Sequelize Model represents tutorials table in MySQL database with columns: id, title, description, published, createdAt, updatedAt.

After initializing Sequelize, we don’t need to write CRUD functions, Sequelize supports all of them, including retrieving all Tutorials with findAll() method that we’re gonna use in our Controller.

Create Controller for downloading Excel file

controllers/tutorial/excel.controller.js

const db = require("../../models");
const Tutorial = db.tutorials;

const excel = require("exceljs");

const download = (req, res) => {
  Tutorial.findAll().then((objs) => {
    let tutorials = [];

    objs.forEach((obj) => {
      tutorials.push({
        id: obj.id,
        title: obj.title,
        description: obj.description,
        published: obj.published,
      });
    });

    let workbook = new excel.Workbook();
    let worksheet = workbook.addWorksheet("Tutorials");

    worksheet.columns = [
      { header: "Id", key: "id", width: 5 },
      { header: "Title", key: "title", width: 25 },
      { header: "Description", key: "description", width: 25 },
      { header: "Published", key: "published", width: 10 },
    ];

    // Add Array Rows
    worksheet.addRows(tutorials);

    res.setHeader(
      "Content-Type",
      "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    );
    res.setHeader(
      "Content-Disposition",
      "attachment; filename=" + "tutorials.xlsx"
    );

    return workbook.xlsx.write(res).then(function () {
      res.status(200).end();
    });
  });
};

module.exports = {
  download,
};

Now look at the download function:
– First we use Sequelize Model findAll() method to return all Tutorials stored in the tutorials table.
– Then we use exceljs to write data to Excel file:

• create a new Workbook
• add a new WorkSheet using Workbook.addWorksheet()
• configure WorkSheet columns with header, key, width
• use WorkSheet.addRows() with array of objects you want to add as parameter
• use Workbook.xlsx.write() to write out Stream as response

Define Routes for downloading Excel File

When a client sends request for an endpoint using HTTP request (GET excel file), we need to determine how the server will response by setting up the routes.

This is the route we need to setup: GET /api/excel/download.

Create a tutorial.routes.js inside routes folder with content like this:

const express = require("express");
const router = express.Router();
const excelController = require("../controllers/tutorials/excel.controller");

let routes = (app) => {
  router.get("/download", excelController.download);

  app.use("/api/excel", router);
};

module.exports = routes;

You can see that we use a controller from excel.controller.js.

Create Express app server

Finally, we create an Express server.

server.js

const express = require("express");
const app = express();
const db = require("./models");
const initRoutes = require("./routes/tutorial.routes");

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

db.sequelize.sync();

let port = 8080;
app.listen(port, () => {
  console.log(`Running at localhost:${port}`);
});

In the code above, we initialize Express Router and call Sequelize sync() method.

db.sequelize.sync();

Run the Node.js Download Excel File App

Run the Node.js App with command: node src/server.js.
Now you can use browser or a HTTP Client to send GET request to http://localhost:8080/api/excel/download.

Conclusion

Today we’ve built a Rest API using Node.js Express to download Excel file that contains table data in MySQL database.

We also see how to use exceljs to write data to Excel Sheet, Sequelize to retrieve items in database table without need of boilerplate code.

You can also find how to upload Excel File to the Node.js Server & store data in MySQL Database in the post:
Node.js: Upload/Import Excel file data into MySQL Database

Or Download CSV File instead:
Node.js Download CSV file example

Happy learning! See you again.

Further Reading

• https://www.npmjs.com/package/express
• https://sequelize.org/v3/api/model/
• exceljs

Source Code

You can find the complete source code for this example on Github.