How to access dynamically added DataGridView inside a TabPage and get values?

Question

I have made a program that can import an excel file and insert all the sheets in a dynamically added tabpage with a dynamically added datagridview.

I want to insert the values of the KPI column depending on the tab selected on my combobox and everytime you change the tabpage, the combobox changes too.

Here are my codes:

ReadExcel Method

  public static void ReadExcel(ComboBox cboSheet, TabControl tabCon)
    {
       try
       {
           OpenFileDialog openFileDialog = new OpenFileDialog();
           openFileDialog.Filter = "Excel Files| *.xls; *xlsx";
           openFileDialog.ShowDialog();

           if (!string.IsNullOrEmpty(openFileDialog.FileName))
           {
               OleDbcon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + openFileDialog.FileName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'");
               OleDbcon.Open();

               DataTable dt = OleDbcon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
               OleDbcon.Close();

               cboSheet.Items.Clear();

               int width = 1330;
               int height = 565;
               //1338, 590

               for (int i = 0; i < dt.Rows.Count; i++)
               {
                    if (!dt.Rows[i]["Table_Name"].ToString().Contains("FilterDatabase") && !dt.Rows[i]["Table_Name"].ToString().EndsWith("$'"))
                    {
                       String sheetName = dt.Rows[i]["Table_Name"].ToString();
                       sheetName = sheetName.Substring(0, sheetName.Length - 1);
                        //cboSheet.Items.Add(sheetName);

                        TabPage tp = new TabPage(sheetName);

                        DataGridView dataGridView = new DataGridView();
                        tp.Controls.Add(dataGridView);
                        tabCon.Controls.Add(tp);
                        CreateDataGrid(dataGridView, sheetName);
                        dataGridView.Size = new Size(width, height);
                        System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle = new System.Windows.Forms.DataGridViewCellStyle();

                        //KPI Column
                    }
               }
           }
       }

       catch (Exception e)
       {
           MessageBox.Show(e.ToString());

       }

    }

---

CreateDataGrid Method

  public static void CreateDataGrid(DataGridView dataGridView1, string TabName)
    {
        OleDbDataAdapter oleAdapt = new OleDbDataAdapter("Select * from [" + TabName + "$]", ExcelMethods.OleDbcon);


        DataTable dt = new DataTable();

        oleAdapt.Fill(dt);

        dataGridView1.DataSource = dt;
    }

Answer 1

Below is a simple win form program that demonstrates what I described in my comments. The form contains an empty ComboBox named cb_KPI. In addition it contains an empty TabControl named tabControl1.

A global variable is created AllDataTables. AlldataTables is a DataSet such that each DataTable in the DataSet is an existing worksheet from the user selected Excel workbook. This is used to hold the DataTable for each TabPage… i.e. AllDataTables.Tables[0] is the DataTable for tabControl1.TabPages[0].

When the form is loaded, AllDataTables is filled with the user selected workbook’s worksheets… then, a loop through all the tables adds a new TabPage to the tabControl1 for each table. Finally, the cb_KPI combo box is updated to contain the values for the currently selected TabPage.

DataSet AllDataTables;

public Form1() {
  InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e) {
  AllDataTables = GetDSFromExcel();
  foreach (DataTable dt in AllDataTables.Tables) {
    tabControl1.TabPages.Add(GetTabPageWithGrid(dt));
  }
  cb_KPI.DataSource = SetKPI_ValuesForComboBox(AllDataTables.Tables[tabControl1.SelectedIndex]);
}

The GetDSFromExcel method returns a DataSet from a user selected Excel file where each DataTable in the DataSet is a worksheet from the user selected Excel file.

private DataSet GetDSFromExcel() {
  DataSet ds = new DataSet();
  try {
    OpenFileDialog openFileDialog = new OpenFileDialog {
      Filter = "Excel Files| *.xls; *xlsx"
    };
    openFileDialog.ShowDialog();
    if (!string.IsNullOrEmpty(openFileDialog.FileName)) {
      using (OleDbConnection OleDbcon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + openFileDialog.FileName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'")) {
        OleDbcon.Open();
        DataTable WorkbookSheetInfo = OleDbcon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        String sheetName;
        DataTable dt;
        OleDbDataAdapter oleAdapt;
        for (int i = 0; i < WorkbookSheetInfo.Rows.Count; i++) {
          if ((WorkbookSheetInfo.Rows[i]["Table_Name"].ToString().EndsWith("$'")) ||
              (WorkbookSheetInfo.Rows[i]["Table_Name"].ToString().EndsWith("$"))) {
            sheetName = WorkbookSheetInfo.Rows[i]["Table_Name"].ToString();
            dt = new DataTable(sheetName.Replace("'", "").Replace("$", ""));
            oleAdapt = new OleDbDataAdapter("Select * from [" + sheetName + "]", OleDbcon);
            oleAdapt.Fill(dt);
            ds.Tables.Add(dt);
          }
        }
      }
    }
  }
  catch (Exception e) {
    MessageBox.Show("ReadExcelIntoDS_Error" + e.ToString());
  }
  return ds;
}

The GetTabPageWithGrid method is given a DataTable and returns a single TabPage with a DataGridView such that the DataGridView’s DataSource will be the given DataTable

private TabPage GetTabPageWithGrid(DataTable dt) {
  TabPage tp = new TabPage(dt.TableName.Replace("'", "").Replace("$", ""));
  tp.Controls.Add(GetDGV(dt));
  return tp;
}

private DataGridView GetDGV(DataTable dt) {
  return new DataGridView {
    Size = new Size(1000, 400),
    Name = "datagridView" + dt.TableName,
    DataSource = dt,
    ScrollBars = ScrollBars.Both
  };
}

Next is the needed code to keep the cb_KPI combo box consistent when the user selects a different tab page. Wiring up the TabControls SelectedIndexChanged event might be helpful here, the code simply loops through the DataTable and gathers all the different “KPI” string values and stores them into a List<string> then returns the completed list. This new list is then used as a DataSource for the cb_KPI combo box. Obviously, if the DataTable is large or there are many different tables, you may consider saving these lists of strings instead of regenerating the list every time the user selects a different tab. NOTE: The code below assumes there is a column in the table named “KPI”.

private void tabControl1_SelectedIndexChanged(object sender, EventArgs e) {
  cb_KPI.DataSource = SetKPI_ValuesForComboBox(AllDataTables.Tables[tabControl1.SelectedIndex]);
}

private List<string> SetKPI_ValuesForComboBox(DataTable dt) {
  List<string> kpiList = new List<string> {
    "All"
  };
  foreach (DataRow row in dt.Rows) {
    if (!kpiList.Contains(row["KPI"].ToString()))
      kpiList.Add(row["KPI"].ToString());
  }
  return kpiList;
}

I hope this helps.