Convert Excel to JSON Javascript 2024

January 30, 2024 / , , , / By

In this article, you will see how to convert Excel to JSON using Javascript. There is a popular library called Apache POI to read Excel file data, but this library is all about doing it server-side. In some cases, you don’t want to allow users to upload an Excel file without doing the proper validation. So in order to validate an Excel file, you have to read it on the client-side itself before uploading it to the server.

This article shows you how can you read an Excel file to convert it into JSON on the client side only. We are going to use a Javascript library (xlsx.min.js) provided by SheetJS to convert Excel file data to JSON.

excel to json converter | Excel file data to JSON using Javascript | excel to json javascript
Excel file data to JSON using Javascript

How to convert Excel to JSON in Javascript?

It is very simple to do in Javascript using the xlsx.min.js library. Follow the below simple steps:

1. Define HTML

We have defined an HTML <input> tag with type=”file” to choose an Excel file. And a <button> tag to upload the file and a <textarea> tag to display the resulting JSON format data.

<h1>Upload an excel file to convert into JSON</h1>
<!-- Input element to upload an excel file -->
<input type="file" id="file_upload" />
<button onclick="upload()">Upload</button>	
<br><br>
<!-- container to display the json result -->
<textarea id="json-result" style="display:none;height:500px;width:350px;"></textarea>

2. Include the library in the HTML file

Include the CDN link of library xlsx.min.js in the <head> tag of an HTML file as follows:

<head>
   <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.17.5/xlsx.min.js"></script>
</head>

3. Write the Javascript for Excel to JSON Conversion

Write the below javascript code to extract the data in JSON format from the uploaded Excel file.

   // Method to upload a valid excel file
  function upload() {
	var files = document.getElementById('file_upload').files;
	if(files.length==0){
	  alert("Please choose any file...");
	  return;
	}
	var filename = files[0].name;
	var extension = filename.substring(filename.lastIndexOf(".")).toUpperCase();
	if (extension == '.XLS' || extension == '.XLSX') {
		excelFileToJSON(files[0]);
	}else{
		alert("Please select a valid excel file.");
	}
  }
  
  //Method to read excel file and convert it into JSON 
  function excelFileToJSON(file){
	  try {
		var reader = new FileReader();
		reader.readAsBinaryString(file);
		reader.onload = function(e) {

			var data = e.target.result;
			var workbook = XLSX.read(data, {
				type : 'binary'
			});
			var result = {};
			workbook.SheetNames.forEach(function(sheetName) {
				var roa = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);
				if (roa.length > 0) {
					result[sheetName] = roa;
				}
			});
			//displaying the json result
			var resultEle=document.getElementById("json-result");
			resultEle.value=JSON.stringify(result, null, 4);
			resultEle.style.display='block';
			}
		}catch(e){
		    console.error(e);
		}
  }

Explanation:

  • Here we have defined a method upload() that will be called on the upload button click. This method allows the user to upload only a valid Excel file.
  • The method excelFileToJSON() is defined to read the uploaded Excel file and convert data into JSON format.
  • Inside excelFileToJSON(), we have read the data of the Excel file by using a file reader as a binary string using readAsBinaryString() method.
  • After that, we used XLSX which has a built-in facility to convert our binary string into a JSON object. And the XLSX.utils.sheet_to_row_object_array() method is used to read each sheet of data in a loop by iterating the workbook.
  • To beautify and display the JSON result, we have used JSON.stringify(result, null, 4).

Complete code example to convert Excel to JSON using Javascript

Putting all the above code in a single HTML document as follow:

<!DOCTYPE html>
<html>
  <head>
    <meta name="viewport" content="width=device-width, initial-scale=1">  
	<title>Excel to JSON | Javacodepoint</title>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.17.5/xlsx.min.js"></script>    
  </head>
  <body>
  	<h1>Upload an excel file to convert into JSON</h1>
    <input type="file" id="file_upload" />
    <button onclick="upload()">Upload</button>	
    <br>
    <br>
	<!-- container to display the json result -->
    <textarea id="json-result" style="display:none;height:500px;width:350px;"></textarea>
    <script>
	
      // Method to upload a valid excel file
	  function upload() {
		var files = document.getElementById('file_upload').files;
		if(files.length==0){
		  alert("Please choose any file...");
		  return;
		}
		var filename = files[0].name;
		var extension = filename.substring(filename.lastIndexOf(".")).toUpperCase();
		if (extension == '.XLS' || extension == '.XLSX') {
			excelFileToJSON(files[0]);
		}else{
			alert("Please select a valid excel file.");
		}
	  }
	  
	  //Method to read excel file and convert it into JSON 
	  function excelFileToJSON(file){
		  try {
			var reader = new FileReader();
			reader.readAsBinaryString(file);
			reader.onload = function(e) {

				var data = e.target.result;
				var workbook = XLSX.read(data, {
					type : 'binary'
				});
				var result = {};
				workbook.SheetNames.forEach(function(sheetName) {
				var roa = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);
				if (roa.length > 0) {
					result[sheetName] = roa;
				}
			  });
				//displaying the json result
				var resultEle=document.getElementById("json-result");
				resultEle.value=JSON.stringify(result, null, 4);
				resultEle.style.display='block';
				}
			}catch(e){
				console.error(e);
			}
	  }
    </script>
  </body>
</html>

Test and Live Demo

Let’s assume a sample Excel file(students.xlsx) that we are going to upload. As you can see in the below image this Excel file contains the student’s data(name, address, email id, and age).

convert excel to json javascript

Let’s see the result once we upload the above Excel file to this application.

Excel file data to convert into JSON | xls to JSON

Conclusion

Converting Excel to JSON is a common requirement in modern web development. With JavaScript and libraries like SheetJS (XLSX), this task becomes straightforward, whether you need to perform it on the client side or server side. Here, you have seen how to upload an Excel file to convert it into JSON format using Javascript on the client side. As it will be very helpful when you want to validate the Excel data before going to upload it to the server.

Related articles:

You might like this:

Share with friends

2 thoughts on “Convert Excel to JSON Javascript 2024”

Leave a Comment

Your email address will not be published. Required fields are marked *