@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: