Your Web News in One Place

Help Webnuz

Referal links:

Sign up for GreenGeeks web hosting
October 28, 2020 01:21 pm GMT

Easily parse an excel spreadsheet into JSON

Did you know that you can easily parse an excel spreadsheet using Node.js? Keep reading to speed up your information retrieval processes.

Why do I need this?

I learned this a few months ago and came back to using it ever since. I've found that there are several use cases in my daily life that can profit from this little script - in my job as well as in my private life or side projects. You can use it for nearly every scenario where you need to transform your spreadsheet into JSON.

Here are some examples:

  • your working with a client and need an easy way for them to maintain the data your using for the project, most people are familiar with working with excel
  • your collaborating with a few people and want a safe place for everybody to collect data
  • you like planning your life in excel but want to process the data further - Maybe you want to create a beautiful website about your carefully planned road trip
  • and many more, get creative!

How do I use it?

You only need two Node.js modules for this: xlsx-stream-reader for parsing the data and fs for saving the created JSON file.

xlsx-stream-reader parses each row of your spreadsheet and lets you process the data however you want. I usually save my data in an array, where each row represents one element:

Code example of the parsing process

let currentRow = {};// iterating the rows row.values.forEach(function (rowVal, colNum) {    if (rowVal && rowVal !== "") {        // parsing all columns         if (colNum == "1") {            currentRow.id = rowVal; // ID        } else if (colNum == "2") {            currentRow.name = rowVal; // name        } else if (colNum == "3") {            currentRow.img = rowVal; // img        }     }});if (currentRow.name) {    console.log(currentRow);    // push the current row into your array    dataInRows.push(currentRow);}
Enter fullscreen mode Exit fullscreen mode

The stream reader also emits events, e.g. when it reaches the end of the spreadsheet or an error occurs.

Overall the script is pretty lightweight and short. The longest part is usually the one where you define the rows (see above).

Your input

I hope you could learn something new from this little post. Feel free to add your thoughts or questions in the comments below! :)


Original Link: https://dev.to/nena/easily-parse-an-excel-spreadsheet-into-json-2dg7

Share this article:    Share on Facebook
View Full Article

Dev To

An online community for sharing and discovering great ideas, having debates, and making friends

More About this Source Visit Dev To