ASP.NET MVC

How To Import Excel In Database Using ASP.NET MVC Entity Framework

Here you will the see import excel in database using ASP.NET MVC Entity Framework.  For that, you can do in many ways but here I am doing using entity framework.

Step 1: Create a Database table

Import-Excel-In-ASP.NET-MVC-DB-Image-

 

Step 2: Add entity data model using entity framework.

Excel Data Model AvtarTime

Step 3: Establish your database connection. import excel in  database using ASP.NET MVC

AvtarConnection avtartime

Step 4: Select your table for which you want to do your operation. import excel in  database using ASP.NET MVC

Excel Entity Avtartime

For reading data from excel here, I am using Linq. For that add a NuGet package LinqToExcel. For that right click on your project -> Click on Manage NuGet Packages, Below window will be opened. Just search LinkToExcel and install it.

 

ExcelLinqToExcel AvtarTime

 

View 

@{
    ViewBag.Title = "Home Page";
}

<h3>Add Customer Using Excel Sheet</h3>
<hr />
@using (Html.BeginForm("UploadCustomerExcel", "Home", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
    <div class="form-horizontal">
        <div class="form-group">

            <div class="control-label col-md-2">Please Browse Excel File:</div>
            <div class="col-md-10">
                <input type="file" id="FileUpload" name="FileUpload" class="" />
            </div>
        </div>
        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Upload" id="btnSubmit" class="btn btn-primary" />
            </div>
        </div>
    </div>
}

 

Namespaces: You have to import the following namespaces

using LinqToExcel;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using UploadExcelInMvc.Models;

 

Controller: Put this piece of code on your controller

namespace UploadExcelInMvc.Controllers
{
    public class HomeController : Controller
    {
        AvtarTimeEntities db = new AvtarTimeEntities();

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

        [HttpPost]
        public JsonResult UploadCustomerExcel(Customer customer, HttpPostedFileBase FileUpload)
        {
            List<string> data = new List<string>();
            if (FileUpload != null)
            {
                if (FileUpload.ContentType == "application/vnd.ms-excel" || FileUpload.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
                {
                    string filename = FileUpload.FileName;
                    string targetpath = Server.MapPath("~/ExcelFolder/");
                    FileUpload.SaveAs(targetpath + filename);
                    string pathToExcelFile = targetpath + filename;
                    var connectionString = "";
                    if (filename.EndsWith(".xls"))
                    {
                        connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", pathToExcelFile);
                    }
                    else if (filename.EndsWith(".xlsx"))
                    {
                        connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", pathToExcelFile);
                    }

                    var adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString);
                    var ds = new DataSet();

                    adapter.Fill(ds, "CustomerExcelTable");
                    DataTable dtable = ds.Tables["CustomerExcelTable"];
                    string sheetName = "Sheet1";

                    var excelFile = new ExcelQueryFactory(pathToExcelFile);
                    var artistAlbums = from a in excelFile.Worksheet<Customer>(sheetName) select a;

                    foreach (var a in artistAlbums)
                    {
                        try
                        {
                            if (a.CustomerName != "" && a.Age != 0 && a.ContactNo != "")
                            {
                                Customer cutomerData = new Customer();
                                cutomerData.CustomerName = a.CustomerName;
                                cutomerData.Age = a.Age;
                                cutomerData.ContactNo = a.ContactNo;
                                db.Customers.Add(cutomerData);
                                db.SaveChanges();
                            }
                        }
                        catch {
                        }
                    }
                    //After insert into database deleting the excel file from the folder  
                    if ((System.IO.File.Exists(pathToExcelFile)))
                    {
                        System.IO.File.Delete(pathToExcelFile);
                    }
                }
            }
            return Json("success", JsonRequestBehavior.AllowGet);
        }
    }
}

Excel Data:

Also Read  CRUD operations in ASP.Net Web Forms using C#

ExcelData

 

 

  

 

 

 

 

 

Final Result: You will get the final result in your database.

ExcelResult

 

 

 

 

More detail check out our website

Sign Up for getting more new articles directly in your mail Inbox. 

About the author

Avtar

I am a blogger and I always believes in sharing a knowledge and happiness because happiness is the last journey of life.

Leave a Comment

Share
Tweet
Pin
+1
Share
0 Shares