从Excel加载数据时检测Datagridview [英] Datagridview detection while loading data from Excel

问题描述

亲爱的所有



i我的小应用程序有一个问题，我计划导入Excel文件并将其加载到DataGridView中，Excel里面有2列哪个列1名称串行和列2名称Phone，

第1列和第2列，我想在将数据从Excel加载到Datagridview时插入代码，以检测列1-Serial或列2- Phone，值小于9位，它会向用户显示一条消息警告，通过使用指针指向或突出显示到Datagridview表中的问题。



这是我的代码我在家里建立，请在Button1_Click活动中帮助我。

Dear all

i have one problem with my small application which i plan to import Excel File and load it into DataGridView, inside the Excel there has 2 columns which column 1 name Serial and column 2 name Phone,
for column 1 and 2 , i want to insert code while loading the data from Excel to Datagridview, to detect if column 1-Serial or column 2- Phone, has value smaller than 9 digit, it will show a message alert to user that where problem by using pointer to point or highlight into Datagridview table.

that is my code which i build at home, please kindly to help me about it at Button1_Click event.

Imports System.IO
Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        OpenFileDialog1.Filter = "Excel Worksheets|*.xls|All files (*.*)|*.*"
        OpenFileDialog1.FilterIndex = 2
        OpenFileDialog1.RestoreDirectory = True
        If OpenFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
            'TextBox1.Text = Path.GetDirectoryName(OpenFileDialog1.FileName) 'Get only folder direcrtory name of path file
            TextBox1.Text = OpenFileDialog1.FileName 'Get full name of path file diretory
            Try

                Dim MyConnection As System.Data.OleDb.OleDbConnection
                Dim DataSet As System.Data.DataSet
                Dim MyCommend As System.Data.OleDb.OleDbDataAdapter
                Dim Path As String = OpenFileDialog1.FileName   'OpenFileDialog1.FileName , use for connect the path file name
                'Dim Path As String = "C:\Users\DELL\Desktop\New folder\test.xlsx"

                MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";Extended Properties=Excel 12.0;")
                MyConnection.Open()
                MyCommend = New System.Data.OleDb.OleDbDataAdapter("Select * from [Sheet1$]", MyConnection)

                DataSet = New System.Data.DataSet
                MyCommend.Fill(DataSet)
                DataGridView1.DataSource = DataSet.Tables(0)

                MyConnection.Close()
                Label4.Text = "You have import " & DataGridView1.Rows.Count - 1 & " Elements"
                Button2.Enabled = True
            Catch ex As Exception
                MsgBox(ex.Message.ToString)
            End Try

        End If
        
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Button2.Enabled = False
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        'String connect the file directory + Add File
        Dim AddFile As String = Path.GetDirectoryName(OpenFileDialog1.FileName) & "\Add.txt"

        'String connect the file directory + Remove File
        Dim RemoveFile As String = Path.GetDirectoryName(OpenFileDialog1.FileName) & "\Remove.txt"

        'Dim FileName As String = "C:\Users\DELL\Desktop\New folder\t.txt"
        Dim ResultAdd As String = ""
        Dim ResultRemove As String = ""
        Dim StrRep As String = ""   'String Replace Phone number

        Dim ObjWriteAddFile As StreamWriter = New StreamWriter(AddFile) 'Obj use for create Add File
        Dim ObjWriteRemoveFile As StreamWriter = New StreamWriter(RemoveFile) 'Obj use for create Remove File
        Dim x As Integer = 0
        Dim SeLength As Integer = 0
        Dim PhLength As Integer = 0
        For i As Integer = 0 To DataGridView1.Rows.Count - 2

            SeLength = DataGridView1.Rows(i).Cells(0).Value.ToString.Length ' Count lenght of Serial
            PhLength = DataGridView1.Rows(i).Cells(1).Value.ToString.Length ' Count lenght of Phone
            Label3.Text = "Serial Lenght=" & (SeLength + 1) & " and Phone Lenght=" & (PhLength - 1) 'Show the lenght of each rows

            If (i + 1) Mod 10 Then
                StrRep = DataGridView1.Rows(i).Cells(1).Value.ToString  'String replace "-" to "" for Phone
                ResultRemove &= StrRep.Replace("-", "") & ";"    'String connect ResultRemove and Replace "-" to ""

                'String connect ResultAdd and Replace "-" to ""
                ResultAdd &= StrRep.Replace("-", "") & ":0" & DataGridView1.Rows(i).Cells(0).Value.ToString & ";"
                'Result &= DataGridView1.Rows(i).Cells(1).Value.ToString & ":" & DataGridView1.Rows(i).Cells(0).Value.ToString & ";"
            Else
                StrRep = DataGridView1.Rows(i).Cells(1).Value.ToString
                ResultRemove &= StrRep.Replace("-", "") & ";" & vbCrLf
                ResultAdd &= StrRep.Replace("-", "") & ":0" & DataGridView1.Rows(i).Cells(0).Value.ToString & ";" & vbCrLf
                'Result &= DataGridView1.Rows(i).Cells(1).Value.ToString & ":" & DataGridView1.Rows(i).Cells(0).Value.ToString & ";" & vbCrLf
            End If
            x += 1
        Next

        ObjWriteAddFile.WriteLine(ResultAdd)
        ObjWriteAddFile.Close()

        ObjWriteRemoveFile.WriteLine(ResultRemove)
        ObjWriteRemoveFile.Close()
        TextBox2.Text = Path.GetDirectoryName(OpenFileDialog1.FileName)
        MessageBox.Show("Successfully exported " & x & " line numbers folder " & vbCrLf & Path.GetDirectoryName(OpenFileDialog1.FileName), "Importance Message")
        'MessageBox.Show("You have successfully exported " & x & " line numbers into file." & vbCrLf & AddFile)
        Process.Start("explorer.exe", Path.GetDirectoryName(OpenFileDialog1.FileName))  'Commend to show only folder directory
        'Process.Start("explorer.exe", FileName)    'Commend open file after write complete
    End Sub
End Class

推荐答案

，MyConnection）

DataSet = 新 System.Data.DataSet
MyCommend.Fill（DataSet）
DataGridView1.DataSource = DataSet.Tables（ 0 ）

MyConnection.Close（）
Label4.Text = 你有导入& DataGridView1.Rows.Count - 1 & 元素
Button2.Enabled = True
Catch ex As 异常
MsgBox（ex.Message。 ToString）
结束 尝试

结束 如果

结束 Sub

私有 Sub Form1_Load（ ByVal sender As System。 Object ， ByVal e As System.EventArgs） Handles MyBase .Load
Button2.Enabled = False
End Sub

私有 Sub Button2_Click（ ByVal sender As System。对象， ByVal e As System.EventArgs）句柄 Button2.Click
' 字符串连接文件目录+添加文件
Dim AddFile 作为 字符串 = Path.GetDirectoryName（OpenFileDialog1.FileName）& \ Add.txt

' 字符串连接文件目录+删除文件
Dim RemoveFile As String = Path.GetDirectoryName（OpenFileDialog1.FileName）& \ Remove.txt

' Dim FileName As String =C：\ Users \DELL\Desktop\New folder \t.txt
Dim ResultAdd 作为 字符串 =
Dim ResultRemove 作为 字符串 =
Dim StrRep As 字符串 = ' 字符串替换电话号码

Dim ObjWriteAddFile As StreamWriter = 新 StreamWriter（AddFile）' 用于创建添加文件的对象
Dim ObjWriteRemoveFile As StreamWriter = 新 StreamWriter（RemoveFile） Obj用于创建删除文件
Dim x 作为 整数 = 0
Dim SeLength 作为 整数 = 0
Dim PhLength As 整数 = 0
i 作为 整数 = 0 DataGridView1.Rows.Count - 2

SeLength = DataGridView1.Rows（i）。单元格（ 0 ）。Value.ToString.Length ' 计数串行长度
PhLength = DataGridView1.Rows（i）.Cells（ 1 ）。Value.ToString.Length ' 计算电话长度
Label3.Text = Serial Lenght =& （SeLength + 1 ）& 和Phone Lenght =& （PhLength - 1 ）' 显示每行的长度

如果（i + 1 ） Mod 10 然后
StrRep = DataGridView1.Rows （i）.Cells（ 1 ）。Value.ToString ' 字符串将 - 替换为用于电话
ResultRemove& = StrRep.Replace（ - ， ）& ; ' 字符串连接ResultRemove并将 - 替换为

' 字符串连接ResultAdd和替换 - 到
ResultAdd& = StrRep.Replace（ - ， ）& ：0& DataGridView1.Rows（i）.Cells（ 0 ）。Value.ToString& ;
' Result& = DataGridView1.Rows（i）.Cells（1）.Value.ToString& ：& DataGridView1.Rows（i）.Cells（0）.Value.ToString& ;
否则
StrRep = DataGridView1.Rows（i）.Cells（ 1 ）。Value.ToString
ResultRemove& = StrRep.Replace（ - ， ）& ;& vbCrLf
ResultAdd& = StrRep.Replace（ - ， ）& ：0& DataGridView1.Rows（i）.Cells（ 0 ）。Value.ToString& ;& vbCrLf
' Result& = DataGridView1.Rows（i）.Cells（1）.Value.ToString& ; ：& DataGridView1.Rows（i）.Cells（0）.Value.ToString& ; &安培; vbCrLf
结束 如果
x + = 1
下一步

ObjWriteAddFile.WriteLine（ResultAdd）
ObjWriteAddFile.Close （）

ObjWriteRemoveFile.WriteLine（ResultRemove）
ObjWriteRemoveFile.Close（）
TextBox2.Text = Path.GetDirectoryName（OpenFileDialog1.FileName）
MessageBox.Show（< span class =code-string> 已成功导出& x& 行号文件夹& vbCrLf& Path.GetDirectoryName（OpenFileDialog1.FileName）， 重要性消息）
' MessageBox.Show（您已成功导出& x&放大器; 行号到文件中。 &安培; vbCrLf& AddFile）
Process.Start（ explorer.exe，Path。 GetDirectoryName（OpenFileDialog1.FileName））' 推荐仅显示文件夹目录
Process.Start（explorer.exe，FileName）'写完成后命令打开文件
结束 Sub
结束 类

", MyConnection) DataSet = New System.Data.DataSet MyCommend.Fill(DataSet) DataGridView1.DataSource = DataSet.Tables(0) MyConnection.Close() Label4.Text = "You have import " & DataGridView1.Rows.Count - 1 & " Elements" Button2.Enabled = True Catch ex As Exception MsgBox(ex.Message.ToString) End Try End If End Sub Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Button2.Enabled = False End Sub Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click 'String connect the file directory + Add File Dim AddFile As String = Path.GetDirectoryName(OpenFileDialog1.FileName) & "\Add.txt" 'String connect the file directory + Remove File Dim RemoveFile As String = Path.GetDirectoryName(OpenFileDialog1.FileName) & "\Remove.txt" 'Dim FileName As String = "C:\Users\DELL\Desktop\New folder\t.txt" Dim ResultAdd As String = "" Dim ResultRemove As String = "" Dim StrRep As String = "" 'String Replace Phone number Dim ObjWriteAddFile As StreamWriter = New StreamWriter(AddFile) 'Obj use for create Add File Dim ObjWriteRemoveFile As StreamWriter = New StreamWriter(RemoveFile) 'Obj use for create Remove File Dim x As Integer = 0 Dim SeLength As Integer = 0 Dim PhLength As Integer = 0 For i As Integer = 0 To DataGridView1.Rows.Count - 2 SeLength = DataGridView1.Rows(i).Cells(0).Value.ToString.Length ' Count lenght of Serial PhLength = DataGridView1.Rows(i).Cells(1).Value.ToString.Length ' Count lenght of Phone Label3.Text = "Serial Lenght=" & (SeLength + 1) & " and Phone Lenght=" & (PhLength - 1) 'Show the lenght of each rows If (i + 1) Mod 10 Then StrRep = DataGridView1.Rows(i).Cells(1).Value.ToString 'String replace "-" to "" for Phone ResultRemove &= StrRep.Replace("-", "") & ";" 'String connect ResultRemove and Replace "-" to "" 'String connect ResultAdd and Replace "-" to "" ResultAdd &= StrRep.Replace("-", "") & ":0" & DataGridView1.Rows(i).Cells(0).Value.ToString & ";" 'Result &= DataGridView1.Rows(i).Cells(1).Value.ToString & ":" & DataGridView1.Rows(i).Cells(0).Value.ToString & ";" Else StrRep = DataGridView1.Rows(i).Cells(1).Value.ToString ResultRemove &= StrRep.Replace("-", "") & ";" & vbCrLf ResultAdd &= StrRep.Replace("-", "") & ":0" & DataGridView1.Rows(i).Cells(0).Value.ToString & ";" & vbCrLf 'Result &= DataGridView1.Rows(i).Cells(1).Value.ToString & ":" & DataGridView1.Rows(i).Cells(0).Value.ToString & ";" & vbCrLf End If x += 1 Next ObjWriteAddFile.WriteLine(ResultAdd) ObjWriteAddFile.Close() ObjWriteRemoveFile.WriteLine(ResultRemove) ObjWriteRemoveFile.Close() TextBox2.Text = Path.GetDirectoryName(OpenFileDialog1.FileName) MessageBox.Show("Successfully exported " & x & " line numbers folder " & vbCrLf & Path.GetDirectoryName(OpenFileDialog1.FileName), "Importance Message") 'MessageBox.Show("You have successfully exported " & x & " line numbers into file." & vbCrLf & AddFile) Process.Start("explorer.exe", Path.GetDirectoryName(OpenFileDialog1.FileName)) 'Commend to show only folder directory 'Process.Start("explorer.exe", FileName) 'Commend open file after write complete End Sub End Class

您可以将某些内容添加到 CellFormatting event

You could put something into the CellFormatting event

Private Sub DataGridView1_CellFormatting(sender As Object, e As DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting

    Const minLength = 9

    Dim cellvalue = DataGridView1.Rows(e.RowIndex).Cells(e.ColumnIndex).Value
    If Not IsNothing(cellvalue) Then
        If cellvalue.ToString().Length < minLength Then
            DataGridView1.Rows(e.RowIndex).Cells(e.ColumnIndex).Style.BackColor = Color.Coral
        End If
    End If
End Sub

但是请注意，此事件的调用次数比填充单元格时要多 - 例如，当鼠标悬停在DataGridView上或最大化表格时。



虽然这可能适用于单元格着色，但它不适合通过消息通知用户。我个人更喜欢使用ataBindingComplete事件

Note however that this event gets called more often than when the cell is populated - when mousing over the DataGridView or maximising the form for example.

Whilst that may be fine for just colouring-in the cell it doesn't lend itself to informing the user via a message. Personally I prefer to use the ataBindingComplete event

Private Sub DataGridView1_DataBindingComplete(sender As Object, e As DataGridViewBindingCompleteEventArgs) Handles DataGridView1.DataBindingComplete

    For Each item As DataGridViewRow In DataGridView1.Rows

        For Each cell In item.Cells
            If cell.Value.ToString().Length < minLength Then
                cell.Style.BackColor = Color.Coral
                _warnUser = True
            End If
        Next
    Next


End Sub

或者如果您更喜欢Linq

or if you prefer Linq

Private Sub DataGridView1_DataBindingComplete(sender As Object, e As DataGridViewBindingCompleteEventArgs) Handles DataGridView1.DataBindingComplete

    For Each cell As DataGridViewCell In From item As DataGridViewRow In DataGridView1.Rows From cell1 In item.Cells Where cell1.Value.ToString().Length < minLength
        cell.Style.BackColor = Color.Coral
        _warnUser = True
    Next

End Sub

这似乎也是放置

This may seem like the ideal place to also put

If _warnUser Then MessageBox.Show(String.Format("Some of the data is less than {0} characters long", minLength))

但是这种方法也比你想象的更频繁地被调用...在这种情况下，它通过在button_click代码中填充datagridview的行为被调用两次。我只是把它放在 Button1_Click 事件的末尾

but this method is also called more often than you think ... in this case it is called twice by the act of filling the datagridview in the button_click code. I just put this at the end of the Button1_Click event

这篇关于从Excel加载数据时检测Datagridview的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！