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>