Reading XLS spreadsheets from ASP.NET
Here's a tip for when you need to read a spreadsheet from ASP.NET (well, .NET really). Thinking about it now I'm surprised that I haven't needed to do this before.
The second or third article I found was John Kilgo's Reading Excel (.xls) Files with ADO.NET... and I admit I was sceptical when I read the method outlined there..but it works a treat. File this under ".NET voodoo"..
Simply put, you add a row of columns names and create a named range around that row plus the data that you want to import. Then you import it with this code...
string connstr = @"Provider=Microsoft.Jet.OleDb.4.0;data source=c:\my.xls;Extended Properties=Excel 8.0;";
string sql = "SELECT TOP 10 * FROM namedrangename"; // or whatever you called the named range
OleDbConnection conn = new OleDbConnection(connstr);
OleDbCommand cmd = new OleDbCommand(sql, conn);
conn.Open();
something.DataSource = cmd.ExecuteReader();
something.DataBind();
Not much is it? But I think it's a pretty cool trick. Thanks John!