How to import excel sheet in your project in C#?

Follow these steps:

• Add Reference "Microsoft Excel 12.0 Object Library" from COM. To know how to add reference click: http://gsbprogramming.blogspot.in/2014/07/how-to-add-reference-to-your-project-in.html

• Add namespace : Using System.Data.Oledb;

• Define the path of your Excel file as

string path="Provider=Microsoft.Ace.OLEDB.12.0; Data Source="+"path"+"; Extended Properties=\"Excel 8.0; HDR=Yes;\";";  

For Example

string path="Provider=Microsoft.Ace.OLEDB.12.0; Data Source="+"d:\\list.xlsx"+"; Extended Properties=\"Excel 8.0; HDR=Yes;\";";

Provider=Microsoft.Ace.OLEDB.12.0;  is used as provider name for excel sheet (.xlsx)

Data Source="+"d:\\list.xlsx"+";  tells the path of excel file (Here file is in d drive named as list.xlsx, to show \ we have to use \\)

HDR=Yes; tells that first row of excel sheet will be header in gridview.

•  Define Oledb connection and Oledb command as:

 OleDbConnection  conn = new OleDbConnection(path); 

 OleDbCommand  cmd= new  OleDbCommand("Select * from[" + "sheet1" + "$]", conn);

Here we have used sheet1 of excel file list.xlsx. 

If you have sheet named as mysheet, then you will weite "mysheet" instead of   "sheet1".

"Select * from[" + "sheet1" + "$]" will select all data from excel sheet whose location is specified by conn

•  Now we require  OleDbDataReader to store the result of  cmd i.e. OleDbCommand as:

 OleDbDataReader odr = cmd.ExecuteReader( ); 
This will store all data of select command in odr.
ExceuteReader( ) is used when we used a select query.

• Now assign this datasource i.e. odr to Data Grid View like:

dataGridView1.DataSource = odr;

for asp.net  add another statement:

dataGridView1.DataBind( );


This will show the entire excel sheet's data in grid view.