Import Data From Excel To SQL Server Database Using MVC Code First Approach

The article given below describes findings of research about importing data from an Excel sheet into a Web project.

What is an Import feature

Import feature lets the users save the data from excel sheets into the 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 in data loss. To prevent this data loss, we prefer to upload the data, using import from an Excel feature.

The other requirement of this feature is that most of the data entry operators have expertise in Excel sheets, which will result in faster data entry to any software.

There are three basic steps to implement this functionality.

  1. Get the filled Excel sheet from the user machine.
  2. Validate the data according to Software Data Model.
  3. Save validated data to the database.

     Import Feature

Now, let us move  on to implement the steps, using coding.

Very first step is to find a third party library to read the content of Excel sheets. The list is given below of the 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 am going to explain how can we import the data into MVC models, using EPPlus reference library. Here, I’m using Database Approach First. The steps are given below.

Step 1

Create database ExcelImportDB. Create table Users.

SQL script for the step 1 is given below.

  1. create database ExcelImportDB  
  2. Go  
  3. use ExcelImportDB  
  4. create table Users2(SNo int PRIMARY KEY NOT NULLName varchar(500), Age int)  

 Import Feature

Step 2

Create a new MVC Project ExcelImport.

Step 3

Add reference EPPlus reference library by executing the command given below.

Install-Package EPPlus

 Import Feature

Step 4 

Add new ADO.NET Entity Model, using the steps given below.
  • Right click Models folder under Solution Explorer. Click Add new Item and the Window given below will open.

     Import Feature
  • Name your entity as ExcelImportEntities. Click Add button and the Window given below will appear.

     Import Feature
  • Click Next and the Window given below will appear.

     Import Feature
  • Click New Connection and the Window given below wiil appear.

     Import Feature
  • Enter your SQL Server credentials and click Test connection. The Window given below will appear.

     Import Feature
  • Click OK in test connection message and click OK in Connection Properties Window. The Window given below will appear.

     Import Feature

  • This will save your connection string in config as ExcelImportDBEntities. Click Next and the Window given below will appear.

     Import Feature
  • Click Finish. Now, your database is connected with your Application. Don’t forget to build solution in this step.

Step 5

Go to Index.cshtml page and write the code given below to create a Web page upload, which will allow the users to upload Excel sheets on the Server. 

  1. @ {  
  2.     ViewBag.Title = "Index";  
  3. }  
  4. @using(Html.BeginForm("Upload""Home", FormMethod.Post, new {  
  5.     enctype = "multipart/form-data"  
  6. })) { < table > < tr > < td > File: < /td> < td > < input type = "file"  
  7.     name = "UploadedFile" / > < /td> < /tr> < tr > < td colspan = "2" > < input type = "submit"  
  8.     name = "Submit"  
  9.     value = "Submit" / > < /td> < /tr> < /table>  
  10. }  

Step 6

Go to HomeContollers and write the Action method given below. 

  1. public ActionResult Upload(FormCollection formCollection) {  
  2.     var usersList = new List < User > ();  
  3.     if (Request != null) {  
  4.         HttpPostedFileBase file = Request.Files["UploadedFile"];  
  5.         if ((file != null) && (file.ContentLength > 0) && !string.IsNullOrEmpty(file.FileName)) {  
  6.             string fileName = file.FileName;  
  7.             string fileContentType = file.ContentType;  
  8.             byte[] fileBytes = new byte[file.ContentLength];  
  9.             var data = file.InputStream.Read(fileBytes, 0, Convert.ToInt32(file.ContentLength));  
  10.             using(var package = new ExcelPackage(file.InputStream)) {  
  11.                 var currentSheet = package.Workbook.Worksheets;  
  12.                 var workSheet = currentSheet.First();  
  13.                 var noOfCol = workSheet.Dimension.End.Column;  
  14.                 var noOfRow = workSheet.Dimension.End.Row;  
  15.                 for (int rowIterator = 2; rowIterator <= noOfRow; rowIterator++) {  
  16.                     var user = new User();  
  17.                     user.SNo = Convert.ToInt32(workSheet.Cells[rowIterator, 1].Value);  
  18.                     user.Name = workSheet.Cells[rowIterator, 2].Value.ToString();  
  19.                     user.Age = Convert.ToInt32(workSheet.Cells[rowIterator, 3].Value);  
  20.                     usersList.Add(user);  
  21.                 }  
  22.             }  
  23.         }  
  24.     }  
  25.     using(ExcelImportDBEntities excelImportDBEntities = new ExcelImportDBEntities()) {  
  26.         foreach(var item in usersList) {  
  27.             excelImportDBEntities.Users.Add(item);  
  28.         }  
  29.         excelImportDBEntities.SaveChanges();  
  30.     }  
  31.     return View("Index");  
  32. }  

Step 7

Build your solution and run it. The Web page given below will open.

 Import Feature

Step 8

Choose your Excel sheet from which you want to upload the data and click Submit. My Excel sheet screenshot is given below.

 Import Feature

As soon as you click Submit button, the data of an Excel sheet will be imported to your SQL Server. The screenshot of SQL Server database is given after importing the data.

 Import Feature

Now, you have successfully imported the data from an Excel sheet into SQL Server database, using MVC Database First Approach.