Reading Excel file in AngularJS

Introduction

Here we will learn Reading Excel file in AngularJS using client-side code javascript. This JavaScript code reads an excel sheet in AngularJs and can be used to upload an excel file and display grid in AngularJs.

Getting Started with Reading Excel file in AngularJS

AnglarJS client side code(java script) does not support for Reading Excel file in AngularJS directly, but GitHub provides SheetJS javascript library for excel(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 below code example browses an excel file using HTML5 control and the help of the above-mentioned library 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. Go through the below code for more about the demonstration.

Code Example of Reading Excel file in AngularJS

 <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>