Click here to Skip to main content
15,116,253 members
Please Sign up or sign in to vote.
2.00/5 (1 vote)
See more:
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

                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;")
                MyCommend = New System.Data.OleDb.OleDbDataAdapter("Select * from [Sheet1$]", MyConnection)

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

                Label4.Text = "You have import " & DataGridView1.Rows.Count - 1 & " Elements"
                Button2.Enabled = True
            Catch ex As Exception
            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 & ";"
                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


        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
Updated 13-Dec-15 4:01am

1 solution

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

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

End Sub
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

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))
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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900