How to Read Excel in ASP.NET MVC and show it in Grid with Paging

How to Read Excel in ASP.NET MVC and show it in Grid with Paging

When reading large size excel files, and showing their data in the form of HTML Grid, on your view then make sure you provide paging links to. Through these paging links user can navigate from one sets of record to another.

Here I will create an application where users will be able to Read Excel files programmatically. First they will upload an excel file through a file upload control then the ASP.NET MVC code will read this excel file and show its content on the view in Grid form.

It will be just a single page application containing a Model, a Controller, a View and few classes.

I have also written tutorials explaining how to Import Excel Data to Database and Export Data from Database to Excel in ASP.NET websites. Check the below 2 links for these tutorials:
Model

Create a new Model class ReadExcel and add the below code to it:

public class ReadExcel
{
    [Required(ErrorMessage = "Please select file")]
    [FileExt(Allow = ".xls,.xlsx", ErrorMessage = "Only excel file")]
    public HttpPostedFileBase file { get; set; }
}

There is only one property of type HttpPostedFileBase because I will only need an input control of type file, on my view.

The FileExt attribute is a Custom Validation Attribute class through which I will validate the file control. This validation will make sure that the users only upload excel files (of extension .xls or .xlsx).

Now Create a PDF file easily in ASP.NET, check my tutorial – How to Create a PDF file in ASP Net MVC using iTextSharp DLL.
Create FileExt Class

Create a folder and name it Class. Add a class with name FileExt.cs to it and add the below code to this class:

public class FileExt : ValidationAttribute
{
    public string Allow;
    protected override ValidationResult IsValid(object value, ValidationContext validationContext)
    {
        if (value != null)
        {
            string extension = ((System.Web.HttpPostedFileBase)value).FileName.Split('.')[1];
            if (Allow.Contains(extension))
                return ValidationResult.Success;
            else
                return new ValidationResult(ErrorMessage);
        }
        else
            return ValidationResult.Success;
    }
}

Explanation: I inherited this class from ValidationAttribute and override the IsValid function. Inside this function I check the uploaded file’s extension with that passed from the Model i.e .xls,.xlsx.

If you want to learn how Custom Validation Attribute works then I strongly recommend you to read my tutorial on Server Side Validation using Data Annotation in ASP.NET MVC.

Controller

Create a Controller and name is ReadExcelController.

  • Add the Post Index Action method to it:

In this Post Action the I will Read Excel from the C# code.

[HttpPost]
public ActionResult Index(ReadExcel.Models.ReadExcel readExcel)
{
    if (ModelState.IsValid)
    {
        string path = Server.MapPath("~/Content/Upload/" + readExcel.file.FileName);
        readExcel.file.SaveAs(path);

        string excelConnectionString = @"Provider='Microsoft.ACE.OLEDB.12.0';Data Source='" + path + "';Extended Properties='Excel 12.0 Xml;IMEX=1'";
        OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);

        //Sheet Name
        excelConnection.Open();
        string tableName = excelConnection.GetSchema("Tables").Rows[0]["TABLE_NAME"].ToString();
        excelConnection.Close();
        //End

        //Putting Excel Data in DataTable
        DataTable dataTable = new DataTable();
        OleDbDataAdapter adapter = new OleDbDataAdapter("Select * from [" + tableName + "]", excelConnection);
        adapter.Fill(dataTable);
        //End

        Session["ExcelData"] = dataTable;
        ReadSession(1);
    }
    return View();
}

Explanation:

  • In the Post method the excel file is uploaded to the Content/Upload folder.
  • Then connection string is created to Read Excel file that is uploaded and for this the Microsoft.ACE.OLEDB.12.0 provider is used.
  • The excel file’s data is filled in a DataTable and this datatable is stored in a Session variable.
  • In the last line ReadSession() function whose work is to fetch the records for the page number that is passed to its parameter.
  • Update the Get Index ActionResult method like shown below:
public ActionResult Index(int? page)
{
    if (Session["ExcelData"] != null)
        ReadSession(Convert.ToInt32(page));
    return View();
}

Explanation: Check if ExcelData in Session is null or not. If it is not null then call the ReadSession() function passing on the current page number.

  • In the controller add ReadSession() function with the following code:
void ReadSession(int page)
{
    DataTable dataTable = (DataTable)Session["ExcelData"];

    var totalRecords = dataTable.Rows.Count;
    var pageSize = 10;
    var skip = pageSize * (page - 1);
    var canPage = skip < totalRecords;

    if (canPage)
    {
        var record = dataTable.AsEnumerable().Select(p => p)
                     .Skip(skip)
                     .Take(pageSize)
                     .ToArray();

        var columnName = dataTable.Columns.Cast<DataColumn>()
                         .Select(x => x.ColumnName)
                         .ToArray();

        StringBuilder sb = new StringBuilder();

        sb.Append("<table>");

        sb.Append("<thead><tr>");
        foreach (var col in columnName)
            sb.Append("<td>" + col + "</td>");
        sb.Append("</tr></thead>");

        sb.Append("<tbody>");
        foreach (var rec in record)
        {
            sb.Append("<tr>");
            foreach (var val in rec.ItemArray)
            {
                sb.Append("<td>" + val + "</td>");
            }
            sb.Append("</tr>");
        }
        sb.Append("</tbody>");

        sb.Append("</table>");

        ViewBag.ExcelData = sb.ToString();

        PagingInfo pagingInfo = new PagingInfo();
        pagingInfo.CurrentPage = page;
        pagingInfo.TotalItems = totalRecords;
        pagingInfo.ItemsPerPage = pageSize;
        ViewBag.Paging = pagingInfo;
    }
} 

Explanation:

  • The excel file data is fetched from the session and added to the DataTable. I have kept the page size as 10 and will show the records on page by page basis. Reading of records of a page is done with LINQ. The variable record holds the records of a given page number similarly the variable columnName contains the names of the excel file’s columns.
  • Next I loop through the variables – columnName and record and create a HTML table structure, for this I use the StringBuilder class. ThIs HTML table is stored in ViewBag.ExcelData variable.
  • In the end I create PagingInfo class object and sets current page, total items and items per page. I store this class object in ViewBag.Paging variable.

The PagingInfo class is used to create pagination links on the view. You can read more about it on my tutorial How to Create ASP.NET MVC Paging.

PagingInfo.cs

Create a new folder and name it Class. Now add PagingInfo.cs inside this folder. The code for this class is:

public class PagingInfo
{
    public int TotalItems { get; set; }
    public int ItemsPerPage { get; set; }
    public int CurrentPage { get; set; }
    public int TotalPages
    {
        get
        {
            return (int)Math.Ceiling((decimal)TotalItems /
                ItemsPerPage);
        }
    }
}
PagingHelper.cs

Add PagingHelper.cs in the Class folder and put the code to it:

public static class PagingHelper
{
    public static MvcHtmlString PageLinks(this HtmlHelper html, PagingInfo pagingInfo, Func<int, string> pageUrl)
    {
        StringBuilder result = new StringBuilder();
        string anchorInnerHtml = "";
        for (int i = 1; i <= pagingInfo.TotalPages; i++)
        {
            TagBuilder tag = new TagBuilder("a");
            anchorInnerHtml = AnchorInnerHtml(i, pagingInfo);

            if (anchorInnerHtml == "..")
                tag.MergeAttribute("href", "#");
            else
                tag.MergeAttribute("href", pageUrl(i));
            tag.InnerHtml = anchorInnerHtml;
            if (i == pagingInfo.CurrentPage)
            {
                tag.AddCssClass("active");
            }
            tag.AddCssClass("paging");
            if (anchorInnerHtml != "")
                result.Append(tag.ToString());
        }
        return MvcHtmlString.Create(result.ToString());
    }
    public static string AnchorInnerHtml(int i, PagingInfo pagingInfo)
    {
        string anchorInnerHtml = "";
        if (pagingInfo.TotalPages <= 10)
            anchorInnerHtml = i.ToString();
        else
        {
            if (pagingInfo.CurrentPage <= 5)
            {
                if ((i <= 8) || (i == pagingInfo.TotalPages))
                    anchorInnerHtml = i.ToString();
                else if (i == pagingInfo.TotalPages - 1)
                    anchorInnerHtml = "..";
            }
            else if ((pagingInfo.CurrentPage > 5) && (pagingInfo.TotalPages - pagingInfo.CurrentPage >= 5))
            {
                if ((i == 1) || (i == pagingInfo.TotalPages) || ((pagingInfo.CurrentPage - i >= -3) && (pagingInfo.CurrentPage - i <= 3)))
                    anchorInnerHtml = i.ToString();
                else if ((i == pagingInfo.CurrentPage - 4) || (i == pagingInfo.CurrentPage + 4))
                    anchorInnerHtml = "..";
            }
            else if (pagingInfo.TotalPages - pagingInfo.CurrentPage < 5)
            {
                if ((i == 1) || (pagingInfo.TotalPages - i <= 7))
                    anchorInnerHtml = i.ToString();
                else if (pagingInfo.TotalPages - i == 8)
                    anchorInnerHtml = "..";
            }
        }
        return anchorInnerHtml;
    }
}

Explanation: The PagingHelper class is a static class. It will be called from the view where paging links have to be created.

View

Create Index view and add the following code:

@using ReadExcel.Class;
@model ReadExcel.Models.ReadExcel

@using (Html.BeginForm("Index", "ReadExcel", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
    @Html.TextBoxFor(m => m.file, new { type = "file" })
    <button id="submitButton" type="submit">Submit</button>
    @Html.ValidationMessageFor(model => model.file)
}
<div class="excelDiv">
    @(new HtmlString(ViewBag.ExcelData))
</div>
<div class="pagingDiv">
    @{
        PagingInfo pagingInfo = (PagingInfo)ViewBag.Paging;
        if (pagingInfo != null)
        {
            @Html.PageLinks(pagingInfo, x => Url.Action("Index", "ReadExcel", new { page = x }))
        }
    }
</div>

Explanation:

  • When you are uploading a file make sure to put enctype = “multipart/form-data” on the form.
  • The excel file’s data is shown inside the excelDiv from the ViewBag.ExcelData variable.
  • The paging links are shown inside the pagingDiv. First the ViewBag.Paging variable data is converted to PagingInfo class then PageLinks function of the static class PagingHelper is called.

So in this way the paging links are created.

CSS

To style the view add the following CSS:

<style>
    .pagingDiv {
        background: #f2f2f2;
    }

        .pagingDiv > a {
            display: inline-block;
            padding: 0px 9px;
            margin-right: 4px;
            border-radius: 3px;
            border: solid 1px #c0c0c0;
            background: #e9e9e9;
            box-shadow: inset 0px 1px 0px rgba(255,255,255, .8), 0px 1px 3px rgba(0,0,0, .1);
            font-size: .875em;
            font-weight: bold;
            text-decoration: none;
            color: #717171;
            text-shadow: 0px 1px 0px rgba(255,255,255, 1);
        }

            .pagingDiv > a:hover {
                background: #fefefe;
                background: -webkit-gradient(linear, 0% 0%, 0% 100%, from(#FEFEFE), to(#f0f0f0));
                background: -moz-linear-gradient(0% 0% 270deg,#FEFEFE, #f0f0f0);
            }

            .pagingDiv > a.active {
                border: none;
                background: #616161;
                box-shadow: inset 0px 0px 8px rgba(0,0,0, .5), 0px 1px 0px rgba(255,255,255, .8);
                color: #f0f0f0;
                text-shadow: 0px 0px 3px rgba(0,0,0, .5);
            }
</style>
RouteConfig.cs

Add the following routes to the RouteConfig.cs file. This will create the SEO Friendly paging links like.

  • /ReadExcel
  • /ReadExcel/2
  • /ReadExcel/3
routes.MapRoute("ReadExcelPageOne", "ReadExcel", new { controller = "ReadExcel", action = "Index", page = 1 });

routes.MapRoute(
    name: "ReadExcelLinks",
    url: "ReadExcel/{page}/",
    defaults: new { controller = "ReadExcel", action = "Index", page = "" },
    constraints: new { page = @"^[0-9]+$" }
);

routes.MapRoute(
    name: "Default",
    url: "{controller}/{action}/{id}",
    defaults: new { controller = "ReadExcel", action = "Index", id = UrlParameter.Optional }
);

If you now run your application and upload an excel file, this is how its data will look on the view:

read excel

Conclusion

I hope you liked this Read Excel application. It is very powerful and can read very large size excel files too. The paging links feature gives extra benefit because users do not have to scroll down to view all the records.

Sharing is Caring – I Request you to share this Read Excel tutorial on your social accounts. Thank you!

DOWNLOAD

Share this article -

yogihosting

ABOUT THE AUTHOR

This article has been written by the Technical Staff of YogiHosting. Check out other articles on "WordPress, SEO, jQuery, HTML" and more.