Click here to Skip to main content
15,896,432 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
how to bind only specific colums in excel sheet to datagridview.? while i'm binding whole excel sheet is displayed in datagridview.

Code:
C#
private void button2_Click(object sender, EventArgs e)
        {
            try
            {

                OpenFileDialog openfiledialog1 = new OpenFileDialog();
                openfiledialog1.ShowDialog();
                string filePath = openfiledialog1.FileName;
                string extension = Path.GetExtension(filePath);
                string header = rdbuttonyes.Checked ? "YES" : "NO";
                string conStr, sheetName;

                conStr = string.Empty;
                switch (extension)
                {

                    case ".xls": //Excel 97-03
                        conStr = string.Format(Excel03ConString, filePath, header);
                        break;

                    case ".xlsx": //Excel 07
                        conStr = string.Format(Excel07ConString, filePath, header);
                        break;
                }

                //Get the name of the First Sheet.
                using (OleDbConnection con = new OleDbConnection(conStr))
                {
                    using (OleDbCommand cmd = new OleDbCommand())
                    {
                        cmd.Connection = con;
                        con.Open();
                        DataTable dtExcelSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                        sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                        con.Close();
                    }
                }

                //Read Data from the First Sheet.
                using (OleDbConnection con = new OleDbConnection(conStr))
                {
                    using (OleDbCommand cmd = new OleDbCommand())
                    {
                        using (OleDbDataAdapter oda = new OleDbDataAdapter())
                        {
                            DataTable dt = new DataTable();
                            cmd.CommandText = "SELECT * From [" + sheetName + "]";
                            cmd.Connection = con;
                            con.Open();
                            oda.SelectCommand = cmd;
                            oda.Fill(dt);
                            //dgvExcelResult.SelectedRows.ToString();
                            con.Close();
                            //dgvExcelResult.AutoGenerateColumns = false;
                            //Populate DataGridView.
                           

                            dgvExcelResult.DataSource = dt;
                            dgvExcelResult.Visible = true;

                          
                        }
                    }
                }
            }
            catch(Exception)
            {
               
            }
        }
Posted
Updated 12-Mar-15 1:23am
v3

1 solution

The following selects all colums.
C#
cmd.CommandText = "SELECT * From [" + sheetName + "]";

Change it to select only the columns you need, e.g.
C#
cmd.CommandText = "SELECT Name, Age, Gender From [" + sheetName + "]";
 
Share this answer
 
Comments
Member 10371894 12-Mar-15 7:17am    
i tried not working
Richard MacCutchan 12-Mar-15 7:32am    
What did you try, what is not working? Please update your question with the proper details.
Member 10371894 12-Mar-15 7:39am    
i tried by giving excel sheet Header ..

i changed my Query like this cmd.CommandText = "SELECT Productfamily From [" + sheetName + "]";

but showing error

No value given for one or more required parameters.

Richard MacCutchan 12-Mar-15 7:51am    
What error, where does it occur?
Please do not expect us to guess what is happening on your system.
Member 10371894 12-Mar-15 7:59am    
this is the exception when i give column filed instead of SELECT * From [" + sheetName + "]"

exception: No value given for one or more required parameters.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900