Dhananjay Kumar

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 

I told him to add below reference

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