read XLS (Excel) file and fill a gridview

Hi all,

I have this code:

string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=provafile.xls;Extended Properties=""Excel 8.0;HDR=YES;""";

            string query = "Select * from [Sheet1$]";
            DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
            DbConnection connection = factory.CreateConnection();
            connection.ConnectionString = connectionString;
            DbCommand selectCommand = factory.CreateCommand();
            selectCommand.CommandText = query;
            selectCommand.Connection = connection;
            DbDataAdapter adapter = factory.CreateDataAdapter();
            adapter.SelectCommand = selectCommand;
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            Session["Dtable"] = dt;
            GridView1.DataSource = dt;
            GridView1.DataBind();
            Response.Write(dt.ToString());

that read provafile.xls sheet1 and fill a datatable used as datasource for a gridview.

The problem is that the page is empty and I got no errors, I don't find errors in code.. so, where am I wrong?

The second question is: how can I build a parametric connection string with the filename coming from a variable?

Thanks a lot in advance

Re: read XLS (Excel) file and fill a gridview

ok I've solved the gridview issue.

Now I have the second thing to resolve: parametric data source in the connection string.

I see that the path does not point to the root of the folder where are my files BUT to C:\Programmi\File comuni\Microsoft Shared\DevServer\10.0\provafile.xls

How can I modify the path to something like ~/xls/provafile.xls?

Thanks in advance

Re: read XLS (Excel) file and fill a gridview

can you try it once

Server.MapPath("~/xls/provafile.xls");

thanks

Re: read XLS (Excel) file and fill a gridview

thanks, but I have to write the remaining string with single quotes as follows:

string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~/xls/provafile.xls") + ";Extended Properties='Excel 8.0;HDR=YES;'";