In this article I will explain with an example, how to upload, read and display
Excel file data using
WebGrid in
ASP.Net MVC.
Download System.Data.OleDb DLL
There are two ways you can download the System.Data.OleDb DLL.
Connection Strings
The first thing is to build connection strings to
Excel files and
Excel files are broadly divided into two types and
Excel 97-2003 and
Excel 2007 and higher.
Excel 97 – 2003 format which uses
Microsoft Jet driver and the
Excel version is set to 8.0 and Header is set to YES i.e. first row in
Excel sheet will be Header Row.
<add name= "Excel03ConString" connectionString= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
Excel 2007 and higher format which uses
Microsoft Ace driver and the
Excel version is set to 12.0 and Header is set to YES i.e. first row in
Excel sheet will be Header Row.
<add name= "Excel07ConString" connectionString= "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
Namespaces
You will need to import the following namespaces.
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
Controller
The Controller consists of following Action methods.
Action method for handling GET operation
Inside this Action method, simply the View is returned.
Action method for handling POST operation
This Action method gets called when the Import Button is clicked and it accepts HttpPostedFileBase class as a parameter.
First a check is performed if the file is selected or not, if selected then the selected file is saved into a Folder (Directory) named Uploads.
Then, using the switch case statement, the
Excel file extension is determined and the connection string is built.
A connection is established with the
Excel file using
OLEDB classes and the name of the first sheet is determined and the first sheet data is read into a
DataSet.
Finally, the
DataSet is returned to the View.
public class HomeController : Controller
{
// GET: Home
public ActionResult Index()
{
return View();
}
[HttpPost]
public ActionResult Index(HttpPostedFileBase postedFile)
{
DataSet ds = new DataSet();
string filePath = string.Empty;
if (postedFile != null)
{
string path = Server.MapPath("~/Uploads/");
if (!Directorys.Exists(path))
{
Directory.CreateDirectory(path);
}
filePath = path + Path.GetFileName(postedFile.FileName);
string extension = Path.GetExtension(postedFile.FileName);
postedFile.SaveAs(filePath);
string constr = string.Empty;
switch (extension)
{
case ".xls": //Excel 97-03.
constr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07 and above.
constr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
}
constr = string.Format(constr, filePath);
using (OleDbConnection connExcel = new OleDbConnection(constr))
{
using (OleDbCommand cmdExcel = new OleDbCommand())
{
using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())
{
cmdExcel.Connection = connExcel;
//Get the name of First Sheet.
connExcel.Open();
DataTable dtExcelSchema = new DataTable();
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
//Read Data from First Sheet.
connExcel.Open();
cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
odaExcel.SelectCommand = cmdExcel;
odaExcel.Fill(ds);
connExcel.Close();
}
}
}
}
return View(ds);
}
}
View
HTML Markup
Inside the View, the
DataSet is declared as Model for the View.
Uploading Excel File
The View consists of an
HTML FileUpload element and a
Submit Button enclosed in a Form element.
The
HTML Form has been created using the
Html.BeginForm method which accepts the following parameters.
ActionName – Name of the Action. In this case the name is Index.
ControllerName – Name of the Controller. In this case the name is Home.
FormMethod – It specifies the Form Method i.e. GET or POST. In this case it will be set to POST.
HtmlAttributes – This array allows to specify the additional Form Attributes. Here we need to specify enctype = “multipart/form-data” which is necessary for uploading Files.
Displaying Excel file data
The
WebGrid is initialized with the
Dynamic Anonymous Type collection by making use of
LINQ i.e. the records from the
DataTable are converted into
Dynamic Anonymous Type collection and are assigned to the
WebGrid.
The
WebGrid is created using the
GetHtml method with the following parameters.
HtmlAttributes – It is used to set the
HTML attributes to the
HTML Table generated by
WebGrid such as
ID,
Name,
Class, etc.
Columns – It is used to specify the columns to be displayed in
WebGrid and also allows to set specific
Header Text for the columns.
@using System.Data
@using System.Linq
@model DataSet
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
</head>
<body>
@using (Html.BeginForm("Index", "Home", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
<input type="file" name="postedFile" />
<input type="submit" value="Import" />
}
<hr />
@if (Model != null)
{
WebGrid webGrid = new WebGrid(source: (from p in Model.Tables[0].AsEnumerable()
select new
{
Id = p.Field<object>("Id").ToString(),
Name = p.Field<object>("Name").ToString(),
Country = p.Field<object>("Country").ToString()
}), canSort: false, canPage: false);
@webGrid.GetHtml(
htmlAttributes: new { @id = "WebGrid" },
columns: webGrid.Columns(
webGrid.Column("Id", "Id"),
webGrid.Column("Name", "Name"),
webGrid.Column("Country", "Country")));
}
</body>
</html>
Screenshots
The Excel File
![Upload, Read and Display Excel file data using WebGrid in ASP.Net MVC]()
WebGrid displaying Excel data
![Upload, Read and Display Excel file data using WebGrid in ASP.Net MVC]()
Downloads
