数据库开发029根据单元格内容设定显示样式

    DataGridView提供多个属性，程序可以对特定的单元格读取和修改，还可以定义特定单元格的显示样式。比如获取当前行的某一列的值：  

DataGridViewI.Current Row.Cells(i).Value.ToString()

   1．获取选定单元格的数据

    使用SelectedCells、SelectedRows和SelectedColumns三个属性可方便地检索选定的单元格。无论使用的是哪种选择模式，SelectedCells都始终返回DataGridⅥewCell对象的集合。如果使用行标题选择了整个行，则SelectedRows返回信息，而如果使用列标题选择了整个列，则SelectedColumns返回信息。
    例如，以下代码片段将检查选定的整个行口只要找到一行，它就会在消息框中显示CustomerID列中的相应值：

For Each  SelectedRow As  Data GridViewRow  In  Da taGridViewl.SelectedRows
     MessageBox.Show(SelectedRow.CeUs("姓名").Value)
Next

        使用CurrentCell或CunentCeIIAddress属性检索对当前单元格的引用也同样简单。使用DataGridMew时，当前单元格被一个矩形围住，看起来像是一个用黑色虚线绘制的方框。这就是用户当前所在的位置。可以使用Columnlndex和Rowlndex来获取行和列的坐标，也可以设置当前选中的位置，如下面的代码所示：

MessageBox.Show(DataGridViewl.CurrentCell.Value)’当前的值
MessageBox.Show(DataGridViewl.CurrentCell.Columnlndex)’列位置
MessageBox. Show(DataGridViewl.CurrentCell.Rowlndex)'行位置
DataGridViewl.CurrentCell=DataGridViewl(0,0)’当前位置

2．根据单元格内容设定显示格式

某些情况下，需要根据单元格的值设置不同的显格式，比如把所有不及格的科目全部标红。
    例如，将当前选定的单元格移至第十一行的第四个单元格：

DataGridView.CurrentCell = DataGridView.Rows(l0).Cells(3)

以编程方式选择OrderID字段的值小于100的所有行：

For Each  Row As  DataGridViewRow  In DataGridView1.Rows
       If Row.Cells(" OrderID").Value < 100 Then
            Row.Selected= True
       End If
Next

       DataGridView提供了CeIIFormatting事件，该事件只在显示单元格值之前引发。通过该事件，可以基于单元格的内容来更新单元格样式，比如根据成绩进行标记，如果数学成绩为90着标记红色。
 

Private Sub DataGridView1.CellFormatting( ByVal sender As System.Object,
ByVal e As System.Windows.Forms. DataGridViewCellFormattingEventArgs) Handles
    DataGridView1.CellFormatting
    ’检查该列是否正确
    If DataGridView1.Columns(e.Columnlndex) .Name=¨数学" Then
        '检查该值是否正确
        If e.Value="90" Then
            e.CellStyle.ForeColor=Color.Red
            e.CellStyle.BackColor=Color.Yellow
        End If
    End If
End Sub

3．设定单元格为只读

默认情况下，DataGridView的单元格内容均可以修改，可以将单元格设定为只读。

DataGridViewl.ReadOnly = True
DataGridViewI.Columns(l).ReadOnly = True
DataGridViewl .Rows(2).ReadOnly = True
DataGridViewl(0, O).ReadOnly = True
DataGridViewl .AllowUserToAddRows = False

Imports System.Data.SqlClient

Public Class Form1
    Dim conn As SqlConnection
    Dim da As SqlDataAdapter
    Dim ds As DataSet           '必须加“NEW”关键字
    Dim dt As DataTable

    Private Function GetConnection() As SqlConnection
        Return New SqlConnection(My.Settings.SalesConnectionString)
    End Function

    Private Sub DisplayData1(ByVal str As String, ByVal datagridview1 As DataGridView) '用DataReader对象

        datagridView1.DataSource = Nothing

        conn = GetConnection()
        conn.Open()
        Dim comm As New SqlCommand(str, conn)
        Dim dr As SqlDataReader = comm.ExecuteReader
        Dim dt As New DataTable
        dt.Load(dr)
        conn.Close()

        DataGridView1.DataSource = dt
    End Sub

    Private Sub DisplayData2(ByVal str As String, ByVal datagridview1 As DataGridView) '用DataSet对象和Adapter的Fill方法
        DataGridView1.DataSource = Nothing

        conn = GetConnection()
        conn.Open()
        da = New SqlDataAdapter(str, conn)
        da.Fill(ds, "dt")

        conn.Close()

        DataGridView1.DataSource = ds.Tables("dt")
    End Sub

    Private Sub DisplayData3(ByVal str As String, ByVal datagridview1 As DataGridView) '用DataSet对象和Adapter的Fill方法
        DataGridView1.DataSource = Nothing

        conn = GetConnection()
        Dim comm As New SqlCommand(str, conn)
        da = New SqlDataAdapter()
        da.SelectCommand = comm
        conn.Open()

        ds = New DataSet
        da.Fill(ds, "grade")
        conn.Close()

        DataGridView1.DataSource = ds.Tables("grade")

    End Sub

    Private Sub DisplayData4(ByVal bds As BindingSource, ByVal dgv As DataGridView)
        conn = GetConnection()
        conn.Open()

        da = New SqlDataAdapter("Select * from grade", conn)
        Dim customerTable As New DataTable
        da.Fill(customerTable)
        bds.DataSource = customerTable
        dgv.DataSource = bds

        dgv.ColumnHeadersVisible = True
        dgv.RowHeadersVisible = True

        conn.Close()
    End Sub

    Private Sub DataReaderData(ByVal str As String)
        conn = GetConnection()
        conn.Open()

        Dim comm As New SqlCommand(str, conn)
        Dim dr As SqlDataReader
        dr = comm.ExecuteReader
        Dim strOutput As String = ""
        Try
            While dr.Read
                strOutput += dr("学号").ToString.PadLeft(10) + Space(5)
                strOutput += dr("姓名").ToString.PadLeft(10) + Space(5)
                strOutput += dr("语文").ToString.PadLeft(10) + Space(5)
                strOutput += dr("数学").ToString.PadLeft(10) + Space(5)
                strOutput += dr("英语").ToString.PadLeft(10) + Space(5)
                strOutput += vbCrLf
            End While
        Catch ex As Exception
            MsgBox("出现异常")
        Finally
            dr.Close()
            conn.Close()
        End Try
        'Label2.Text = strOutput
    End Sub

    Private Sub UpdateData()
        conn = GetConnection()
        conn.Open()
        Dim comm As New SqlCommand
        Dim trans As SqlTransaction
        trans = conn.BeginTransaction
        comm.Connection = conn
        comm.Transaction = trans
        Try
            comm.CommandText = "Update grade set 数学=95 Where 姓名 like '%周%'"
            comm.ExecuteNonQuery()
            comm.CommandText = "Update grade set 数学=65 Where 姓名 like '%张%'"
            comm.ExecuteNonQuery()

            trans.Commit()
            'Label1.Text = "事务运行成功"
        Catch ex As Exception
            trans.Rollback()
            'Label1.Text = "有错"
        Finally
            conn.Close()
        End Try
    End Sub

    Private Sub InsertRecord1()
        'conn = GetConnection()
        'conn.Open()
        'Dim strSql As String = "Insert into grade(学号,姓名,语文,数学,英语) " & _
        '"Values('" & txtId.Text & "','" & txtName.Text & "','" & _
        'txtChinese.Text & "','" & txtMaths.Text & "','" & txtEnglish.Text & "')"
        'Dim comm As New SqlCommand(strSql, conn)
        'comm.ExecuteNonQuery()
        'conn.Close()

    End Sub

    Private Sub InserRecord2()
        conn = GetConnection()

        Dim strInsertSql As String = "Insert into grade(学号,姓名,数学) Values(27,'于谦',59)"
        Dim strUpdateSql As String = "Update grade Set 数学=60 where 学号=27"
        Dim strSelectSql As String = "Select * From grade where 学号='27'"

        Dim InsertComm As New SqlCommand(strInsertSql, conn)
        Dim UpdateComm As New SqlCommand(strUpdateSql, conn)
        Dim SelectComm As New SqlCommand(strSelectSql, conn)

        da = New SqlDataAdapter
        conn.Open()

        da.InsertCommand = InsertComm
        da.UpdateCommand = UpdateComm
        da.SelectCommand = SelectComm

        ds = New DataSet

        da.InsertCommand.ExecuteNonQuery()
        da.Fill(ds, "grade1")
        da.UpdateCommand.ExecuteNonQuery()
        da.Fill(ds, "grade2")
        da.Fill(ds, "grade3")

        '显示插入结果
        'DataGridView2.DataSource = ds.Tables("grade1")
        '显示更新结果
        'DataGridView3.DataSource = ds.Tables("grade2")
        '显示查询结果
        'DataGridView4.DataSource = ds.Tables("grade3")

        conn.Close()

    End Sub

    Private Sub RunningCreateDataSet(ByVal dgv As DataGridView)
        Dim myds As New DataSet
        Dim mydt As New DataTable("Squares")
        Dim mydr As DataRow
        Dim i As Integer
        mydt.Columns.Add(New DataColumn("数字", GetType(Integer)))
        mydt.Columns.Add(New DataColumn("平方", GetType(Integer)))
        For i = 0 To 10
            mydr = mydt.NewRow
            mydr(0) = i
            mydr(1) = i * i
            mydt.Rows.Add(mydr)
        Next
        myds.Tables.Add(mydt)
        dgv.DataSource = myds.Tables("Squares")

        myds.WriteXml("Squares.xml")
    End Sub

    Private Sub SortDataView(ByVal dgv As DataGridView)
        conn = GetConnection()
        conn.Open()
        da = New SqlDataAdapter
        ds = New DataSet
        Dim strSql As String = "Select * from grade"
        Dim comm As New SqlCommand(strSql, conn)
        da.SelectCommand = comm
        da.Fill(ds, "grade")

        Dim dv As DataView
        dv = New DataView(ds.Tables("grade"))
        dv.RowFilter = "语文>60"
        dv.Sort = "学号 asc,语文 desc"

        Label1.Text = "满足条件的记录有：" + dv.Count.ToString + "条"

        dgv.DataSource = dv

        conn.Close()
    End Sub

    Private Sub SearchfromDataView()
        conn = GetConnection()
        conn.Open()
        Dim strSql As String = "Select * from Grade"
        Dim comm As New SqlCommand(strSql, conn)
        da = New SqlDataAdapter()
        da.SelectCommand = comm
        ds = New DataSet
        da.Fill(ds, "grade")

        Dim dv As DataView
        dv = New DataView(ds.Tables("grade"), "", "姓名", DataViewRowState.CurrentRows)
        'Dim rowIndex As Integer = dv.Find(TextBox1.Text)
        'If rowIndex = -1 Then
        'Label2.Text = "没找到"
        'Else
        'Label2.Text = "姓名=" & dv(rowIndex)("姓名").ToString & Space(5) & _
        '"语文=" & dv(rowIndex)("语文").ToString & Space(5) & _
        ' "数学=" & dv(rowIndex)("数学").ToString & Space(5) & _
        '"英语=" & dv(rowIndex)("英语").ToString

        'End If

        conn.Close()
    End Sub

    Private Sub myFilter(ByVal bds As BindingSource, ByVal dgv As DataGridView)
        conn = GetConnection()
        conn.Open()

        da = New SqlDataAdapter("Select * from grade order by 数学 asc", conn)
        Dim customerTable As New DataTable
        da.Fill(customerTable)
        bds.DataSource = customerTable
        dgv.DataSource = bds

        conn.Close()

    End Sub


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        DisplayData1("select * from grade", DataGridView1)
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        DisplayData1("select * from grade", DataGridView2)
        DataGridView2.CurrentCell = DataGridView2(1, 1)

        MsgBox("当前位置：" & DataGridView2.CurrentCell.Value)
        MsgBox("当前列：" & DataGridView2.CurrentCell.ColumnIndex)
        MsgBox("当前行：" & DataGridView2.CurrentCell.RowIndex)
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        DisplayData1("select * from grade", DataGridView2)

        For i As Integer = 0 To DataGridView2.Rows.Count - 1
            If DataGridView2.Rows(i).Cells(2).Value < 80 Then
                DataGridView2.Rows(i).Cells(2).Selected = True
            End If
        Next
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        DataGridView2.ReadOnly = True
        DataGridView2.Columns(1).ReadOnly = True
        DataGridView2.Rows(2).ReadOnly = True
        DataGridView2(0, 0).ReadOnly = True
        DataGridView2.AllowUserToAddRows = False
    End Sub

    Private Sub DataGridView2_CellFormatting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGridView2.CellFormatting
        If DataGridView2.Columns(e.ColumnIndex).Name = "语文" Then
            If e.Value >= 76 Then
                e.CellStyle.ForeColor = Color.Red
                e.CellStyle.BackColor = Color.Yellow
            End If
        End If
    End Sub
End Class