Search This Blog

Saturday, October 22, 2016

Import data from excel to Sql server database using MVC code first approach

What is Import Feature:


Import feature lets users to save the data from excel sheets into databases. This is useful when  we have a requirement of entering bulk data into a database and there may be chances of less internet connectivity, which results into data loss. To prevent this data loss we prefer to upload data using import from excel feature.

The other requirement of this feature is that most of the Data Entry Operator have expertise in excel sheets, that will result faster data entry to any software.

There are three basic steps for implementing this functionality.
  1. Get the filled excel sheet from user machine.
  2. Validate data according to software data model.
  3. Save validated data to database.


Image1.png


Now let us move to implement these steps using coding.


Very first step is to find a third party library for reading the content of excel sheets. The following is the list to reference libraries


  1. DoddleReport

  2. Aspose.Cells for .NET

We can find more reference libraries here


Here I’m going to explain how can we import data into MVC models using EPPlus reference library. Here I’m using database approach first approach. Steps are given below.


Step 1. Create database ExcelImportDB. Create table Users.
            Sql script for Step1


create database ExcelImportDB
Go
use ExcelImportDB
create table Users2 (SNo int PRIMARY KEY NOT NULL, Name varchar(500), Age int)


ExcelImport1.png


Step 2.  Create a new MVC Project ExcelImport


Step 3. Add reference EPPlus reference library by executing following command
            Install-Package EPPlus
          
ExcelImport2.png


Step 4.  Add new ADO.net entity model using below steps.
  • Right click Models folder under Solution Explorer. Click Add new Item. The following window will open
ExcelImport3.png


  • Name your entity as ExcelImportEntities. Click on add button, the following window will appear
ExcelImport4.png


  • Click on Next , the following window will appear


ExcelImport5.png


  • Click on New Connection, the following window wiil appear
ExcelImport6.png


  • Enter your sql server credentials and Click on test connection, the following window will appear
ExcelImport7.png


  • Click OK in test connection message and click OK in Connection Properties Window. The following window will appear
ExcelImport8.png
  • This will save your connection string in web.config as ExcelImportDBEntities. Click on Next, the following window will appear
ExcelImport9.png


  • Click on Finish. Now your Database connected with your application. Don’t forget to Build Solution in this step.


Step 5. Go to Index.cshtml page and write following code to create a web page upload that will let users to upload excel sheets in server.


@{
   ViewBag.Title = "Index";
}


@using(Html.BeginForm("Upload", "Home", FormMethod.Post, new {enctype = "multipart/form-data"}))
{


   <table>
       <tr>
           <td>File:</td>
           <td>
               <input type="file" name="UploadedFile" />
           </td>
       </tr>
       <tr>
           <td colspan="2">
               <input type="submit" name="Submit" value="Submit" />
           </td>
       </tr>
   </table>
}  


Step 6. Go to HomeContollers, write the following action method.


public ActionResult Upload(FormCollection formCollection)
       {
           var usersList = new List<User>();
           if (Request != null)
           {
               HttpPostedFileBase file = Request.Files["UploadedFile"];
               if ((file != null) && (file.ContentLength > 0) && !string.IsNullOrEmpty(file.FileName))
               {
                   string fileName = file.FileName;
                   string fileContentType = file.ContentType;
                   byte[] fileBytes = new byte[file.ContentLength];
                   var data = file.InputStream.Read(fileBytes, 0, Convert.ToInt32(file.ContentLength));
                   using (var package = new ExcelPackage(file.InputStream))
                   {
                       var currentSheet = package.Workbook.Worksheets;
                       var workSheet = currentSheet.First();
                       var noOfCol = workSheet.Dimension.End.Column;
                       var noOfRow = workSheet.Dimension.End.Row;
                       for (int rowIterator = 2; rowIterator <= noOfRow; rowIterator++)
                       {
                           var user = new User();
                           user.SNo = Convert.ToInt32(workSheet.Cells[rowIterator, 1].Value);
                           user.Name = workSheet.Cells[rowIterator, 2].Value.ToString();
                           user.Age = Convert.ToInt32(workSheet.Cells[rowIterator, 3].Value);
                           usersList.Add(user);
                       }
                   }
               }
           }
           using (ExcelImportDBEntities excelImportDBEntities = new ExcelImportDBEntities())
           {
               foreach (var item in usersList)
               {
                   excelImportDBEntities.Users.Add(item);
               }
               excelImportDBEntities.SaveChanges();
           }
               return View("Index");
       }


Step 7. Build your solution and Run. The following web page will open.


ExcelImport10.png


Step 8.  Choose your excel sheet from which you want to upload data and click submit. My excel sheet screenshot is given below.
ExcelImport11.png


As soon you click on Submit button the data of excel sheet will be imported to your sql server.  Attaching herewith screenshot of sql server database after importing data.


ExcelImport12.png


Now you have successfully imported data from excel sheet into sql server database usin MVC database first approach.
References




No comments:

Post a Comment