萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> 程式語言綜合

使用 VB 實現類電子表格數據輸入界面的完整指南

前言

在應用程式開發中,經常需要處理大量數據輸入。若能在程式中提供類似電子表格的直觀輸入界面,將大幅提升使用者體驗與效率。雖然 VB 內建的 Grid 控制項僅具備顯示功能,但透過適當的改造,即可將其轉化為可編輯的試算表式介面。本文將詳細說明如何在 VB 5.0 環境下,結合 TextBox、Grid、ScrollBar 等控制項,打造一個功能完整的類試算表數據錄入元件。

設計思路與控制項佈局

核心概念是利用一個隱藏的 TextBox 作為實際編輯器,當使用者點選 Grid 某儲存格時,將 TextBox 移動到對應位置並顯示該儲存格內容,使用者編輯完成後再寫回 Grid。這樣既保留了 Grid 的表格顯示優勢,又獲得了文字方塊的編輯能力。

所需控制項

  • Form1:主視窗
  • Grid1:MSFlexGrid 或同類網格控制項,用於顯示表格
  • Text1:TextBox,作為儲存格編輯器
  • Command1、Command2:按鈕,分別對應「確定」與「結束」
  • VScroll1、HScroll1:垂直與水平捲軸,處理大量數據捲動

關鍵程式碼實作

1. 變數宣告與初始化

Dim inputColNum As Integer, inputRowNum As Integer

2. Form_Load:介面佈局與屬性設定

在表單載入時,設定 Grid 的儲存格寬高、總行列數、可視範圍,並根據解析度計算控制項尺寸與位置,同步捲軸範圍。

Private Sub Form_Load()
    Grid1.ColWidth(0) = 540
    Grid1.RowHeight(0) = 216
    inputColNum = 13
    inputRowNum = 15
    Grid1.Cols = 20
    Grid1.Rows = 20

    ' 依解析度微調(800×600 為 12,640×480 為 15)
    Const twipsPerPixel = 12

    Grid1.Height = (inputRowNum + 1) * (Grid1.RowHeight(0) + Grid1.GridLineWidth * twipsPerPixel) + HScroll1.Height
    Grid1.Width = (inputColNum + 1) * (Grid1.ColWidth(0) + Grid1.GridLineWidth * twipsPerPixel) + VScroll1.Width

    HScroll1.Width = Grid1.Width - VScroll1.Width
    VScroll1.Height = Grid1.Height - HScroll1.Height

    HScroll1.Left = Grid1.Left
    HScroll1.Top = Grid1.Top + Grid1.Height - HScroll1.Height
    VScroll1.Left = Grid1.Left + Grid1.Width - VScroll1.Width
    VScroll1.Top = Grid1.Top

    HScroll1.Min = 1
    HScroll1.Max = Grid1.Cols - inputColNum
    VScroll1.Min = 1
    VScroll1.Max = Grid1.Rows - inputRowNum

    ' TextBox 初始大小與位置對齊第一個資料儲存格
    Text1.Width = Grid1.ColWidth(0)
    Text1.Height = Grid1.RowHeight(0)
    Text1.Left = Grid1.Left + Grid1.ColWidth(0) + Grid1.GridLineWidth * twipsPerPixel
    Text1.Top = Grid1.Top + Grid1.RowHeight(0) + Grid1.GridLineWidth * twipsPerPixel
    Text1.Visible = True

    ' 初始化標題列與行號
    For i = 1 To Grid1.Cols - 1
        Grid1.Col = i
        Grid1.Row = 0
        Grid1.Text = Str(i)
        Grid1.Col = 0
        Grid1.Row = i
        Grid1.Text = Str(i)
    Next i

    Grid1.Col = 1
    Grid1.Row = 1
    Grid1.SelStartCol = 1
    Grid1.SelStartRow = 1
    Grid1.LeftCol = 1
    Grid1.TopRow = 1
End Sub

3. Grid1_MouseDown:滑鼠點選啟動編輯

當使用者在 Grid 上按下滑鼠,取得目前選取儲存格的行列,將內容填入 TextBox,並計算 TextBox 應顯示的座標。

Private Sub Grid1_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Grid1.Col = Grid1.SelStartCol
    Grid1.Row = Grid1.SelStartRow
    Text1.Text = Grid1.Text

    Dim colNum As Integer, rowNum As Integer
    colNum = Grid1.Col - Grid1.LeftCol + 1
    rowNum = Grid1.Row - Grid1.TopRow + 1

    Text1.Left = Grid1.Left + (Grid1.ColWidth(0) + Grid1.GridLineWidth * 12) * colNum
    Text1.Top = Grid1.Top + (Grid1.RowHeight(0) + Grid1.GridLineWidth * 12) * rowNum
End Sub

4. Grid1_MouseUp:釋放滑鼠時讓 TextBox 取得焦點

Private Sub Grid1_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Text1.SetFocus
End Sub

5. HScroll1_Change / VScroll1_Change:捲軸捲動同步

水平與垂直捲軸變更時,更新 Grid 的 LeftCol / TopRow,並同步調整選取儲存格與 TextBox 內容。

Private Sub HScroll1_Change()
    Dim startCol As Integer
    startCol = Grid1.LeftCol
    Grid1.LeftCol = HScroll1.Value
    Grid1.SelStartCol = Grid1.SelStartCol + Grid1.LeftCol - startCol
    Grid1.Col = Grid1.SelStartCol
    Text1.Text = Grid1.Text
End Sub

Private Sub VScroll1_Change()
    Dim startRow As Integer
    startRow = Grid1.TopRow
    Grid1.TopRow = VScroll1.Value
    Grid1.SelStartRow = Grid1.SelStartRow + Grid1.TopRow - startRow
    Grid1.Row = Grid1.SelStartRow
    Text1.Text = Grid1.Text
End Sub

6. Text1_Change:即時回寫 Grid

Private Sub Text1_Change()
    Grid1.Text = Text1.Text
End Sub

7. Text1_KeyDown:鍵盤導航(上下左右、Enter、Tab)

處理方向鍵移動儲存格,並自動捲動捲軸;Enter/Tab 移至下一儲存格;Esc 取消編輯。

Private Sub Text1_KeyDown(KeyCode As Integer, Shift As Integer)
    Select Case KeyCode
        Case vbKeyUp    ' 向上
            Grid1.Text = Text1.Text
            If Grid1.SelStartRow < 2 Then
                If Grid1.TopRow > 1 Then VScroll1.Value = VScroll1.Value - 1
            Else
                If Grid1.TopRow = Grid1.SelStartRow Then
                    VScroll1.Value = VScroll1.Value - 1
                Else
                    Grid1.SelStartRow = Grid1.SelStartRow - 1
                End If
            End If
            Call UpdateTextBoxPosition

        Case vbKeyDown  ' 向下
            Grid1.Text = Text1.Text
            If Grid1.SelStartRow > inputRowNum - 1 Then
                If Grid1.TopRow < Grid1.Rows - inputRowNum Then
                    VScroll1.Value = VScroll1.Value + 1
                Else
                    Grid1.SelStartRow = Grid1.Rows - 1
                End If
            Else
                If Grid1.TopRow = Grid1.SelStartRow - inputRowNum Then
                    VScroll1.Value = VScroll1.Value + 1
                Else
                    Grid1.SelStartRow = Grid1.SelStartRow + 1
                End If
            End If
            Call UpdateTextBoxPosition

        Case vbKeyLeft  ' 向左
            Grid1.Text = Text1.Text
            If Grid1.SelStartCol > 1 Then
                Grid1.SelStartCol = Grid1.SelStartCol - 1
            ElseIf Grid1.LeftCol > 1 Then
                HScroll1.Value = HScroll1.Value - 1
            End If
            Call UpdateTextBoxPosition

        Case vbKeyRight ' 向右
            Grid1.Text = Text1.Text
            If Grid1.SelStartCol < inputColNum Then
                Grid1.SelStartCol = Grid1.SelStartCol + 1
            ElseIf Grid1.LeftCol < Grid1.Cols - inputColNum Then
                HScroll1.Value = HScroll1.Value + 1
            End If
            Call UpdateTextBoxPosition

        Case vbKeyReturn, vbKeyTab ' 移至下一儲存格
            Grid1.Text = Text1.Text
            If Grid1.SelStartCol < inputColNum Then
                Grid1.SelStartCol = Grid1.SelStartCol + 1
            ElseIf Grid1.SelStartRow < inputRowNum Then
                Grid1.SelStartCol = 1
                Grid1.SelStartRow = Grid1.SelStartRow + 1
            End If
            Call UpdateTextBoxPosition
    End Select
End Sub

Private Sub UpdateTextBoxPosition()
    Grid1.Col = Grid1.SelStartCol
    Grid1.Row = Grid1.SelStartRow
    Dim colNum As Integer, rowNum As Integer
    colNum = Grid1.Col - Grid1.LeftCol + 1
    rowNum = Grid1.Row - Grid1.TopRow + 1
    Text1.Text = Grid1.Text
    Text1.Left = Grid1.Left + (Grid1.ColWidth(0) + Grid1.GridLineWidth * 12) * colNum
    Text1.Top = Grid1.Top + (Grid1.RowHeight(0) + Grid1.GridLineWidth * 12) * rowNum
    Text1.SetFocus
End Sub

進階優化建議

  • 資料驗證:在 Text1_LostFocus 或 Text1_KeyDown (Enter) 時加入型別、範圍檢查。
  • Undo/Redo:紀錄編輯歷史,支援復原。
  • 複製/貼上:整合 Clipboard 物件,支援多儲存格批次操作。
  • 效能:大量資料時考慮虛擬模式,僅渲染可視區域。
  • 樣式:利用 Grid 的 CellFontAlignment、CellBackColor 等屬性美化外觀。

結語

透過「Grid 顯示 + TextBox 編輯 + 捲軸同步」的經典組合,我們在 VB 5.0 中成功打造出一個具備試算表操作感的數據輸入介面。此架構概念亦可移植至 VB6、VBA 或 .NET (DataGridView + EditingControl) 等新平台。掌握核心邏輯後,開發者可依專案需求擴充更豐富的試算表功能。

copyright © 萬盛學電腦網 all rights reserved