Read Excel Files and convert to JSON in Node.js

Sometimes in an application, you may have a requirement to upload data via an excel file or a job to read data from a bunch of excel files or may be run some analytics. Dealing with excel files is common in web applications. This tutorial aims to make reading excel files using NodeJS easier for developers.

After this tutorial, you will be able to upload an excel file on your application to a particular path, read the file and convert it into json data and then, obviously you can do whatever you want with that programmatically sensible data.

We will be taking care of two formats of excel files. That is converting ‘.xls’ to json and ‘.xlsx’ to json

Roadmap

Here we will do the following things.

1. Setup Node express server and routes
2. Upload file, using Multer
3. Read and convert excel to json

Note: The below section deals with uploading of the files further, if not required you can directly skip to Read and convert excel to json further below.

DOWNLOAD

Setting Up Express server and Multer for upload

We have already done a tutorial on File Upload, which involves setting up node-express along with multer for upload, where we have explained in detail each step. For this tutorial instead of repeating the same thing we will be picking up parts of code from there. Please give a read to the below-mentioned tutorial for a detailed explanation.

File Upload with NodeJS and AngularJS

Navigate into your working directory via your console and run the following commands.


npm init --yes
npm install express --save
npm install multer --save
npm install body-parser --save
mkdir uploads
touch app.js


Now I will be picking up the code from the file upload tutorial and adding it to my app.js. At the same time, we will be removing the unwanted code blocks.


var express = require('express');
var app = express();
var bodyParser = require('body-parser');
var multer = require('multer');
app.use(bodyParser.json());
var storage = multer.diskStorage({ //multers disk storage settings
destination: function (req, file, cb) {
cb(null, './uploads/')
},
filename: function (req, file, cb) {
var datetimestamp = Date.now();
cb(null, file.fieldname + '-' + datetimestamp + '.' + file.originalname.split('.')[file.originalname.split('.').length -1])
}
});
var upload = multer({ //multer settings
storage: storage
}).single('file');
/** API path that will upload the files */
app.post('/upload', function(req, res) {
upload(req,res,function(err){
if(err){
res.json({error_code:1,err_desc:err});
return;
}
res.json({error_code:0,err_desc:null});
});
});
app.get('/',function(req,res){
res.sendFile(__dirname + "/index.html");
});
app.listen('3000', function(){
console.log('running on 3000...');
});


Just a quick explanation of the above code, firstly we are requiring the different modules, we will need the body-parser to parse the post data. Next, we are setting up the multer’s storage settings, for the storage destination, uploaded file name and also the storage type as disk storage. Below it, are the settings for multer and further, we have added an express route/path where we will be uploading the file.
We also have a simple index.html file rendered at the root path. index.html is nothing but a form where we will be able to upload our files. Finally, we are listening to port 3000 to start a node server.

Upload

Now if you run your app, you will be able to upload any file into our uploads directory. But we need to restrict our app to only allow excel files i.e (.xls, .xlsx)

Adding filetype filter

We will change our upload function to only allow excel file by adding a fileFilter option provided by Multer.


var upload = multer({ //multer settings
storage: storage,
fileFilter : function(req, file, callback) { //file filter
if (['xls', 'xlsx'].indexOf(file.originalname.split('.')[file.originalname.split('.').length-1]) === -1) {
return callback(new Error('Wrong extension type'));
}
callback(null, true);
}
}).single('file');

 

Read and convert excel to json

Now, that we are able to upload files on our server and that we have added the extension validation. The next challenge is to read the uploaded file and convert it to json.

To do this we will make use of two node modules, xls-to-json-lc and xlsx-to-json-lc for converting .xls to json and .xlsx to json respectively.

Install them by running the below commands.


npm install xlsx-to-json-lc --save
npm install xls-to-json-lc --save

 

Usage


var exceltojson = require("xls-to-json-lc");
exceltojson({
input: "pass the input excel file here (.xls format)"
output: "if you want output to be stored in a file"
sheet: "sheetname", // specific sheetname inside excel file (if you have multiple sheets)
lowerCaseHeaders:true //to convert all excel headers to lowr case in json
}, function(err, result) {
if(err) {
console.error(err);
} else {
console.log(result);
//result will contain the overted json data
}
});

 

Implementation

Remember we have two different node modules for the two excel extensions. We will first check the extension of the incoming file and then depending on it, we will use the right module. Let us get started.

Start by requiring our modules into our app.js.


var xlstojson = require("xls-to-json-lc");
var xlsxtojson = require("xlsx-to-json-lc");


Let’s make changes to our /upload path.


/** API path that will upload the files */
app.post('/upload', function(req, res) {
var exceltojson; //Initialization
upload(req,res,function(err){
if(err){
res.json({error_code:1,err_desc:err});
return;
}
/** Multer gives us file info in req.file object */
if(!req.file){
res.json({error_code:1,err_desc:"No file passed"});
return;
}
//start convert process
/** Check the extension of the incoming file and
* use the appropriate module
*/
if(req.file.originalname.split('.')[req.file.originalname.split('.').length-1] === 'xlsx'){
exceltojson = xlsxtojson;
} else {
exceltojson = xlstojson;
}
try {
exceltojson({
input: req.file.path, //the same path where we uploaded our file
output: null, //since we don't need output.json
lowerCaseHeaders:true
}, function(err,result){
if(err) {
return res.json({error_code:1,err_desc:err, data: null});
}
res.json({error_code:0,err_desc:null, data: result});
});
} catch (e){
res.json({error_code:1,err_desc:"Corupted excel file"});
}
});
});

 
We will get our converted data in the results variable (i.e the second parameter). Thats it, now we are ready to run and try our app.

Our final files look like this.


var express = require('express');
var app = express();
var bodyParser = require('body-parser');
var multer = require('multer');
var xlstojson = require("xls-to-json-lc");
var xlsxtojson = require("xlsx-to-json-lc");
app.use(bodyParser.json());
var storage = multer.diskStorage({ //multers disk storage settings
destination: function (req, file, cb) {
cb(null, './uploads/')
},
filename: function (req, file, cb) {
var datetimestamp = Date.now();
cb(null, file.fieldname + '-' + datetimestamp + '.' + file.originalname.split('.')[file.originalname.split('.').length -1])
}
});
var upload = multer({ //multer settings
storage: storage,
fileFilter : function(req, file, callback) { //file filter
if (['xls', 'xlsx'].indexOf(file.originalname.split('.')[file.originalname.split('.').length-1]) === -1) {
return callback(new Error('Wrong extension type'));
}
callback(null, true);
}
}).single('file');
/** API path that will upload the files */
app.post('/upload', function(req, res) {
var exceltojson;
upload(req,res,function(err){
if(err){
res.json({error_code:1,err_desc:err});
return;
}
/** Multer gives us file info in req.file object */
if(!req.file){
res.json({error_code:1,err_desc:"No file passed"});
return;
}
/** Check the extension of the incoming file and
* use the appropriate module
*/
if(req.file.originalname.split('.')[req.file.originalname.split('.').length-1] === 'xlsx'){
exceltojson = xlsxtojson;
} else {
exceltojson = xlstojson;
}
try {
exceltojson({
input: req.file.path,
output: null, //since we don't need output.json
lowerCaseHeaders:true
}, function(err,result){
if(err) {
return res.json({error_code:1,err_desc:err, data: null});
}
res.json({error_code:0,err_desc:null, data: result});
});
} catch (e){
res.json({error_code:1,err_desc:"Corupted excel file"});
}
})
});
app.get('/',function(req,res){
res.sendFile(__dirname + "/index.html");
});
app.listen('3000', function(){
console.log('running on 3000...');
});



{
"name": "excelupload",
"version": "1.0.0",
"description": "",
"main": "app.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1"
},
"keywords": [],
"author": "Rahil Shaikh",
"license": "ISC",
"dependencies": {
"body-parser": "1.15.1",
"express": "4.13.4",
"multer": "1.1.0",
"xls-to-json-lc": "0.3.3",
"xlsx-to-json-lc": "0.2.5"
}
}

 

Run the app

To run the app just navigate into your woking directory using command line tool and run.

node app.js

 

Demo and Quick setup

To quickly run the application follow the bellow steps


1) git clone https://github.com/rahil471/excel-to-json-in-Node.js.git
2) cd excel-to-json-in-Node.js
3) npm install
4) node app.js
5) In your browser http://localhost:3000
6) Upload excel file and see result

 

Deleting the uploaded file

In some cases, you may not want to keep the uploaded excel file on the server. Here is a piece of code for deleting the uploaded files as soon as they are converted.


var fs = require('fs');
try {
fs.unlinkSync(req.file.path);
} catch(e) {
//error deleting the file
}


Use this code just below the excel to json conversion. req.file.path would refer to the currently uploaded file.

Conclusion

Excel to json is a regular requiremnt for web applications. This tutorial explains how easy it is to acomplish in Node.js