How to add data from Excel sheet to a C# program?

I have a coding article for all the geeks out there. I had a strange requirement to access data from a dynamic excel sheet on the fly and use it within my .net program.

Here's how it can be done easily in C#.


string strConn;

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +

"Data Source=C:\\Master Report List.xls;" +

"Extended Properties=Excel 8.0;";

//You must use the $ after the object you reference in the spreadsheet

OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Production Reports$]", strConn);

DataSet myDataSet = new DataSet();

myCommand.Fill(myDataSet, "ExcelInfo");

DataGrid1.DataSource = myDataSet.Tables["ExcelInfo"].DefaultView;

DataGrid1.DataBind();