Nodejs Upload Excel files to MySQL/PostgreSQL – (Express RestAPI Upload/Import Download/Export Multiple Excel files)

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 RestApis
• Multer is used to upload/download Excel files
• Sequelize ORM is used to manipulate data with MySQL/PostgreSQL
• Exceljs is used to save data objects to Excel file
• Read-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 file
• models package is used to define Sequelize Customer model
• routers package is used to define Express router for Nodejs RestAPIs
• controllers 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 files
• resource/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 having INTEGER type
• name property has STRING type
• address property has STRING type
• age property has INTEGER 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 file
• exports.uploadMultipleFiles = async (req, res) is used to upload multiple excel files
• exports.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 + "&nbsp; : &nbsp;" + 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:

Nodejs-Upload-Excel-Files

– GitHub Sourcecode:

Nodejs-Upload-Excel-Files – Github Sourcecode