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 the same method name, we can call 2 actions using the 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 = Path.GetExtension(Request.Files["FileUpload1"].FileName).ToLower();
                string query = null;
                string connString = "";

                string[] validFileTypes = { ".xls", ".xlsx", ".csv" };
                string path1 = Path.Combine(Server.MapPath("~/Content/Uploads"), Request.Files["FileUpload1"].FileName);

                if (!Directory.Exists(Server.MapPath("~/Content/Uploads")))
                {
                    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;
                    }
                    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 the 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
            {
                // Handle exceptions
            }
            finally
            {
                oledbConn.Close();
            }

            return dt;
        }
    }
}

Now we will create a view that contains a 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 the button it will make a post request to the Home Controller and the ImportExcel1 method will be called. The following is the Razor View for both requests.

@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 DataTable).Columns)
                {
                    <th>@column.ColumnName.ToUpper()</th>
                }
            </thead>

            if ((ViewBag.Data as DataTable).Rows.Count > 0)
            {
                foreach (DataRow dr in (ViewBag.Data as DataTable).Rows)
                {
                    <tr>
                        @foreach (DataColumn column in (ViewBag.Data as DataTable).Columns)
                        {
                            <td data-title='@column.ColumnName'>
                                @dr[column].ToString()
                            </td>
                        }
                    </tr>
                }
            }
            else
            {
                int count = (ViewBag.Data as 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>