How to convert XLSX to JSON

Do you need to get data from a spreadsheet and turn it into a JSON ? That's not 🚀 science and I'm gonna prove it !

First, install the xlsx package.

With npm :

// NPM
npm  install  xlsx

// Yarn
yarn  add  xlsx

In the app.js file, import xlsx and fs to read the excel file, and declare a variable that we will use later.

const XLSX = require('xlsx');
const fs = require('fs');
const finalObject = {};

To read and get the content of the file with the Buffer type :

const data = XLSX.read(myFile, { type: 'buffer' });

N.B: The different types are "string", "buffer", "base64", "binary", "file", "array"

If you write console.log(data.Sheet) you will see your spreadsheets and the cells content.

Then you have to write the process for each row of each spreadsheet.

data.SheetNames.forEach(sheetName => {
  let rowObject = XLSX.utils.sheet_to_json(data.Sheets[sheetName]);

  finalObject[sheetName] = rowObject;
});

The sheet_to_json function allows to convert a spreadsheet into an array of objects.
It takes differents optionnal parameters that you can find here
Here we won't need anything

If you do a console.log(rowObject), you will see that it contains an array, and each row of the spreadsheet is turned into an object like so :

[
  { "ID": 1, "Last name": "Doe", "First name": "John" },

  { "ID": 2, "Last Name": "Doe", "First name": "Jane" }
]

Do you remember the variable we declared at the beginning ? It is time to use it. We are going to create a key for each spreadsheet and assign rowObject to it :

data.SheetNames.forEach(sheetName => {
  let rowObject = XLSX.utils.sheet_to_json(data.Sheets[sheetName]);

  finalObject[sheetName] = rowObject;
});

If you console.log(finalObject) :

"Utilisateurs": [
{ "ID": 1, "Last name": "Doe", "First name": "John" },
{ "ID": 2, "Last name": "Doe", "First name": "Jane" }
]

If you want to write the output into a file, simply add this line :

fs.writeFileSync('./target.json', JSON.stringify(dataObject));

Voilà 🎉
Now you know how to convert an Excel spreadsheet into JSON !

Originally posted on my blog. Check out my instagram account to learn more about web development.