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.
- Setup Node express server and routes
- Upload file, using Multer
- 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.
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 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 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.
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.
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 xls-to-json-lc --save
Usage
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 xlsxtojson = require("xlsx-to-json-lc");
Let’s make changes to our /upload path.
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 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.
Demo and Quick setup
To quickly run the application follow the bellow steps
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.
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
Abdulaziz
Good day. I am new in NodeJS world. Could anuone recommend me any web sites, videocourse or books.
Rahil Shaikh
Hi Abdulaziz,
CipherTrick has a good collection of NodeJS tutorials, you can browse them on this website. We also have a YouTube Channel where you can find excellent video tutorials, make sure you subscribe to us. 🙂
Other than that CodeForGeek is a good blog focused mainly on Node.js, you can check that out as well.
Ian Poston
I am using angular on the front-end to upload a xlsx file. I am trying to integrate your converter code but it’s not working properly. My goal is to upload a xlsx file and parse to json data so I can show the data in a view. I have been able to save the file in my upload directory in my root but it doesn’t show json and I also get error messages sometimes. My form is different than the one you used and I am using a 3rd party module to help with uploading. I have one line in server.js that seems to be throwing error var type = upload.array(‘files[]’); then it keeps saying upload is not a function. I put my controller and server.js in a jsbin file here http://jsbin.com/weniminigo/edit?js if you have time to look at it. If you can give me any suggestions it would be a great help Thank you!
Rahil Shaikh
Hi at a quick glance, I think your uploads.array seems to be invalid.
Should be of the form.
And your form filed name should match with the selector
Try logging req.files it should be an array of photo file objects
Prasad VS
Thanks for the wonderful tutorial. I am new to this and learning. my req is to send the json output to a database and return to the home screen with a success message ( an alert would do) . I was able to send the result (json) to a mongodb collection. However, I wasn’t able to stop the json output showing up on the sceen, I commented the below line and did a plain ‘return’, but that doesn’t work. Can you pls guide me on how to do that?
res.json({error_code:0,err_desc:null, data: result});
Rahil Shaikh
Hi Prasad,
I will have to look into your code to answer your question.
Probably there would be some minor thing that you are missing, you can create a plkr or mail me your code.
Aaron
Rahil, fantastic job with this, you helped me get over a major hurdle I’d been stuck on for too long. Again, well done.
Rahil Shaikh
Thanks for stopping by, Aaron.
Er Raja Abinesh
send the download code in node.js
Rahil Shaikh
The download link is provided in the article.
vamsee
i want read properties of a uploaded excle file like author name,when it is modified, bcoz i want to check wheather uploaded xls(or)xlsx file are my own formated file are not, if it is not my formated file i want to intimate to end user
Rahil Shaikh
The properties are returned as json object, where the headers will be properties!
vamsee
how to read headen fields data of a xls(or)xlsx file
Gopi Malla
Hey.. Great articel!! By the way, How to extend this to support multi sheet reading in the same excel file?
Rahil Shaikh
There is an option that accepts sheet name, by default it reads the 1st sheet.
mohg
Hi.. I want to upload a file and convert it to json or array without saving that uploaded file in any temp directory. can you help me with the sample code on this one
Rahil Shaikh
Check-out the Multer memoryStorage option.
mohg
thank you..i will try this. before that, i tried the code which you posted here. Its returning empty result.. i git cloned your code and ran the app.
mohg
your code is working perfectly fine.. discard my above comments
krishna
good article. how can we read multiple sheets at a time