In this article I am going to show how we can read a MS
Excel sheet in a Grid in asp.net .
Below is my excel sheet...
Image 1.
Here in above excel we can have multiple sheet...
Below is my aspx code
<%@ Page Language="C#"
AutoEventWireup="true"
CodeFile="Default.aspx.cs"
Inherits="_Default"
%>
<!DOCTYPE html PUBLIC
"-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Excel in
GridView</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table cellpadding="1" cellspacing="1" width="50%" align="center">
<tr>
<td>
<asp:GridView ID="GridViewExcel" runat="server" EnableViewState="false" />
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
This is my aspx.cs code
using System;
using
System.Configuration;
using
System.Data;
using
System.Linq;
using
System.Web;
using
System.Web.Security;
using
System.Web.UI;
using
System.Web.UI.HtmlControls;
using
System.Web.UI.WebControls;
using
System.Web.UI.WebControls.WebParts;
using
System.Xml.Linq;
using
System.Data.OleDb;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object
sender, EventArgs e)
{
GetExcelData();
}
private void GetExcelData()
{
string
file = Server.MapPath("~/App_Data/Employee_Excel.xlsx");
//Here your
excel can in 2003 or it can be in 2007
//if 2003
then connection string
string
connStr2003 = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source={0};Extended Properties="Excel
8.0;HDR=YES;IMEX=1"",file);
// if 2007
then connection string
string
connStr2007 = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data
Source={0};Extended Properties="Excel 12.0
Xml;HDR=YES;IMEX=1"", file);
// in this
example I have 2007
DataTable
dt= new DataTable();
using (OleDbConnection conn = new
OleDbConnection(connStr2007))
{
string
sheet = @"SELECT * FROM [Sheet1$]";
using
(OleDbCommand cmd = new
OleDbCommand(sheet, conn))
{
conn.Open();
using
(OleDbDataAdapter ad = new OleDbDataAdapter(cmd))
{
ad.Fill(
dt );
}
conn.Close();
};
}
GridViewExcel.DataSource =
dt ;
GridViewExcel.DataBind();
}
}
When I run my application then output..
Image 2.