Welcome to a tutorial on how to export data from a MySQL database to an Excel file in NodeJS. So you need to generate a report from the database, or create a list? Well, it is actually a simple “fetch from database and write to Excel file” process – Read on for the example!
ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.
TABLE OF CONTENTS
DOWNLOAD & NOTES
Firstly, here is the download link to the example code as promised.
QUICK NOTES
- Download and unzip into your project folder. For example,
D:\xlsx
- Create a database for testing, and import
1-users.sql
. - Open
2-export.js
, change the database settings to your own. - Launch the terminal, navigate to your project folder
cd D:\xlsx
. Install the required modules –npm install mysql xlsx
. - Run the demo –
node 2-export.js
EXAMPLE CODE DOWNLOAD
Click here to download all the example source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.
EXPORT DATABASE TO EXCEL
All right, let us now get into the example of exporting data from the MySQL database to writing it to an Excel file.
PART 1) DUMMY DATABASE
-- (A) USERS TABLE
CREATE TABLE `users` (
`user_id` bigint(20) NOT NULL,
`user_name` varchar(255) NOT NULL,
`user_email` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `users`
ADD PRIMARY KEY (`user_id`),
ADD UNIQUE KEY `user_email` (`user_email`),
ADD KEY `user_name` (`user_name`);
ALTER TABLE `users`
MODIFY `user_id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
-- (B) DUMMY USERS
INSERT INTO `users` (`user_id`, `user_name`, `user_email`) VALUES
(1, 'Jo Doe', 'jo@doe.com'),
(2, 'Job Doe', 'job@doe.com'),
(3, 'Joe Doe', 'joe@doe.com'),
(4, 'Jog Doe', 'jog@doe.com'),
(5, 'Joh Doe', 'joh@doe.com'),
(6, 'Joi Doe', 'joi@doe.com'),
(7, 'Jol Doe', 'jol@doe.com'),
(8, 'Jon Doe', 'jon@doe.com'),
(9, 'Jou Doe', 'jou@doe.com'),
(10, 'Joy Doe', 'joy@doe.com');
First, let us start by creating a simple users
database and defining some dummy users.
user_id
User ID, primary key, and auto-increment.user_name
The user’s name.user_email
The user’s email.
PART 2) EXPORT TO EXCEL FILE
// (A) LOAD MODULES
// npm install mysql xlsx
// https://www.npmjs.com/package/mysql
// https://www.npmjs.com/package/xlsx
const mysql = require("mysql"),
xlsx = require("xlsx");
// (B) CONNECT TO DATABASE - CHANGE SETTINGS TO YOUR OWN!
const db = mysql.createConnection({
host: "localhost",
user: "root",
password: "",
database: "test"
});
db.connect();
// (C) EXPORT TO EXCEL
db.query("SELECT * FROM `users`", (error, results) => {
// (C1) EXTRACT DATA FROM DATABASE
if (error) throw error;
var data = [];
results.forEach(row => {
data.push([row["user_name"], row["user_email"]]);
});
// (C2) WRITE TO EXCEL FILE
var worksheet = xlsx.utils.aoa_to_sheet(data),
workbook = xlsx.utils.book_new();
xlsx.utils.book_append_sheet(workbook, worksheet, "Users");
xlsx.writeFile(workbook, "demo.xlsx");
});
// (D) DONE - CLOSE DB CONNECTION
db.end();
Lastly, I don’t think this needs a line-by-line explanation…
- Load the necessary modules. Yes, NodeJS does not have the native capabilities to connect to MySQL and generate Excel files. Just make sure that you have properly installed
mysql
andxlsx
. - Connect to the MySQL Database. A reminder to change the settings to your own.
- Extract data from the database, and write them into a new Excel file.
- Close the database connection.
The end.
EXTRA BITS & LINKS
That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.
EXPORT PERFORMANCE & EFFICIENCY
Take note of how the export works – We are reading all the database entries into the data
array first, then writing them into the Excel file in one shot. While this works for small to medium datasets, this will probably run into performance issues while exporting massive amounts of data.
So do your own due diligence if you have to deal with large exports. Change the export process to “read row by row” and “append to the Excel file row by row” instead.
LINKS & REFERENCES
- SheetJS – NPM
- MySQL – NPM
- Import Excel Into Database In NodeJS – Code Boxx
THE END
Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!