How to Read Excel Files in JavaScript

1. Install the xlsx Node.js Package
2. Read Excel Files in JavaScript

There are times when we want to read and parse data from excel files and convert it into a JSON format to use on our web page. We can do this in various ways.

This article will discuss reading the excel file using the xlsx package in JavaScript.

For this example, we will be using an excel file called ss.xls which will have two sheets, Sheet1 and Sheet2, with some data inside them, as shown in the below image.

Install the xlsx Node.js Package

To install the xlsx package, you can run the below command. Ensure that you have Node.js installed on your system.

If you are unsure whether you have installed Node.js, you can use the node -v command inside the terminal. If you get the version number as output, Node.js is installed on your system.

Command:

npm install xlsx

You can parse the excel files with the .xls and xlsx extensions using this library. The .xls is the older file format that stores the data in binary format, whereas the .xlsx is the latest file format that stores data in compressed XML files in ZIP format.

Read Excel Files in JavaScript

After installing the package, we first have to import the package inside our Node.js project, i.e., inside the index.js file. Now, we will create a function parseExcel(), which will take a file filename as a parameter and return an array of objects.

Now that we have the excel filename, we can read and extract all the information from that file using the readFile function of the XLSX package. And then, we will store the result inside the excelData variable.

The excelData variable now contains all the information related to the excel file like author name, modified data, number of sheets, file data, and more.

To get more information about the data stored inside the excelData variable related to the excel file, you can print the variable using the console.log() function.

Code snippet:

const XLSX = require('xlsx');

const parseExcel = (filename) => {
  const excelData = XLSX.readFile(filename);

  return Object.keys(excelData.Sheets).map(name => ({
                                             name,
                                             data: XLSX.utils.sheet_to_json(
                                                 excelData.Sheets[name]),
                                           }));
};

parseExcel('./ss.xls').forEach(element => {
  console.log(element.data);
});

The excelData variable now contains an object called Sheets. This object has a collection of objects; each object in the Sheets represents a sheet of the excel file.

To parse the data from all the sheets present inside the excel file, we will use the excelData.Sheets object.

Code snippet:

Sheets: {
  Sheet1: {'!ref': 'A1:D7', A1: [Object], B1: [Object], '!margins': [Object]},
  Sheet2: {'!ref': 'A1:C3', A1: [Object], B1: [Object], '!margins': [Object]}
},

Since the excelData.Sheets object contains various objects inside it, as shown above, we will get the key (here, key refers to the excel sheet name like Sheet1, Sheet2, etc.) of those objects using the Object.key() function.

Now, we will use the map() function and pass the name as a parameter which we get from the Object.keys() to the map() function. Using the map() function, we will return an object which will contain two properties name of the excel sheet and the data that the excel sheet contains.

Here, we already have the name to directly store that name inside the object. But to get the data from the excelData variable we have to use the sheet_to_json() function provided by the XLSX.utils.

As you can see in the above code snippet, the data we get from the file is in A1: [Object] format, but we need to parse this data.

We will use the sheet_to_json() function to convert the data present inside the excel sheet into the JSON format. Then, we will store this data inside the second property of the object data.

You can give any name to the data property.

So, at this stage, if you run the parseExcel() function by providing the file path as an argument, this will return an array that will contain all the excel sheets with their respective data in the form of an object.

Code snippet:

[{
  name: 'Sheet1',
  data: [[Object], [Object], [Object], [Object], [Object], [Object]]
},
 {name: 'Sheet2', data: [[Object], [Object]]}]

Now, to only get the data of all the excel sheets and not the name, we can iterate over the array returned by the parseExcel() function using the forEach loop. We will take an object and store it inside the element variable at every iteration.

Using the dot notation, we can access the data property of that object element.data and then print it on the console.

Output: