read XLS (Excel) file and fill a gridview RSS

• • Reply
  

  dofoo

  Member

  196 Points

  289 Posts

  read XLS (Excel) file and fill a gridview

  May 02, 2012 10:16 AM|LINK

  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
  
  

  pixelfarm.it
• • Reply
  

  dofoo

  Member

  196 Points

  289 Posts

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

  May 02, 2012 10:42 AM|LINK

  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

  pixelfarm.it
• • Reply
  

  bhaskar.mule

  Contributor

  2270 Points

  659 Posts

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

  May 02, 2012 10:44 AM|LINK

  hi

  can you try it once

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

  thanks

  Bhaskar.Mule
  Site:Rare technical solutions
  • Edited by bhaskar.mule on May 02, 2012 10:46 AM
  • Marked as answer by dofoo on May 02, 2012 11:43 AM
• • Reply
  

  dofoo

  Member

  196 Points

  289 Posts

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

  May 02, 2012 11:43 AM|LINK

  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;'";

  pixelfarm.it