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
               OleDbDataAdapter dbAdapter =
                   new OleDbDataAdapter
                       ("SELECT * FROM [Sheet1$]", excelConnection);
return dt;

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