Mastering Data Formats: Fetching Excel Data in JSON with Next.js

crs: Adobe Firefly

Hello there curious people, today we are going to try and master data formatting with easy steps and we will take these steps together.

To start the project firstly we need to install the xlsx package.

for that, we will need to use the command

npm i xlsx

With this command we installed SheetJS that is the main and mostly used library to work with documents.

After the installation, we will build a file uploader, which will take an Excel file.

So we take a file and on change, we’re going to pass the event and say, handle the file.
The code looks like this:

 <input type='file' onChange={(e) => handleFile(e)} />

Now we need to declare the handleFile function to be able to use it. The function will take an event and will handle file input events.

const handleFile = (e) => {
console.log(e.target.files[0] }

With this, we will be able to upload an excel document and see it in the console. It will look something like this, look under the name.

src: https://www.youtube.com/watch?v=GUFzw4jo_E4&t=610s

We use another piece of code inside our already declared function, to retrieve the first selected file, to read the file’s content as an ArrayBuffer and interpret it as an Excel workbook.

But before that, we don't need to forget about importing XLSX, do we?

import * as XLSX from 'xlsx';

Here's the code for the function itself.

const handleFile = async (e) => {
const file = e.target.files[0];
const data = await file.arrayBuffer();
const workbook = XLSX.read(data);
 }

Okay, so we get the file, we get the data and read it in an array buffer and then we parse the workbook.
So now once we read the Excel worksheet, let’s display a name right here. For that we’re going to use date, let’s call it filename. We’re going to set it to null and we have to import usestate from react.

const [fileName, setFileName] = useState(null);

So now let’s read all the data from our excel sheet and parse it to JSON.

Let’s go ahead, and say const JSON data is equal to let’s paste it. And here we’re going to have to pass an excel workbook. Let’s get the first sheet and then we gonna pass the worksheet here. And once we parse that we will get the JSON data.

const worksheet = workbook.Sheete[workbook.SheetNames[0]];
const jsonData = XLSX.utils.sheet_to_json(worksheet);

now we have it, it’s converted into json format and our function fully looks like this:

const handleFile = async (e) => {

const file = e.target.files[0];
setFileName(file.name);
const data = await file.arrayBuffer();
const workbook = XLSX.read(data);

const worksheet = workbook.Sheete[workbook.SheetNames[0]];
const jsonData = XLSX.utils.sheet_to_json(worksheet);
 }

It was easy, wasn’t it?