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.

upload-and-read-excel-file-in-asp-net-mvc-1

Type the following command in it.

upload-and-read-excel-file-in-asp-net-mvc-2

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>&copy; @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:

output