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
- Returning DataTable
- Reading XLS file from called YourFile.xls from F Drive.
- 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
Leave a Reply