Read Data From Excel File (xls, xlsx, csv) In ASP.NET MVC

We will connect to a Microsoft Excel workbook using the OLEDB.NET data provider, extract data and then display the data in a View.

Firstly, we will create an ImportExcel in Home Controller which returns a View.This method will return a View for Get Request. Now we will create another method ImportExcel1 and decorate it with [HttpPost] Attribute. Since in MVC 2 Methods cannot have same method name, we can call 2 actions using Action Name attribute. So we will decorate ImportExcel1 with [ActionName("Importexcel")]. Now if we make a get request then ImportExcel will be called and for post request ImportExcel1 will be called. The following is the code to read excel files.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using ExcelUpload.Models;

namespace ExcelUpload.Controllers
{
    public class HomeController : Controller
    {

        public ActionResult ImportExcel()
        {


            return View();
        }
        [ActionName("Importexcel")]
        [HttpPost]
        public ActionResult Importexcel1()
        {


            if (Request.Files["FileUpload1"].ContentLength > 0)
            {
                string extension = System.IO.Path.GetExtension(Request.Files["FileUpload1"].FileName).ToLower();
                string query = null;
                string connString = "";




                string[] validFileTypes = { ".xls", ".xlsx", ".csv" };

                string path1 = string.Format("{0}/{1}", Server.MapPath("~/Content/Uploads"), Request.Files["FileUpload1"].FileName);
                if (!Directory.Exists(path1))
                {
                    Directory.CreateDirectory(Server.MapPath("~/Content/Uploads"));
                }
                if (validFileTypes.Contains(extension))
                {
                    if (System.IO.File.Exists(path1))
                    { System.IO.File.Delete(path1); }
                    Request.Files["FileUpload1"].SaveAs(path1);
                    if(extension==".csv")
                    {
                     DataTable dt=   Utility.ConvertCSVtoDataTable(path1);
                     ViewBag.Data = dt;
                    }
                    //Connection String to Excel Workbook
                   else if (extension.Trim() == ".xls")
                    {
                        connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path1 + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                        DataTable dt = Utility.ConvertXSLXtoDataTable(path1,connString);
                        ViewBag.Data = dt;
                    }
                    else if (extension.Trim() == ".xlsx")
                    {
                        connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path1 + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                        DataTable dt = Utility.ConvertXSLXtoDataTable(path1, connString);
                        ViewBag.Data = dt;
                    }

                }
                else
                {
                    ViewBag.Error = "Please Upload Files in .xls, .xlsx or .csv format";

                }

              }

            return View();
        }


    }
}

Here we have created a static class Utility that contains 2 methods ConvertCSVtoDataTable and ConvertXSLXtoDataTable. The following is the code for Utility class.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;
using System.Data.OleDb;

namespace ExcelUpload.Models
{
    public static class Utility
    {
        public static DataTable ConvertCSVtoDataTable(string strFilePath)
        {
            DataTable dt = new DataTable();
            using (StreamReader sr = new StreamReader(strFilePath))
            {
                string[] headers = sr.ReadLine().Split(',');
                foreach (string header in headers)
                {
                    dt.Columns.Add(header);
                }

                while (!sr.EndOfStream)
                {
                    string[] rows = sr.ReadLine().Split(',');
                    if (rows.Length > 1)
                    {
                        DataRow dr = dt.NewRow();
                        for (int i = 0; i < headers.Length; i++)
                        {
                            dr[i] = rows[i].Trim();
                        }
                        dt.Rows.Add(dr);
                    }
                }

            }


            return dt;
        }

        public static DataTable ConvertXSLXtoDataTable(string strFilePath,string connString)
        {
            OleDbConnection oledbConn = new OleDbConnection(connString);
            DataTable dt=new DataTable();
            try
            {

                oledbConn.Open();
                using (OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn))
                {
                  OleDbDataAdapter oleda = new OleDbDataAdapter();
                oleda.SelectCommand = cmd;
                DataSet ds = new DataSet();
                oleda.Fill(ds);

                dt= ds.Tables[0];
                }
            }
            catch
            {
            }
            finally
            {

                oledbConn.Close();
            }

            return dt;

        }
        }
}

Now we will create a view that contains file upload control and a button. When a request for ImportExcel of Home Controller is made, we will show file upload control with button control. When we select a file and press button it will make a post request to Home Controller and ImportExcel1 method will be called. The following is the Razor View for both request.

@using System.Data;
@{
    ViewBag.Title = "ImportExcel";
    Layout = "~/Views/Shared/_Layout.cshtml";
}

<h2>ImportExcel</h2>
    <!--[if !IE]><!-->
<style type="text/css">


  /* Generic Styling, for Desktops/Laptops */
table {
  width: 100%;
  border-collapse: collapse;
}
/* Zebra striping */
tr:nth-of-type(odd) {
  background: #eee;
}
th {
  background: #333;
  color: white;
  font-weight: bold;
}
td, th {
  padding: 6px;
  border: 1px solid #ccc;
  text-align: left;
}
/*
Max width before this PARTICULAR table gets nasty
This query will take effect for any screen smaller than 760px
and also iPads specifically.
*/
@@media only screen and (max-width: 760px),
(min-device-width: 768px) and (max-device-width: 1024px)  {

 /* Force table to not be like tables anymore */
 table, thead, tbody, th, td, tr {
  display: block;
 }

 /* Hide table headers (but not display: none;, for accessibility) */
 thead tr {
  position: absolute;
  top: -9999px;
  left: -9999px;
 }

 tr { border: 1px solid #ccc; }

 td {
  /* Behave  like a "row" */
  border: none;
  border-bottom: 1px solid #eee;
  position: relative;
  padding-left: 50%;
 }

 td:before {
  /* Now like a table header */
  position: absolute;
  /* Top/left values mimic padding */
  top: 6px;
  left: 6px;
  width: 45%;
  padding-right: 10px;
  white-space: nowrap;
 }

 /*
 Label the data
 */
        td:before {
            content: attr(data-title);
        }

}
</style>

<!--<![endif]-->
@using (Html.BeginForm("ImportExcel","Home",FormMethod.Post,new { enctype = "multipart/form-data" } ))

{
    <table>
        <tr><td>Excel file</td><td><input type="file" id="FileUpload1" name="FileUpload1" /></td></tr>
        <tr><td></td><td><input type="submit" id="Submit" name="Submit" value="Submit" /></td></tr>
    </table>
}

<div>
    <table id="">

            @if (ViewBag.Data != null)
            {
            <thead>

                @foreach (DataColumn column in (ViewBag.Data as System.Data.DataTable).Columns)
                {
                <th>@column.ColumnName.ToUpper()</th>


                }
       </thead>
                if ((ViewBag.Data as System.Data.DataTable).Rows.Count > 0)
                {
                    foreach (DataRow dr in (ViewBag.Data as System.Data.DataTable).Rows)
                    {

                <tr>

                   @foreach (DataColumn column in (ViewBag.Data as System.Data.DataTable).Columns)
                   {
                    <td data-title='@column.ColumnName'>

                        @dr[column].ToString()
                    </td>
                   }




                </tr>

                    }

                }
                else
                {
                    int count = (ViewBag.Data as System.Data.DataTable).Columns.Count;
                    <tr>

                          <td colspan='@count' style="color:red;" >

                               No Data Found.
                            </td>



                    </tr>

                }

            }
            else
            {
                if (ViewBag.Error != null)
                {
                    <tr>
                    <td  style = "color:red;" >

       @(ViewBag.Error != null ? ViewBag.Error.ToString() : "")
                            </td >



                    </tr >
                    }
                }
            </table>
</div>