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

In this article, we will see how to display data from an Excel spreadsheet (xlx, xlsx, csv) 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.

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>