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

Question

Hello I would like to do the following: Reading from excel sheet to visual c# datagridview using excel object library. Thanks in advance ali

Amit Choudhary · Answer

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.

Ali Alfaraj · Answer

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

Suthish Nair · Answer

If the query got resolved, then mark the post that helped you as Answer so will be helpful for others. Otherwise if you got the solution please share with us.