Reading Excel file in AngularJS

Kailash Chandra Behera | Tuesday, August 22, 2017

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>

Reading Excel file in AngularJS

Related Articles

  1. Import Excel in C#
  2. Export DataTable To Excel in C#
  3. Import XML into SQL Table
  4. Creating controller for File Upload
  5. Export Table Data into XML in SQL
  6. Export to Excel in MVC
  7. Export HTML Table to Excel Using JavaScript
  8. Validating Excel Sheet in C#

Summary

In this blog we see, the code example for Reading Excel file in AngularJS. Above code example 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 you have enjoyed it a lot.

Thanks

By at