How to import an Excel file in to a database using angularJS1, hibernate(4.3.5), Spring(4.2.4)?

Question

How to import an Excel file in to a database using angularJS1, hibernate(4.3.5), Spring(4.2.4)?

6.3k views Asked by woodson At 30 April 2018 at 09:13

I want to import an Excel file into my database using angularJS1, Hibernate 4.3.5, Spring mvc 4.2.4. The Excel file is imported via a window (table consisting of children "last name, first name", and parents), the table was filled before manually. The goal now is to fill the table automatically by importing an Excel file. I can read the Excel file on google (json format), but I can not import it into my database. The project consists of a front part (angularJS1) and a back part (hibernate, postgresSQL, DAO). Could you help me please ? This is since Thursday that I seek a solution. Thank you

Here is the code to read my excel file in json format : file : ... Controller.js (front part)

$scope.uploadFile = function (element) {
var file = element.files[0];
console.log("FILE", file);
var reader = new FileReader();
    reader.onload = function (event) {
        var data = event.target.result;
        /*Call XLSX*/
        var workbook = XLSX.read(data, {
            type: 'binary'
        });
        /* DO SOMETHING WITH workbook HERE */
        var first_sheet_name = workbook.SheetNames[0];
        /* Get worksheet */
        var worksheet = workbook.Sheets[first_sheet_name];
        var excelData = XLSX.utils.sheet_to_json(worksheet);
        console.log("EXCELDATA", excelData);
    }
}

Original Q&A

There are 3 answers

**Vaibhav Tekade** · Answer 1 · 2018-04-30T09:30:24+00:00

Instead of trying to read Excel on Front-End side just upload your excel to the server. Excel Reading via JS will consume a significant amount of MEM in browser.

On Java side its quite easy to read/Write Excel all you need is Apache POI For Excel reading ref : https://www.mkyong.com/java/apache-poi-reading-and-writing-excel-file-in-java/

Once you done with excel reading, you can pass required data to hibernate to store in DB.

**woodson** · Answer 2 · 2018-04-30T09:57:28+00:00

woodson On 30 April 2018 at 09:57

I have to read the excel file on the front because it is the user who update the data in the table by importing the Excel file through a upload interface.

**Vaibhav Tekade** · Answer 3 · 2018-04-30T12:50:44+00:00

Here is a sample codepen example created for you.

angular.module('app', [])
  .controller('ExcelReadCtrl', function($scope) {
  
   $scope.data = [{firstName:'AAA',lastName:'BBB',age:30}];
  
 
    $scope.READ = function() {
          /*Checks whether the file is a valid excel file*/
           var regex = /^([a-zA-Z0-9\s_\\.\-:])+(.xlsx|.xls)$/;
           var xlsxflag = false; /*Flag for checking whether excel is .xls format or .xlsx format*/  
           if ($("#ngexcelfile").val().toLowerCase().indexOf(".xlsx") > 0) {  
             xlsxflag = true;  
            } 
           var reader = new FileReader();  
           reader.onload = function (e) {  
             var data = e.target.result;  
             if (xlsxflag) {  
               var workbook = XLSX.read(data, { type: 'binary' });  
             }  
             else {  
               var workbook = XLS.read(data, { type: 'binary' });  
             }    
             
             var sheet_name_list = workbook.SheetNames;  
             var cnt = 0; 
             sheet_name_list.forEach(function (y) { /*Iterate through all sheets*/  
               
               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.data.push(exceljson[i]);  
                                      $scope.$apply();  
                                         }  
               }  
             });  
           }  
           if (xlsxflag) {
             reader.readAsArrayBuffer($("#ngexcelfile")[0].files[0]);  
           }  
           else {  
             reader.readAsBinaryString($("#ngexcelfile")[0].files[0]);  
           }
    };
 
   
  });

<html ng-app="app">
 <head>
    <script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.6.9/angular.min.js"></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   src="https://code.jquery.com/jquery-1.12.4.min.js" 
  crossorigin="anonymous"></script>
  </head>
<body>
<div ng-app>
  <h2>Excel Format should be same as table below, xls.core.min.js reads first row as headers</h2>
  
  <div ng-controller="ExcelReadCtrl">
     <form>  
 <input type="file" id="ngexcelfile" />   
 <input type="button" value="Read Data" ng-click="READ()" />   
    <br />   
    <br />  
 <table border=1>  
 <thead>  
 <tr>  
 <th>Last Name</th>  
 <th>First Name</th>  
 <th>Age</th>  
 </tr>  
 </thead>  
 <tbody>  
 <tr ng-repeat="item in data"> 
 <td>{{item.lastName}}</td>   
 <td>{{item.firstName}}</td>    
  <td>{{item.age}}</td>   
  </tr>   
 </tbody>  
 </table>   
 </form>
  </div>
</div>
</body>
</html>

your excel format must be same as data you are trying to load. Here is example format. Once you load Excel data is stored in $scope.data use same to pass to backend

TechQA.

How to import an Excel file in to a database using angularJS1, hibernate(4.3.5), Spring(4.2.4)?

There are 3 answers

Related Questions in ANGULARJS-1.5

Popular Questions

Popular Tags

Trending Questions