Sunday, January 3, 2016

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.
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", ".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 ,which contains 2 methods ConvertCSVtoDataTable and ConvertXSLXtoDataTable.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 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(); 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.Below 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()&nbsp; </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>

No comments :

Post a Comment