excel - 在tabcontrol中,VB.Net 使用多个选项卡中的一个 datagridview

  显示原文与译文双语对照的内容
129 3

我在TabControl1的TabPage1上有一个 datagridview ( datagridview1 ),它是我的'唱片'表单。目前,我使用一个在页加载事件上执行的回循环填充 datagridview,并从excel文件中加载数据。在循环中,还为excel文件的每个工作表和工作表名称创建并命名了选项卡。excel文件当前中的两个工作表是名称" 2012"和" 2013"。TabControl中的第一个选项卡名为" 2013",第二个选项卡动态创建,因为我通过将当前工作表设置为第一个选项卡,并将 2013工作表中的数据读取到datagridview中,读取了工作表中的数据。我需要帮助做的是在每个新的选项卡上使用相同的datagridview,但是更新它显示年份数据。

这是我的记录表格

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值的数据。我希望这能帮助你 !

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
时间:原作者:0个回答

81 5

你可以只将DataGridView移动到选定的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 
原作者:
...