Tech Tidbits - Ruby, Ruby On Rails, Merb, .Net, Javascript, jQuery, Ajax, CSS...and other random bits and pieces.

Tuesday, May 6, 2008

ASP.NET - Import and display Excel spreadsheet

I needed to upload and parse an Excel spreadsheet and display it in a GridView. For the most part, pretty straight forward. I ran into an issue "Could not find installable ISAM" once I added "IMEX=1" to my connection string. This was solved by adding (char)34 as shown in my working example:

string filePath = "~/somedirectory/somespreadsheet.xls";

string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;"
+ "Data Source=" + MapPath(filePath) + ";"
+ "Extended Properties=" + (char)34
+ "Excel 8.0;IMEX=1;" + (char)34;

DataSet dsRecords = new DataSet();
// "Sheet" is name of sheet being opened, must add $ after the name
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);

//da.TableMappings.Add("Table", "ExcelText");
scheduleGridView.DataSource = dsRecords.Tables[0].DefaultView;

As a note, you can also access the spreadsheet directly using the file path:
string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;"
+ "Data Source=c:/somedirectory/somespreadsheet.xls;"
+ "Extended Properties=Excel 8.0;";

No comments:

About Me

My photo
Developer (Ruby on Rails, iOS), musician/composer, Buddhist, HSP, Vegan, Aspie.