Month: October 2014

Parse Excel and convert it to JSON with Node.js

This is a great, simple and lovely module for Node.js to parse Excel files.

Install the module:

npm install --save excel

Use it like this:

var xls = require('excel');

xls('Sheet.xlsx', function(err, data) {
  if(err) throw err;
    // data is an array of arrays

As it says, the data it returns is an array of arrays. We want it to be JSON, so that we can do whatever we want with it.

This is a function that converts an array of arrays to JSON:

function convertToJSON(array) {
  var first = array[0].join()
  var headers = first.split(',');
  var jsonData = [];
  for ( var i = 1, length = array.length; i < length; i++ )
    var myRow = array[i].join();
    var row = myRow.split(',');
    var data = {};
    for ( var x = 0; x < row.length; x++ )
      data[headers[x]] = row[x];

  return jsonData;


xlsx('tasks.xlsx', function(err,data) {
    if(err) throw err;

It’s almost embarrassing how Node enables me to use other people’s modules to create apps in such a simple way.

Beginning Node.js – REST API with Express 4 – part 2

This is the most simple REST API possible. I will explain every single detail in this post.

var express = require('express');
var app = express();

var menu = [{"description" : "papadums", "price":"2,00"}, {"description" : "chicken tikka masala", "price":"14,00"},{"description" : "fisher king beer", "price":"2,50"},{"description" : "sag paneer", "price":"6,00"}];

app.use(express.static(__dirname + '/public'));

app.get('/food', function(req, res){

app.set('port', process.env.PORT || 3000);
console.log("the server is running on http://localhost:" + app.get('port'));

Let’s get started

First create a folder named ‘restaurant’ or whatever you would like to name the API.
Then run ‘npm init’ to create a package.json file. This file contains metadata, dependencies etc.:

mkdir restaurant
cd restaurant
npm init

You will get a bunch of questions. Answer them (or accept the defaults) and hit enter:

About to write to /Users/jacqueline/Dropbox/devel/restaurant/package.json:
  "name": "restaurant",
  "version": "0.1.0",
  "description": "",
  "main": "main.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  "author": "Jacqueline",
  "license": "MIT"

Is this ok? (yes)

Install Express

Next we will install Express with NPM. Express is a minimalist web framework for Node.js.

jacqueline@nsa:~/Dropbox/devel/restaurant$ npm install express --save

You will now see ‘express’ mentioned in the package.json.
The ‘–save’ parameter adds a “^” before the package, it will make sure the right version is used.

Create the app

Create a file named ‘main.js’ (that’s how I named the main file in my package.json, you can also name it app.js or server.js).

Add these contents:

var express = require('express');
var app = express();
app.get('/', function(req, res){
      res.send('hello world');


Next install nodemon because it is awesome. It restarts node every time a file in the project is changed.
Add the ‘-g’ parameter because nodemon should be installed globally and not just as a module in the project folder.

npm install -g nodemon

Now run nodemon main.js on the commandline. It will return:

jacqueline@nsa:~/Dropbox/devel/restaurant$ nodemon main.js 
18 Oct 08:04:59 - [nodemon] v1.2.0
18 Oct 08:04:59 - [nodemon] to restart at any time, enter `rs`
18 Oct 08:04:59 - [nodemon] watching: *.*
18 Oct 08:04:59 - [nodemon] starting `node main.js`

Now browse to http://localhost:3000. This will display ‘Hello World’ in the browser.

Serve JSON data

Alter your main.js file and add some json and change the app.get bit:

var express = require('express');
var app = express();

var menu = [{"description" : "papadums", "price":"2,00"}, {"description" : "chicken tikka masala", "price":"14,00"},
{"description" : "fisher king beer", "price":"2,50"},{"description" : "sag paneer", "price":"6,00"}];

app.get('/food', function(req, res){


Now if we browse to http://localhost/food, we will retrieve our delicious menu. We can also a REST client like POSTMAN to test:

With curl:

curl http://localhost:3000/food
[{"description":"papadums","price":"2,00"},{"description":"chicken tikka masala","price":"14,00"},{"description":"fisher king beer","price":"2,50"},{"description":"sag paneer","price":"6,00"}]

Consuming the API

Create a folder called public in your app directory.

Next add this line to main.js, with this we’ll tell Express to go and find index.html files in a subfolder called public:

app.use(express.static(__dirname + '/public'));

Drop an index.html in the public folder.
This makes the directory structure look like so:

├── main.js
├── node_modules
│   └── express
├── package.json
└── public
└── index.html

Open index.html and add the following code:


Welcome to my restaurant!

On the menu:

And the result is..:


Add Bootstrap with Bower

Bower is like NPM, but for front end scripts. You can use NPM for frontend scripts as well, but I like to keept them separate.

npm install -g bower

Create a file named .bowerrc to tell Bower to put the javascripts into a /public/js:

vim .bowerrc
    "directory" : "public/js"

You can create a bower.json file to organize the dependencies:

bower init

jacqueline@nsa:~/Dropbox/devel/restaurant$ bower init
? name: restaurant
? version: 0.1.0
? description: menu app
? main file: main.js
? what types of modules does this package expose?: node
? keywords:
? authors: jacqueline <>
? license: MIT

Then install your client side javascript dependencies like so:

bower install --save bootstrap

Now we can add some styling:


Last but not least: some feedback when starting the server

In Express you can define variables with app.set. I would suggest to configure the port like this:

app.set('port', process.env.PORT || 3000);
console.log("the server is running on http://localhost:" + app.get('port'));

process.env.PORT || 3000 means: whatever is in the environment variable PORT, or use 3000 if there’s nothing there.

This makes completed main.js look like this:

var express = require('express');
var app = express();

var menu = [{"description" : "papadums", "price":"2,00"}, {"description" : "chicken tikka masala", "price":"14,00"},
{"description" : "fisher king beer", "price":"2,50"},{"description" : "sag paneer", "price":"6,00"}];

app.get('/food', function(req, res){

app.use(express.static(__dirname + '/public'));

app.set('port', process.env.PORT || 3000);
console.log("the server is running on http://localhost:" + app.get('port'));

And this is where this tutorial ends. I love open source. It’s great to be able to use all these tools (Node, NPM, Express) and be very productive. Next time I will show how to use a (NoSQL) database. Not sure to use MongoDb or Postgresql yet. We’ll see.

Beginning Node.js – callbacks – part 1

Disclaimer: This is a series about me, creating a web application in Node.js. The completed example is available here.

And from there the series end, because frontend frameworks will take over.

In this article about the MEAN stack, I’m not spending much time on Node.js. However, Node.js is quite awesome. It’s lightweight, cross platform and it runs JavaScript files. But what is it?

  • Node.js is like IIS.
  • You can use a module like Express.js (or Restify, Sails.js, Hapi etc.) to implement a RESTful API, much like the ASP.NET Web API.
  • You install modules (packages) with NPM, which is comparable to Nuget.

Node.js is a platform built on Chrome’s JavaScript runtime (v8). Node.js uses an event-driven, non-blocking I/O model that makes it lightweight and efficient, perfect for data-intensive real-time applications that run across distributed devices.

So what does that even mean? And how to get started? Apart from this blog, there are plenty of tutorials out there and also great books. These are probably much better than this intro, however I would like to share that piece of code that made it click for me. And if I not write down my words, they are wasted.

What do we need?

  • Node.js (I’m on v0.10.30). We will use Node.js to to build server side applications. So no IIS, applications pools and .NET. All you need is the 5 MB’s of Node.js! Let’s sink that in for a moment!
  • A code editor of choice (I prefer Vim and Brackets)
  • Linux OS, Mac OS or Windows 7/8. I’m on a Mac and on Ubuntu 14.04. I only use Windows at work.
  • For Windows, you will need Python 2.7, Visual Studio Express and node-gyp installed locally. Check this. These are prerequisites to build node modules on Windows (a bit of a pain indeed)
  • Just a little bit of a coding background.

Node.js installation

Linux or Mac
To install Node.js on Linux or Mac, I would advise to follow this gist:

echo 'export PATH=$HOME/local/bin:$PATH' >> ~/.bashrc
. ~/.bashrc
mkdir ~/local
mkdir ~/node-latest-install
cd ~/node-latest-install
curl | tar xz --strip-components=1
./configure --prefix=~/local
make install # ok, fine, this step probably takes more than 30 seconds...
curl | sh

To install Node.js on Windows just install the .msi.from the Node.js website.
For NPM don’t forget to create an NPM folder in C:\Users\Username\AppData\Roaming\npm\

The basics: callbacks

Normally, code gets executed from top to bottom:

function dothis() {
// do some IO heavy work, or get data from a file

function dothat() {
//doing that


If ‘dothis’ is CPU intensive, or IO intensive, it takes a long time before the code reaches ‘dothat’. In the mean time, nothing happens. The code is ‘blocking’.
In .NET you can solve blocking operations by using another thread, or write the code asynchronously.
But Node.js code is almost exclusively asynchronous. There is but ONE thread in Node.js.

So in Node, code is executed asynchronously. While some IO heavy operations occurs, Node can do other stuff. When the heavy operation is finished, the function involved ‘calls back’, like saying: ” Hey, I’m done. Here is the result”.

So how do we write asynchronous code in Node?

Consider this example where I want to display an order of Indian food on the screen.
order.txt contains a list of Indian food.

var fs = require('fs'); // this is like using System.IO

//initialize the variable
var theOrder;

//this is a function that reads the contents of order.txt. 

function orderSomething() {
   //the 'readFile' method takes (at least) 2 arguments: the file, and also a callback (result).
   fs.readFile('order.txt', function(result){
   theOrder = result;

console.log("The customer ordered:\n" + orderSomething());

Now save this code as test.js and run it:

jacqueline@laptop:~$ node test.js
The custumer ordered:

So this does not display my order. Why not?
Because when console.log is executed, the function orderSomething is not ready yet.

We can solve the problem by adding a parameter to the orderSomething function. In Node, the last parameter of a function is a function, that will be executed when orderSomething is finished: orderSomething(doThisWhenFinished). (In C# you can also pass a function as a parameter, with Func<> and Action<>, however I’m not sure it knows about async behavior).

So, we basically add a function to a function (which can contain another function and so on).
This is very important. The parameter of a function in JavaScript can be another function, that gets executed when the first function is done.

var theOrder;

//first, orderSomething and when it's done, execute 'done'
function orderSomething(done) {
  fs.readFile('order.txt', function(err,result) {
    if (err) { throw err; }
    theOrder = result;
// call this when done:

function showOrder(){
 console.log("The customer ordered:\n" + theOrder);


Now run this code:

jacqueline@laptop:~$ node test.js
The custumer ordered:
//Chicken Tikka Massala
//King Fisher Beer

This is how Node.js works. The last argument of a function is the callback, which gets executed when the function is ready.

Wrapping up

We can rewrite the above example as follows:

var fs = require("fs");
fs.readFile("order.txt","utf-8", function (error, result) {

Read the file, then display the result.

That was all.