Step 1 – Create Excel worksheet
1) Open Microsoft Excel and create a new Worksheet.
2) The sample data is as follows.
|
Date
|
Dist
|
Type
|
Surface
|
Period
|
|
4/15/2006
|
4 mi
|
Almost Flat
|
Pavement, Black
|
40 min
|
|
4/17/2006
|
4 mi
|
Almost Flat
|
Treadmill
|
40 min
|
|
4/19/2006
|
4 mi
|
Almost Flat
|
Pavement, Black
|
38 min
|
|
4/21/2006
|
3.75 mi
|
Almost Flat
|
Pavement, Black
|
36 min
|
|
4/23/2006
|
4 mi
|
Almost Flat
|
Pavement, Black
|
36 min
|
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.
Listing 1
[Visual Basic 2005]
Imports System.Data
Imports System.Data.OleDb
[C#]
6) Below code goes in the Page_Load:.
Listing 2
[Visual Basic 2005]
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Dim 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()
Dim SQLString As String = "SELECT * FROM [Sheet1$]"
Dim DBCommand = New OleDbCommand(SQLString, DBConnection)
Dim DBReader As IDataReader = DBCommand.ExecuteReader()
GridView1.DataSource = DBReader
GridView1.DataBind()
DBReader.Close()
DBConnection.Close()
End Sub[C#]
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();
}Note that the connection string uses the Server.MapPath
function. This function takes a path that is relative to Microsoft Internet
Information Services (IIS) to a file and returns a hard disk path to that file.
