Post
Ask Question

How To Import Excel Data Into SQL Server Using Angular In MVC 5

Introduction

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

Step 1

Open SQL Server 2014 and create a database table to insert and retrieve the data.

  1. CREATE TABLE [dbo].[StudentsList](  
  2.     [RollNumber] [int] IDENTITY(1000,1) NOT NULL,  
  3.     [Student_Name] [nvarchar](50) NULL,  
  4.     [Hindi] [int] NULL,  
  5.     [English] [int] NULL,  
  6.     [Physics] [int] NULL,  
  7.     [Chemistry] [int] NULL,  
  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 [PRIMARY]  
  15.   
  16. GO  

Screenshot for the database table

MVC

Step 2

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

Screenshot for creating new project 1

 

MVC

After clicking on New Project, one window will appear. Select Web from the left panel, choose ASP.NET Web Application, give it a meaningful name and click on OK.

Screenshot for creating new project 2

 

MVC

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

Screenshot for creating new project 3

MVC

After clicking on OK, the project will be created with the name of ImportExcelDataAngularJS_Demo.

Step 3

Add Entity Framework, right click on Models folder, select Add and then select New Item followed by a click on it. 

Screenshot for adding Entity Framework 1

 

MVC

After clicking on New Item, you will get a window. From there, select Data from the left panel and choose ADO.NET Entity Data Model, give it a name as DBModels (this name is not mandatory; you can give any name), then click on Add.

Screenshot for adding Entity Framework 2

MVC

After you click on Add, a window wizard will open. Choose EF Designer from the database and click Next.

Screenshot for adding Entity Framework 3

MVC

After clicking on Next, a new window will appear. Choose New Connection.

Screenshot for adding Entity Framework 4

MVC

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

Screenshot for adding Entity Framework 5

MVC

Connection will get added. If you wish to save connection as you want, you can change the name of your connection below. It will save connection in web config. Just click on Next.

Screenshot for adding Entity Framework 6

MVC

After clicking on NEXT another window will appear. Choose database table name as shown in below screenshot then click on Finish. Entity framework will be added and respective class gets generated under Models folder.

Screenshot for adding Entity Framework 7

MVC

Screenshot for adding Entity Framework 8

MVC

Following class will be added.

  1. namespace ImportExcelDataAngularJS_Demo.Models  
  2. {  
  3.     using System;  
  4.     using System.Collections.Generic;  
  5.       
  6.     public class StudentsList  
  7.     {  
  8.         public int RollNumber { get; set; }  
  9.         public string Student_Name { get; set; }  
  10.         public Nullable<int> Hindi { get; set; }  
  11.         public Nullable<int> English { get; set; }  
  12.         public Nullable<int> Physics { get; set; }  
  13.         public Nullable<int> Chemistry { get; set; }  
  14.         public Nullable<int> Biology { get; set; }  
  15.         public Nullable<int> Mathematics { get; set; }  
  16.     }  
  17. }  

Step 4

Right click on Controllers folder select Add then choose Controller. As shown in below screenshot.

MVC

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

MVC

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

 

MVC

Add the following namespace in controller

  1. using ImportExcelDataAngularJS_Demo.Models;  

Complete controller code.

  1. using ImportExcelDataAngularJS_Demo.Models;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web.Mvc;  
  5.   
  6. namespace ImportExcelDataAngularJS_Demo.Controllers  
  7. {  
  8.     public class HomeController : Controller  
  9.     {  
  10.         // GET: Home  
  11.         public ActionResult Index()  
  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 { data = studentList }, JsonRequestBehavior.AllowGet);  
  21.             }  
  22.         }  
  23.         public ActionResult ImportData()  
  24.         {  
  25.             return View();  
  26.         }  
  27.   
  28.         [HttpPost]  
  29.         public ActionResult ImportData(List<StudentsList> studentList)  
  30.         {  
  31.             bool status = false;  
  32.             if (ModelState.IsValid)  
  33.             {  
  34.                 using (DBModel db = new DBModel())  
  35.                 {  
  36.                     foreach (var i in studentList)  
  37.                     {  
  38.                         db.StudentsLists.Add(i);  
  39.                     }  
  40.                     db.SaveChanges();  
  41.                     status = true;  
  42.                 }  
  43.             }  
  44.             return new JsonResult { Data = new { status = status } };  
  45.         }  
  46.     }  
  47. }  

Step 5

Right click on ImportData action method in controller. Add view. A window will appear with default ImportData name unchecked (use a Layout page) click on Add, as show in the below screenshot. View will be added in views folder under Home folder with name Column.

Screenshot for adding view

 

MVC

Step 6

Click on Tools select NuGet Package Manager then choose Manage NuGet Packages for Solution click on it.

Screenshot for NuGet Package

 

MVC

After that a window will appear choose Browse type bootstrap and install package in project. 

 

MVC

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

 

MVC

Keep required bootstrap and jQuery file and delete remaining file if not using. Or you can download and add it in the project.

MVC

Step 7

Add required script and style in head section of view.

  1. <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">  
  2. <link href="~/Content/bootstrap.min.css" rel="stylesheet" />  
  3. <script src="~/scripts/jquery-3.3.1.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.full.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.6.1/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.                 { "data": "RollNumber" },  
  22.                 { "data": "Student_Name" },  
  23.                 { "data": "Hindi" },  
  24.                 { "data": "English" },  
  25.                 { "data": "Physics" },  
  26.                 { "data": "Chemistry" },  
  27.                 { "data": "Biology" },  
  28.                 { "data": "Mathematics" }  
  29.   
  30.             ]  
  31.         });  
  32.     });  
  33. </script>  

Step 8

Right click on scripts folder, select Add, and choose JavaScript File. Give name as ImportData.js. Write thescript to get data from database.

  1. var app = angular.module('MyApp', []);  
  2. app.controller('MyController', ['$scope', '$http', function ($scope, $http) {  
  3.     $scope.SelectedFileForUpload = null;  
  4.     $scope.UploadFile = function (files) {  
  5.         $scope.$apply(function () { //I have used $scope.$apply because I will call this function from File input type control which is not supported 2 way binding  
  6.             $scope.Message = "";  
  7.             $scope.SelectedFileForUpload = files[0];  
  8.         })  
  9.     }  
  10.     //Parse Excel Data   
  11.     $scope.ParseExcelDataAndSave = function () {  
  12.         var file = $scope.SelectedFileForUpload;  
  13.         if (file) {  
  14.             var reader = new FileReader();  
  15.             reader.onload = function (e) {  
  16.                 var data = e.target.result;  
  17.                 //XLSX from js-xlsx library , which I will add in page view page  
  18.                 var workbook = XLSX.read(data, { type: '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.                     //Save data   
  23.                     $scope.SaveData(excelData);  
  24.                 }  
  25.                 else {  
  26.                     $scope.Message = "No data found";  
  27.                 }  
  28.             }  
  29.             reader.onerror = function (ex) {  
  30.                 console.log(ex);  
  31.             }  
  32.   
  33.             reader.readAsBinaryString(file);  
  34.         }  
  35.     }  
  36.     // Save excel data to our database  
  37.     $scope.SaveData = function (excelData) {  
  38.         $http({  
  39.             method: "POST",  
  40.             url: "/Home/ImportData",  
  41.             data: JSON.stringify(excelData),  
  42.             headers: {  
  43.                 'Content-Type': 'application/json'  
  44.             }  
  45.         }).then(function (data) {  
  46.             if (data.status) {  
  47.                 $scope.Message = excelData.length + " record inserted";  
  48.             }  
  49.             else {  
  50.                 $scope.Message = "Failed";  
  51.             }  
  52.         }, function (error) {  
  53.             $scope.Message = "Error";  
  54.         })  
  55.     }  
  56. }])  

Step 9

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

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

Complete View code

  1. @{  
  2.     Layout = null;  
  3. }  
  4.   
  5. <!DOCTYPE html>  
  6.   
  7. <html>  
  8. <head>  
  9.     <meta name="viewport" content="width=device-width" />  
  10.     <title>ImportData</title>  
  11.     <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">  
  12.     <link href="~/Content/bootstrap.min.css" rel="stylesheet" />  
  13.     <script src="~/scripts/jquery-3.3.1.min.js"></script>  
  14.     <script src="~/scripts/bootstrap.min.js"></script>  
  15.     <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.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/angular.js/1.6.1/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 type="text/javascript">  
  23.         $(document).ready(function () {  
  24.             $('#dataTable').DataTable({  
  25.                 "ajax": {  
  26.                     "url": "/Home/GetData",  
  27.                     "type": "GET",  
  28.                     "datatype": "json"  
  29.                 },  
  30.                 "columns": [  
  31.                     { "data": "RollNumber" },  
  32.                     { "data": "Student_Name" },  
  33.                     { "data": "Hindi" },  
  34.                     { "data": "English" },  
  35.                     { "data": "Physics" },  
  36.                     { "data": "Chemistry" },  
  37.                     { "data": "Biology" },  
  38.                     { "data": "Mathematics" }  
  39.   
  40.                 ]  
  41.             });  
  42.         });  
  43.     </script>  
  44. </head>  
  45. <body ng-app="MyApp">  
  46.     <div class="container py-4" ng-controller="MyController">  
  47.         <h5 class="text-center text-uppercase">How to Import Excel Data into Sql Server Using Angular Js in Mvc5</h5>  
  48.         <div class="card">  
  49.             <div class="card-header bg-primary text-white">  
  50.                 <h5>Students Result List</h5>  
  51.             </div>  
  52.             <div class="card-body">  
  53.                 <button style="margin-bottom:10px;" type="button" class="btn btn-primary rounded-0" data-toggle="modal" data-target="#myModal">  
  54.                   <i class="fa fa-file-excel-o"></i> Upload Excel File  
  55.                 </button>  
  56.                 <div class="modal" id="myModal">  
  57.                     <div class="modal-dialog">  
  58.                         <div class="modal-content">  
  59.                             <div class="modal-header">  
  60.                                 <h4 class="modal-title">Upload Students Result</h4>  
  61.                                 <button type="button" class="close" data-dismiss="modal">×</button>  
  62.                             </div>  
  63.                             <div class="modal-body">  
  64.                                 <div class="col-md-12">  
  65.                                     <div class="input-group">  
  66.                                         <div class="custom-file">  
  67.                                             <input type="file" name="file" class="custom-file-input" onchange="angular.element(this).scope().UploadFile(this.files)" />  
  68.                                             <label class="custom-file-label" for="inputGroupFile04">Choose file</label>  
  69.                                         </div>  
  70.                                         <div class="input-group-append">  
  71.                                             <button class="btn btn-outline-secondary" type="button" ng-disabled="!SelectedFileForUpload" ng-click="ParseExcelDataAndSave()"><i class="fa fa-upload"></i> Upload</button>  
  72.                                         </div>  
  73.                                     </div>  
  74.                                     <span class="text-success">  
  75.                                         {{Message}}  
  76.                                     </span>  
  77.                                 </div>  
  78.                             </div>  
  79.                             <div class="modal-footer">  
  80.                                 <button type="button" class="btn btn-danger rounded-0" data-dismiss="modal">Close</button>  
  81.                             </div>  
  82.                         </div>  
  83.                     </div>  
  84.                 </div>  
  85.                 <table id="dataTable" class="table table-bordered table-striped">  
  86.                     <thead>  
  87.                         <tr>  
  88.                             <th>Roll No.</th>  
  89.                             <th>Name</th>  
  90.                             <th>Hindi</th>  
  91.                             <th>English</th>  
  92.                             <th>Physics</th>  
  93.                             <th>Chemistry</th>  
  94.                             <th>Biology</th>  
  95.                             <th>Mathematics</th>  
  96.                         </tr>  
  97.                     </thead>  
  98.                 </table>   
  99.             </div>  
  100.         </div>  
  101.     </div>  
  102. </body>  
  103. </html>  

Step 10

Run the project by pressing Ctrl+F5.

Screenshot 1

MVC

Screenshot 2

MVC

Screenshot 3

MVC

Screenshot 4

 

MVC

Conclusion

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


Similar Articles