Upload and Read Excel File in Node.js

Nodejs Upload Read Excel Files

Tutorial “Upload and Read Excel File in Node.js – 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
Related posts:


Architecture – Upload and Read Excel File in Node.js

Nodejs Express Upload Download Excel Files - Overview Architecture
Nodejs Express Upload Download Excel Files – Overview 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.

Project Goal – Upload and Read Excel File in Node.js

– Project Structure:

Nodejs Project Structure
Nodejs 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:

Upload Multiple Excel Files
Upload Multiple Excel Files

Check MySQL database:

Check MySQL database records after uploading
Check MySQL database records after uploading

Create Nodejs Express project – Upload and Read Excel File in Node.js

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"
  }
}

Nodejs Database Configuration

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;

Nodejs Express Multer Configuration

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/'.

Nodejs Express 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;
}

Nodejs Express Define Excel RestAPI 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;

Nodejs Express Implement Excel RestAPI 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

Nodejs 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);
            }

Nodejs 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);
...

Nodejs 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);
}

Nodejs 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();
        });

Nodejs 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();
                });
    });
}

Implement Html Client Upload Form

We use Bootstrap framework to build a html upload forms as below structure:

HTML Upload Excel File Form
HTML Upload Excel File Form

– 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 Client 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
Ajax Upload functions
Ajax Upload functions

Ajax Client 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;
});

Ajax Client 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;
});

Read More

Related posts:


Testing

1. Testcase 1 – Nodejs Upload Single Excel File:

Testcase 1 - RestAPIs - Upload Single Excel File
Testcase 1 – RestAPIs – Upload Single Excel File
Testcase 1 - RestAPIs - Upload Single Excel File - Check database records
Testcase 1 – RestAPIs – Upload Single Excel File – Check database records

2. Testcase 2 – Nodejs Upload Multiple Excel Files:

Testcase 2 - RestAPIs - Upload multiple Excel Files
Testcase 2 – RestAPIs – Upload multiple Excel Files
Testcase 2 - RestAPIs - Upload multiple Excel Files - Check database records
Testcase 2 – RestAPIs – Upload multiple Excel Files – Check database records

3. Testcase 3 – Nodejs Download Excel File:

Testcase 3 - Download Excel Files
Testcase 3 – Download Excel Files

4. Testcase 4 – Nodejs Express Upload Single Excel File:

Testcase 1 - Frontend - Upload Single Excel File - Successfully
Testcase 1 – Frontend – Upload Single Excel File – Successfully
Testcase 1 - Frontend - Upload Single Excel File
Testcase 1 – Frontend – Upload Single Excel File

5. Testcase 5 – Nodejs Express Upload Multiple Excel File:

Testcase 2 - Frontend - Upload multiple Excel Files - Successfully
Testcase 2 – Frontend – Upload multiple Excel Files – Successfully
Testcase 2 - Frontend - Upload multiple Excel Files - Network logs
Testcase 2 – Frontend – Upload multiple Excel Files – Network logs

6. Testcase 6 – Nodejs Express Download Excel File:

Testcase 3 - Frontend - Download Excel Files
Testcase 3 – Frontend – Download Excel Files
Testcase 3 - Frontend - Download Excel Files - Successfully
Testcase 3 – Frontend – Download Excel Files – Successfully
Testcase 3 - Frontend - Download Excel Files - Check Files
Testcase 3 – Frontend – Download Excel Files – Check Files

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

Leave a Reply

Your email address will not be published. Required fields are marked *