Code Snippet for Fetching Data from Excel Using ADO.Net

Today one of my team members asked me a very simple yet very important question:

"How could we fetch Excel Records using ADO.Net? Could you give me code snippet of same? "

I replied to him; it is pretty possible using OledDbConnection.

I told him to add the following reference.

I gave him the following straightforward code snippet. This function is:

1. Returning DataTable
2. Reading XLS file called YourFile.xls from F Drive.
3. Reading Sheet1

public static  DataTable  GetItemsFromExcel1()
       {

                      DataTable dt = new DataTable();
 
           OleDbConnection excelConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;"
                                                + @"Data Source=F:\YourFile.xls;"
                                                + @"Extended Properties=""Excel
8.0;HDR=Yes;IMEX=1;""");
 
           excelConnection.Open();
           try
           {
               OleDbDataAdapter dbAdapter =
                   new OleDbDataAdapter
                       ("SELECT * FROM [Sheet1$]", excelConnection);
               dbAdapter.Fill(dt);
           }
           finally
           {
               excelConnection.Close();
           }
 
return dt;
}

After using this function in his code, he was very happy and paid my coffee bill...