vb.net 将 Excel 剪贴板文本粘贴到数据绑定 DataGridView 和实体框架验证
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18003482/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pasting Excel Clipboard Text to Databound DataGridView and Entity Framework Validation
提问by jeff
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.
在 Visual Studio 2010 中开发的 Windows 窗体项目中，我有一个 DataGridView 绑定到一个 BindingSource，它的 DataSource 是一个 BindingList(Of T)。T 是来自实体框架 5 模型的实体。
My entities implement INotifyPropertyChanged and IDataErrorInfo.
我的实体实现 INotifyPropertyChanged 和 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.
我的用户是 Excel 主管并坚持要求我将 Excel 数据粘贴到我们应用程序中使用的网格中。
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.
- 尽可能模仿 Excel 中的复制和粘贴行为。
- 将数据粘贴到 DataGridView 中的新行应该创建并验证网格中表示的类型的新实体。
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.
在定位单元格本身时，我希望我可以编写例程，以便在需要时触发 DataGridView 中内置的验证事件，无论是编辑现有行还是创建新行。
I shortly discovered that it wasn't working as expected because CellValidating doesn't fire until the cell loses focus.
我很快发现它没有按预期工作，因为 CellValidating 在单元格失去焦点之前不会触发。
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.
在定位基础数据源（将行的 DataBoundItem 转换为适当的实体类型）时，我可以从剪贴板数据创建新实体并在将更改提交到 DbContext 之前验证它们。
In either case, when validation fails, the DataGridView seems to have lost the previous value for the cell.
无论哪种情况，当验证失败时，DataGridView 似乎都丢失了单元格的先前值。
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.
如果验证失败，将提示用户并退出例程。我希望用户能够按 Esc 键返回单元格的先前值，但该单元格仍为空。
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:
这是我目前正在做的事情。我通过调用表单的 .Validate 方法强制验证事件触发。我不知道我是否应该这样做。这是不完整的，因为我还没有处理新行：
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!
感谢您对此的任何帮助。希望其他人正在使用 EF5 和 Winforms。如果没有，我就靠我自己了！
回答by jeff
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.
假定开发人员正在正确处理 CellValidating 事件，这意味着如果验证失败，则该事件将被取消。
This routine closely resembles the copy and paste behavior one observes in Excel.
此例程非常类似于人们在 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
回答by OneFineDay
I would parse the data update the Entity Framework object with new objects of type that fits your Entity model. Then just save the Entity object and rebind your DGV.
我会解析数据，使用适合您的实体模型的新类型对象更新实体框架对象。然后只需保存实体对象并重新绑定您的 DGV。
