Reading from excel sheet to visual c# datagridview using excel object library

Hi Amit,

Thanks for the code.

But the code you provided was using OleDbConnection while I want to use Excel object library.
I'm not sure, but I heard that using Excel object library to read excel sheets is easier than using OleDb.

Many thanks,
Ali  

Hi friend,

Use below code:
 System.Data.DataTable dsImport = new System.Data.DataTable();
                string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source= ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";";

//The above connection string is for reading the excel 2007 format if you have 97-2003 format then use Extended Properties=\"Excel 10.0 and rest remain the same

                OleDbConnection connection = new OleDbConnection();
                connection.ConnectionString = connectionString;
                connection.Open();

                 //Get the all sheets in the excel workbook
                 dtSchema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                // Get the first sheet name
                 string sheetName = dtSchema.Rows[0]["TABLE_NAME"].ToString();

                DbCommand command = connection.CreateCommand();
                //Create commnad object ready to read from a sheet
                command.CommandText = "SELECT NUMBER FROM [" + sheetName + "]";

                command.Connection = connection;

                //adapter.SelectCommand = command;
               
                DataSet cities = new DataSet();
                Cursor.Current = Cursors.WaitCursor;
                //adapter.Fill(cities);
                long a = 0;
                DbDataReader dr = command.ExecuteReader();
                GridView1.DataSorce=dr;

Don't forget to mark "Do you like this answer"
Thanks.