Search This Blog

Wednesday, August 19, 2020

Read Excel file using JavaScript with AngularJS- SharePoint online

 <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/angularjs/1.3.9/angular.min.js"></script>

<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/danialfarid-angular-file-upload/12.2.13/ng-file-upload.min.js"></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">
    var app = angular.module('MyApp', ['ngFileUpload'])
    app.controller('MyController'function ($scope$window) {
        $scope.SelectFile = function (file) {
            $scope.SelectedFile = file;
        };
        $scope.Upload = function () {
            var regex = /^([a-zA-Z0-9\s_\\.\-:])+(.xls|.xlsx)$/;
            if (regex.test($scope.SelectedFile.name.toLowerCase())) {
                if (typeof (FileReader) != "undefined") {
                    var reader = new FileReader();
                    //For Browsers other than IE.
                    if (reader.readAsBinaryString) {
                        reader.onload = function (e) {
                            $scope.ProcessExcel(e.target.result);
                        };
                        reader.readAsBinaryString($scope.SelectedFile);
                    } else {
                        //For IE Browser.
                        reader.onload = function (e) {
                            var data = "";
                            var bytes = new Uint8Array(e.target.result);
                            for (var i = 0i < bytes.byteLengthi++) {
                                data += String.fromCharCode(bytes[i]);
                            }
                            $scope.ProcessExcel(data);
                        };
                        reader.readAsArrayBuffer($scope.SelectedFile);
                    }
                } else {
                    $window.alert("This browser does not support HTML5.");
                }
            } else {
                $window.alert("Please upload a valid Excel file.");
            }
        };
        $scope.CustomersSheet = [];
        $scope.ProcessExcel = function (data) {
            //Read the Excel File data.
            var workbook = XLSX.read(data, {
                type: 'binary'
            });
            //Display the data from Excel file in Table.
            $scope.$apply(function () {
                $scope.CustomersSheet = workbook.SheetNames;
            });
            $scope.onCategoryChange = function () {

                //  $window.alert("Selected Value: " + $scope.itemSelected);


                //Fetch the name of First Sheet.
                var firstSheet = $scope.itemSelected;//workbook.SheetNames[0];                      
                //Read all rows from First Sheet into an JSON array.
                var excelRows = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[firstSheet]);


                $scope.Customers = excelRows;
                $scope.IsVisible = true;

            };
        };

    });
</script>



<div ng-app="MyApp" ng-controller="MyController">
    <input type="file" ngf-select="SelectFile($file)"/>
    <input type="button" value="Upload" ng-click="Upload()"/>
    <hr/>
    <select ng-model="itemSelected" ng-change="onCategoryChange(itemSelected)">
        <option ng-repeat="x in CustomersSheet">{{x}}</option>
    </select>
    <table id="tblCustomers" cellpadding="0" cellspacing="0" ng-show="IsVisible" style="border: 1px; background-color: azure;">
    <tbody ng-repeat="m in Customers">
            <tr>
                <td>{{m.__EMPTY}}</td>
                <td>{{m.__EMPTY_1}}</td>
                <td>{{m.__EMPTY_2}}</td>
                <td>{{m.__EMPTY_3}}</td>
                <td>{{m.__EMPTY_4}}</td>
                <td>{{m.__EMPTY_5}}</td>
                <td>{{m.__EMPTY_6}}</td>                
            </tr>
        </tbody>
    </table>
</div>
<style>
    table {
      font-familyarialsans-serif;
      border-collapsecollapse;
      width100%;
    }
    
    tdth {
      border1px solid #dddddd;
      text-alignleft;
      padding8px;
    }
    
    tr:nth-child(even) {
      background-colorlightGray;
    }
    </style>

    

No comments:

Post a Comment