ASP.NET

Read Data From Excel With Preview In ASP.NET MVC Using Angular

Introduction

Normally to read Excel we will use OLEDB to read excel but if we use oledb sometimes we may get some error like The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. If we don’t have 2007 Office driver installed in our PC. So here we will use ExcelDataReader from Nuget - package manager to read excel and it supports both xslx and xls formats.

Steps to Create Application 

Step1

Install the required packages from Nuget-pacakge manager or from others...

  • Install-Package ExcelDataReader -Version 3.2.0
  • Install-Package angularjs -Version 1.6.6

Here we will be using Angularjs to Read and insert in the database, so install Angularjs in your code and refer the javascript files in your view.

Step2

Create one Action method in controller in MVC and render the following view.

  1. public ActionResult ExcelUpload()  
  2.         {  
  3.             return View();  
  4.         }  

Html for the present action use the below code.

  1. <div class="form-inline">  
  2.         <input type="file" class="form-control" name="file"  accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel" onchange="angular.element(this).scope().UploadFiles(this.files)" />  
  3.     </div>  

Here we are using input type file to read the uploaded excel file and to restrict users to upload only excel files and here we are capturing the file data on change event in angularjs so write the below code as soon as files changes the scope variable will be updated with the latest file.

  1. $scope.SelectedFileForUpload = null; //initially make it null   
  2.   
  3. $scope.UploadFiles = function (files) {  
  4.         $scope.$apply(function () {  
  5.             $scope.Message = '';  
  6.             $scope.SelectedFileForUpload = files[0];  
  7.         })  
  8.     }  

Add a button to show preview when user clicks on that.

  1. <input type="button" value="Preview" class="btn btn-primary" ng-disabled="!SelectedFileForUpload" ng-click="ParseExcel() " />  

 

Here we are first disabling the button when no file is selected so the output looks like below...

ASP.NET
Create one Angular service to send the uploaded excel file data to server side method here we are using FormData to pass the data to server side, so now our service accepts the formdata on Button click of preview and the data will be sent to the server method so the code looks below.

  1. .service("Excelservice", function ($http) {  
  2.     this.SendExcelData = function (data) {  
  3.         var request = $http({  
  4.             method: "post",  
  5.             withCredentials: true,  
  6.             url: '/File/ReadExcel',  
  7.             data: data,  
  8.             headers: {  
  9.                 'Content-Type': undefined  
  10.             },  
  11.             transformRequest: angular.identity  
  12.         });  
  13.         return request;  
  14.     }  
  15. }  

Create one Action method in MVC to accept the file data and that method reads the data from excel using library and returns List as result so the method will looks like below.

  1. [HttpPost]  
  2.       public ActionResult ReadExcel()  
  3.       {  
  4.           List<Student> lstStudent = new List<Student>();  
  5.           if (ModelState.IsValid)  
  6.           {  
  7.   
  8.               string filePath = string.Empty;  
  9.               if (Request != null)  
  10.               {  
  11.                   HttpPostedFileBase file = Request.Files["file"];  
  12.                   if ((file != null) && (file.ContentLength > 0) && !string.IsNullOrEmpty(file.FileName))  
  13.                   {  
  14.   
  15.                       string fileName = file.FileName;  
  16.                       string fileContentType = file.ContentType;  
  17.                       string path = Server.MapPath("~/Uploads/");  
  18.                       if (!Directory.Exists(path))  
  19.                       {  
  20.                           Directory.CreateDirectory(path);  
  21.                       }  
  22.                       filePath = path + Path.GetFileName(file.FileName);  
  23.                       string extension = Path.GetExtension(file.FileName);  
  24.                       file.SaveAs(filePath);  
  25.                       Stream stream = file.InputStream;  
  26.                       // We return the interface, so that  
  27.                       IExcelDataReader reader = null;  
  28.                       if (file.FileName.EndsWith(".xls"))  
  29.                       {  
  30.                           reader = ExcelReaderFactory.CreateBinaryReader(stream);  
  31.                       }  
  32.                       else if (file.FileName.EndsWith(".xlsx"))  
  33.                       {  
  34.                           reader = ExcelReaderFactory.CreateOpenXmlReader(stream);  
  35.                       }  
  36.                       else  
  37.                       {  
  38.                           ModelState.AddModelError("File", "This file format is not supported");  
  39.                           return RedirectToAction("ExcelUpload");  
  40.                       }  
  41.                       reader.IsFirstRowAsColumnNames = true;  
  42.                       DataSet result = reader.AsDataSet();  
  43.                       reader.Close();  
  44.                       //delete the file from physical path after reading   
  45.                       string filedetails = path + fileName;  
  46.                       FileInfo fileinfo = new FileInfo(filedetails);  
  47.                       if (fileinfo.Exists)  
  48.                       {  
  49.                           fileinfo.Delete();  
  50.                       }  
  51.                       DataTable dt = result.Tables[0];  
  52.                       lstStudent = ConvertDataTable<Student>(dt);  
  53.                       TempData["Excelstudent"] = lstStudent;  
  54.                   }  
  55.               }  
  56.   
  57.           }  
  58.           // var files = Request.Files;  
  59.   
  60.           return new JsonResult { Data = lstStudent, JsonRequestBehavior = JsonRequestBehavior.AllowGet };  
  61.       }  

In the above method we are reading the file from request and checking file extension whether it is .xlsx or .xls, depending on the extension it reads the data and is stored in DataTable to convert the DataTable to List. To convert it write the following method and use the below method in above DataTable to List conversion. And the list is stored in Temp variable and we will be using this temp variable in some method.

  1. private static List<T> ConvertDataTable<T>(DataTable dt)  
  2.      {  
  3.          List<T> data = new List<T>();  
  4.          foreach (DataRow row in dt.Rows)  
  5.          {  
  6.              T item = GetItem<T>(row);  
  7.              data.Add(item);  
  8.          }  
  9.          return data;  
  10.      }  
  11.      private static T GetItem<T>(DataRow dr)  
  12.      {  
  13.          Type temp = typeof(T);  
  14.          T obj = Activator.CreateInstance<T>();  
  15.   
  16.          foreach (DataColumn column in dr.Table.Columns)  
  17.          {  
  18.              foreach (PropertyInfo pro in temp.GetProperties())  
  19.              {  
  20.                  if (pro.Name == column.ColumnName)  
  21.                      pro.SetValue(obj, dr[column.ColumnName], null);  
  22.                  else  
  23.                      continue;  
  24.              }  
  25.          }  
  26.          return obj;  
  27.      }  

Now in angular controller method call our created angular service in button click event as below.

  1. $scope.BindData = null;  
  2. $scope.showLoader = false;  
  3. $scope.IsVisible = false;  
  4. pe.ParseExcel = function () {  
  5.     var formData = new FormData();  
  6.     var file = $scope.SelectedFileForUpload;  
  7.     formData.append('file', file);  
  8.     $scope.showLoader = true;   //show spinner  
  9.     var response = Excelservice.SendExcelData(formData);   //calling service  
  10.     response.then(function (d) {  
  11.         var res = d.data;  
  12.         $scope.BindData = res;  
  13.         $scope.IsVisible = true; //showing the table after databinding  
  14.         $scope.showLoader = false; //after success hide spinner  
  15.     }, function (err) {  
  16.         console.log(err.data);  
  17.         console.log("error in parse excel");  
  18.     });  
  19. }  

In the above method we are binding the result to scope variable named BindData  now use that variable in html to show the preview of the data so on Preview click we are showing the data.

  1. <input type="button" value="Preview" class="btn btn-primary" ng-disabled="!SelectedFileForUpload" ng-click="ParseExcel() " />  
  2. <span style="color:red">  
  3.         {{Message}}  
  4.         </span>  
  5.     <table class="tableData" ng-show="IsVisible" style="margin-left: 0px;margin-top: 40px;">  
  6.         <tr>  
  7.             <th>FirstName</th>  
  8.             <th>LastName</th>  
  9.             <th>EmailID</th>  
  10.             <th>City</th>  
  11.             <th>Country</th>  
  12.         </tr>  
  13.         <tr ng-repeat="s in  BindData" ng-class-odd="'odd'" ng-class-even="'even'">  
  14.             <td>{{s.FirstName}}</td>  
  15.             <td>{{s.LastName}}</td>  
  16.             <td>{{s.EmailID}}</td>  
  17.             <td>{{s.City}}</td>  
  18.             <td>{{s.Country}}</td>  
  19.         </tr>  
  20.     </table>  

The view looks like below and on Preview click we are showing the table and insert button to be visible and on Insert button click we will insert the data in to DB.

ASP.NET

To insert the data into Database write the below code and we are calling InsertData click event to call the server side method create one input tag in html

  1. <input type="button" value="Insert" style="margin-left: 15px;" class="btn btn-success"  ng-show="IsVisible" ng-click="InsertData()"  />  

Create one method in angular controller which makes http service to the server side method and returns the count of inserted records and show it in view after insert button click.

  1. $scope.InsertData = function () {  
  2.         var response = Excelservice.InsertToDB();  
  3.         response.then(function (d) {  
  4.             var res = d.data;  
  5.   
  6.             if (res.toString().length > 0) {  
  7.                 $scope.Message = res + "  Records Inserted";  
  8.                 $scope.IsVisible = false;   //used to disable the insert button and table after submitting data  
  9.                 angular.forEach(  
  10.                 angular.element("input[type='file']"),  
  11.                 function (inputElem) {  
  12.                     angular.element(inputElem).val(null); //used to clear the file upload after submitting data  
  13.                 });  
  14.                 $scope.SelectedFileForUpload = null;   //used to disable the preview button after inserting data  
  15.             }  
  16.   
  17.         }, function (err) {  
  18.             console.log(err.data);  
  19.             console.log("error in insertdata");  
  20.         });  
  21.     }  

Angular Service to call the Server side method looks like below.

  1. this.InsertToDB = function () {  
  2.         var request = $http({  
  3.             method: "get",  
  4.             url: '/File/InsertExcelData',  
  5.             data: {},  
  6.             datatype: 'json'  
  7.         });  
  8.         return request;  
  9.     }  

Server Method looks like below and copy the code and paste it in your server controller and in previous server method while reading we are storing the data in Temp variable and we are using same temp variable to get the result and typecast it to required data type.

  1. public ActionResult InsertExcelData()  
  2.       {  
  3.           int length = 0;  
  4.           try  
  5.           {  
  6.               if (TempData["Excelstudent"] != null)  
  7.               {  
  8.                   List<Student> lstStudent = (List<Student>)TempData["Excelstudent"];  
  9.                   using (DemoEntities db = new DemoEntities())  
  10.                   {  
  11.                       foreach (var s in lstStudent)  
  12.                       {  
  13.                           db.Students.Add(s);  
  14.                       }  
  15.                       db.SaveChanges();  
  16.                       length = lstStudent.Count();  
  17.                   }  
  18.               }  
  19.           }  
  20.           catch (Exception ex)  
  21.           {  
  22.               ex.ToString();  
  23.           }  
  24.           return new JsonResult { Data = length, JsonRequestBehavior = JsonRequestBehavior.AllowGet };  
  25.       }  

The output will look like below

ASP.NET
ASP.NET
Note

Some times you may face problem due to ambiguous reference using ExcelDataReader in that you case you can use Excel library which i have uploaded in source code.

Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs