Hey all, While web application we may need to convert data into one format to other formats. So today we are going to convert data from excel file into json file. At the end, we will be able to change a proper excel sheet data into json format.
So let's try to complete step by step,
1. create nodejs app with express
2. Excel sheet uploading
3. Read the uploaded excel file and convert it into json with nodejs package
So lets have a look on our nodejs part(server.js):
let express = require('express'),
app = express(),
bodyParser = require('body-parser'),
multer = require('multer'),
crypto = require('crypto'),
xlsxtojson = require('xlsx-to-json'),
xlstojson = require("xls-to-json");
let fileExtension = require('file-extension');
app.use(bodyParser.json());
let storage = multer.diskStorage({ //multers disk storage settings
destination: function (req, file, cb) {
cb(null, './input/')
},
filename: function (req, file, cb) {
crypto.pseudoRandomBytes(16, function (err, raw) {
cb(null, raw.toString('hex') + Date.now() + '.' + fileExtension(file.mimetype));
});
}
});
let upload = multer({storage: storage}).single('file');
/** Method to handle the form submit */
app.post('/sendFile', function(req, res) {
let excel2json;
upload(req,res,function(err){
if(err){
res.json({error_code:401,err_desc:err});
return;
}
if(!req.file){
res.json({error_code:404,err_desc:"File not found!"});
return;
}
if(req.file.originalname.split('.')[req.file.originalname.split('.').length-1] === 'xlsx'){
excel2json = xlsxtojson;
} else {
excel2json = xlstojson;
}
// code to convert excel data to json format
excel2json({
input: req.file.path,
output: "output/"+Date.now()+".json", // output json
lowerCaseHeaders:true
}, function(err, result) {
if(err) {
res.json(err);
} else {
res.json(result);
}
});
})
});
// load index file to upload file on http://localhost:3000/
app.get('/',function(req,res){
res.sendFile(__dirname + "/index.html");
});
app.listen('3000', function(){
console.log('Server running on port 3000');
});
In the above file we have created a nodejs application and then on http://localhost:3000, served an index.html page to upload the excel file. And after uploading of our excel file nodejs excel-to-json package does the main task of conversion of the data from excel to json format.
Now let's have a look on the view part of our demo(index.html).
<!DOCTYPE html>
<html lang="en">
<head>
<title>Excel to Json in nodejs | jsonworld</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<body>
<div class="jumbotron text-center">
<h1>Excel to Json in nodejs</h1>
<p>source : <a href="https://jsonworld.com">jsonworld</a></p>
</div>
<div class="container">
<div class="row">
<div class="col-sm-4 col-md-offset-4">
<form id="form" enctype ="multipart/form-data" action="sendFile" method="post">
<div class="form-group">
<input type="file" name="file" class="form-control"/>
<input type="submit" value="Upload" name="submit" class="btn btn-primary" style="float:right; margin-top:30px;">
</form>
</div>
</div>
</div>
</body>
</html>
In the above file we have simply a form which has a input type file for file uploading and a submit button.
Pretty cool! Finally, our task completes here.
That’s all for now. Thank you for reading and I hope this post will be very helpfu.
Let me know your thoughts over the email demo.jsonworld@gmail.com. I would love to hear them and If you like this article, share with your friends.
