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  

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

 

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