Converting Data from an Excel Sheet to JSON or

Converting data from an excel sheet in an xlsx file to JSON or JavaScript object in NodeJs:

Step 1:

Install the xlsx-to-json package from npm.

npm install xlsx-to-json

This package makes converting excel sheet's data to JSON/object really easy.

All you will need is an input excel file and a node js file.

Step 2:

The JavaScript file:

        //First, lets fetch the xlsx-to-json package
        const xlsxToJson = require('xlsx-to-json');

        //Set up the callback function
        var callback = function(err, result) {
            //Check for errors
            if(err) {
              // Oops something went wrong!
              console.log('Error : ', err);
            }
            //If everything's alright go ahead
            else { 
              /*
                If you don't want to save the json to a file, instead just use it directly.
                You can use the result returned by the xlsxToJson() function, directly like as follows:
                        doSomething(result);
              */
              console.log('You are done!');
            }
          };

        //Set up options for xlsx-to-json:
        var options = {
            //Set the path to input file
            input: "someSampleExcelFile.xlsx", 
            //Set the path for Output file, if you want the json result saved in a file directly
            output: "outputFile.json",
            //Optionally you can specify which spreadsheet to take the input data from, 
            //if you do not provide the sheet name, it reads it from the first sheet by default.
            sheet: "someSheetName"
        };

        //Finally, call the xlsxToJson() to generate the Json from excel data.
        xlsxToJson(options, callback);
    

A sample example for better understanding :

Let say you have an excel sheet like as follows:

An important thing to remember is the first row values of the excel sheet are considered as the object's properties

So, name them as the property name you want the JSON/object to having.

And, the rest of the rows are considered as values matched to the particular property name given.

All the values that are automatically converted to the string, even the numbers will be converted to the string, as shown in the example above.

So, for any other type fields like a number you will have to convert the value to the type to want.

This will be converted to:

        [
        {
          "firstname": "Jane",
          "lastname": "Doe",
          "email": "[email protected]",
          "mobile": "99999999999"
        },
        {
          "firstname": "Miny",
          "lastname": "Moe",
          "email": "[email protected]",
          "mobile": "88888888888"
        },
        {
          "firstname": "Eenie",
          "lastname": "Meanie",
          "email": "[email protected]",
          "mobile": "77777777777"
        }
      ]
      

So, you can use the above json as follows in your callback function:

result[0].firstname to get the first name of the first user.