Monday, 3 October 2016

How to Read and Display Excel file in ASP.NET MVC5 web application

4 comments
In this post we are going to implement How to read Microsoft Excel file and How to display excel excel data in asp.net mvc 5 web application.To read that data from Excel file we need install a package in our application is called "ExcelDataReader" package.We can install it using Nuget package manager or using Package manager console using command.
Install-Package ExcelDataReader
1.Search package in Nuget package and install after creation of Empty web application.

installing-ExcelDataReader-in-visualstudio

What is ExcelDataReader

1.ExcelDataReader is a light weight and fast library written in C Sharp language for reading Microsoft Excel files.
2.It is a open source library developer no need to pay for licenses.
3.We can import the this library using below statement into our application.
Using System.Excel;

Reading Excel file

1.First create a Excel file with sample data like below.

reading table data to excel

2.After Add a Controller to Controllers folder.
3.And Add a Index.cshtml page to upload Excel file.In this application we gonna read and display the uploaded  excel file.(we can also read a file in some destination also by specifying path).

4.Add below code for uploading file.
@{
    ViewBag.Title = "Read Excel File and Display in mvc5";
}

<h2>Read Excel File and Display in mvc5</h2>
@using (Html.BeginForm("Index", "ReadExcel", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
    @Html.AntiForgeryToken();
    @Html.ValidationSummary();

    <label class="text-info">Upload Excel File</label>
    <input type="file" class="form-control" name="uploadfile" id="uploadfile" />
    <input type="submit" value="submit" class="btn btn-default" />
}
5.The output of view is like this.

reading and displaying excel data

6.Now upload sample excel file to read the data from PC.
7.After clicking on Submit button the file come to Index (Post) action.
8.Implement below logic in Index Post action.
ReadExcelController.cs
using System.Web;
using System.Web.Mvc;
using Excel;
using System.IO;
using System.Data;

namespace ReadAndDisplayExcel.Controllers
{
    public class ReadExcelController : Controller
    {
        // GET: ReadExcel
        public ActionResult Index()
        {
            return View();
        }
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Index(HttpPostedFileBase uploadfile)
        {
            if (ModelState.IsValid)
            {
                if (uploadfile != null && uploadfile.ContentLength > 0)
                {
                    //ExcelDataReader works on binary excel file
                    Stream stream = uploadfile.InputStream;
                    //We need to written the Interface.
                    IExcelDataReader reader = null;
                    if (uploadfile.FileName.EndsWith(".xls"))
                    {
                        //reads the excel file with .xls extension
                        reader = ExcelReaderFactory.CreateBinaryReader(stream);
                    }
                    else if (uploadfile.FileName.EndsWith(".xlsx"))
                    {
                        //reads excel file with .xlsx extension
                        reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                    }
                    else
                    {
                        //Shows error if uploaded file is not Excel file
                        ModelState.AddModelError("File", "This file format is not supported");
                        return View();
                    }
                    //treats the first row of excel file as Coluymn Names
                    reader.IsFirstRowAsColumnNames = true;
                    //Adding reader data to DataSet()
                    DataSet result = reader.AsDataSet();
                    reader.Close();
                    //Sending result data to View
                    return View(result.Tables[0]);
                }
            }
            else
            {
                ModelState.AddModelError("File","Please upload your file");
            }
            return View();
        }
    }
}

Displaying Data in View

1.From above controller we read all the data in Microsoft Excel file and we return the data to View also using Strongly typed view concept.

2.Now display the data in model object in Table.
Index.cshtml
@{
    ViewBag.Title = "Read Excel File and Display in mvc5";
}
@model System.Data.DataTable
@using System.Data

<h2>Read Excel File and Display in mvc5</h2>
@using (Html.BeginForm("Index", "ReadExcel", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
    @Html.AntiForgeryToken();
    @Html.ValidationSummary();

    <label class="text-info">Upload Excel File</label>
    <input type="file" class="form-control" name="uploadfile" id="uploadfile" />
    <input type="submit" value="submit" class="btn btn-default" />

    if (Model != null)
    {
        <table class="table table-responsive table-bordered">
            <thead>
                <tr>
                    @foreach(DataColumn column in Model.Columns)
                    {
                        <th>@column.ColumnName</th>
                    }
                </tr>
            </thead>
            <tbody>
                @foreach(DataRow row in Model.Rows)
                {
                    <tr>
                        @foreach(DataColumn col in Model.Columns)
                        {
                            <td>@row[col.ColumnName]</td>
                        }
                    </tr>
                }
            </tbody>
        </table>
    }
}
3.Finally after uploading excel file we will get a table like this in View...

displaying microsoft excel data in mvc application

Download source for this application from here(source VS2015 code)this code is for reference only.Try to implement this application on your own.

Conclusion

I hope this tutorial is understandable and useful for every reader.Please comment if you suggest any modification to improve the quality of article.If you are not subscribed to this blog subscribe.follow on Social networks for daily awesome updates[Facebook,twitter,google plus].

4 comments:

  1. please change ReadExcel to index in index.cshtml

    ReplyDelete
  2. Hi ..Can you tell me how to Save the data back when hitting submit. My model is empty on save

    ReplyDelete
  3. Hi ..Can you tell me how to Save the data back when hitting submit. My model is empty on save

    ReplyDelete