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.
Table of Contents
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 usingreadAsBinaryString()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).
Let’s see the result once we upload the above Excel file to this application.
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:
- File upload validations in javascript
- Drag & Drop or Browse – File upload Feature in JavaScript
- Preview an image before uploading using Javascript
- Preview an image before uploading using jQuery
Point well taken!
Awesome!! Thanks a lot!!