Geeks With Blogs

News


Rodney Vinyard - .NET & SQL Developer When all is said and done, more will be said than done

DataGridView CSV Editor

·          Load CSV File into Dataset -> DataGridView

·          Show cells with Validation Errors with backcolor & tooltip

·          Right-Click cell to correct cell via tooltip

·          Restrict KeyPress Keys

·          Show cell as “edited” via cell backcolor

   

    Public Function GetCSVDataTable(ByVal filePath As String) As DataTable

 

        ' The dataset to return

        Dim dt As DataTable = Nothing

        Try

            '--------------------------------------------------------------------

            ' Create "Excel Like" Column Headers

            '--------------------------------------------------------------------

            Dim columns() As String = {"A", "B", "C", "D", "E", "F", "G", "H"}

 

            '--------------------------------------------------------------------

            ' Create dataset and datatable to hol;d the csv data

            '--------------------------------------------------------------------

            Dim ds As New DataSet

            Dim mTablename As String = "table1"

            ' Add the new Datatable to the DataSet

            ds.Tables.Add(mTablename)

            dt = ds.Tables("table1")

 

            '--------------------------------------------------------------------

            ' Add columns to datatable

            '--------------------------------------------------------------------

            For Each col As String In columns

                Dim added As Boolean = False

                Dim _next As String = ""

                Dim i As Integer = 0

 

                While Not added

                    ' Build the column name and remove any unwanted characters

                    Dim columnname As String = col + _next

                    columnname = columnname.Replace("#", "")

                    columnname = columnname.Replace("'", "")

                    columnname = columnname.Replace("&", "")

                    columnname = columnname.Replace("""", "")

 

                    ' See if the column already exists

                    If Not ds.Tables(mTablename).Columns.Contains(columnname) Then

                        ds.Tables(mTablename).Columns.Add(columnname)

                        added = True

                    Else

                        ' If it did exist then we increment the sequencer and try again

                        i = i + 1

                        _next = "_" + i.ToString()

                    End If

                End While

            Next

 

            '--------------------------------------------------------------------

            ' Open the CSV file with a stream reader

            '--------------------------------------------------------------------

            Dim sr As New StreamReader(filePath)

 

            '--------------------------------------------------------------------

            ' Read the entire CSV string into one big string

            '--------------------------------------------------------------------

            Dim allData As String = sr.ReadToEnd()

 

            '--------------------------------------------------------------------

            ' Split off each CSV row at the Carriage Return / Line Feed

            ' Default line ending in most windows exports

            ' You may have to edit this to match your particular file

            '--------------------------------------------------------------------

            Dim rows As String() = allData.Split(vbCr.ToCharArray)

 

            '--------------------------------------------------------------------

            ' Add each row to the Dataset until _MaxRowsCount met

            '--------------------------------------------------------------------

            Dim rowCount As Integer = 0

            For Each rowValue As String In rows

                rowCount += 1

                If rowCount > Me._MaxRowsCount Then Exit For

                '--------------------------------------------------------------------

                ' Remove quotation field markers

                '--------------------------------------------------------------------

                Dim row As String = rowValue.ToString().Replace("""", "")

 

                '--------------------------------------------------------------------

                ' Split the row at the delimiter

                '--------------------------------------------------------------------

                Dim rowItems As String() = row.Split(",".ToCharArray())

 

                '--------------------------------------------------------------------

                ' set array to Me._MaxColsCount - 1

                '--------------------------------------------------------------------

                ReDim Preserve rowItems(Me._MaxColsCount - 1)

 

                '--------------------------------------------------------------------

                ' Add the rowItems to a new DataSet.DataTable.DataRow

                '--------------------------------------------------------------------

                ds.Tables(mTablename).Rows.Add(rowItems)

            Next

 

            ' Cleanup - Release StreamReader Resources

            sr.Close()

            sr.Dispose()

 

        Catch ex As Exception

            MessageBox.Show(ex.Message)

        End Try

        ' Return the imported data

        Return dt

 

    End Function

 

Private Sub ValidateDataInGrid()

 

' cell init

dataGridViewRow.Cells(iCol).Style.BackColor = _colorBackLnum

sb = New StringBuilder

dataGridViewRow.Cells(iCol).ToolTipText = ""

dataGridViewRow.Cells(iCol).Tag = ""

 

' cell rules

If x.Length < 17 Then

              sb.Append("Field is too short must be 17 charaters." + vbCrLf)

dataGridViewRow.Cells(iCol).Style.BackColor = Me._colorErrorManual

              cellErrorCount += 1

ElseIf x.Length > 17 Then

              sb.Append("Field is too long must be 17 characters." + vbCrLf)

              dataGridViewRow.Cells(iCol).Style.BackColor = Me._colorErrorManual

              cellErrorCount += 1

End If

 

' cell right-clickable error

If compareDtPointer < _CompareDt.Rows.Count Then

              Dim lnumber = _CompareDt.Rows(compareDtPointer)("Field").ToString.Replace("-", "").ToUpper

              If x.ToUpper <> lnumber Then

                     sb = New StringBuilder

                     sb.Append("Right click to change Field to " + lnumber + "." + vbCrLf)

                     cellErrorCount += 1

                     dataGridViewRow.Cells(iCol).Tag = "rightClickable"

                     dataGridViewRow.Cells(iCol).Style.BackColor = _colorRightClickable

              End If

End If

 

' cell results

dataGridViewRow.Cells(iCol).Value = x

Dim err As String = sb.ToString

If err <> "" Then

       dataGridViewRow.Cells(iCol).ToolTipText = err

End If

 

End Sub

 

    Dim _CellRow As Integer = -1

    Dim _CellCol As Integer = -1

    Private Sub gvCSV_CellMouseDown(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles gvCSV.CellMouseDown

 

        '--------------------------------------------------------------------

        'right mouse click?

        '--------------------------------------------------------------------

        If e.Button = Windows.Forms.MouseButtons.Right Then

            '--------------------------------------------------------------------

            'yes, if right clickable

            '   get column & row of grid cell

            '   Isolate last string from tooltip message:

            '       e.g. "Right click to change Field to L2345678901234567." --> "L2345678901234567"

            '   change cell text to "L2345678901234567"

            '--------------------------------------------------------------------

            Dim col As Integer = e.ColumnIndex

            Dim row As Integer = e.RowIndex

            If gvCSV.Item(col, row).Tag.ToString.ToLower = "rightclickable" Then

                Dim toolTip As String = gvCSV.Item(col, row).ToolTipText.Replace(vbCrLf, "").Replace(".", "")

                Dim toolTipArray As String() = toolTip.Split(" ".ToCharArray)

 

                Dim x As String = toolTipArray(toolTipArray.Length - 1)

                gvCSV.Item(col, row).Value = x

                gvCSV.Rows(row).Cells(col).Style.BackColor = Me._colorEdited

            End If

        End If

 

    End Sub

    Private Sub gvCSV_CellEnter(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles gvCSV.CellEnter

        _CellCol = e.ColumnIndex

        _CellRow = e.RowIndex

    End Sub

    Private Sub gvCSV_EditingControlShowing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles gvCSV.EditingControlShowing

        If Not e.Control Is Nothing Then 'gvCSV.CurrentCell.ColumnIndex = 1 And Not

            Dim tb As TextBox = CType(e.Control, TextBox)

            '---add an event handler to the TextBox control---           

            AddHandler tb.KeyPress, AddressOf gvCSV_KeyPress

            AddHandler tb.TextChanged, AddressOf gvCSV_TextChanged

        End If

 

    End Sub

    Private Sub gvCSV_KeyDown(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles gvCSV.KeyDown

        'Nothing to do

    End Sub

    Private Sub gvCSV_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles gvCSV.KeyPress

 

        '--------------------------------------------------------------------

        'Allow only A-Z or Numeric (supress all other keys

        '--------------------------------------------------------------------

        If e.KeyChar Like "[A-z]" Or IsNumeric(e.KeyChar) Then

            e.Handled = False

        Else

            e.Handled = True

        End If

 

    End Sub

    Private Sub gvCSV_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtFilePath.TextChanged

        'Nothing to do

    End Sub

    Private Sub gvCSV_CellEndEdit(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles gvCSV.CellEndEdit

        '--------------------------------------------------------------------

        'chane cell backcolor to "Edited"

        '--------------------------------------------------------------------

        If _CellRow <> -1 And _CellRow <> -1 Then

            gvCSV.Rows(_CellRow).Cells(_CellCol).Style.BackColor = Me._colorEdited

            'No!  -> Application.DoEvents()

        End If

    End Sub

 

Posted on Monday, November 2, 2009 8:10 AM Excel , GridView, Datagrid, DataList & Repeater | Back to top


Comments on this post: DataGridView CSV Editor

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Rodney Vinyard | Powered by: GeeksWithBlogs.net