Pasting Excel Clipboard Text to Databound DataGridView and Entity Framework Validation

Question

In a Windows Forms project being developed in Visual Studio 2010, I have a DataGridView bound to a BindingSource whose DataSource is a BindingList(Of T). T is an entity from an Entity Framework 5 model.

My entities implement INotifyPropertyChanged and IDataErrorInfo.

My users are Excel heads and insist that I provide for the pasting of Excel data into the grids in use in our application.

So, I set out with a couple of simple rules.

Mimic as closely as possible the copy & paste behavior within Excel.
Pasting data into a new row in a DataGridView should create and validate new entities of the type represented in the grid.

I've come a long way with this but have now bumped up against something I can't figure out.

Judging by what information I can find, it seems clear that when pasting into a bound grid that the underlying data source should be the target of your edits and creates.

Or should it?

I've tried both ways.

When targeting the cells themselves, I hoped that I could write the routine such that the validation events built into DataGridView would fire when needed, whether I was editing an existing row or creating a new one.

I shortly discovered that it wasn't working as expected because CellValidating doesn't fire until the cell loses focus.

While pasting, I'd like to validate the cell at the moment a value is pasted into it - cancelling the rest of the paste operation if it fails.

When targeting the underlying data source (a row's DataBoundItem cast as the appropriate entity type), I can create new entities from the clipboard data and validate them before committing changes to the DbContext.

In either case, when validation fails, the DataGridView seems to have lost the previous value for the cell.

If validation fails the user is prompted and the routine exits. I'd like for user to be able to hit the Esc key to return the previous value for the cell, but the cell remains empty.

Does anyone know why the previous value is no longer available when editing a cell's value programatically?

Here's what I'm doing so far. I am forcing the validation events to fire by calling the form's .Validate method. I don't know if I should be doing that or not. It is incomplete in that I am not yet handling new rows:

    Private Sub PasteFromClipboard(ByVal sender As Object, ByVal e As KeyEventArgs)
    Dim dgv = TryCast(sender, DataGridView)

    If Not IsNothing(dgv) Then
        If dgv.SelectedCells.Count > 0 Then
            Dim rowSplitter = {ControlChars.Cr, ControlChars.Lf}
            Dim columnSplitter = {ControlChars.Tab}
            Dim topLeftCell = CopyPasteFunctions.GetTopLeftSelectedCell(dgv.SelectedCells)

            If Not IsNothing(topLeftCell) Then
                Dim data = Clipboard.GetData(DataFormats.Text)

                If Not IsNothing(data) Then
                    Dim columnIndex = topLeftCell.ColumnIndex
                    Dim rowIndex = topLeftCell.RowIndex
                    Dim columnCount = dgv.Columns.Count
                    Dim rowCount = dgv.Rows.Count

                    'Split clipboard data into rows
                    Dim rows = data.ToString.Split(rowSplitter, StringSplitOptions.RemoveEmptyEntries)

                    For i = 0 To rows.Length - 1
                        'Split row into cell values
                        Dim values = rows(i).Split(columnSplitter)

                        For j = 0 To values.Length - 1
                            If (j <= (columnCount - 1)) AndAlso (i <= (rowCount - 1)) Then
                                Dim cell = dgv.Rows(rowIndex + i).Cells(columnIndex + j)
                                dgv.CurrentCell = cell
                                dgv.BeginEdit(False)
                                cell.Value = values(j)

                                If Not Me.Validate() Then
                                    dgv.CancelEdit()
                                    Exit Sub
                                Else
                                    dgv.EndEdit()
                                End If
                            Else
                                Debug.Print(String.Format("RowIndex: {0}, ColumnIndex: {1}", i, j))
                            End If
                        Next
                    Next
                End If
            End If
        End If
    End If
End Sub

Public Module CopyPasteFunctions
Public Function GetTopLeftSelectedCell(ByVal cells As DataGridViewSelectedCellCollection) As DataGridViewCell
    If Not IsNothing(cells) AndAlso cells.Count > 0 Then
        Dim cellList = (From c In cells.Cast(Of DataGridViewCell)()
                        Order By c.RowIndex, c.ColumnIndex
                        Select c).ToList

        Return cellList(0)
    End If

    Return Nothing
End Function
End Module

Thanks for any help on this. Hopefully others are working with EF5 and Winforms. If not, I'm on my own!

jeff jeff · Accepted Answer · 2013-08-06T12:17:27

This gets the job done when the grid contains one column.

It is assumed that the developer is handling the CellValidating event correctly, meaning that if validation fails the event is cancelled.

This routine closely resembles the copy and paste behavior one observes in Excel.

    Private Sub PasteFromClipboard(ByVal sender As Object, ByVal e As KeyEventArgs)
    Dim dgv = TryCast(sender, DataGridView)

    If Not IsNothing(dgv) AndAlso Clipboard.ContainsText Then
        If dgv.SelectedCells.Count > 0 Then
            Dim rowSplitter = {ControlChars.NewLine}
            Dim columnSplitter = {ControlChars.Tab}
            Dim topLeftCell = CopyPasteFunctions.GetTopLeftSelectedCell(dgv.SelectedCells)

            If Not IsNothing(topLeftCell) Then
                Dim clipBoardText = Clipboard.GetText(TextDataFormat.Text)
                Dim columnIndex = topLeftCell.ColumnIndex
                Dim rowIndex = topLeftCell.RowIndex
                Dim columnCount = dgv.Columns.Count
                Dim rows = clipBoardText.Split(rowSplitter, StringSplitOptions.None)

                For i = 0 To rows.Length - 2
                    'Split row into cell values
                    Dim values = rows(i).Split(columnSplitter)
                    Dim rowCount = dgv.Rows.Count

                    For j = 0 To values.Length - 1
                        If (i <= (rowCount - 1)) AndAlso ((j + 1) <= columnCount) Then
                            Dim cell = dgv.Rows(rowIndex + i).Cells(columnIndex + j)
                            dgv.CurrentCell = cell
                            dgv.BeginEdit(False)
                            dgv.EditingControl.Text = values(j)

                            If Not Me.Validate() Then
                                Exit Sub
                            Else
                                dgv.EndEdit()
                            End If
                        End If
                    Next
                Next
            End If
        End If
    End If
End Sub

Public Module CopyPasteFunctions
Public Function GetTopLeftSelectedCell(ByVal cells As DataGridViewSelectedCellCollection) As DataGridViewCell
    If Not IsNothing(cells) AndAlso cells.Count > 0 Then
        Dim cellList = (From c In cells.Cast(Of DataGridViewCell)()
                        Order By c.RowIndex, c.ColumnIndex
                        Select c).ToList

        Return cellList(0)
    End If

    Return Nothing
End Function
End Module

Pasting Excel Clipboard Text to Databound DataGridView and Entity Framework Validation

2 Answers