Read Excel Files and convert to JSON in Node.js

In many web applications, it’s common to deal with excel files for data upload, data analytics, and other requirements. This tutorial will guide you on how to upload, read, and convert excel files to JSON using Node.js, making the process easier for developers.

Setting Up Express Server and Multer for File Upload

Firstly, we need to set up an Express server and Multer for file upload. Multer is a node.js middleware for handling multipart/form-data, which is primarily used for uploading files. Here are the steps to set up Express and Multer:

1. Initialize a new npm project and install the necessary packages:

npm init --yes
npm install express multer body-parser --save

2. Create an uploads directory and an app.js file:

mkdir uploads
touch app.js

3. In the app.js file, set up Express, Multer, and a simple file upload endpoint:

var express = require('express');
var app = express();
var bodyParser = require('body-parser');
var multer = require('multer');

app.use(bodyParser.json());

var storage = multer.diskStorage({
  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({ storage: storage }).single('file');

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.listen('3000', function(){
  console.log('running on 3000...');
});

In this code, we define a storage location for Multer and a filename function to handle the naming of uploaded files. We then define an upload middleware with our storage settings, and a POST endpoint for file upload.

Reading and Converting Excel to JSON

After setting up the file upload, the next step is to read the uploaded Excel files and convert them to JSON. We will use two node modules, xls-to-json-lc and xlsx-to-json-lc, to convert .xls and .xlsx files to JSON, respectively.

1. Install the necessary packages:

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

2. In the app.js file, require the modules and modify the /upload endpoint to read and convert the uploaded Excel file:

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

app.post('/upload', function(req, res) {
  var exceltojson;
  upload(req,res,function(err){
    if(err){
      res.json({error_code:1,err_desc:err});
      return;
    }
    if(!req.file){
      res.json({error_code:1,err_desc:"No file passed"});
      return;
    }
    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,
        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:"Corrupted excel file"});
    }
  })
});

In this code, we first check the extension of the uploaded file and use the appropriate module to convert the file to JSON. We then send the converted data as a response.

Conclusion

Reading Excel files and converting them to JSON is a common requirement in web applications. With Node.js, this process becomes straightforward and easy to implement. By following this tutorial, you can upload Excel files in your application, read the files, and convert them to JSON data.