Introduction
This article demonstrates how to read excel file in AngularJS using client side code that means java script and jquery. This demonstration reads first sheet of excel file which contains details about students in the table format and displays students details in UI in table format.
Getting Started
AnglarJS client side code(java script) does not support reading excel file directly, but GitHub provides SheetJS library(Spreadsheet Parser and Writer) to read excel sheet using jquery. currently two jquery libraries (xlsx.core.min.js,xls.core.min.js) available in GitHub. As name describes this two libraries are used to read excel sheet having extension .xlsx and xls.
This demonstrator browses excel file using HTML5 control and help of above mentioned library parses each row of excel sheet into JSON object.This library treats each column header as properties of class and cell values as value of each properties. Go through the below code for more about demonstration.
Demonstration
<HTML ng-app="studentapp">
<HEAD>
<TITLE>
Learning AngularJS
</TITLE>
<script src="angular.min.js"></script>
<script src="jquery-1.10.2.min.js" type="text/javascript"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.7.7/xlsx.core.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xls/0.7.4-a/xls.core.min.js"></script>
<script type="text/javascript">
var app=angular.module("studentapp",[]);
app.controller("studentcontroller",function($scope){
$scope.students=[
{StudentRollNo:1,StudentName:"Kailash",Class:"1 std",Div:"A"},
{StudentRollNo:2,StudentName:"Sudhir",Class:"1 std",Div:"B"},
{StudentRollNo:3,StudentName:"Pankaj",Class:"2 std",Div:"C"},
{StudentRollNo:4,StudentName:"Panxi",Class:"2 std",Div:"D"},
{StudentRollNo:5,StudentName:"Praban",Class:"3 std",Div:"E"},
{StudentRollNo:6,StudentName:"Praphul",Class:"3 std",Div:"F"},
]
$scope.ReadExcelData=function() {
var regex = /^([a-zA-Z0-9\s_\\.\-:])+(.xlsx|.xls)$/;
/*Checks whether the file is a valid excel file*/
if (regex.test($("#ngexcelfile").val().toLowerCase())) {
var xlsxflag = false; /*Flag for checking whether excel is .xls format or .xlsx format*/
if ($("#ngexcelfile").val().toLowerCase().indexOf(".xlsx") > 0) {
xlsxflag = true;
}
/*Checks whether the browser supports HTML5*/
if (typeof (FileReader) != "undefined") {
var reader = new FileReader();
reader.onload = function (e) {
var data = e.target.result;
/*Converts the excel data in to object*/
if (xlsxflag) {
var workbook = XLSX.read(data, { type: 'binary' });
}
else {
var workbook = XLS.read(data, { type: 'binary' });
}
/*Gets 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*/
sheet_name_list.forEach(function (y) { /*Iterate through all sheets*/
/*Convert the cell value to Json*/
if (xlsxflag) {
var exceljson = XLSX.utils.sheet_to_json(workbook.Sheets[y]);
}
else {
var exceljson = XLS.utils.sheet_to_row_object_array(workbook.Sheets[y]);
}
if (exceljson.length > 0) {
for (var i = 0; i < exceljson.length; i++) {
$scope.students.push(exceljson[i]);
$scope.$apply();
}
}
});
}
if (xlsxflag) {/*If excel file is .xlsx extension than creates a Array Buffer from excel*/
reader.readAsArrayBuffer($("#ngexcelfile")[0].files[0]);
}
else {
reader.readAsBinaryString($("#ngexcelfile")[0].files[0]);
}
}
else {
alert("Sorry! Your browser does not support HTML5!");
}
}
else {
alert("Please upload a valid Excel file!");
}
}
});
</script>
</HEAD>
<BODY ng-controller="studentcontroller" border="1">
<h2>Reading Excel data in AngularJS</h2>
<hr/>
<form>
<input type="file" id="ngexcelfile" />
<input type="button" value="Read Data" ng-click="ReadExcelData()" />
<br />
<br />
<table border=1>
<thead>
<tr>
<th>Student Name</th>
<th>Roll No.</th>
<th>Class</th>
<th>DIV</th>
</tr>
</thead>
<tbody>
<tr ng-repeat="item in students">
<td>{{item.StudentName}}</td>
<td>{{item.StudentRollNo}}</td>
<td>{{item.Class}}</td>
<td>{{item.Div}}</td>
</tr>
</tbody>
</table>
</form>
</BODY
</HTML>
Summary
In this article we see, how to read excel file using java script in AngularJS. Above demonstration displays excel data in the UI, but it can be pase to remote service as above libraries are converting excel data to JSON obje. Hope this article may help you, enjoy and happy coding.
Thanks
Kailash Chandra Behera
No comments:
Post a Comment