How To Upload Excel File To Sql Database Mvc

Updated date Jun 11, 2021

392.8k
10

In this article we volition learn how to Import excel data to database using ASP.Cyberspace MVC Entity framework.

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,

Effigy 2 :Add new item ADO.NET Entity Data Model and click add push 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 terminate,

Figure five:Install NuGet package LinqToExcel in our project,

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

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

              @using (Html.BeginForm("UploadExcel", "User", FormMethod.Mail, new { enctype = "multipart/class-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.Championship = "Index";   }      <h4>Add Users via Excel</h4>   <hr />   @using (Html.BeginForm("UploadExcel", "User", FormMethod.Post, new { enctype = "multipart/course-data", onsubmit = "render myFunction()" }))   {       <div class="form-horizontal">           <div form="form-grouping">               <div course="control-label col-md-2">Download Format:</div>               <div class="col-medico-10">                   <a href="/User/DownloadExcel/"><img src="~/excel.ico" width="25" meridian="25" title="Download Excel format" alt="excel" /></a>               </div>           </div>         <div course="grade-group">             <div class="control-characterization col-md-2">Excel:</div>               <div class="col-dr.-10">                   <input type="file" id="FileUpload" proper name="FileUpload" class="" />               </div>           </div>           <div course="form-group">               <div class="col-md-showtime-two col-md-10">                   <input type="submit" value="Upload" id="btnSubmit" class="btn btn-main" />             </div>           </div>       </div>   }

Model

Userlist .cs

              using System;   using System.Collections.Generic;   using System.Linq;   using System.Web;      namespace ExcelImport.Models   {       public course UserList       {                  public string Name { get; set; }           public string Address{ get; set; }           public cord ContactNo { get; set; }             }   }

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

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

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

Controller Full Code:The JsonResult UploadExcel role using HttpPost return Json issue,

              using System;   using System.Collections.Generic;   using Organization.Data;   using System.Data.Entity;   using System.Data.Entity.Validation;   using System.Information.OleDb;   using System.IO;   using System.Linq;   using System.Net;   using System.Text.RegularExpressions;   using System.Spider web;   using System.Spider web.Mvc;   using ExcelImport.Models;   using LinqToExcel;   using System.Data.SqlClient;      namespace ExcelImport.Controllers   {       public class UserController : Controller       {           individual test2Entities db = new test2Entities();           // Become: User           public ActionResult Alphabetize()           {               render View();           }         /// <summary>           /// This function is used to download excel format.           /// </summary>           /// <param name="Path"></param>           /// <returns>file</returns>           public FileResult DownloadExcel()           {               string path = "/Physician/Users.xlsx";               render File(path, "application/vnd.ms-excel", "Users.xlsx");           }              [HttpPost]           public JsonResult UploadExcel(User users, HttpPostedFileBase FileUpload)           {                  List<string> data = new List<string>();               if (FileUpload != zippo)               {                   // tdata.ExecuteCommand("truncate tabular array OtherCompanyAssets");                   if (FileUpload.ContentType == "application/vnd.ms-excel" || FileUpload.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")                   {                                         string filename = FileUpload.FileName;                       cord targetpath = Server.MapPath("~/Medico/");                       FileUpload.SaveAs(targetpath + filename);                       string pathToExcelFile = targetpath + filename;                       var connectionString = "";                       if (filename.EndsWith(".xls"))                       {                           connectionString = cord.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Backdrop=Excel 8.0;", pathToExcelFile);                       }                       else if (filename.EndsWith(".xlsx"))                       {                           connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Information Source={0};Extended Backdrop=\"Excel 12.0 Xml;HDR=Yep;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)                       {                           effort                           {                               if (a.Name != "" && a.Address != "" && a.ContactNo != "")                               {                                   User TU = new User();                                   TU.Proper name = a.Name;                                   TU.Address = a.Address;                                   TU.ContactNo = a.ContactNo;                                   db.Users.Add(TU);                                 db.SaveChanges();                             }                               else                               {                                   information.Add("<ul>");                                   if (a.Proper noun == "" || a.Name == null) data.Add("<li> name is required</li>");                                   if (a.Address == "" || a.Address == goose egg) data.Add("<li> Address is required</li>");                                   if (a.ContactNo == "" || a.ContactNo == null) data.Add("<li>ContactNo is required</li>");                                 data.Add("</ul>");                                   information.ToArray();                                   render Json(information, JsonRequestBehavior.AllowGet);                               }                           }                         catch (DbEntityValidationException ex)                           {                               foreach (var entityValidationErrors in ex.EntityValidationErrors)                               {                                 foreach (var validationError in entityValidationErrors.ValidationErrors)                                   {                                     Response.Write("Property: " + validationError.PropertyName + " Fault: " + validationError.ErrorMessage);                                 }                             }                           }                       }                       //deleting excel file from folder                       if ((Organization.IO.File.Exists(pathToExcelFile)))                       {                           Arrangement.IO.File.Delete(pathToExcelFile);                       }                       return Json("success", JsonRequestBehavior.AllowGet);                   }                   else                   {                       //warning message for invalid file format                       data.Add("<ul>");                       data.Add together("<li>Simply Excel file format is immune</li>");                       data.Add("</ul>");                       data.ToArray();                       return Json(data, JsonRequestBehavior.AllowGet);                   }               }               else               {                   data.Add together("<ul>");                   if (FileUpload == zilch) data.Add together("<li>Delight choose Excel file</li>");                   data.Add("</ul>");                   data.ToArray();                   return Json(data, JsonRequestBehavior.AllowGet);               }           }       }   }

Output