Code snippet for Fetching Data from Excel using ADO.Net

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

4 responses to “Code snippet for Fetching Data from Excel using ADO.Net”

  1. […] Code snippet for Fetching Data from Excel using ADO.Net (Dhananjay Kumar) […]

  2. This code dont func on Windows 64 bit server

  3. […] Code snippet for Fetching Data from Excel using ADO.Net […]

  4. 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 )

Connecting to %s