How to read Excel files in Javascript/Jquery and convert the value to Json

Introduction

We have multiple ways to read excel file in C# and then convert it to JSON or insert the data in to database etc. However there are not much options available when it comes to reading the Excel file using javascript. In this article I will explain an option using a Jquery Plugin called SheetJS.

SheetJS is Excel parser which supports multiple formats of excel files like 2007, 2010, 2013 office versions etc.

Implementation

To use the SheetJS user need to add the reference files first. You can do this either by downloading files from here and then refer these js files to project. Another option is to use the online CDN available.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.7.7/xlsx.core.min.js"></script>

Reading the Excel File

To read the excel file we use the read method in SheetJs like below

var workbook = XLSX.read(data, { type: 'binary' });

Converting Excel value to Json
To convert the values to Json will use “sheet_to_json” method

          var sheet_name_list = workbook.SheetNames;
           sheet_name_list.forEach(function (y) { /* iterate through sheets */
                    //Convert the cell value to Json
                    var roa = XLSX.utils.sheet_to_json(workbook.Sheets[y]);
                    if (roa.length > 0) {
                        result = roa;
                    }
                });

Complete Code

	<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script&gt;
	<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.7.7/xlsx.core.min.js"></script&gt;
	<script>
	function handleFile(e) {
	//Get the files from Upload control
	var files = e.target.files;
	var i, f;
	//Loop through files
	for (i = 0, f = files[i]; i != files.length; ++i) {
	var reader = new FileReader();
	var name = f.name;
	reader.onload = function (e) {
	var data = e.target.result;
	var result;
	var workbook = XLSX.read(data, { type: 'binary' });
	var sheet_name_list = workbook.SheetNames;
	sheet_name_list.forEach(function (y) { /* iterate through sheets */
	//Convert the cell value to Json
	var roa = XLSX.utils.sheet_to_json(workbook.Sheets[y]);
	if (roa.length > 0) {
	result = roa;
	}
	});
	//Get the first column first cell value
	alert(result[0].Column1);
	};
	reader.readAsArrayBuffer(f);
	}
	}
	//Change event to dropdownlist
	$(document).ready(function(){
	$('#files').change(handleFile);
	});
	</script>
	<input type="file" id="files" name="files"/>

view raw

SheeJsSample

hosted with ❤ by GitHub

Sample Excel I used for this Demo