在tabcontrol中的多个选项卡中使用一个datagridview [英] Using one datagridview across multiple tabs in tabcontrol
本文介绍了在tabcontrol中的多个选项卡中使用一个datagridview的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我在TabControl1的TabPage1上有一个datagridview（datagridview1），它是我的'Records'表单。目前，我使用一个在页面加载事件上执行的循环来填充datagridview，并从包含资源的excel文件加载数据。在循环中，我还为Excel文件具有的每个工作表和工作表名称创建并命名选项卡。目前excel文件中的两张表名称为2012和2013。 TabControl中的第一个选项卡被命名为2013，第二个选项卡动态创建并命名为2012，因为我通过将当前年份表设置为第一个选项卡并将2013表中的数据读入datagridview，从而启动循环我的循环。我需要帮助的是在每个新选项卡上使用相同的datagridview，但更新它以显示相应的年份（工作表）数据。
I have a datagridview (datagridview1) on TabPage1 of TabControl1 which is one my 'Records' form. Currently I populate the datagridview with a loop that executes on the page load event and loads the data in from an excel file that it is an included resource. In the loop I also have tabs created and named for each sheet and sheet name that the excel file has. The two sheets I have in the excel file current are name "2012" and "2013". the first tab in the TabControl is named "2013" and the second tab dynamically created and named "2012" because I start the loop by setting the current year sheet to the first tab and the data in the 2013 sheet is read into the datagridview via my loop. What I need help doing is using the same datagridview on each new tab, but updating it to show the respective year's (sheet) data.
这是我的代码记录表单
Imports System.Data.OleDb
Imports Microsoft.Office.Interop.Excel
Public Class Records
Dim excel_app As Excel.Application
Dim workbook As Excel.Workbook
Dim sheet_name As String
Dim sheet As Excel.Worksheet
Dim ColumnCount, RowCount, TotalCellCount As Long
Dim yearstamp As String = _
DateTime.Now.ToString("yyyy")
Dim exeDir As New IO.FileInfo(Reflection.Assembly.GetExecutingAssembly.FullName)
Dim xlPath = IO.Path.Combine(exeDir.DirectoryName, "Records.xlsx")
Sub PopulateDataGridView()
'Loop through each column
Dim cIndex As Integer = 0
Dim rIndex As Integer = 0
While cIndex < DataGridView1.ColumnCount
'Loop through and populate each row in column
rIndex = 0
While rIndex < DataGridView1.RowCount
If cIndex = 0 Then
'Set row header titles
DataGridView1.Rows.Item(rIndex).HeaderCell.Value = sheet.Range("A1").Offset(rIndex + 1, cIndex).Value()
DataGridView1.Rows(rIndex).Cells(cIndex).Value = sheet.Range("A1").Offset(rIndex + 1, cIndex + 1).Value()
End If
If cIndex > 0 Then
DataGridView1.Rows(rIndex).Cells(cIndex).Value = sheet.Range("A1").Offset(rIndex + 1, cIndex + 1).Value()
End If
'Set column header title
DataGridView1.Columns(cIndex).HeaderText = sheet.Range("A1").Offset(0, cIndex + 1).Value
'Change last cell (Result) color Red or Green to represent positive gain or negative loss
If rIndex = RowCount - 2 Then
If DataGridView1.Rows(rIndex).Cells(cIndex).Value < 0 Then
DataGridView1.Item(cIndex, rIndex).Style.BackColor = Color.Red
DataGridView1.Item(cIndex, rIndex).Style.ForeColor = Color.White
End If
If DataGridView1.Rows(rIndex).Cells(cIndex).Value > 0 Then
DataGridView1.Item(cIndex, rIndex).Style.BackColor = Color.Green
DataGridView1.Item(cIndex, rIndex).Style.ForeColor = Color.White
End If
If DataGridView1.Rows(rIndex).Cells(cIndex).Value = 0 Then
DataGridView1.Rows(rIndex).Cells(cIndex).Value = "Broke Even"
End If
End If
rIndex = rIndex + 1
End While
'Make column unsortable
DataGridView1.Columns(cIndex).SortMode = DataGridViewColumnSortMode.NotSortable
cIndex = cIndex + 1
End While
End Sub
Private Sub Records_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
' Get the Excel application object.
excel_app = New Excel.Application
' Make Excel visible (optional).
excel_app.Visible = False
' Open the workbook.
workbook = excel_app.Workbooks.Open(xlPath)
sheet_name = yearstamp
'Adds tabs (if needed) and names each tab after respective excel file sheet
Dim name As String
For w As Integer = 1 To workbook.Sheets.Count
name = workbook.Sheets(w).name
If Not (TabControl1.TabPages.Count = workbook.Sheets.Count) Then
TabPage1.Text = sheet_name
' Create the new tab page
Dim tab As New TabPage(name)
' Add the tabpage to the existing TabCrontrol
TabControl1.TabPages.Add(tab)
End If
Next w
sheet = excel_app.Worksheets(sheet_name)
ColumnCount = sheet.Range("A1").CurrentRegion.Columns.Count
RowCount = sheet.Range("A1").CurrentRegion.Rows.Count
DataGridView1.ColumnCount = ColumnCount - 1
DataGridView1.RowCount = RowCount - 1
DataGridView1.ColumnHeadersVisible = True
DataGridView1.RowHeadersVisible = True
TotalCellCount = DataGridView1.ColumnCount * DataGridView1.RowCount
DataGridView1.RowHeadersWidthSizeMode = DataGridViewRowHeadersWidthSizeMode.DisableResizing
DataGridView1.AllowUserToResizeColumns = False
DataGridView1.AllowUserToResizeRows = False
DataGridView1.ReadOnly = True
DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
'Calls Loop to populate the datagridview
PopulateDataGridView()
DataGridView1.AutoResizeColumns()
'Resize all Row Headers so user can see Row Titles without resizing
DataGridView1.AutoResizeRowHeadersWidth(DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders)
End Sub
Private Sub dataGridView1_CellPainting(ByVal sender As System.Object, ByVal e As DataGridViewCellPaintingEventArgs) Handles DataGridView1.CellPainting
If e.ColumnIndex > -1 And e.RowIndex > -1 Then
If DataGridView1.Item(e.ColumnIndex, e.RowIndex).Value = " $0.00" Then
DataGridView1.Item(e.ColumnIndex, e.RowIndex).Style.ForeColor = Color.White
DataGridView1.Item(e.ColumnIndex, e.RowIndex).Style.BackColor = DataGridView1.Item(e.ColumnIndex, e.RowIndex).Style.ForeColor
DataGridView1.Item(e.ColumnIndex, e.RowIndex).Style.SelectionForeColor = SystemColors.Highlight
DataGridView1.Item(e.ColumnIndex, e.RowIndex).Style.SelectionBackColor = DataGridView1.Item(e.ColumnIndex, e.RowIndex).Style.SelectionForeColor
End If
End If
End Sub
我正在编辑这个仅附加解决方案，以防其他人需要一些指导。下面的代码允许我的datagridview被携带到哪个选项卡是活动的，并且还更新datagridview单元格以显示来自相应的excel表的值的数据。我希望这可以帮助！
I'm editing this only to append the solution in case anyone else needs some guidance. The code below allows my datagridview to be carried to whichever tab is active and also updates the datagridview cells to show the data from the corresponding excel sheet's values. I hope this is able to help!
Imports System.Data.OleDb
Imports Microsoft.Office.Interop.Excel
Public Class Records
Dim excel_app As Excel.Application
Dim workbook As Excel.Workbook
Dim sheet_name As String
Dim sheet As Excel.Worksheet
Dim ColumnCount, RowCount, TotalCellCount As Long
Dim yearstamp As String = _
DateTime.Now.ToString("yyyy")
Dim exeDir As New IO.FileInfo(Reflection.Assembly.GetExecutingAssembly.FullName)
Dim xlPath = IO.Path.Combine(exeDir.DirectoryName, "Records.xlsx")
Sub PopulateDataGridView()
'Loop through each column
Dim cIndex As Integer = 0
Dim rIndex As Integer = 0
While cIndex < DataGridView1.ColumnCount
'Loop through and populate each row in column
rIndex = 0
While rIndex < DataGridView1.RowCount
If cIndex = 0 Then
'Set row header titles
DataGridView1.Rows.Item(rIndex).HeaderCell.Value = sheet.Range("A1").Offset(rIndex + 1, cIndex).Value()
DataGridView1.Rows(rIndex).Cells(cIndex).Value = sheet.Range("A1").Offset(rIndex + 1, cIndex + 1).Value()
End If
If cIndex > 0 Then
DataGridView1.Rows(rIndex).Cells(cIndex).Value = sheet.Range("A1").Offset(rIndex + 1, cIndex + 1).Value()
End If
'Set column header title
DataGridView1.Columns(cIndex).HeaderText = sheet.Range("A1").Offset(0, cIndex + 1).Value
'Change last cell (Result) color Red or Green to represent positive gain or negative loss
If rIndex = RowCount - 2 Then
If DataGridView1.Rows(rIndex).Cells(cIndex).Value < 0 Then
DataGridView1.Item(cIndex, rIndex).Style.BackColor = Color.Red
DataGridView1.Item(cIndex, rIndex).Style.ForeColor = Color.White
End If
If DataGridView1.Rows(rIndex).Cells(cIndex).Value > 0 Then
DataGridView1.Item(cIndex, rIndex).Style.BackColor = Color.Green
DataGridView1.Item(cIndex, rIndex).Style.ForeColor = Color.White
End If
If DataGridView1.Rows(rIndex).Cells(cIndex).Value = 0 Then
DataGridView1.Rows(rIndex).Cells(cIndex).Value = "Broke Even"
End If
End If
rIndex = rIndex + 1
End While
'Make column unsortable
DataGridView1.Columns(cIndex).SortMode = DataGridViewColumnSortMode.NotSortable
cIndex = cIndex + 1
End While
End Sub
Private Sub Records_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
' Get the Excel application object.
excel_app = New Excel.Application
' Make Excel visible (optional).
excel_app.Visible = False
' Open the workbook.
workbook = excel_app.Workbooks.Open(xlPath)
sheet_name = yearstamp
'Adds tabs (if needed) and names each tab after respective excel file sheet
Dim name As String
For w As Integer = 1 To workbook.Sheets.Count
name = workbook.Sheets(w).name
If Not (TabControl1.TabPages.Count = workbook.Sheets.Count) Then
TabPage1.Text = sheet_name
' Create the new tab page
Dim tab As New TabPage(name)
' Add the tabpage to the existing TabCrontrol
TabControl1.TabPages.Add(tab)
End If
Next w
sheet = excel_app.Worksheets(sheet_name)
ColumnCount = sheet.Range("A1").CurrentRegion.Columns.Count
RowCount = sheet.Range("A1").CurrentRegion.Rows.Count
DataGridView1.ColumnCount = ColumnCount - 1
DataGridView1.RowCount = RowCount - 1
DataGridView1.ColumnHeadersVisible = True
DataGridView1.RowHeadersVisible = True
TotalCellCount = DataGridView1.ColumnCount * DataGridView1.RowCount
DataGridView1.RowHeadersWidthSizeMode = DataGridViewRowHeadersWidthSizeMode.DisableResizing
DataGridView1.AllowUserToResizeColumns = False
DataGridView1.AllowUserToResizeRows = False
DataGridView1.ReadOnly = True
DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
'Calls Loop to populate the datagridview
PopulateDataGridView()
DataGridView1.AutoResizeColumns()
'Resize all Row Headers so user can see Row Titles without resizing
DataGridView1.AutoResizeRowHeadersWidth(DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders)
End Sub
Private Sub dataGridView1_CellPainting(ByVal sender As System.Object, ByVal e As DataGridViewCellPaintingEventArgs) Handles DataGridView1.CellPainting
If e.ColumnIndex > -1 And e.RowIndex > -1 Then
If DataGridView1.Item(e.ColumnIndex, e.RowIndex).Value = " $0.00" Then
DataGridView1.Item(e.ColumnIndex, e.RowIndex).Style.ForeColor = Color.White
DataGridView1.Item(e.ColumnIndex, e.RowIndex).Style.BackColor = DataGridView1.Item(e.ColumnIndex, e.RowIndex).Style.ForeColor
DataGridView1.Item(e.ColumnIndex, e.RowIndex).Style.SelectionForeColor = SystemColors.Highlight
DataGridView1.Item(e.ColumnIndex, e.RowIndex).Style.SelectionBackColor = DataGridView1.Item(e.ColumnIndex, e.RowIndex).Style.SelectionForeColor
End If
End If
End Sub
Private Sub TabControl1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TabControl1.SelectedIndexChanged
Dim tab As TabPage = Me.TabControl1.SelectedTab
If ((Not tab Is Nothing) AndAlso (Not tab.Controls.Contains(Me.DataGridView1))) Then
tab.Controls.Add(Me.DataGridView1)
End If
sheet_name = TabControl1.SelectedTab.Text
sheet = excel_app.Worksheets(sheet_name)
PopulateDataGridView()
End Sub
推荐答案
您可以将DataGridView移动到所选的TabPage：
You could just move the DataGridView to the selected TabPage:
Private Sub TabControl1_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles TabControl1.SelectedIndexChanged
Dim tab As TabPage = Me.TabControl1.SelectedTab
If ((Not tab Is Nothing) AndAlso (Not tab.Controls.Contains(Me.DataGridView1))) Then
tab.Controls.Add(Me.DataGridView1)
If (Me.isDataLoaded) Then
'TODO: Me.DataGridView1.DataSource = ?
End If
End If
End Sub
Private isDataLoaded As Boolean = False
这篇关于在tabcontrol中的多个选项卡中使用一个datagridview的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
