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

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 the Software Data Model.
3. Save validated data to the database.
    

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

 

The 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 the 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 step 1 is given below.

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 the command given below.

 

Install-Package EPPlus

 

 

Step 4 

 

Add new ADO.NET Entity Model, using the steps given below.

• Right-click the Models folder under Solution Explorer. Click Add new Item and the Window was given below will open.
   

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

• Click Next and the Window given below will appear.
   
   

• Click New Connection and the Window given below will appear.
   

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

• Click OK in the test connection message and click OK in Connection Properties Window. The Window given below will appear.
   
   
• This will save your connection string in config as ExcelImportDBEntities. Click Next and the Window given below will appear.
   

• Click Finish. Now, your database is connected with your Application. Don’t forget to build a 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. 

@ {
    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 and write the Action method given below. 

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 it. The Web page given below will open.

 

 

Step 8

 

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

 

 

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

 

 

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