Node.js Extract MySQL Data to Excel(.xlsx) File – using exceljs

In the tutorial, Grokonez shows how to extract data from MySQL to Excel(.xlsx) File with Node.js using exceljs lib.

Technologies

• Node.js
• MySQL
• exceljs

Goal

– We create a Node.js project as below structure:

– MySQL Data:

– Excel File:

Practice

Install MySQL & Exceljs

– Init package.json file by cmd: npm init -> Then install mysql & exceljs libs:

$npm install --save mysql
$npm install --save exceljs

-> package.json file:

{
  "name": "nodejs-export-mysql-data-to-excel-file",
  "version": "1.0.0",
  "description": "Nodejs exports MySQL data to Excel file",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
	"start": "node index.js"
  },
  "keywords": [
    "Nodejs",
    "MySQL",
    "Excel"
  ],
  "author": "ozenero.com",
  "license": "ISC",
  "dependencies": {
    "exceljs": "^1.7.0",
    "mysql": "^2.16.0"
  }
}

Extract MySQL Data to Excel

-> index.js file:

const mysql = require('mysql');
const excel = require('exceljs');

// Create a connection to the database
const con = mysql.createConnection({
 host: 'localhost',
 user: 'root',
 password: '12345',
 database: 'testdb'
});

// Open the MySQL connection
con.connect((err) => {
	if (err) throw err;
 
	// -> Query data from MySQL
	con.query("SELECT * FROM customer", function (err, customers, fields) {
		
		const jsonCustomers = JSON.parse(JSON.stringify(customers));
		console.log(jsonCustomers);
		/**
			[ { id: 1, address: 'Jack Smith', age: 23, name: 'Massachusetts' },
			{ id: 2, address: 'Adam Johnson', age: 27, name: 'New York' },
			{ id: 3, address: 'Katherin Carter', age: 26, name: 'Washington DC' },
			{ id: 4, address: 'Jack London', age: 33, name: 'Nevada' },
			{ id: 5, address: 'Jason Bourne', age: 36, name: 'California' } ]
		*/
		
		let workbook = new excel.Workbook(); //creating workbook
		let worksheet = workbook.addWorksheet('Customers'); //creating worksheet
	 
		//  WorkSheet Header
		worksheet.columns = [
			{ header: 'Id', key: '_id', width: 10 },
			{ header: 'Name', key: 'name', width: 30 },
			{ header: 'Address', key: 'address', width: 30},
			{ header: 'Age', key: 'age', width: 10, outlineLevel: 1}
		];
	 
		// Add Array Rows
		worksheet.addRows(jsonCustomers);
	 
		// Write to File
		workbook.xlsx.writeFile("customer.xlsx")
		.then(function() {
			console.log("file saved!");
		});
		
		// -> Close MySQL connection
		con.end(function(err) {
		  if (err) {
			return console.log('error:' + err.message);
		  }
		  console.log('Close the database connection.');
		});
		
		// -> Check 'customer.csv' file in root project folder
	});
});

SourceCode

– MySQL Script to create customer table:

CREATE TABLE `customer` (
  `id` bigint(20) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

– MySQL Insert Data’s script:

INSERT INTO customer (id, name, address, age) VALUES (1, "Jack Smith", "Massachusetts", 23);
INSERT INTO customer (id, name, address, age) VALUES (2, "Adam Johnson", "New York", 27);
INSERT INTO customer (id, name, address, age) VALUES (3, "Katherin Carter", "Washington DC", 26);
INSERT INTO customer (id, name, address, age) VALUES (4, "Jack London", "Nevada", 33);
INSERT INTO customer (id, name, address, age) VALUES (5, "Jason Bourne", "California", 36);

– Sourcecode: Nodejs-Export-MySQL-Data-to-Excel-File