Beginner Web Development projects

In this tutorial, we will be learning about ways to read and write data from a JSON file and then from a JSON to an excel file. We will be using very basic Node.js and an npm package "xlsx" to perform these tasks.

Prerequisites

Node.js(beginner level is sufficient)
A little bit about JSON

Let's get started

The folder structure will be very simple. You can follow mine or create a separate sub-folder for JSON or excel files.

jsonToexcel
|
|----main.js
|----example.json

We will start by initializing the package.json file which stores the information about all the dependencies. So head over to your terminal and type:

npm init -y

This will create a package.json file in our root directory. Let's add some example JSON data to our "example.json" file:

[
    {
        "name":"Steve",
        "interest":"ios",
        "age":19,
        "ishuman":true
    },
    {
        "name":"Elon",
        "interest":"Blockchain",
        "age":21,
        "ishuman":true
    },
    {
        "name":"Dog",
        "interest":"Bark",
        "age":7,
        "ishuman":false
    },
    {
        "name":"Cat",
        "interest":"meow",
        "age":8,
        "ishuman":false
    }
]

Reading data from JSON file

Inside your "main.js" file, require the "fs" module. Fs is a built-in node module that is useful for reading and writing into files. Then import your JSON file and store it inside a variable(say data). Now, if you type "console.log(data)" and run the code(by typing "node main.js" in your terminal) then you will be able to read from your JSON file.

const fs=require("fs");
let data=require('./example.json')
console.log(data)

Writing data into the JSON file

This is the task that will require the "fs" module. To write into the "example.json" file, we will first push our new data into the "data" variable that we just created, and since it is stored in RAM, which is not a permanent memory, we will have to save our changes by using writeFileSync function from the "fs" module.

data.push(
    {
        "name":"John",
        "interest":"webD",
        "age":19,
        "ishuman":true

    }
)
let string_data=JSON.stringify(data)
fs.writeFileSync("./example.json",string_data);

Run it and you can see your "example.json" file has been modified.

Storing JSON data into excel sheet

To do this, we will first install our npm package "xlsx". Head over to your terminal and install it by typing:

npm i xlsx

After installation is over, you can see two new files inside your root folder:

jsonToexcel
|
|----node_modules
|----main.js
|----example.json
|----package-lock.json
|----package.json

This means that our package has been installed successfully. Inside your "main.js" file, require the "xlsx" module. A basic understanding of excel files that is required here is that an excel file has a "WorkBook" which has many "WorkSheets". So to create a new workbook and new worksheet we use:

let newWB=xlsx.utils.book_new();
let newWS=xlsx.utils.json_to_sheet(data);

The argument "data" in the second line is the variable that we used above to store our JSON data. Now, we have our workbook and worksheet ready. The next steps will be to append these two and store them in a file:

xlsx.utils.book_append_sheet(newWB,newWS,"sheet1");
xlsx.writeFile(newWB,"abc.xlsx")

Sheet1 and "abc" are the names of the sheet and file respectively that I have given. You can name this anything you want. After you run the "main.js" file again, you can notice a new xlsx file has been created inside your folder.

jsonToexcel
|
|----abc.xlsx
|----node_modules
|----main.js
|----example.json
|----package-lock.json
|----package.json

###Reading from an excel file

Reading data is fairly simple and similar to what we did to store the data. We have to provide the name of the file and sheet and nodejs does its work from that:

let wb=xlsx.readFile("abc.xlsx");
let exceldata=wb.Sheets["sheet1"];
let ans=xlsx.utils.sheet_to_json(exceldata);
console.log(ans)

That's it! It is that simple and understandable. If you liked this tutorial, do hit those emojis to show me your love. Share your feedback in the comment section. Please follow🥺if you want more such tutorials .

Byeeeeeeeee

Aarushi Shanker