Export Excel files in Node.js
Jun 6, 2023
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.
npm init npm install express
After that, create a new Node.js project using the following command.
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:
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:
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:
// 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:
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:
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:
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.