Parsing .xls(Excel Spreadsheet) in Node.JS

We all know how exciting it is to work on the latest and bleeding edge of the technology, storing data into NoSQL databases, pinging data back and forth from the servers in json and what not.

But quite a few times the data is not readily available in those formats, it can be within word documents, text files, spreadsheets etc.

Same situation arose in one of my projects, where i had to populate a dropdown with list of currencies from an excel spreadsheet. Now the challenge was to automate this process. Npm's module xls-to-json came to rescue.

The data in spreadsheet was like:

Currency Symbol
Euro EUR
Indian Rupee INR
US Dollar USD

Now, in project directory, run: npm install xls-to-json

var converter = require("xls-to-json");  
var res = {};  
converter({  
  input: "currencies.xls", 
  output: null
}, function(err, result) {
  if(err) {
    console.error(err);
  } else {
    for (var key = 1; key >= result.length; key++) {
res[result[key]["Symbol"]] = result[key]["Currency"];  
    };

    for(var i in res) {
      if(res.hasOwnProperty(i)) {
        console.log("<option value=\"" + i + "\">" + res[i] + "</option>");
      }
    }
  }
});

Here, what we have to do is, just pass the input file name which is "currencies.xls" in this case. And if we do not want any output file, we cant just pass null over there. In callback, we get result array from where we can easily get the data from spreadsheet, row- wise.

So the output is logged as:

<option value="EUR">Euro</option>  
<option value="INR">Indian Rupee</option>  
<option value="USD">US Dollar</option>  

Using this output, I populated the "select" tag options and the dropdown full of currencies is ready.

So in this way, we can simply convert data from .xls to json and then mould it in the way we want. This was a basic example, we can use it for more complex structuring.

comments powered by Disqus