How to import excel sheet in MVC
In this article, we learn how to import excel file in MVC or how to display excel sheet file in webgrid in MVC, Let’s start
Connection String
First we need to add connection string in web config. We need to add two connectionstring
1. The Excel files of version 97-2003
<add name="ExcelConString03" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
2. The Excel files of version 2007 and above
<add name="ExcelConString07" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
Controller:-
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
namespace CODESOLUTIONS.Controllers
{
public class HomeController : Controller
{
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("~/file/");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
filePath = path + Path.GetFileName(postedFile.FileName);
string extension = Path.GetExtension(postedFile.FileName);
postedFile.SaveAs(filePath);
string conString = string.Empty;
switch (extension)
{
case ".xls": //Excel 97-03.
conString = ConfigurationManager.ConnectionStrings["ExcelConString03"].ConnectionString;
break;
case ".xlsx": //Excel 07 and above.
conString = ConfigurationManager.ConnectionStrings["ExcelConString07"].ConnectionString;
break;
}
conString = string.Format(conString, filePath);
using (OleDbConnection oleconexcel = new OleDbConnection(conString))
{
using (OleDbCommand cmdexcel = new OleDbCommand())
{
using (OleDbDataAdapter oleexcel = new OleDbDataAdapter())
{
cmdexcel.Connection = oleconexcel;
//Get the name of First Sheet.
oleconexcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = oleconexcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
oleconexcel.Close();
//Read Data from First Sheet.
oleconexcel.Open();
cmdexcel.CommandText = "SELECT * From [" + sheetName + "]";
oleexcel.SelectCommand = cmdexcel;
oleexcel.Fill(ds);
oleconexcel.Close();
}
}
}
}
return View(ds);
}
}
}
View:-
@using System.Data
@using System.Linq
@model DataSet
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
<style type="text/css">
body {
font-family: Arial;
font-size: 10pt;
}
.Grid {
border: 1px solid #ccc;
border-collapse: collapse;
}
.Grid th {
background-color: #F7F7F7;
font-weight: bold;
}
.Grid th, .Grid td {
padding: 5px;
border: 1px solid #ccc;
}
.Grid, .Grid table td {
border: 0px solid #ccc;
}
.Grid th a, .Grid th a:visited {
color: #333;
}
</style>
</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(),
Contact = p.Field<object>("Contact").ToString(),
Cource = p.Field<object>("Cource").ToString()
}), canSort: false, canPage: false);
@webGrid.GetHtml(
htmlAttributes: new { @id = "WebGrid", @class = "Grid" },
columns: webGrid.Columns(
webGrid.Column("Id", "Id"),
webGrid.Column("Name", "Name"),
webGrid.Column("Contact", "Contact"),
webGrid.Column("Cource", "Cource")));
}
</body>
</html>
Excel file with Book1 .xlsx
Out-Put:
How to import excel sheet in MVC
Reviewed by NEERAJ SRIVASTAVA
on
11:21:00 PM
Rating:
Reviewed by NEERAJ SRIVASTAVA
on
11:21:00 PM
Rating:
No comments: