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.

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Data;  
  4. using System.Data.OleDb;  
  5. using System.IO;  
  6. using System.Linq;  
  7. using System.Web;  
  8. using System.Web.Mvc;  
  9. using ExcelUpload.Models;  
  10.   
  11. namespace ExcelUpload.Controllers  
  12. {  
  13.     public class HomeController : Controller  
  14.     {  
  15.          
  16.         public ActionResult ImportExcel()  
  17.         {  
  18.              
  19.   
  20.             return View();  
  21.         }  
  22.         [ActionName("Importexcel")]  
  23.         [HttpPost]  
  24.         public ActionResult Importexcel1()  
  25.         {  
  26.   
  27.   
  28.             if (Request.Files["FileUpload1"].ContentLength > 0)  
  29.             {  
  30.                 string extension = System.IO.Path.GetExtension(Request.Files["FileUpload1"].FileName).ToLower();  
  31.                 string query = null;  
  32.                 string connString = "";  
  33.                   
  34.   
  35.   
  36.   
  37.                 string[] validFileTypes = { ".xls"".xlsx"".csv" };  
  38.                   
  39.                 string path1 = string.Format("{0}/{1}", Server.MapPath("~/Content/Uploads"), Request.Files["FileUpload1"].FileName);  
  40.                 if (!Directory.Exists(path1))  
  41.                 {  
  42.                     Directory.CreateDirectory(Server.MapPath("~/Content/Uploads"));  
  43.                 }  
  44.                 if (validFileTypes.Contains(extension))  
  45.                 {  
  46.                     if (System.IO.File.Exists(path1))  
  47.                     { System.IO.File.Delete(path1); }  
  48.                     Request.Files["FileUpload1"].SaveAs(path1);  
  49.                     if(extension==".csv")  
  50.                     {  
  51.                      DataTable dt=   Utility.ConvertCSVtoDataTable(path1);  
  52.                      ViewBag.Data = dt;  
  53.                     }  
  54.                     //Connection String to Excel Workbook  
  55.                    else if (extension.Trim() == ".xls")  
  56.                     {  
  57.                         connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path1 + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";  
  58.                         DataTable dt = Utility.ConvertXSLXtoDataTable(path1,connString);  
  59.                         ViewBag.Data = dt;  
  60.                     }  
  61.                     else if (extension.Trim() == ".xlsx")  
  62.                     {  
  63.                         connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path1 + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";  
  64.                         DataTable dt = Utility.ConvertXSLXtoDataTable(path1, connString);  
  65.                         ViewBag.Data = dt;  
  66.                     }  
  67.   
  68.                 }  
  69.                 else  
  70.                 {  
  71.                     ViewBag.Error = "Please Upload Files in .xls, .xlsx or .csv format";  
  72.   
  73.                 }  
  74.                   
  75.               }  
  76.   
  77.             return View();  
  78.         }  
  79.   
  80.           
  81.     }  
  82. }  
Here we have created a static class Utility that contains 2 methods ConvertCSVtoDataTable and ConvertXSLXtoDataTable. The following is the code for Utility class.
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.ComponentModel;  
  4. using System.Data;  
  5. using System.Data.SqlClient;  
  6. using System.IO;  
  7. using System.Linq;  
  8. using System.Web;  
  9. using System.Data.OleDb;  
  10.   
  11. namespace ExcelUpload.Models  
  12. {  
  13.     public static class Utility  
  14.     {  
  15.         public static DataTable ConvertCSVtoDataTable(string strFilePath)  
  16.         {  
  17.             DataTable dt = new DataTable();  
  18.             using (StreamReader sr = new StreamReader(strFilePath))  
  19.             {  
  20.                 string[] headers = sr.ReadLine().Split(',');  
  21.                 foreach (string header in headers)  
  22.                 {  
  23.                     dt.Columns.Add(header);  
  24.                 }  
  25.   
  26.                 while (!sr.EndOfStream)  
  27.                 {  
  28.                     string[] rows = sr.ReadLine().Split(',');  
  29.                     if (rows.Length > 1)  
  30.                     {  
  31.                         DataRow dr = dt.NewRow();  
  32.                         for (int i = 0; i < headers.Length; i++)  
  33.                         {  
  34.                             dr[i] = rows[i].Trim();  
  35.                         }  
  36.                         dt.Rows.Add(dr);  
  37.                     }  
  38.                 }  
  39.   
  40.             }  
  41.   
  42.   
  43.             return dt;  
  44.         }  
  45.   
  46.         public static DataTable ConvertXSLXtoDataTable(string strFilePath,string connString)  
  47.         {  
  48.             OleDbConnection oledbConn = new OleDbConnection(connString);  
  49.             DataTable dt=new DataTable();  
  50.             try  
  51.             {  
  52.                  
  53.                 oledbConn.Open();  
  54.                 using (OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn))  
  55.                 {
  56.                   OleDbDataAdapter oleda = new OleDbDataAdapter();  
  57.                 oleda.SelectCommand = cmd;  
  58.                 DataSet ds = new DataSet();  
  59.                 oleda.Fill(ds);  
  60.   
  61.                 dt= ds.Tables[0];  
  62.                 }
  63.             }  
  64.             catch  
  65.             {  
  66.             }  
  67.             finally  
  68.             {  
  69.                   
  70.                 oledbConn.Close();  
  71.             }  
  72.   
  73.             return dt;  
  74.   
  75.         }  
  76.         }  
  77. }  
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.
  1. @using System.Data;  
  2. @{  
  3.     ViewBag.Title = "ImportExcel";  
  4.     Layout = "~/Views/Shared/_Layout.cshtml";  
  5. }  
  6.   
  7. <h2>ImportExcel</h2>  
  8.     <!--[if !IE]><!-->  
  9. <style type="text/css">  
  10.   
  11.   
  12.   /* Generic Styling, for Desktops/Laptops */  
  13. table {  
  14.   width: 100%;  
  15.   border-collapse: collapse;  
  16. }  
  17. /* Zebra striping */  
  18. tr:nth-of-type(odd) {  
  19.   background: #eee;  
  20. }  
  21. th {  
  22.   background: #333;  
  23.   color: white;  
  24.   font-weight: bold;  
  25. }  
  26. td, th {  
  27.   padding: 6px;  
  28.   border: 1px solid #ccc;  
  29.   text-align: left;  
  30. }  
  31. /* 
  32. Max width before this PARTICULAR table gets nasty 
  33. This query will take effect for any screen smaller than 760px 
  34. and also iPads specifically. 
  35. */  
  36. @@media only screen and (max-width: 760px),  
  37. (min-device-width: 768px) and (max-device-width: 1024px)  {  
  38.   
  39.  /* Force table to not be like tables anymore */  
  40.  table, thead, tbody, th, td, tr {  
  41.   display: block;  
  42.  }  
  43.   
  44.  /* Hide table headers (but not display: none;, for accessibility) */  
  45.  thead tr {  
  46.   position: absolute;  
  47.   top: -9999px;  
  48.   left: -9999px;  
  49.  }  
  50.   
  51.  tr { border: 1px solid #ccc; }  
  52.   
  53.  td {  
  54.   /* Behave  like a "row" */  
  55.   border: none;  
  56.   border-bottom: 1px solid #eee;  
  57.   position: relative;  
  58.   padding-left: 50%;  
  59.  }  
  60.   
  61.  td:before {  
  62.   /* Now like a table header */  
  63.   position: absolute;  
  64.   /* Top/left values mimic padding */  
  65.   top: 6px;  
  66.   left: 6px;  
  67.   width: 45%;  
  68.   padding-right: 10px;  
  69.   white-space: nowrap;  
  70.  }  
  71.   
  72.  /* 
  73.  Label the data 
  74.  */  
  75.         td:before {  
  76.             content: attr(data-title);  
  77.         }  
  78.    
  79. }  
  80. </style>  
  81.              
  82. <!--<![endif]-->  
  83. @using (Html.BeginForm("ImportExcel","Home",FormMethod.Post,new { enctype = "multipart/form-data" } ))  
  84.   
  85. {  
  86.     <table>  
  87.         <tr><td>Excel file</td><td><input type="file" id="FileUpload1" name="FileUpload1" /></td></tr>  
  88.         <tr><td></td><td><input type="submit" id="Submit" name="Submit" value="Submit" /></td></tr>  
  89.     </table>  
  90. }  
  91.   
  92. <div>  
  93.     <table id="">  
  94.           
  95.             @if (ViewBag.Data != null)  
  96.             {  
  97.             <thead>  
  98.   
  99.                 @foreach (DataColumn column in (ViewBag.Data as System.Data.DataTable).Columns)  
  100.                 {  
  101.                 <th>@column.ColumnName.ToUpper()</th>  
  102.   
  103.   
  104.                 }  
  105.        </thead>  
  106.                 if ((ViewBag.Data as System.Data.DataTable).Rows.Count > 0)  
  107.                 {  
  108.                     foreach (DataRow dr in (ViewBag.Data as System.Data.DataTable).Rows)  
  109.                     {  
  110.   
  111.                 <tr>  
  112.   
  113.                    @foreach (DataColumn column in (ViewBag.Data as System.Data.DataTable).Columns)  
  114.                    {  
  115.                     <td data-title='@column.ColumnName'>  
  116.                           
  117.                         @dr[column].ToString()   
  118.                     </td>  
  119.                    }  
  120.   
  121.   
  122.   
  123.   
  124.                 </tr>  
  125.   
  126.                     }  
  127.   
  128.                 }  
  129.                 else  
  130.                 {  
  131.                     int count = (ViewBag.Data as System.Data.DataTable).Columns.Count;  
  132.                     <tr>  
  133.   
  134.                           <td colspan='@count' style="color:red;" >  
  135.   
  136.                                No Data Found.  
  137.                             </td>  
  138.                          
  139.   
  140.   
  141.                     </tr>  
  142.   
  143.                 }  
  144.   
  145.             }  
  146.             else  
  147.             {  
  148.                 if (ViewBag.Error != null)  
  149.                 {  
  150.                     <tr>  
  151.                     <td  style = "color:red;" >  
  152.   
  153.        @(ViewBag.Error != null ? ViewBag.Error.ToString() : "")  
  154.                             </td >  
  155.   
  156.   
  157.   
  158.                     </tr >  
  159.                     }  
  160.                 }  
  161.             </table>  
  162. </div>