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.

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. EPPlus
2. Open XML
3. Spreadsheet Gear
4. Closed Xml
5. Leniel Converter
6. TMS Flex Cell Converter
7. Excel DNA
8. Spire Office

9. DoddleReport

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

Step 2.  Create a new MVC Project ExcelImport

Step 3. Add reference EPPlus reference library by executing following command

            Install-Package EPPlus

          

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

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

• Click on Next , the following window will appear

• Click on New Connection, the following window wiil appear

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

• Click OK in test connection message and click OK in Connection Properties Window. The following window will appear

• This will save your connection string in web.config as ExcelImportDBEntities. Click on Next, the following window will appear

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

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

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.

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

References

• Reference1
• Reference2
• Reference3