Nodejs Upload Excel files to MySQL/PostgreSQL – (Express RestAPI Upload/Import Download/Export Multiple Excel files)
In the tutorial, I will introduce how to build a Nodejs/Express RestAPIs application to upload/import and download/extract data from multiple Excel files to MySQL or PostgreSQL database by:
Express
is used to build Nodejs RestApisMulter
is used to upload/download Excel filesSequelize
ORM is used to manipulate data with MySQL/PostgreSQLExceljs
is used to save data objects to Excel fileRead-excel-file
is used to parse Excel file to data objects
Overview – Nodejs/Express Upload Download Excel Files
Architecture

We build Nodejs RestAPIs on the top of Express framework and use the Multer library to handle upload multiple excel files. For handling the business processing of our application, We implement a Controller.js that uses exceljs
and read-excel-file
libraries to write/read data objects to excel files. And for doing CRUD operations with MySQL and PostgreSQL database, we use Sequelizez ORM to finalize the jobs.
Goal
Project Structure

config
package is used to setup database configuration with Sequelize ORM and Multer’s configuration for uploading filemodels
package is used to define Sequelize Customer modelrouters
package is used to define Express router for Nodejs RestAPIscontrollers
is used to implement coding to read/write Excel files and interact with database (storing/retrieving)view
package is used to define a html view page for upload/download Excel filesresource/static/js
is used to implement Ajax Javascript code to upload multiples Excel files
Results

Check MySQL database:

Implementation – How to code it?
Backend Development
Create Nodejs project
Install express, multer, exceljs, read-excel-file, sequelize, mysql2 by cmd:
npm install --save express, multer, exceljs, read-excel-file, sequelize, mysql2
Check the package.json
file, you see:
{
"name": "nodejs-upload-excel-files",
"version": "1.0.0",
"description": "Nodejs Upload/Extract and Download/Import Excels files to MySQL",
"main": "index.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1"
},
"keywords": [
"nodejs",
"mysql",
"upload_excel_files",
"download_excel_file",
"export_excel_file",
"import_excel_file"
],
"author": "https://loizenjava.com",
"license": "ISC",
"dependencies": {
"await": "^0.2.6",
"exceljs": "^4.0.1",
"express": "^4.17.1",
"multer": "^1.4.2",
"mysql2": "^2.1.0",
"read-excel-file": "^4.0.6",
"sequelize": "^5.21.13"
}
}
Node.js Configuration
Database Config
We define a Sequelize setting in db.config.js
file for interacting with database:
const env = require('./env.js');
const Sequelize = require('sequelize');
const sequelize = new Sequelize(env.database, env.username, env.password, {
host: env.host,
dialect: env.dialect,
operatorsAliases: false,
pool: {
max: env.max,
min: env.pool.min,
acquire: env.pool.acquire,
idle: env.pool.idle
}
});
const db = {};
db.Sequelize = Sequelize;
db.sequelize = sequelize;
db.Customer = require('../models/customer.model.js')(sequelize, Sequelize);
module.exports = db;
– For MySQL database, we add the below configured environment’s properties in env.js
:
const env = {
database: 'loizenjavadb',
username: 'root',
password: '12345',
host: 'localhost',
dialect: 'mysql',
pool: {
max: 5,
min: 0,
acquire: 30000,
idle: 10000
}
};
module.exports = env;
– For PostgreSQL, we change the env.js
as below:
const env = {
database: 'loizenjava',
username: 'postgres',
password: '123',
host: 'localhost',
dialect: 'postgres',
pool: {
max: 5,
min: 0,
acquire: 30000,
idle: 10000
}
};
module.exports = env;
Multer Config
const multer = require('multer');
const storage = multer.diskStorage({
destination: (req, file, cb) => {
cb(null, __basedir + '/uploads/')
},
filename: (req, file, cb) => {
cb(null, file.fieldname + "-" + Date.now() + "-" + file.originalname)
}
});
const upload = multer({storage: storage});
module.exports = upload;
We use multer to create a uploading engine with storage’s path at __basedir + '/uploads/'
.
Create Sequelize Data Model
I create a Sequelize model for customer entity with 4 properties:
id
is a primary key havingINTEGER
typename
property hasSTRING
typeaddress
property hasSTRING
typeage
property hasINTEGER
type
module.exports = (sequelize, Sequelize) => {
const Customer = sequelize.define('customer', {
id: {
type: Sequelize.INTEGER,
autoIncrement: true,
primaryKey: true
},
name: {
type: Sequelize.STRING
},
address: {
type: Sequelize.STRING
},
age: {
type: Sequelize.INTEGER
}
});
return Customer;
}
Define Excel Router
We create 4 RestAPIs for upload/download Excel files:
- GET Api at
/
returns the upload/download form - POST Api at
/api/file/upload
is used to upload single Excel file - POST Api at
/api/file/multiple/upload
is used to upload multiple Excel files - GET Api at
/api/file
is used to download Excel file
Coding:
let express = require('express');
let router = express.Router();
let upload = require('../config/multer.config.js');
const excelWorker = require('../controllers/excel.controller.js');
let path = __basedir + '/views/';
router.get('/', (req,res) => {
console.log("__basedir" + __basedir);
res.sendFile(path + "index.html");
});
router.post('/api/file/upload', upload.single("file"), excelWorker.uploadFile);
router.post('/api/file/multiple/upload', upload.array('files', 4), excelWorker.uploadMultipleFiles);
router.get('/api/file', excelWorker.downloadFile);
module.exports = router;
Implement Excel Controller
For proccesing Upload/Download Excel files, we implement excel.controller.js
file with 3 functions:
exports.uploadFile = (req, res)
is used to upload single excel fileexports.uploadMultipleFiles = async (req, res)
is used to upload multiple excel filesexports.downloadFile = (req, res)
is used to download Excel file
Upload Excel Files
Parse Excel File to data object
For parsing Data Objects from Excel file rows, we use read-excel-file
library as below:
readXlsxFile(filePath).then(rows => {
// `rows` is an array of rows
// each row being an array of cells.
console.log(rows);
// Remove Header ROW
rows.shift();
const customers = [];
let length = rows.length;
for(let i=0; i<length; i++){
let customer = {
id: rows[i][0],
name: rows[i][1],
address: rows[i][2],
age: rows[i][3]
}
customers.push(customer);
}
Upload Single Excel File
exports.uploadFile = (req, res) => {
try{
let filePath = __basedir + "/uploads/" + req.file.filename;
readXlsxFile(filePath).then(rows => {
// `rows` is an array of rows
// each row being an array of cells.
console.log(rows);
// Remove Header ROW
rows.shift();
const customers = [];
let length = rows.length;
for(let i=0; i<length; i++){
let customer = {
id: rows[i][0],
name: rows[i][1],
address: rows[i][2],
age: rows[i][3]
}
customers.push(customer);
}
Customer.bulkCreate(customers).then(() => {
const result = {
status: "ok",
filename: req.file.originalname,
message: "Upload Successfully!",
}
res.json(result);
});
});
}catch(error){
const result = {
status: "fail",
filename: req.file.originalname,
message: "Upload Error! message = " + error.message
}
res.json(result);
}
}
After uploading, Excel file is saved to path __basedir + "/uploads/" + req.file.filename
.
We use readXlsxFile
to parse the Excel file to rows
data. Then we iterate through the rows
to build an array data objects customers
.
Then we use function Customer.bulkCreate(customers)
to save an array customer objects to database.
After all, we build a result object and return it to client with simple format as below:
...
const result = {
status: "ok",
filename: req.file.originalname,
message: "Upload Successfully!",
}
res.json(result);
...
Upload Multiple Excel Files
We create a function exports.uploadMultipleFiles = async (req, res)
for uploading multiple Excel files:
exports.uploadMultipleFiles = async (req, res) => {
const messages = [];
for (const file of req.files) {
try{
let filePath = __basedir + "/uploads/" + file.filename;
let rows = await readXlsxFile(filePath);
// `rows` is an array of rows
// each row being an array of cells.
console.log(rows);
// Remove Header ROW
rows.shift();
const customers = [];
let length = rows.length;
for(let i=0; i<length; i++){
let customer = {
id: rows[i][0],
name: rows[i][1],
address: rows[i][2],
age: rows[i][3]
}
customers.push(customer);
}
uploadResult = await Customer.bulkCreate(customers);
// It will now wait for above Promise to be fulfilled and show the proper details
console.log(uploadResult);
if (!uploadResult){
const result = {
status: "fail",
filename: file.originalname,
message: "Can NOT upload Successfully",
}
messages.push(result);
} else {
const result = {
status: "ok",
filename: file.originalname,
message: "Upload Successfully!",
}
messages.push(result);
}
}catch(error){
const result = {
status: "fail",
filename: file.originalname,
message: "Error -> " + error.message
}
messages.push(result);
}
}
return res.json(messages);
}
Download Excel File
Write data objects to Excel file
For writing data objects to Excel file, we use const excel = require('exceljs');
to do it:
const excel = require('exceljs');
...
const jsonCustomers = JSON.parse(JSON.stringify(customers));
let workbook = new excel.Workbook(); //creating workbook
let worksheet = workbook.addWorksheet('Customers'); //creating worksheet
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();
});
Download Excel File
We implement a function exports.downloadFile = (req, res)
to get data from database and save it to Excel file then returns back to client:
exports.downloadFile = (req, res) => {
Customer.findAll().then(objects => {
var customers = [];
let length = objects.length;
for(let i=0; i<length; i++){
let datavalues = objects[i].dataValues;
let customer = {
id: datavalues.id,
name: datavalues.name,
address: datavalues.address,
age: datavalues.age
} ;
customers.push(customer);
}
console.log(customers);
const jsonCustomers = JSON.parse(JSON.stringify(customers));
let workbook = new excel.Workbook(); //creating workbook
let worksheet = workbook.addWorksheet('Customers'); //creating worksheet
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();
});
});
}
Frontend Development
Implement Html Upload Form
We use Bootstrap framework to build a html upload forms as below structure:

– Details html source code in ./views/index.html
file:
<!DOCTYPE html>
<html lang="en">
<head>
<title>Upload Download File Examples</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js"></script>
<script type="text/javascript" src="/static/js/uploaddownloadfiles.js"></script>
</head>
<body>
<div class="container">
<div class="row">
<div class="col-sm-7" style="background-color:#e6fffa; padding:10px; border-radius:3px">
<h3>Upload Single File</h3>
<form id="uploadSingleFileForm">
<div class="form-group">
<label class="control-label" for="uploadfile">Choose a File:</label>
<input type="file" class="form-control"
placeholder="Choose a upload file" name="file" required></input>
</div>
<button type="submit" class="btn btn-danger" id="btnUploadSingleFileSubmit">Submit</button>
</form>
<div id="response" style="display:none">
</div>
</div>
</div>
<hr>
<div class="row">
<div class="col-sm-7" style="background-color:#e6fffa; padding:10px; border-radius:3px">
<h3>Upload Multiple Files</h3>
<form id="uploadMultipleFilesForm">
<div class="form-group">
<label class="control-label" for="uploadfiles">Choose Files:</label>
<input type="file" class="form-control"
placeholder="Choose upload files" name="files" multiple required></input>
</div>
<button type="submit" class="btn btn-danger" id="btnUploadMultipleFilesSubmit">Submit</button>
</form>
<div id="responses" style="display:none">
</div>
</div>
</div>
<hr>
<div id="downloadfiles" class="row">
<div class="col-sm-7">
<a href="/api/file" class="btn btn-info" role="button">Download Excel File</a>
</div>
</div>
</div>
</body>
</html>
Implement Ajax Upload Excel files
We implement Ajax function to upload Excel files to Nodejs server in file resources/static/js/uploaddownloadfiles.js
with 2 functions:
$("#uploadSingleFileForm").submit(function(evt)
is used to upload single Excel file$("#uploadMultipleFilesForm").submit(function(evt)
is used to upload multiple Excel files

Upload single Excel File
– Coding:
$("#uploadSingleFileForm").submit(function(evt) {
evt.preventDefault();
let formData = new FormData($(this)[0]);
$.ajax({
url : '/api/file/upload',
type : 'POST',
data : formData,
async : false,
cache : false,
contentType : false,
enctype : 'multipart/form-data',
processData : false,
success : function(response) {
$("#response").empty();
if(response.status !== "error"){
let displayInfo = response.filename + " : " + response.message + "<br>";
$("#response").append(displayInfo);
// add some css
$("#response").css("display", "block");
$("#response").css("background-color", "#e6e6ff");
$("#response").css("border", "solid 1px black");
$("#response").css("border-radius", "3px");
$("#response").css("margin", "10px");
$("#response").css("padding", "10px");
}else{
$("#response").css("display", "none");
let error = response.error;
alert(error);
}
},
error: function(e){
alert("Fail! " + e);
}
});
return false;
});
Upload Multiple Excel Files
– Coding:
$("#uploadMultipleFilesForm").submit(function(evt) {
evt.preventDefault();
let formData = new FormData($(this)[0]);
$.ajax({
url : '/api/file/multiple/upload',
type : 'POST',
data : formData,
async : false,
cache : false,
contentType : false,
enctype : 'multipart/form-data',
processData : false,
success : function(response) {
$("#responses").empty();
let displayInfo = "<ul>";
for(let i=0; i<response.length; i++){
displayInfo += "<li>" + response[i].filename + " : " + response[i].message;
displayInfo += "</li>";
}
$("#responses").append(displayInfo + "</ul>");
$("#responses").css("display", "block");
// add some css
$("#responses").css("background-color", "#e6e6ff");
$("#responses").css("border", "solid 1px black");
$("#responses").css("border-radius", "3px");
$("#responses").css("margin", "10px");
$("#responses").css("padding", "10px");
},
error: function(e){
alert("Fail! " + e);
}
});
return false;
});
Testing
Backend APIs Testing
Testcase 1 – Nodejs Upload Single Excel File


Testcase 2 – Nodejs Upload Multiple Excel Files


Testcase 3 – Nodejs Download Excel File

Frontend Testing
Testcase 1 – Nodejs Express Upload Single Excel File


Testcase 2 – Nodejs Express Upload Multiple Excel File


Testcase 3 – Nodejs Express Download Excel File



Sourcecode – Nodejs Express Upload Download Excel files to MySQL/PostgreSQL
I include a running sourcecode for the tutorial Nodejs RestAPIs Upload Download Multiple Excel Files to MySQL/PostgreSQL. All implemented features of coding:
– GitHub Sourcecode:
Save my life!
I am getting an error
Fail! [object Object]
When I press submit to upload the file.
Error: ENOENT: no such file or directory, open ‘C:\…\uploads\file-1603674310740-customers.xlsx’