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
4 responses to “Code snippet for Fetching Data from Excel using ADO.Net”
[…] Code snippet for Fetching Data from Excel using ADO.Net (Dhananjay Kumar) […]
This code dont func on Windows 64 bit server
[…] Code snippet for Fetching Data from Excel using ADO.Net […]
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.