使用 DataGridView 控件建立一个类似电子表格的累计结余接口

图表 1

 

很多人希望如图表 1 所示，利用 DataGridView 控件制作一个类似电子表格的累计结余接口。图表 1 所示之接口的程序代码如下所列，我们已经将删除数据列纳入考虑，请自行参考：

 

 

Imports System
Imports System.Drawing
Imports System.Windows.Forms

Public Class Form1
    Inherits Form

    Private WithEvents DataGridView1 As New DataGridView()

    <STAThreadAttribute()> _
    Public Shared Sub Main()
        Application.Run(New Form1())
    End Sub

    Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        With Me
            .AutoSize = True
            .Controls.Add(DataGridView1)
            .PopulateDataGridView()
            .UpdateBalance()
        End With
    End Sub


    Private Sub PopulateDataGridView()
        With Me.DataGridView1
            .Size = New Size(400, 300)
            .AllowUserToDeleteRows = True
            .ColumnCount = 4
            .SelectionMode = DataGridViewSelectionMode.RowHeaderSelect
            .Columns(0).Name = "款项说明"
            .Columns(1).Name = "支出"
            .Columns(2).Name = "存入"

            .Columns(3).Name = "累计结余"
            .Columns("款项说明").HeaderText = "款项说明"
            .Columns("支出").HeaderText = "支出(请输入负值)"
            .Columns("存入").HeaderText = "存入"
            .Columns("累计结余").HeaderText = "累计结余"
            .Columns("累计结余").ReadOnly = True
            .Columns("款项说明").SortMode = _
                DataGridViewColumnSortMode.NotSortable
            .Columns("支出").SortMode = _
                DataGridViewColumnSortMode.NotSortable
            .Columns("存入").SortMode = _
                DataGridViewColumnSortMode.NotSortable
            .Columns("累计结余").SortMode = _
                DataGridViewColumnSortMode.NotSortable
        End With

        With Me.DataGridView1.Rows
            .Add(New String() {"起始累计结余", "", "", "1000"})
            .Add(New String() {"股息收入", "", "850", ""})
            .Add(New String() {"房租", "-500", "", ""})
            .Add(New String() {"汽车保养费", "-25", "", ""})
            .Add(New String() {"退税", "", "300", ""})
        End With

        ' 允许用户编辑起始累计结余储存格
        DataGridView1.Rows(0).ReadOnly = True
        DataGridView1.Rows(0).Cells("累计结余").ReadOnly = False

        ' 自动调整数据行大小
        Me.DataGridView1.AutoResizeColumns()

    End Sub

    Private Sub CellValueChanged(ByVal sender As Object, _
        ByVal e As DataGridViewCellEventArgs) _
        Handles DataGridView1.CellValueChanged

        ' 每当有任何储存格的值变更时就更新累计结余数据行
        UpdateBalance()
    End Sub

    Private Sub RowsRemoved(ByVal sender As Object, _
        ByVal e As DataGridViewRowsRemovedEventArgs) _
        Handles DataGridView1.RowsRemoved

        ' 每当有任何数据列被删除时就更新累计结余数据行
        UpdateBalance()
    End Sub

    Private Sub UpdateBalance()
        Dim counter As Integer
        Dim balance As Integer
        Dim deposit As Integer
        Dim withdrawal As Integer

        ' 循环处理起始累计结余数据列以外的每一列
        For counter = 1 To (DataGridView1.Rows.Count - 2)
            deposit = 0
            withdrawal = 0
            balance = Integer.Parse(DataGridView1.Rows(counter - 1) _
                .Cells("累计结余").Value.ToString())

            If Not DataGridView1.Rows(counter) _
                .Cells("存入").Value Is Nothing Then

                ' 确认储存格值不是一个空字符串
                If Not DataGridView1.Rows(counter) _
                    .Cells("存入").Value.ToString().Length = 0 Then
                    deposit = Integer.Parse(DataGridView1.Rows(counter) _
                        .Cells("存入").Value.ToString())
                End If
            End If

            If Not DataGridView1.Rows(counter) _
                .Cells("支出").Value Is Nothing Then
                If Not DataGridView1.Rows(counter) _
                    .Cells("支出").Value.ToString().Length = 0 Then
                    withdrawal = Integer.Parse(DataGridView1.Rows(counter) _
                        .Cells("支出").Value.ToString())
                End If
            End If

            DataGridView1.Rows(counter).Cells("累计结余").Value = _
                (balance + deposit + withdrawal).ToString()
        Next
    End Sub

    Private Sub CellValidating(ByVal sender As Object, _
        ByVal e As DataGridViewCellValidatingEventArgs) _
        Handles DataGridView1.CellValidating

        Dim testInteger As Integer

        If Not e.ColumnIndex = 0 Then
            If Not e.FormattedValue.ToString().Length = 0 Then

                ' 尝试将储存格值转换成一个整数
                If Not Integer.TryParse(e.FormattedValue.ToString(), _
                    testInteger) Then

                    DataGridView1.Rows(e.RowIndex).ErrorText = _
                        "错误: 无效的输入"

                    e.Cancel = True
                End If
            End If
        End If
    End Sub

    Private Sub CellValidated(ByVal sender As Object, _
        ByVal e As DataGridViewCellEventArgs) _
        Handles DataGridView1.CellValidated

        DataGridView1.Rows(e.RowIndex).ErrorText = Nothing
    End Sub

    Private Sub UserDeletingRow(ByVal sender As Object, _
        ByVal e As DataGridViewRowCancelEventArgs) _
        Handles DataGridView1.UserDeletingRow

        Dim starting累计结余Row As DataGridViewRow = DataGridView1.Rows(0)

        ' 检查是否起始累计结余资料列内含于选取的数据列中
        If DataGridView1.SelectedRows.Contains(starting累计结余Row) Then

            If e.Row.Equals(starting累计结余Row) Then

                ' 不允许用户去删除起始数据列
                MessageBox.Show("不可以删除累计结余列!")
            End If

            ' 如果起始数据列被内含, 则取消删除
            e.Cancel = True

        End If
    End Sub
End Class