Convert Excel file data to JSON in Javascript

In this article, you will see how to convert excel file data to JSON format in 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, if 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 at 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 at 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 file data to JSON using Javascript
Excel file data to JSON using Javascript

How to convert Excel file data 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 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 logic

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 upload button click. This method allows 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 have 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 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 extract the excel data in JSON

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) which 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, age).

Excel file with students data

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

Excel file data to convert into JSON

Conclusion

In this article, 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:

Share with friends

Leave a Comment

Your email address will not be published.