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",
  1. @using (Html.BeginForm("UploadExcel", "User", FormMethod.Post, new { enctype = "multipart/form-data"onsubmit = "return myFunction()" }))    
Download Excel file format href link,
  1. <a href="/User/DownloadExcel/"><img src="~/excel.ico" width="25" height="25" title="Download Excel format" alt="excel" />  
View
  1. @{  
  2.     ViewBag.Title = "Index";  
  3. }  
  4.   
  5. <h4>Add Users via Excel</h4>  
  6. <hr />  
  7. @using (Html.BeginForm("UploadExcel", "User", FormMethod.Post, new { enctype = "multipart/form-data"onsubmit = "return myFunction()" }))  
  8. {  
  9.     <div class="form-horizontal">  
  10.         <div class="form-group">  
  11.             <div class="control-label col-md-2">Download Format:</div>  
  12.             <div class="col-md-10">  
  13.                 <a href="/User/DownloadExcel/"><img src="~/excel.ico" width="25" height="25" title="Download Excel format" alt="excel" /></a>  
  14.             </div>  
  15.         </div>  
  16.   
  17.   
  18.         <div class="form-group">  
  19.   
  20.             <div class="control-label col-md-2">Excel:</div>  
  21.             <div class="col-md-10">  
  22.                 <input type="file" id="FileUpload" name="FileUpload" class="" />  
  23.             </div>  
  24.         </div>  
  25.         <div class="form-group">  
  26.             <div class="col-md-offset-2 col-md-10">  
  27.                 <input type="submit" value="Upload" id="btnSubmit" class="btn btn-primary" />  
  28.   
  29.             </div>  
  30.         </div>  
  31.     </div>  
  32. }  
Model
 
Userlist .cs 
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5.   
  6. namespace ExcelImport.Models  
  7. {  
  8.     public class UserList  
  9.     {         
  10.         public string Name { getset; }  
  11.         public string Address{ getset; }  
  12.         public string ContactNo { getset; }  
  13.   
  14.   
  15.     }  
  16. }  
Download Excel file format and enter your own data to this format for uploading. In the doc folder here's format of sheet,
 
  
  1. public FileResult DownloadExcel()    
  2.   {    
  3.       string path = "/Doc/Users.xlsx";    
  4.       return File(path, "application/vnd.ms-excel""Users.xlsx");    
  5.   }    
  1. //deleting excel file from folder    
  2.                     if ((System.IO.File.Exists(pathToExcelFile)))    
  3.                     {    
  4.                         System.IO.File.Delete(pathToExcelFile);    
  5.                     }    
  6.                     return Json("success", JsonRequestBehavior.AllowGet);    
Controller Full Code: The JsonResult UploadExcel function using HttpPost return Json result,
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Data;  
  4. using System.Data.Entity;  
  5. using System.Data.Entity.Validation;  
  6. using System.Data.OleDb;  
  7. using System.IO;  
  8. using System.Linq;  
  9. using System.Net;  
  10. using System.Text.RegularExpressions;  
  11. using System.Web;  
  12. using System.Web.Mvc;  
  13. using ExcelImport.Models;  
  14. using LinqToExcel;  
  15. using System.Data.SqlClient;  
  16.   
  17. namespace ExcelImport.Controllers  
  18. {  
  19.     public class UserController : Controller  
  20.     {  
  21.         private test2Entities db = new test2Entities();  
  22.         // GET: User  
  23.         public ActionResult Index()  
  24.         {  
  25.             return View();  
  26.         }  
  27.   
  28.   
  29.         /// <summary>  
  30.         /// This function is used to download excel format.  
  31.         /// </summary>  
  32.         /// <param name="Path"></param>  
  33.         /// <returns>file</returns>  
  34.         public FileResult DownloadExcel()  
  35.         {  
  36.             string path = "/Doc/Users.xlsx";  
  37.             return File(path, "application/vnd.ms-excel""Users.xlsx");  
  38.         }  
  39.   
  40.         [HttpPost]  
  41.         public JsonResult UploadExcel(User users, HttpPostedFileBase FileUpload)  
  42.         {  
  43.   
  44.             List<string> data = new List<string>();  
  45.             if (FileUpload != null)  
  46.             {  
  47.                 // tdata.ExecuteCommand("truncate table OtherCompanyAssets");  
  48.                 if (FileUpload.ContentType == "application/vnd.ms-excel" || FileUpload.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")  
  49.                 {  
  50.   
  51.                       
  52.                     string filename = FileUpload.FileName;  
  53.                     string targetpath = Server.MapPath("~/Doc/");  
  54.                     FileUpload.SaveAs(targetpath + filename);  
  55.                     string pathToExcelFile = targetpath + filename;  
  56.                     var connectionString = "";  
  57.                     if (filename.EndsWith(".xls"))  
  58.                     {  
  59.                         connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", pathToExcelFile);  
  60.                     }  
  61.                     else if (filename.EndsWith(".xlsx"))  
  62.                     {  
  63.                         connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", pathToExcelFile);  
  64.                     }  
  65.   
  66.                     var adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString);  
  67.                     var ds = new DataSet();  
  68.   
  69.                     adapter.Fill(ds, "ExcelTable");  
  70.   
  71.                     DataTable dtable = ds.Tables["ExcelTable"];  
  72.   
  73.                     string sheetName = "Sheet1";  
  74.   
  75.                     var excelFile = new ExcelQueryFactory(pathToExcelFile);  
  76.                     var artistAlbums = from a in excelFile.Worksheet<User>(sheetName) select a;  
  77.   
  78.                     foreach (var a in artistAlbums)  
  79.                     {  
  80.                         try  
  81.                         {  
  82.                             if (a.Name != "" && a.Address != "" && a.ContactNo != "")  
  83.                             {  
  84.                                 User TU = new User();  
  85.                                 TU.Name = a.Name;  
  86.                                 TU.Address = a.Address;  
  87.                                 TU.ContactNo = a.ContactNo;  
  88.                                 db.Users.Add(TU);  
  89.                                  
  90.                                 db.SaveChanges();  
  91.   
  92.                            
  93.   
  94.                             }  
  95.                             else  
  96.                             {  
  97.                                 data.Add("<ul>");  
  98.                                 if (a.Name == "" || a.Name == null) data.Add("<li> name is required</li>");  
  99.                                 if (a.Address == "" || a.Address == null) data.Add("<li> Address is required</li>");  
  100.                                 if (a.ContactNo == "" || a.ContactNo == null) data.Add("<li>ContactNo is required</li>");  
  101.   
  102.                                 data.Add("</ul>");  
  103.                                 data.ToArray();  
  104.                                 return Json(data, JsonRequestBehavior.AllowGet);  
  105.                             }  
  106.                         }  
  107.   
  108.                         catch (DbEntityValidationException ex)  
  109.                         {  
  110.                             foreach (var entityValidationErrors in ex.EntityValidationErrors)  
  111.                             {  
  112.   
  113.                                 foreach (var validationError in entityValidationErrors.ValidationErrors)  
  114.                                 {  
  115.   
  116.                                     Response.Write("Property: " + validationError.PropertyName + " Error: " + validationError.ErrorMessage);  
  117.   
  118.                                 }  
  119.   
  120.                             }  
  121.                         }  
  122.                     }  
  123.                     //deleting excel file from folder  
  124.                     if ((System.IO.File.Exists(pathToExcelFile)))  
  125.                     {  
  126.                         System.IO.File.Delete(pathToExcelFile);  
  127.                     }  
  128.                     return Json("success", JsonRequestBehavior.AllowGet);  
  129.                 }  
  130.                 else  
  131.                 {  
  132.                     //alert message for invalid file format  
  133.                     data.Add("<ul>");  
  134.                     data.Add("<li>Only Excel file format is allowed</li>");  
  135.                     data.Add("</ul>");  
  136.                     data.ToArray();  
  137.                     return Json(data, JsonRequestBehavior.AllowGet);  
  138.                 }  
  139.             }  
  140.             else  
  141.             {  
  142.                 data.Add("<ul>");  
  143.                 if (FileUpload == null) data.Add("<li>Please choose Excel file</li>");  
  144.                 data.Add("</ul>");  
  145.                 data.ToArray();  
  146.                 return Json(data, JsonRequestBehavior.AllowGet);  
  147.             }  
  148.         }  
  149.     }  
  150. }  
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: