Transfer data from Excel to dataGridView

Question

I have a problem when I want to transfer data from Excel to dataGridView in C #.

My Excel column has numeric and alphanumeric values. But, for example, if there are 3 digits and 2 alphanumeric values ​​in a column, then only numbers are displayed in the dataGridView, and vice versa. Why aren't all the values ​​shown? The following is what happens:

Excel Column: DataGridView Column: 45654 45654 P745K 31233 31233 23111 23111 45X2Y 

Here is my code for loading dataGridView:

 string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\test.xls;Extended Properties=""Excel 8.0;HDR=YES;"""; DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb"); DbDataAdapter adapter = factory.CreateDataAdapter(); DbCommand selectCommand = factory.CreateCommand(); selectCommand.CommandText = "SELECT * FROM [sheet1$]"; DbConnection connection = factory.CreateConnection(); connection.ConnectionString = connectionString; selectCommand.Connection = connection; adapter.SelectCommand = selectCommand; data = new DataSet(); adapter.Fill(data); dataGridView1.DataSource = data.Tables[0].DefaultView; 

Answer 1

You can simply add "IMEX = 1" to the connection string, for example ↓

 string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\test.xls;Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""; 

It will force read all values ​​as strings.

Answer 2

Try formatting all cells as text in an excel sheet before importing. (What could you do manually or programmatically)