Import an Excel 2007 spreadsheet into a GridView in ASP.Net

Import an Excel 2007 spreadsheet into a GridView in ASP.Net

I had a request earlier today for an existing web-based solution written in C# ASP.Net 2.0 to be able to upload an Excel 2007 spreadsheet and have the site immediately read from that file and put the results into a GridView control on the same page for editing, review, etc. I had never done this before, all of my data access was typically SQL, Access, XML, etc. So I did some light research and ended up writing the following code to accomplish this:

This code assumes you have already added a FileUpload control called “Fileupload1” and a GridView control called “GridView1”

if (Fileupload1.HasFile)
   {
      Fileupload1.SaveAs(Server.MapPath(Fileupload1.FileName));
      string filename = Fileupload1.FileName;
      string serverfilename = Server.MapPath(filename);
      string strCon = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” + serverfilename + “;Extended Properties=”Excel 12.0 Xml;HDR=NO;IMEX=1″”;
      OleDbDataAdapter cmd = new OleDbDataAdapter(“SELECT * FROM [Sheet1$]”, strCon);

      DataSet ds = new DataSet();
      cmd.Fill(ds, “XL”);
      GridView1.DataSource = ds.Tables[“XL”].DefaultView;
      GridView1.DataBind();
   }

This did the trick, worked perfectly for me. If anyone has a better method of accomplishing the same task I welcome your suggestions.

The following ASP.Net forums posting helped a lot: http://forums.asp.net/t/1169005.aspx

Hope this helps someone!

One thought on “Import an Excel 2007 spreadsheet into a GridView in ASP.Net

Comments are closed.

Comments are closed.