The following example shows how to display data from an excel spread sheet in a GridView. We will connect to a Microsoft Excel workbook using the OLEDB.NET data provider, extract data and then display the data in a GridView.
In the aspx page;
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true">
</asp:GridView>
</div>
</form>
</body>
In the aspx.cs page;
using System.Data.OleDb;
protected void Page_Load(object sender, EventArgs e)
{
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;data source=E:/DummyProject/Excel/Contacts.xlsx;Extended Properties=Excel 12.0;";
OleDbConnection objConn = new OleDbConnection(strConn);
//You must use the $ after the object you refer in the spreadsheet
string strSql = "Select * From [Sheet1$]";
OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
try
{
objConn.Open();
GridView1.DataSource = objCmd.ExecuteReader();
GridView1.DataBind();
}
catch (Exception exc)
{
Response.Write(exc.ToString());
}
finally
{
objConn.Dispose();
}
}
As you see in the connection string ‘Microsoft.ACE.OLEDB.12.0′ is the new Access database engine OLE DB driver and is also capable of reading Excel 2003. For older versions of excel files, you can use the connection string ‘Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:/DummyProject/Excel/Contacts.xls;Extended Properties=Excel 8.0’
Silent Killer 