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

 

01 public static  DataTable  GetItemsFromExcel1()
02        {
03  
04            List<Items> lstItems = new List<Items>();
05            Items item;
06            DataTable dt = new DataTable();
07  
08            OleDbConnection excelConnection = new OleDbConnection (@"Provider=Microsoft.Jet.OLEDB.4.0;"
09                                                 + @"Data Source=F:\YourFile.xls;"
10                                                 + @"Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;""");
11  
12            excelConnection.Open();
13            try
14            {
15                OleDbDataAdapter dbAdapter =
16                    new OleDbDataAdapter
17                        ("SELECT * FROM [Sheet1$]", excelConnection);
18                dbAdapter.Fill(dt);
19            }
20            finally
21            {
22                excelConnection.Close();
23            }
24  
25 return dt;
26 }

 

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