Reader Level:
Article

Code Snippet for Fetching Data from Excel Using ADO.Net

By Dhananjay Kumar on Jun 06, 2011
How to use 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...

Dhananjay Kumar

Dhananjay kumar is 6 times Microsoft MVP, 7 times C# Corner MVP. He works as consultant for Infragistics in India. He is known for mentoring teams on various technologies.

Personal Blog: http://debugmode.net/

COMMENT USING