Import Excel Data To Database Using ASP.NET MVC Entity Framework Functionality

Let's start uploading excel data to Microsoft SQL Server Database.

 

Prerequisite :

• Microsoft SQL Server 2014
• Visual studio 2013

Figure 1: Create table as in the following,

 

 

Figure 2 : Add new item ADO.NET Entity Data Model and click add button,

 

 

Figure 3: Choose Entity framework and click next button,

 

 

Figure 4 : Include database object from our SQL database please and select our target table Users and click finish,

 

 

Figure 5: Install NuGet package LinqToExcel in our project,

 

 

 

Download Excel file format and enter your own data to this format for uploading,

 

In this view using FormMethod.Post "UploadExcel"  function name Controller name "User",

@using (Html.BeginForm("UploadExcel", "User", FormMethod.Post, new { enctype = "multipart/form-data", onsubmit = "return myFunction()" }))

Download Excel file format href link,

<a href="/User/DownloadExcel/"><img src="~/excel.ico" width="25" height="25" title="Download Excel format" alt="excel" />

View

@{
    ViewBag.Title = "Index";
}

<h4>Add Users via Excel</h4>
<hr />
@using (Html.BeginForm("UploadExcel", "User", FormMethod.Post, new { enctype = "multipart/form-data", onsubmit = "return myFunction()" }))
{
    <div class="form-horizontal">
        <div class="form-group">
            <div class="control-label col-md-2">Download Format:</div>
            <div class="col-md-10">
                <a href="/User/DownloadExcel/"><img src="~/excel.ico" width="25" height="25" title="Download Excel format" alt="excel" /></a>
            </div>
        </div>


        <div class="form-group">

            <div class="control-label col-md-2">Excel:</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>
}

Model

 

Userlist .cs 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace ExcelImport.Models
{
    public class UserList
    {
        public string Name { get; set; }
        public string Address{ get; set; }
        public string ContactNo { get; set; }


    }
}

Download Excel file format and enter your own data to this format for uploading. In the doc folder here's format of sheet,

 

  

public FileResult DownloadExcel()
  {
      string path = "/Doc/Users.xlsx";
      return File(path, "application/vnd.ms-excel", "Users.xlsx");
  }

//deleting excel file from folder
                    if ((System.IO.File.Exists(pathToExcelFile)))
                    {
                        System.IO.File.Delete(pathToExcelFile);
                    }
                    return Json("success", JsonRequestBehavior.AllowGet);

Controller Full Code: The JsonResult UploadExcel function using HttpPost return Json result,

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.Entity.Validation;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Net;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.Mvc;
using ExcelImport.Models;
using LinqToExcel;
using System.Data.SqlClient;

namespace ExcelImport.Controllers
{
    public class UserController : Controller
    {
        private test2Entities db = new test2Entities();
        // GET: User
        public ActionResult Index()
        {
            return View();
        }


        /// <summary>
        /// This function is used to download excel format.
        /// </summary>
        /// <param name="Path"></param>
        /// <returns>file</returns>
        public FileResult DownloadExcel()
        {
            string path = "/Doc/Users.xlsx";
            return File(path, "application/vnd.ms-excel", "Users.xlsx");
        }

        [HttpPost]
        public JsonResult UploadExcel(User users, HttpPostedFileBase FileUpload)
        {

            List<string> data = new List<string>();
            if (FileUpload != null)
            {
                // tdata.ExecuteCommand("truncate table OtherCompanyAssets");
                if (FileUpload.ContentType == "application/vnd.ms-excel" || FileUpload.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
                {


                    string filename = FileUpload.FileName;
                    string targetpath = Server.MapPath("~/Doc/");
                    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, "ExcelTable");

                    DataTable dtable = ds.Tables["ExcelTable"];

                    string sheetName = "Sheet1";

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

                    foreach (var a in artistAlbums)
                    {
                        try
                        {
                            if (a.Name != "" && a.Address != "" && a.ContactNo != "")
                            {
                                User TU = new User();
                                TU.Name = a.Name;
                                TU.Address = a.Address;
                                TU.ContactNo = a.ContactNo;
                                db.Users.Add(TU);

                                db.SaveChanges();



                            }
                            else
                            {
                                data.Add("<ul>");
                                if (a.Name == "" || a.Name == null) data.Add("<li> name is required</li>");
                                if (a.Address == "" || a.Address == null) data.Add("<li> Address is required</li>");
                                if (a.ContactNo == "" || a.ContactNo == null) data.Add("<li>ContactNo is required</li>");

                                data.Add("</ul>");
                                data.ToArray();
                                return Json(data, JsonRequestBehavior.AllowGet);
                            }
                        }

                        catch (DbEntityValidationException ex)
                        {
                            foreach (var entityValidationErrors in ex.EntityValidationErrors)
                            {

                                foreach (var validationError in entityValidationErrors.ValidationErrors)
                                {

                                    Response.Write("Property: " + validationError.PropertyName + " Error: " + validationError.ErrorMessage);

                                }

                            }
                        }
                    }
                    //deleting excel file from folder
                    if ((System.IO.File.Exists(pathToExcelFile)))
                    {
                        System.IO.File.Delete(pathToExcelFile);
                    }
                    return Json("success", JsonRequestBehavior.AllowGet);
                }
                else
                {
                    //alert message for invalid file format
                    data.Add("<ul>");
                    data.Add("<li>Only Excel file format is allowed</li>");
                    data.Add("</ul>");
                    data.ToArray();
                    return Json(data, JsonRequestBehavior.AllowGet);
                }
            }
            else
            {
                data.Add("<ul>");
                if (FileUpload == null) data.Add("<li>Please choose Excel file</li>");
                data.Add("</ul>");
                data.ToArray();
                return Json(data, JsonRequestBehavior.AllowGet);
            }
        }
    }
}

Output : 

 

 

Summary

We learned how to import excel data to Database using ASP.NET MVC Entity framework. I hope this article is useful for all .NET beginners.

 

Read more articles on ASP.NET:

• MVC - Show Record Using Cascading Dropdown List In MVC Using jQuery And JSON
• Creating An ASP.NET MVC 5 Site Using Bootstrap Custom Templates