Today one of my team members asked me a very simple question though very important question,

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

I replied him; it is pretty possible using oledDbConnection  Smile

I told him to add below reference

image

I gave him below straight forward code snippet. This function is

  1. Returning DataTable
  2. Reading XLS file from called YourFile.xls from F Drive.
  3. Reading Sheet1
public static  DataTable  GetItemsFromExcel1()
       {

           List<Items> lstItems = new List<Items>();
           Items item;
           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  Smile

Advertisements

4 thoughts on “Code snippet for Fetching Data from Excel using ADO.Net

  1. Its like you learn my thoughts! You seem to understand so much approximately this,
    such as you wrote the guide in it or something.
    I believe that you just could do with a few % to pressure the message home a little bit,
    however instead of that, this is excellent blog. A great read.
    I’ll definitely be back.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s