Here, we will learn about reading excel file in ASP.NET MVC. As excel file are very common in the daily routine for peoples. So its quite important to have some idea of uploading and reading excel file and save the data in the database. So, we will use EPPlus for reading data from an excel file.
Prerequisite:
- Basic knowledge of ASP.NET MVC
- Basic knowledge of jQuery
Open the NuGet package manager console and add the EPPlus library.
Type the following command in it.
Install-Package EPPlus -Version 4.5.3.2
So, create a new project in ASP.NET MVC and open the _Layout.cshtml file and add the toastr reference which we are going to use and loader images in it.
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>@ViewBag.Title - My ASP.NET Application</title>
@Styles.Render("~/Content/css")
@Scripts.Render("~/bundles/modernizr")
@Scripts.Render("~/bundles/jquery")
<script type="text/javascript" src="//cdnjs.cloudflare.com/ajax/libs/toastr.js/latest/js/toastr.min.js"></script>
<link href="//cdnjs.cloudflare.com/ajax/libs/toastr.js/latest/css/toastr.min.css" rel="stylesheet">
<style>
#loading {
position: fixed;
top: -50%;
left: -50%;
width: 200%;
height: 200%;
background: rgba(241, 241, 241, 0.48);
z-index: 2000;
overflow: hidden;
}
#loading img {
position: absolute;
top: 0;
left: 0;
right: 0;
bottom: 0;
margin: auto;
}
</style>
</head>
<body>
<div id="loading">
<img src="~/Content/ajax-loader.gif" />
</div>
<div class="navbar navbar-inverse navbar-fixed-top">
<div class="container">
<div class="navbar-header">
<button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">
<span class="icon-bar"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
</button>
@Html.ActionLink("Read Excel File", "Index", "Home", new { area = "" }, new { @class = "navbar-brand" })
</div>
<div class="navbar-collapse collapse">
<ul class="nav navbar-nav">
<li>@Html.ActionLink("Home", "Index", "Home")</li>
</ul>
</div>
</div>
</div>
<div class="container body-content">
@RenderBody()
<hr />
<footer>
<p>© @DateTime.Now.Year - My ASP.NET Application</p>
</footer>
</div>
@Scripts.Render("~/bundles/bootstrap")
@RenderSection("scripts", required: false)
</body>
</html>
<script>
function showLoader() {
$('#loading').show();
}
function hideLoader() {
$('#loading').fadeOut();
}
$(document).ready(function () {
hideLoader();
});
</script>
Create a new model in the Model folder as ExcelViewModel.cs
public class ExcelViewModel
{
public string EnrollmentNo { get; set; }
public string Semester { get; set; }
public string Month { get; set; }
public string Year { get; set; }
public string StatementNo { get; set; }
}
Open the Index.cshtml and add the code in it.
@{
ViewBag.Title = "Home Page";
}
<div class="row">
<div class="col-md-6 form-group">
<input type="file" id="file" class="form-control" />
</div>
<div class="col-md-6 form-group">
<input type="button" id="btnUpload" value="Upload Excel" class="btn btn-success" />
</div>
</div>
<script>
$(document).on('click', '#btnUpload', function () {
if (window.FormData !== undefined) {
var fileUpload = $("#file").get(0);
if ($("#file").get(0).files.length == 0) {
toastr.error("Please upload the file");
return;
}
showLoader();
var files = fileUpload.files;
var fileData = new FormData();
for (var i = 0; i < files.length; i++) {
fileData.append(files[i].name, files[i]);
}
$.ajax({
url: '/Home/SaveExcel',
type: "POST",
contentType: false,
processData: false,
data: fileData,
success: function (result) {
hideLoader();
if (result != null)
toastr.success("Excel uploaded successfully");
else
toastr.error("Something went wrong.Please contact administrator");
},
error: function (err) {
hideLoader();
toastr.error("Something went wrong.Please contact administrator");
}
});
} else {
toastr.error("FormData is not supported in the browser.");
}
});
</script>
Finally, open the HomeController and add the logic for reading excel file.
using OfficeOpenXml;
using ReadExcelFileInMvc5.Models;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
namespace ReadExcelFileInMvc5.Controllers
{
public class HomeController : Controller
{
public ActionResult Index() => View();
[HttpPost]
public JsonResult SaveExcel()
{
List<ExcelViewModel> excelData = new List<ExcelViewModel>();
if (Request.Files.Count > 0)
{
try
{
HttpFileCollectionBase files = Request.Files;
for (int i = 0; i < files.Count; i++)
{
HttpPostedFileBase file = files[i];
string fname;
if (Request.Browser.Browser.ToUpper() == "IE" || Request.Browser.Browser.ToUpper() == "INTERNETEXPLORER")
{
string[] testfiles = file.FileName.Split(new char[] { '\\' });
fname = testfiles[testfiles.Length - 1];
}
else
{
fname = file.FileName;
}
var newName = fname.Split('.');
fname = newName[0] + "_" + DateTime.Now.Ticks.ToString() + "." + newName[1];
var uploadRootFolderInput = AppDomain.CurrentDomain.BaseDirectory + "\\ExcelUploads";
Directory.CreateDirectory(uploadRootFolderInput);
var directoryFullPathInput = uploadRootFolderInput;
fname = Path.Combine(directoryFullPathInput, fname);
file.SaveAs(fname);
string xlsFile = fname;
excelData = readExcel(fname);
}
if (excelData.Count > 0)
{
return Json(excelData, JsonRequestBehavior.AllowGet);
}
else
return Json(false, JsonRequestBehavior.AllowGet);
}
catch (Exception ex)
{
return Json(false, JsonRequestBehavior.AllowGet);
}
}
else
{
return Json(false, JsonRequestBehavior.AllowGet);
}
}
public List<ExcelViewModel> readExcel(string FilePath)
{
try
{
List<ExcelViewModel> excelData = new List<ExcelViewModel>();
FileInfo existingFile = new FileInfo(FilePath);
using (ExcelPackage package = new ExcelPackage(existingFile))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
int rowCount = worksheet.Dimension.End.Row;
for (int row = 1; row <= rowCount; row++)
{
string[] getMonthYear = worksheet.Cells[row, 4].Value.ToString().Trim().Split(' ');
excelData.Add(new ExcelViewModel()
{
EnrollmentNo = worksheet.Cells[row, 2].Value.ToString().Trim(),
Semester = worksheet.Cells[row, 3].Value.ToString().Trim(),
Month = getMonthYear[0],
Year = getMonthYear[1],
StatementNo = worksheet.Cells[row, 5].Value.ToString().Trim()
});
}
}
return excelData;
}
catch (Exception ex)
{
return null;
}
}
}
}
Output:
To scale your business growth, Hire .NET web developers to construct feature-rich, secure, and scalable web apps. Our .NET developers are experts in creating custom web applications to meet your company’s specific requirements. Use our low-cost services to construct well-tailored apps that will provide users with a better web experience.
hello
i am having normal table in mvc view and i want to add data from excel to that table using Epplus. I will be thankful if i can get any help
When i am clicking on choose file, no file selelction selection.
No, I don’t have a repo for this article, but you can follow steps it will work, and if not for some reason let me know I’ll help.
Hi Thanks for the tutorial
do you have a github repo for this tutorial?