Connecting excel file and binding the GridView in ASP.NET
Connecting to a database is a easy task, but connecting to a excel file is little bit tedious.
Now we will try to connect the Microsoft excel file, get the data from it and bind the gridview control.
Web.config file is XML configuration file to define the conncetion strings,variable..etc. Here we have declaring the Connction string to connect MS Excel files from ASP.NET with the name xls2003 and xlsx2005.
XML config file to declare the connection string.
<connectionStrings>
<add name="xls2003" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\5th june\Interview Questions1.xls;Extended Properties=Excel 8.0"/>
<add name="xlsx2005" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\5th june\Interview Questions1.xlsx;Extended Properties=Excel 12.0"/>
</connectionStrings>
C# code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data.OleDb;
using System.Data;
namespace WebApplication1
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
string connStr = ConfigurationManager.ConnectionStrings["xls2005"].ConnectionString;
// Create the connection object
OleDbConnection excelConnection = new OleDbConnection(connStr);
try
{
// Open connection
excelConnection.Open();
// Create OleDbCommand object and select data from worksheet Sheet1
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", excelConnection);
// Create new OleDbDataAdapter
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = cmd;
// Create a DataSet which will hold the data extracted from the worksheet.
DataSet ds = new DataSet();
// Fill the DataSet from the data extracted from the worksheet.
da.Fill(ds, "ExcelData");
// Bind the data to the GridView
GridView1.DataSource = ds.Tables[0].DefaultView;
GridView1.DataBind();
}
catch
{
}
finally
{
// Close connection
excelConnection.Close();
}
}
}
}
We have to get the conncetion details form the web.config and nedds to connect to Excel file. For that we are using the OLEDBConnection and OledbCommand for to get the data from the Sheet1 tab.