Import Data to Gridview from Excel Sheet (xls)

Import to Gridview from Excel Sheet (xls)

Step 1 – Create Excel worksheet
1) Open Microsoft Excel and create a new Worksheet.
2) The sample data is as follows: - (any sample data)
We will keep the default name for the Worksheet (Sheet1).
3) Let us name and save the excel file as Running.xls.
Note: You can download the Excel file along with the code files from the Downloads section at the end of this article.

Step 2 –Display Excel Data using GridView Control
1) Start Visual Studio 2005.
2) Select Create Website and choose the Template ASP.NET Web Site. We can choose the language as C#/VB. Set the name for the project as ExcelGV.
3) Add the existing Excel file (Running.xls) that we created to the App_data folder.
4) Add a new .aspx file. Choose language C#/VB and, depending on the language, we will set the appropriate names: ExcelGVCS.aspx for C# or ExcelGVVB for VB.NET.
4) Drag and drop the GridView control on the .aspx page.
5) We will add the following statement in code behind above the namespace section.

-----------------------------------------------------------------------------------
using System.Data.OleDb;
------------------------------
protected void Page_Load(object sender, EventArgs e)
{
OleDbConnection DBConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("~/App_Data/Running.xls") + ";" + "Extended Properties=\"Excel 8.0;HDR=Yes\"");
DBConnection.Open();
string SQLString = "SELECT * FROM [Sheet1$]";
OleDbCommand DBCommand = new OleDbCommand(SQLString, DBConnection);
IDataReader DBReader = DBCommand.ExecuteReader();
GridView1.DataSource = DBReader;
GridView1.DataBind();
DBReader.Close();
DBConnection.Close();
}