Display Excel 2007 data on GridView using ASP.NET 2.0


I came across this question from ASP.NET forum: http://forums.asp.net/t/1205825.aspx.

The connection string to use with Excel 2007 is the key for this question.

Answer:

Here is the code to show Excel 2007 spreedsheet on a GridView: 

<asp:GridView ID="GridView1" runat="server"></asp:GridView>

Code:

VB.NET 

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim objDs As New System.Data.DataSet()
       

Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _

"Data Source=" & Server.MapPath(".") & "\App_Data\Book1.xlsx;" & _

"Extended Properties=Excel 12.0"

  '//http://groups.google.com/group/microsoft.public.data.oledb/browse_thread/thread/791376f08015fc55 Dim objDataAdapter As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn) objDataAdapter.Fill(objDs) GridView1.DataSource = objDs.Tables(0).DefaultView GridView1.DataBind() End Sub

C#:
 
 protected void Page_Load(object sender, System.EventArgs e)
    {
        System.Data.DataSet objDs = new System.Data.DataSet();

       

string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="

+ Server.MapPath(".") + "\\App_Data\\Book1.xlsx;" +

"Extended Properties=Excel 12.0";
 
////http://groups.google.com/group/microsoft.public.data.oledb/browse_thread/thread/791376f08015fc55 

        System.Data.OleDb.OleDbDataAdapter objDataAdapter = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);

        objDataAdapter.Fill(objDs);

        GridView1.DataSource = objDs.Tables[0].DefaultView;
        GridView1.DataBind();
    }
// Here is another source to find the connection string: http://blogs.msdn.com/adamze/archive/2007/09/21/oledb-and-excel-2007.aspx 

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s