In the tutorial, Grokonez shows how to download & extract Excel(.xlsx) File/Data from MySQL with Nodejs Express and exceljs lib.
Related posts:
– Node.js Extract MySQL Data to Excel(.xlsx) File – using exceljs
Contents
Technologies
- nodejs
- express
- exceljs
- mysql
Goal
– We create a Node.js project as below structure:
– MySQL Data:
– Results:
Practice
Install Express, MySQL & Exceljs
Init package.json file by cmd: npm init -> Then install express, mysql & exceljs libs:
1 | $ npm install epxress exceljs mysql --save |
-> package.json file:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | { "name": "node.js-express-restapi-download-extract-excel-file-from-mysql", "version": "1.0.0", "description": "Nodejs Express RestAPI Download/Extract Excel-Data-File-from-MySQL", "main": "index.js", "scripts": { "test": "echo \"Error: no test specified\" && exit 1", "start": "node index.js" }, "keywords": [ "Nodejs", "Express", "RestAPI", "RestAPI", "Download-File", "MySQL" ], "author": "grokonez.com", "license": "ISC", "dependencies": { "exceljs": "^1.7.0", "express": "^4.16.4", "mysql": "^2.16.0" } } |
RestAPI Download/Extract Excel data from MySQL
-> index.js file:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 | const mysql = require('mysql'); const excel = require('exceljs'); const express = require('express'); const app = express(); // -> Express RestAPIs app.get("/api/download/excel", function (req, res) { // -> Create a connection to the database let con = mysql.createConnection({ host: 'localhost', user: 'root', password: '12345', database: 'gkzdb' }); // Open the MySQL connection con.connect((err) => { if (err) throw err; console.log("Connected!"); // -> Query data from MySQL con.query("SELECT * FROM customer", function (err, customers, fields) { if (err) throw err; 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); res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); res.setHeader('Content-Disposition', 'attachment; filename=' + 'customer.xlsx'); return workbook.xlsx.write(res) .then(function() { res.status(200).end(); }); }); }) }); // Create a Server let server = app.listen(8080, function () { let host = server.address().address let port = server.address().port console.log("App listening at http://%s:%s", host, port) }) |
SourceCode
– MySQL Script to create customer table:
1 2 3 4 5 6 7 | 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:
1 2 3 4 5 | 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: Node.js-Express-RestAPI-Download-Extract-Excel-File-from-MySQL