Here at UseCSV we're focused on bringing you the best solution for adding CSV and Excel import to your app. But we often get asked about how to export data in Excel format from you app. We'll show you how you can use the ExcelJS library to create Excel files for download in your Node.js app. Let's get started.

Step 1: Setting up the Project

You'll need the latest version of Node.js and NPM installed on your machine. Then, we need to create a Node.js project and install Express.

Copy text
npm init
npm install express

After that, create a new Node.js project using the following command.

Copy text
express <your_app_name> --view=ejs

This command will create a basic Express.js setup with EJS as the view engine. After creating the project, we can start the Node development server by using the command:

Copy text
npm start

This should start your application and visit http://localhost:3000/.

So far, these steps are pretty standard. Now let's get to the specific implementation of the Excel file downloader.

Step 2: Install the ExcelJS package

Once the project is set up, we must install the ExcelJS package. This package provides a simple API to generate Excel spreadsheets with formulas, formatting, and images.

Open your terminal and type the following command:

Copy text
npm install exceljs

Step 3: Create a Model

Now, we will create a model that holds the user data we want to export. In this case, we will write the data directly in the model.

Create a new file named User.js in the models directory:

Copy text
// models/User.js
// GET DATA FROM DATABASE 
// Sample data

const User = [
  {
   fname: "John",
   lname: "Doe",
   email: "john.doe@example.com",
   gender: "Male",
  },
  {
   fname: "Jane",
   lname: "Doe",
   email: "jane.doe@example.com",
   gender: "Female",
  },
  {
   fname: "Bob",
   lname: "Smith",
   email: "bob.smith@example.com",
   gender: "Male",
  },
];

module.exports = User;

Step 4: Create a Express controller

Next, we need to create a controller that will link to our route. Create a new file in the controllers directory called User.js with the following contents:

Copy text
const User = require("../models/User");
const excelJS = require("exceljs");
const exportUser = async (req, res) => {
   // We'll write this code in a moment
};
module.exports = exportUser;

Step 5: Set up the Express route

Now, we need to set up a route. Open the routes/index.js file and add the following code:

Copy text
const express = require("express");
const exportUser = require("../controllers/User");

const router = express.Router();

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

module.exports = router;

This code sets up a API route called /downloadExcel that uses the exportUser controller we created in the previous step.

Step 6: Write the Excel download logic

Let's write the logic for generating and downloading the Excel file in the controller we created earlier. Add the following code to the exportUser function in the User.js file:

Copy text
const workbook = new excelJS.Workbook(); 
const worksheet = workbook.addWorksheet("Users");

// Define columns in the worksheet 
worksheet.columns = [ 
{ header: "First Name", key: "fname", width: 15 }, 
{ header: "Last Name", key: "lname", width: 15 }, 
{ header: "Email", key: "email", width: 25 }, 
{ header: "Gender", key: "gender", width: 10 }, 
];

// Add data to the worksheet 
User.forEach(user => { worksheet.addRow(user); });

// Set up the response headers 
res.setHeader("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); res.setHeader("Content-Disposition", "attachment; filename=" + "users.xlsx");

// Write the workbook to the response object 
workbook.xlsx.write(res).then(() => res.end());

This code creates a new Excel workbook, adds a worksheet, defines the columns in the worksheet, adds data, and sets up the response headers. Finally, it writes the workbook to the response object, which triggers the Excel file download.

Step 7: Test the download

To test the application, start the development server by running the command npm start in the terminal and then navigate to http://localhost:3000/downloadExcel in your web browser. This will trigger the Excel file download with the data we defined in the User.js model.

Conclusion

This tutorial taught us how to create and download an Excel file using Node.js and ExcelJS packages. If you also need to support users uploading and importing CSV and Excel files, you'll find our product UseCSV useful. We give you a complete data import experience that you can embed into your app, with powerful features like auto column matching and validation rules.