Import excel with multiple sheets in Asp.net MVC

In this article, we will see how to import data from an Excel spreadsheet(xlx,xlsx) which contains multiple sheets using 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.
First of all we will create a 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.Below 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" };
                
                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);
                    
                    //Connection String to Excel Workbook
                   if (extension.Trim() == ".xls")
                    {
                        connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path1 + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                        DataSet dt = Utility.ConvertXSLXtoDataSet(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\"";
                        DataSet dt = Utility.ConvertXSLXtoDataSet(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 ,which contains a method ConvertXSLXtoDataSet. Below 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 DataSet ConvertXSLXtoDataSet(string strFilePath, string connString)
        {
            OleDbConnection oledbConn = new OleDbConnection(connString);
            DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            try
            {

                oledbConn.Open();
                using (DataTable Sheets = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null))
                {

                    for (int i = 0; i < Sheets.Rows.Count; i++)
                    {
                        string worksheets = Sheets.Rows[i]["TABLE_NAME"].ToString();
                        OleDbCommand cmd = new OleDbCommand(String.Format("SELECT * FROM [{0}]", worksheets), oledbConn);
                        OleDbDataAdapter oleda = new OleDbDataAdapter();
                        oleda.SelectCommand = cmd;
                        DataTable t = new DataTable(worksheets);

                        oleda.Fill(t);
                        ds.Tables.Add(t);
                    }

                    
                }

            }
            catch(Exception ex)
            {
            }
            finally
            {

                oledbConn.Close();
            }

            return ds;

        }
        }
}

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.Below is the Razor View for both request.

@using System.Data;
@{
    ViewBag.Title = "Import Excel With Multiple Sheets";
    Layout = "~/Views/Shared/_Layout.cshtml";
}

<h2>Import Excel With Multiple Sheets</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>
    @if (ViewBag.Data != null)
    {
        foreach (DataTable dt in (ViewBag.Data as System.Data.DataSet).Tables)
        {
            <h3>@dt.TableName.Replace("$","")</h3>
            <hr/>
            <table id="">
                <thead>
                    <tr>

                        @foreach (DataColumn column in dt.Columns)
                    {
                            <th>@column.ColumnName.ToUpper()</th>


                        }
                    </tr>
                </thead>





                @if (dt.Rows.Count > 0)
                    {
                        foreach (DataRow dr in dt.Rows)
                        {

                        <tr>

                            @foreach (DataColumn column in dt.Columns)
                            {
                                <td data-title='@column.ColumnName'>

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




                        </tr>

                    }

                }
                else
                {
                    int count = dt.Columns.Count;
                    <tr>

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

                            No Data Found.
                        </td>



                    </tr>


                }

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

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



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