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.

public ActionResult ExcelUpload()
        {
            return View();
        }

Html for the present action use the below code.

<div class="form-inline">
        <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)" />
    </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.

$scope.SelectedFileForUpload = null; //initially make it null

$scope.UploadFiles = function (files) {
        $scope.$apply(function () {
            $scope.Message = '';
            $scope.SelectedFileForUpload = files[0];
        })
    }

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

<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...


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.

.service("Excelservice", function ($http) {
    this.SendExcelData = function (data) {
        var request = $http({
            method: "post",
            withCredentials: true,
            url: '/File/ReadExcel',
            data: data,
            headers: {
                'Content-Type': undefined
            },
            transformRequest: angular.identity
        });
        return request;
    }
}

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.

[HttpPost]
      public ActionResult ReadExcel()
      {
          List<Student> lstStudent = new List<Student>();
          if (ModelState.IsValid)
          {

              string filePath = string.Empty;
              if (Request != null)
              {
                  HttpPostedFileBase file = Request.Files["file"];
                  if ((file != null) && (file.ContentLength > 0) && !string.IsNullOrEmpty(file.FileName))
                  {

                      string fileName = file.FileName;
                      string fileContentType = file.ContentType;
                      string path = Server.MapPath("~/Uploads/");
                      if (!Directory.Exists(path))
                      {
                          Directory.CreateDirectory(path);
                      }
                      filePath = path + Path.GetFileName(file.FileName);
                      string extension = Path.GetExtension(file.FileName);
                      file.SaveAs(filePath);
                      Stream stream = file.InputStream;
                      // We return the interface, so that
                      IExcelDataReader reader = null;
                      if (file.FileName.EndsWith(".xls"))
                      {
                          reader = ExcelReaderFactory.CreateBinaryReader(stream);
                      }
                      else if (file.FileName.EndsWith(".xlsx"))
                      {
                          reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                      }
                      else
                      {
                          ModelState.AddModelError("File", "This file format is not supported");
                          return RedirectToAction("ExcelUpload");
                      }
                      reader.IsFirstRowAsColumnNames = true;
                      DataSet result = reader.AsDataSet();
                      reader.Close();
                      //delete the file from physical path after reading
                      string filedetails = path + fileName;
                      FileInfo fileinfo = new FileInfo(filedetails);
                      if (fileinfo.Exists)
                      {
                          fileinfo.Delete();
                      }
                      DataTable dt = result.Tables[0];
                      lstStudent = ConvertDataTable<Student>(dt);
                      TempData["Excelstudent"] = lstStudent;
                  }
              }

          }
          // var files = Request.Files;

          return new JsonResult { Data = lstStudent, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
      }

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.

private static List<T> ConvertDataTable<T>(DataTable dt)
     {
         List<T> data = new List<T>();
         foreach (DataRow row in dt.Rows)
         {
             T item = GetItem<T>(row);
             data.Add(item);
         }
         return data;
     }
     private static T GetItem<T>(DataRow dr)
     {
         Type temp = typeof(T);
         T obj = Activator.CreateInstance<T>();

         foreach (DataColumn column in dr.Table.Columns)
         {
             foreach (PropertyInfo pro in temp.GetProperties())
             {
                 if (pro.Name == column.ColumnName)
                     pro.SetValue(obj, dr[column.ColumnName], null);
                 else
                     continue;
             }
         }
         return obj;
     }

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

$scope.BindData = null;
$scope.showLoader = false;
$scope.IsVisible = false;
pe.ParseExcel = function () {
    var formData = new FormData();
    var file = $scope.SelectedFileForUpload;
    formData.append('file', file);
    $scope.showLoader = true;   //show spinner
    var response = Excelservice.SendExcelData(formData);   //calling service
    response.then(function (d) {
        var res = d.data;
        $scope.BindData = res;
        $scope.IsVisible = true; //showing the table after databinding
        $scope.showLoader = false; //after success hide spinner
    }, function (err) {
        console.log(err.data);
        console.log("error in parse excel");
    });
}

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.

<input type="button" value="Preview" class="btn btn-primary" ng-disabled="!SelectedFileForUpload" ng-click="ParseExcel() " />
<span style="color:red">
        {{Message}}
        </span>
    <table class="tableData" ng-show="IsVisible" style="margin-left: 0px;margin-top: 40px;">
        <tr>
            <th>FirstName</th>
            <th>LastName</th>
            <th>EmailID</th>
            <th>City</th>
            <th>Country</th>
        </tr>
        <tr ng-repeat="s in  BindData" ng-class-odd="'odd'" ng-class-even="'even'">
            <td>{{s.FirstName}}</td>
            <td>{{s.LastName}}</td>
            <td>{{s.EmailID}}</td>
            <td>{{s.City}}</td>
            <td>{{s.Country}}</td>
        </tr>
    </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.

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

<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.

$scope.InsertData = function () {
        var response = Excelservice.InsertToDB();
        response.then(function (d) {
            var res = d.data;

            if (res.toString().length > 0) {
                $scope.Message = res + "  Records Inserted";
                $scope.IsVisible = false;   //used to disable the insert button and table after submitting data
                angular.forEach(
                angular.element("input[type='file']"),
                function (inputElem) {
                    angular.element(inputElem).val(null); //used to clear the file upload after submitting data
                });
                $scope.SelectedFileForUpload = null;   //used to disable the preview button after inserting data
            }

        }, function (err) {
            console.log(err.data);
            console.log("error in insertdata");
        });
    }

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

this.InsertToDB = function () {
        var request = $http({
            method: "get",
            url: '/File/InsertExcelData',
            data: {},
            datatype: 'json'
        });
        return request;
    }

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.

public ActionResult InsertExcelData()
      {
          int length = 0;
          try
          {
              if (TempData["Excelstudent"] != null)
              {
                  List<Student> lstStudent = (List<Student>)TempData["Excelstudent"];
                  using (DemoEntities db = new DemoEntities())
                  {
                      foreach (var s in lstStudent)
                      {
                          db.Students.Add(s);
                      }
                      db.SaveChanges();
                      length = lstStudent.Count();
                  }
              }
          }
          catch (Exception ex)
          {
              ex.ToString();
          }
          return new JsonResult { Data = length, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
      }

The output will look like below

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.