DEV Community

Ludivine A
Ludivine A

Posted on • Updated on

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
Enter fullscreen mode Exit fullscreen mode

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 = {};
Enter fullscreen mode Exit fullscreen mode

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

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

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;
});
Enter fullscreen mode Exit fullscreen mode

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" }
]
Enter fullscreen mode Exit fullscreen mode

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;
});
Enter fullscreen mode Exit fullscreen mode

If you console.log(finalObject) :

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

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

fs.writeFileSync('./target.json', JSON.stringify(dataObject));
Enter fullscreen mode Exit fullscreen mode

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.

Discussion (8)

Collapse
lukeshiru profile image
Luke Shiru

Nice! One thing tho: Nowadays ideally you should use import instead of require, and you could use Object.fromEntries with Array.prototype.map to simplify it a bit, so the code would look more like this:

// We use import
import XLSX from "xlsx";
import { writeFile } from "node:fs/promises";

// Because "xlsx" is a CJS package, we extract what we need here instead of
// doing it in the import itself. Once they migrate to ESM we would be able to
// do this on top: `import { readFile, utils } from 'XLSX'`, bur for now...
const { readFile, utils } = XLSX;

// We also extract only what we need from `readFile`
const { Sheets, SheetNames } = readFile("./source.xls");

// Finally we write it to a `target.json` file
writeFile(
    "./target.json",
    JSON.stringify(
        Object.fromEntries(
            SheetNames.map(sheetName => [
                sheetName,
                utils.sheet_to_json(Sheets[sheetName]),
            ]),
        ),
    ),
)
    // We also added some logging to make UX a little bit better
    .then(() => console.log("Conversion done!"))
    .catch(console.error);
Enter fullscreen mode Exit fullscreen mode

Cheers!

Collapse
lachouri profile image
Ludivine A Author

Thank you for your feedback it's really interesting ! I'll definitely try that out !

Collapse
cincybc profile image
CincyBC

You could do it in two steps in Python.

import pandas as pd
excel_file = pd.read_excel(excel_filepath)
excel_file.to_json(json_filepath, ***choose json format ***)
Enter fullscreen mode Exit fullscreen mode

Pandas to json is very powerful and can do a lot of customizations. Check it out!
pandas.pydata.org/docs/reference/a...

Collapse
lachouri profile image
Ludivine A Author

Hi ! Thank you for your feedback. That's really useful !

Collapse
aminmansuri profile image
hidden_dude

I'm assuming your choice of type: "buffer" was due to the fact that it won't load all in memory at once?

It would be nice to be able to write the JSON without having to store the entire file in RAM as well. Because in multi-user applications that would end creating a scalability issue.

Collapse
lachouri profile image
Ludivine A Author

Hi ! Thank you for your feedback !
When I wrote that article, I needed a method to convert one file once in a while and I didn't think about scalability.
But you're right, this method can certainly be improved greatly !
I will definitly look into that πŸ€”

Collapse
raguram1991 profile image
RaguRam1991

thanks buddy, much useful one. thanks a lot

Collapse
lachouri profile image
Ludivine A Author

Thank you ! Glad I could help !