How to Upload Excel File in Angularjs Using Mvc

Introduction

In this article, I volition demonstrate how to import Excel data into SQL Server using AngularJS in MVC5. I will use jQuery AJAX to think the data from the database and also use jQuery data table for sorting, searching and paging functionality.

Step 1Open SQL Server 2014 and create a database table to insert and call back the data.

1. CREATE Tabular array  [dbo].[StudentsList](
2.     [RollNumber] [int ] IDENTITY(grand,i) Not NULL ,
3.     [Student_Name] [nvarchar](50)Cypher ,
4.     [Hindi] [int ] NULL ,
5.     [English] [int ] NULL ,
6.     [Physics] [int ] NULL ,
7.     [Chemical science] [int ] Cipher ,
8.     [Biology] [int ] NULL ,
9.     [Mathematics] [int ] NULL ,
10. CONSTRAINT  [PK_StudentsList] PRIMARY Key  Clustered
11. (
12.     [RollNumber]ASC
13. )WITH  (PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON  [ Primary ]
14. )ON  [ Principal ]
16. Get

Screenshot for the database table

Stride 2Open Visual Studio 2015, click on New Project, and create an empty web application project.

Screenshot for creating new project i

After clicking on New Project, i window will appear. Select Spider web from the left console, choose ASP.NET Web Awarding, requite it a meaningful proper noun and click on OK.

Screenshot for creating new project ii

One more than window will appear; choose Empty check on MVC checkbox and click on OK.

Screenshot for creating new projection 3

Subsequently clicking on OK, the project will exist created with the name of ImportExcelDataAngularJS_Demo.

Pace 3Add together Entity Framework, right click on Models binder, select Add and and then select New Particular followed by a click on it.

Screenshot for calculation Entity Framework i

Later on clicking on New Detail, you will go a window. From there, select Data from the left console and choose ADO.Cyberspace Entity Information Model, give it a name as DBModels (this name is not mandatory; you tin give any name), then click on Add.

Screenshot for adding Entity Framework ii

Afterward y'all click on Add, a window wizard will open. Choose EF Designer from the database and click Next.

Screenshot for calculation Entity Framework 3

Afterward clicking on Next, a new window will appear. Cull New Connection.

Screenshot for calculation Entity Framework 4

Another window will announced. Add your server proper noun; if it is local then enter dot (.). Choose your database and click on OK.

Screenshot for adding Entity Framework 5

Connection will get added. If you wish to save connection every bit you want, you tin modify the proper name of your connectedness below. It volition save connection in web config. Only click on Next.

Screenshot for adding Entity Framework vi

Afterwards clicking on NEXT another window volition appear. Choose database table proper name as shown in below screenshot then click on Finish. Entity framework will exist added and respective grade gets generated under Models folder.

Screenshot for adding Entity Framework 7

Screenshot for adding Entity Framework viii

Following form will be added.

1. namespace ImportExcelDataAngularJS_Demo.Models
2. {
3.     using Organisation;
4.     using Organisation.Collections.Generic;
6. publicclass StudentsList
7.     {
8. publicint RollNumber { get; set; }
9. public cord Student_Name { get; set; }
10. public Nullable<int> Hindi { get; set; }
11. public Nullable<int> English { get; set up; }
12. public Nullable<int> Physics { become; fix; }
13. public Nullable<int> Chemistry { go; gear up; }
14. public Nullable<int> Biology { get; fix; }
15. public Nullable<int> Mathematics { get; set; }
16.     }
17. }

Step 4Right click on Controllers folder select Add together then cull Controller. Every bit shown in below screenshot.

After click on controller a window will appear choose MVC5 Controller-Empty click on Add.

After clicking on Add another window will appear with DefaultController. Modify the name HomeController then click on Add. HomeController will be added under Controllers binder. As shown in the below screenshot.

Add the following namespace in controller

1. using ImportExcelDataAngularJS_Demo.Models;

Consummate controller lawmaking.

1. using ImportExcelDataAngularJS_Demo.Models;
2. using System.Collections.Generic;
3. using System.Linq;
4. using Arrangement.Web.Mvc;
6. namespace ImportExcelDataAngularJS_Demo.Controllers
7. {
8. publicclass HomeController : Controller
9.     {
10.         
11. public ActionResult Alphabetize()
12.         {
13. return View();
14.         }
15. public ActionResult GetData()
16.         {
17.             using (DBModel db =new DBModel())
18.             {
19.                 List<StudentsList> studentList = db.StudentsLists.ToList<StudentsList>();
20. return Json(new { information = studentList }, JsonRequestBehavior.AllowGet);
21.             }
22.         }
23. public ActionResult ImportData()
24.         {
25. return View();
26.         }
28.         [HttpPost]
29. public ActionResult ImportData(List<StudentsList> studentList)
30.         {
31.             bool status =simulated;
32. if (ModelState.IsValid)
33.             {
34.                 using (DBModel db =new DBModel())
35.                 {
36.                     foreach (var iin studentList)
37.                     {
38.                         db.StudentsLists.Add(i);
39.                     }
40.                     db.SaveChanges();
41.                     status =true;
42.                 }
43.             }
44. returnnew JsonResult { Data =new { status = status } };
45.         }
46.     }
47. }

Footstep 5

Right click on ImportData activeness method in controller. Add view. A window will appear with default ImportData name unchecked (utilize a Layout page) click on Add, every bit show in the below screenshot. View will be added in views binder nether Home folder with name Cavalcade.

Screenshot for adding view

Step 6

Click on Tools select NuGet Bundle Manager then choose Manage NuGet Packages for Solution click on information technology.

Screenshot for NuGet Package

After that a window volition announced cull Browse type bootstrap and install package in project.

Similarly type JQuery and install latest version of JQuery package in project and jquery validation file from NuGet then close NuGet Solution.

Go along required bootstrap and jQuery file and delete remaining file if not using. Or you tin download and add it in the project.

Step 7

Add required script and style in head section of view.

1. <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-crawly/4.vii.0/css/font-awesome.min.css">
2. <link href="~/Content/bootstrap.min.css" rel="stylesheet" />
3. <script src="~/scripts/jquery-three.3.one.min.js"></script>
4. <script src="~/scripts/bootstrap.min.js"></script>
5. <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.1/xlsx.total.min.js"></script>
6. <script src="http://oss.sheetjs.com/js-xlsx/jszip.js"></script>
7. <script src="https://cdnjs.cloudflare.com/ajax/libs/angular.js/1.half-dozen.one/angular.min.js"></script>
8. <script src="~/scripts/ImportData.js"></script>
9. <link href="~/Content/dataTables.bootstrap4.min.css" rel="stylesheet" />
10. <script src="~/scripts/jquery.dataTables.min.js"></script>
11. <script src="~/scripts/dataTables.bootstrap4.min.js"></script>
12. <script type="text/javascript">
13.     $(document).ready(function () {
14.         $('#dataTable').DataTable({
15. "ajax": {
16. "url":"/Home/GetData",
17. "type":"GET",
18. "datatype":"json"
19.             },
20. "columns": [
21.                 {"information":"RollNumber" },
22.                 {"data":"Student_Name" },
23.                 {"data":"Hindi" },
24.                 {"data":"English" },
25.                 {"data":"Physics" },
26.                 {"data":"Chemistry" },
27.                 {"information":"Biology" },
28.                 {"data":"Mathematics" }
30.             ]
31.         });
32.     });
33. </script>

Footstep 8

Right click on scripts folder, select Add, and choose JavaScript File. Give name every bit ImportData.js. Write thescript to go information from database.

1. var app = angular.module('MyApp', []);
2. app.controller('MyController', ['$scope','$http',function ($scope, $http) {
3.     $scope.SelectedFileForUpload =aught;
4.     $telescopic.UploadFile =function (files) {
5.         $scope.$apply(function () { 
6.             $scope.Message ="";
7.             $scope.SelectedFileForUpload = files[0];
8.         })
9.     }
10.     
11.     $telescopic.ParseExcelDataAndSave =role () {
12. var file = $scope.SelectedFileForUpload;
13. if (file) {
14. var reader =new FileReader();
15.             reader.onload =function (east) {
16. var data = east.target.result;
17.                 
18. var workbook = XLSX.read(data, { blazon:'binary' });
19. var sheetName = workbook.SheetNames[0];
20. var excelData = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);
21. if (excelData.length > 0) {
22.                     
23.                     $telescopic.SaveData(excelData);
24.                 }
25. else {
26.                     $telescopic.Message ="No data found";
27.                 }
28.             }
29.             reader.onerror =function (ex) {
30.                 console.log(ex);
31.             }
33.             reader.readAsBinaryString(file);
34.         }
35.     }
36.     
37.     $scope.SaveData =part (excelData) {
38.         $http({
39.             method:"POST",
40.             url:"/Home/ImportData",
41.             data: JSON.stringify(excelData),
42.             headers: {
43. 'Content-Blazon':'application/json'
44.             }
45.         }).then(role (data) {
46. if (data.status) {
47.                 $scope.Message = excelData.length +" record inserted";
48.             }
49. else {
50.                 $telescopic.Bulletin ="Failed";
51.             }
52.         },function (error) {
53.             $scope.Bulletin ="Error";
54.         })
55.     }
56. }])

Step 9Design the view with HTML, cshtml and bootstrap 4 classes.

1. <body ng-app="MyApp">
2.     <divclass="container py-four" ng-controller="MyController">
3.         <h5class="text-center text-uppercase">How to Import Excel Data into Sql Server Using Angular Jsin Mvc5</h5>
4.         <divgrade="card">
5.             <divclass="card-header bg-primary text-white">
6.                 <h5>Students Event Listing</h5>
7.             </div>
8.             <divclass="carte-body">
9.                 <button style="margin-bottom:10px;" type="push"form="btn btn-principal rounded-0" data-toggle="modal" data-target="#myModal">
10.                   <iclass="fa fa-file-excel-o"></i> Upload Excel File
11.                 </button>
12.                 <divclass="modal" id="myModal">
13.                     <divclass="modal-dialog">
14.                         <divclass="modal-content">
15.                             <divclass="modal-header">
16.                                 <h4class="modal-title">Upload Students Result</h4>
17.                                 <button type="button"class="close" data-dismiss="modal">×</button>
18.                             </div>
19.                             <divclass="modal-body">
20.                                 <divclass="col-md-12">
21.                                     <divclass="input-grouping">
22.                                         <divclass="custom-file">
23.                                             <input type="file" name="file"course="custom-file-input" onchange="angular.element(this).scope().UploadFile(this.files)" />
24.                                             <labelform="custom-file-characterization"for="inputGroupFile04">Choose file</label>
25.                                         </div>
26.                                         <divclass="input-group-append">
27.                                             <push buttoncourse="btn btn-outline-secondary" type="push" ng-disabled="!SelectedFileForUpload" ng-click="ParseExcelDataAndSave()"><iclass="fa fa-upload"></i> Upload</button>
28.                                         </div>
29.                                     </div>
30.                                     <spanclass="text-success">
31.                                         {{Message}}
32.                                     </span>
33.                                 </div>
34.                             </div>
35.                             <divclass="modal-footer">
36.                                 <push button type="button"class="btn btn-danger rounded-0" data-dismiss="modal">Close</button>
37.                             </div>
38.                         </div>
39.                     </div>
40.                 </div>
41.                 <table id="dataTable"grade="tabular array table-bordered table-striped">
42.                     <thead>
43.                         <tr>
44.                             <th>Roll No.</thursday>
45.                             <th>Name</th>
46.                             <thursday>Hindi</thursday>
47.                             <th>English</th>
48.                             <th>Physics</th>
49.                             <th>Chemistry</th>
50.                             <th>Biology</th>
51.                             <th>Mathematics</thursday>
52.                         </tr>
53.                     </thead>
54.                 </tabular array>
55.             </div>
56.         </div>
57.     </div>
58. </body>

Complete View code

1. @{
2.     Layout =null;
3. }
5. <!DOCTYPE html>
7. <html>
8. <head>
9.     <meta name="viewport" content="width=device-width" />
10.     <championship>ImportData</title>
11.     <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/four.vii.0/css/font-crawly.min.css">
12.     <link href="~/Content/bootstrap.min.css" rel="stylesheet" />
13.     <script src="~/scripts/jquery-three.3.1.min.js"></script>
14.     <script src="~/scripts/bootstrap.min.js"></script>
15.     <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.eight.1/xlsx.full.min.js"></script>
16.     <script src="http://oss.sheetjs.com/js-xlsx/jszip.js"></script>
17.     <script src="https://cdnjs.cloudflare.com/ajax/libs/athwart.js/1.six.one/angular.min.js"></script>
18.     <script src="~/scripts/ImportData.js"></script>
19.     <link href="~/Content/dataTables.bootstrap4.min.css" rel="stylesheet" />
20.     <script src="~/scripts/jquery.dataTables.min.js"></script>
21.     <script src="~/scripts/dataTables.bootstrap4.min.js"></script>
22.     <script blazon="text/javascript">
23.         $(document).ready(office () {
24.             $('#dataTable').DataTable({
25. "ajax": {
26. "url":"/Habitation/GetData",
27. "type":"Go",
28. "datatype":"json"
29.                 },
30. "columns": [
31.                     {"information":"RollNumber" },
32.                     {"data":"Student_Name" },
33.                     {"data":"Hindi" },
34.                     {"information":"English language" },
35.                     {"data":"Physics" },
36.                     {"data":"Chemistry" },
37.                     {"data":"Biological science" },
38.                     {"information":"Mathematics" }
40.                 ]
41.             });
42.         });
43.     </script>
44. </head>
45. <body ng-app="MyApp">
46.     <divclass="container py-4" ng-controller="MyController">
47.         <h5grade="text-centre text-uppercase">How to Import Excel Information into Sql Server Using Angular Jsin Mvc5</h5>
48.         <divclass="card">
49.             <divclass="card-header bg-master text-white">
50.                 <h5>Students Result List</h5>
51.             </div>
52.             <divclass="bill of fare-body">
53.                 <button manner="margin-bottom:10px;" blazon="button"class="btn btn-primary rounded-0" data-toggle="modal" information-target="#myModal">
54.                   <iclass="fa fa-file-excel-o"></i> Upload Excel File
55.                 </button>
56.                 <divgrade="modal" id="myModal">
57.                     <divgrade="modal-dialog">
58.                         <divclass="modal-content">
59.                             <divgrade="modal-header">
60.                                 <h4course="modal-title">Upload Students Result</h4>
61.                                 <button type="button"class="close" data-dismiss="modal">×</push button>
62.                             </div>
63.                             <divgrade="modal-body">
64.                                 <divform="col-md-12">
65.                                     <divclass="input-group">
66.                                         <divclass="custom-file">
67.                                             <input type="file" name="file"class="custom-file-input" onchange="angular.element(this).scope().UploadFile(this.files)" />
68.                                             <characterizationcourse="custom-file-label"for="inputGroupFile04">Cull file</label>
69.                                         </div>
70.                                         <divclass="input-grouping-append">
71.                                             <buttongrade="btn btn-outline-secondary" type="push" ng-disabled="!SelectedFileForUpload" ng-click="ParseExcelDataAndSave()"><iclass="fa fa-upload"></i> Upload</button>
72.                                         </div>
73.                                     </div>
74.                                     <spanclass="text-success">
75.                                         {{Message}}
76.                                     </bridge>
77.                                 </div>
78.                             </div>
79.                             <divclass="modal-footer">
80.                                 <push blazon="button"class="btn btn-danger rounded-0" data-dismiss="modal">Close</button>
81.                             </div>
82.                         </div>
83.                     </div>
84.                 </div>
85.                 <table id="dataTable"course="table tabular array-bordered table-striped">
86.                     <thead>
87.                         <tr>
88.                             <th>Coil No.</thursday>
89.                             <th>Proper name</th>
90.                             <th>Hindi</th>
91.                             <th>English</th>
92.                             <th>Physics</th>
93.                             <th>Chemistry</th>
94.                             <th>Biology</th>
95.                             <thursday>Mathematics</thursday>
96.                         </tr>
97.                     </thead>
98.                 </tabular array>
99.             </div>
100.         </div>
101.     </div>
102. </body>
103. </html>

Step 10

Run the project by pressing Ctrl+F5.

Screenshot 1

Screenshot two

Screenshot iii

Screenshot four

Conclusion

In this article, I have explained how to upload Excel data into SQL Server database table using Angular with MVC5 step by stride. I hope it will exist useful for you.

bainhied1960.blogspot.com

Source: https://www.c-sharpcorner.com/article/how-to-import-excel-data-into-sql-server-using-angular-in-mvc-5/

Share this post