如何将Excel工作表特定的列绑定到C＃中的datagridview？ [英] how to bind Excel sheet specific Column to datagridview in C#?

问题描述

如何仅将Excel工作表中的特定列绑定到datagridview。我绑定整个excel表格显示在datagridview中。



代码：

  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; 
 开关（分机）
 {
 
 案例  。xls： //  < span class =code-comment> Excel 97-03  
 conStr =  string  .Format（Excel03ConString，filePath，header）; 
  break ; 
 
  case   。xlsx ： //   Excel 07  
 conStr =  string  .Format（Excel07ConString，filePath，header）; 
  break ; 
} 
 
  //  获取第一张表的名称。 
 使用（OleDbConnection con =  new  OleDbConnection（conStr））
 {
 使用（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（）; 
} 
} 
 
  //  从第一张表中读取数据。 
 使用（OleDbConnection con =  new  OleDbConnection（conStr））
 {
 使用（OleDbCommand cmd =  new  OleDbCommand（））
 { 
 使用（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;  
  //  填充DataGridView。 
 
 
 dgvExcelResult.DataSource = dt; 
 dgvExcelResult.Visible =  true ; 
 
 
} 
} 
} 
} 
  catch （例外） 
 {
 
} 
} 

解决方案

以下选择所有列。

 cmd.CommandText =   SELECT * From [ + sheetName +  ]; 

更改它以仅选择您需要的列，例如

 cmd.CommandText =  < span class =code-string> SELECT名称，年龄，性别来自[ + sheetName +   ]; 

how to bind only specific colums in excel sheet to datagridview.? while i'm binding whole excel sheet is displayed in datagridview.

Code:

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)
            {
               
            }
        }

解决方案

The following selects all colums.

cmd.CommandText = "SELECT * From [" + sheetName + "]";

Change it to select only the columns you need, e.g.

cmd.CommandText = "SELECT Name, Age, Gender From [" + sheetName + "]";

这篇关于如何将Excel工作表特定的列绑定到C＃中的datagridview？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！