Introduction
It is very good news to all web developers that GitHub provides an ajax library developed in the javascript to read excel sheets. currently two ajax libraries (xlsx.core.min.js,xls.core.min.js) available on GitHub. As the name describes these two libraries are used to read excel sheets having extensions .xlsx and xls.
 |
JavaScript Read Excel File |
Getting Started
This article provides code blocks that read all the sheet data of an excel sheet and displays the sheet data on the web page in the table format. Each table represents the sheet data, the demo excel sheet contains two sheets.
The following code block contains the completed code to read and display the excel data on the web page. You can directly copy and past it into notepad and test the functionality.
<HTML>
<HEAD>
<TITLE>
JavaScript Read Excel File
</TITLE>
<script type="text/javascript">
function onChange(event) {
var file = event.target.files[0];
/*Checks whether the browser supports HTML5*/
if (typeof (FileReader) != "undefined") {
var filetype = validateFile();
if (filetype == "NA") {
alert("Please upload a valid Excel file!");
}
else {
var reader = new FileReader();
var data = "";
if (reader.readAsBinaryString) {
reader.onload = function (e) {
Readexceldata(filetype, e.target.result);
};
reader.readAsBinaryString(file);
}
else {
reader.onload = function (e) {
var bytes = new Uint8Array(e.target.result);
for (var i = 0; i < bytes.byteLength; i++) {
data += String.fromCharCode(bytes[i]);
}
Readexceldata(filetype, data);
};
reader.readAsArrayBuffer(file);
}
}
}
else {
alert("Sorry! Your browser does not support HTML5!");
}
}
</script>
<script type="text/javascript">
/*Checks whether the file is a valid excel file*/
function validateFile() {
var _fileuploader = document.getElementById("fileUploader");
var regex = /^([a-zA-Z0-9\s_\\.\-:])+(.xlsx|.xls)$/;
if (regex.test(_fileuploader.value.toLowerCase())) {
/*Flag for checking whether excel is .xls format or .xlsx format*/
if (_fileuploader.value.toLowerCase().indexOf(".xls") > 0) {
return "xls";
}
else {
return "xlsX";
}
}
else {
return "NA";
}
}
</script>
<script type="text/javascript">
function PopulateData(sheetName, data) {
//Create a HTML _table element.
var _table = document.createElement("table");
_table.border = "1";
_table.innerHTML = "<b>" + sheetName + "</b >";
_table.setAttribute("style", "float:left;width:200px;");
//Adds the a _row s
var _row = _table.insertRow(-1);
//Adds the cells header.
var _cellheader = document.createElement("TH");
_cellheader.innerHTML = "Name";
_row.appendChild(_cellheader);
_cellheader = document.createElement("TH");
_cellheader.innerHTML = "ID";
_row.appendChild(_cellheader);
_cellheader = document.createElement("TH");
_cellheader.innerHTML = "Class";
_row.appendChild(_cellheader);
//Add the data _rows from Excel file.
for (var i = 0; i < data.length; i++) {
//Add the data _row.
var _row = _table.insertRow(-1);
//Add the data cells.
var _cell = _row.insertCell(-1);
_cell.innerHTML = data[i].Name;
_cell = _row.insertCell(-1);
_cell.innerHTML = data[i].ID;
_cell = _row.insertCell(-1);
_cell.innerHTML = data[i].Class;
}
var dvExcel = document.getElementById("dvexceldata");
dvExcel.appendChild(_table);
//var _br = document.createElement("br");
//dvExcel.appendChild(_br);
//_br=document.createElement("br");
//dvExcel.appendChild(_br);
}
</script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.13.5/xlsx.full.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.13.5/jszip.js"></script>
<script type="text/javascript">
function Readexceldata(filetype,bynarydata) {
if (bynarydata == "") {
alert("data is blank");
return;
}
/*Converts the excel data in to object based on tye file type*/
if (filetype == "xls") {
var workbook = XLS.read(bynarydata, { type: 'binary' });
}
else {
var workbook = XLSX.read(bynarydata, { type: 'binary' });
}
//alert("sheetname"+JSON.stringify(workbook.SheetNames))
/*Fetching all the sheetnames of excel in to a variable*/
var sheet_name_list = workbook.SheetNames;
var cnt = 0; /*This is used for restricting the script to consider only first sheet of excel*/
/*Iterate through all sheets*/
sheet_name_list.forEach(function (y) {
/*Convert the cell value to Json*/
if (filetype == "xls") {
var exceljson = XLS.utils.sheet_to_json(workbook.Sheets[y]);
PopulateData(String(y), exceljson);
}
else {
var exceljson = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[y]);
PopulateData(String(y), exceljson);
}
});
}
</script>
</HEAD>
<BODY>
<input type="file" id="fileUploader" onchange="onChange(event)"/>
<hr />
<div id="dvexceldata" style="text-align: center;padding:10px"></div>
</BODY>
</HTML>
The above code example browses an excel file using HTML5 control(input type file) and reads the excel data with the help of the above-mentioned ajax library (xlsx.core.min.js)that parses each row of the excel sheet into a JSON object. This library treats each column header as properties of class and cell values as the value of each property.
In the above example, the excel contains two sheets. The first sheet is contains data for teacher and the second sheet contains student data. the ajax library reads the two sheet from excel file and converts into JSON string. Finally using javascript language displays fetched data onthe page.
 |
| Javascript Read Excel |
Note:-The above code will work on the browser which supports HTML5.
Thanks
No comments:
Post a Comment