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”
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();
GridView1.DataSource = ds.Tables[“XL”].DefaultView;
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!