如何在DataGridView中计算一系列单元格是否符合某些限制

Question

我想计算一些产品尺寸的合格性。DataGridView的第四列将根据值由"合适"或"不合适"填充​​(长度、宽度和高度(，用同一行的第1、2、3列表示。

首先，我从CSV文件中读取数据，并填充DGV:

Public Class Form1
Dim OpenFileDialog1 = New OpenFileDialog
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim fname As String = ""
OpenFileDialog1.InitialDirectory = "c:desktop"
OpenFileDialog1.Filter = "CSV files(*.csv)|*.csv"
OpenFileDialog1.FilterIndex = 2
OpenFileDialog1.RestoreDirectory = True
If (OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK) Then
fname = OpenFileDialog1.FileName
End If
txtpathfile.Text = fname
Dim TextLine As String = ""
Dim SplitLine() As String

If System.IO.File.Exists(fname) = True Then                         'CARICA CSV
Dim objReader As New System.IO.StreamReader(txtpathfile.Text, Encoding.ASCII)
Do While objReader.Peek() <> -1
TextLine = objReader.ReadLine()
SplitLine = Split(TextLine, ";")
Me.DataGridView1.Rows.Add(SplitLine)
Loop
Else
MsgBox("File Does Not Exist")
End If

End Sub

然后我想计算所有产品的尺寸的合格性，它们必须低于或等于600(长度(、400(宽度(和600(高度(。

示例：我有一个长=600、宽=300、高=400的产品(一行(。结果将显示在同一行"合适"的第四列。必须对DGV中显示的所有产品执行此操作。

我试着循环所有的细胞，但我失败了。现在我正在尝试至少计算选定的行，但它不起作用。

有人能帮我吗？

我在这里：

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
Dim c1 As Integer
Dim c2 As Integer
Dim c3 As Integer
Dim c4 As String

c1 = DataGridView1.CurrentRow.Cells(1).Value
c2 = DataGridView1.CurrentRow.Cells(2).Value
c3 = DataGridView1.CurrentRow.Cells(3).Value
c4 = DataGridView1.CurrentRow.Cells(4).Value
**'controll null or empty values**
If String.IsNullOrEmpty(c1) OrElse String.IsNullOrEmpty(c2) OrElse String.IsNullOrEmpty(c3) Then Exit Sub
If Not IsNumeric(c1) OrElse Not IsNumeric(c2) OrElse Not IsNumeric(c3) Then Exit Sub
**'control dimensions**
If c1 <= 800 And c2 <= 600 And c3 <= 600 Then
c4 = "Suitable"
End If
End Sub

Answer 1

使用datacolumn表达式让您的生活变得轻松。在这里，我们还使用TextFieldParser来读取CSV:

Public Class Form1
Dim ofd = New OpenFileDialog
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim ofd = New OpenFileDialog

ofd.InitialDirectory = "c:desktop"
ofd.Filter = "CSV files(*.csv)|*.csv"
If (ofd.ShowDialog() <> Windows.Forms.DialogResult.OK) Then
Return
End If

Using csv As New Microsoft.VisualBasic.FileIO.TextFieldParser(ofd.FileName)
csv.TextFieldType = FileIO.FieldType.Delimited
csv.SetDelimiters(",")
Dim dt As New DataTable()
'Change the order of these so it is correct
dt.Columns.Add("Width", GetType(Int32))
dt.Columns.Add("Height", GetType(Int32))
dt.Columns.Add("Length", GetType(Int32))
Dim sdc = dt.Columns.Add("Suitability")
sdc.Expression = "IIF([Width] <= 800 And [Height] <= 600 And [Length] <= 600, 'Suitable' ,'Unsuitable')"
Dim line = 0
While Not csv.EndOfData
Try
line += 1
Dim fields = csv.ReadFields()
Dim ro = dt.NewRow()
ro(0) = Convert.ToInt32(fields(0))
ro(1) = Convert.ToInt32(fields(1))
ro(2) = Convert.ToInt32(fields(2))
dt.Rows.Add(ro)
Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
MsgBox("Line " & ex.Message & "is not valid and will be skipped.")
Catch ex as FormatException
MsgBox("Some bad data could not be converted to integer, near line " & line & " and it will be skipped")
End Try
End While
DataGridView1.DataSource = dt
End Using
End Sub

它的螺母和螺栓是：

• 使用TextFieldParser读取CSV，而不是滚动自己的CSV
• 使用数据表，而不是直接将值放入数据网格视图
• 在数据表中将列创建为INTEGER类型
• 您可以使用数据列表达式来计算适用性——它将根据其他列中的值自动设置数据表列的值
• 您甚至可以通过键入来更新网格视图，最后一列将自动从"适合"/"不适合"更改，具体取决于

还要考虑宽度/高度/长度是合理相对的(除非包装必须向上(，所以在翻转时拒绝输入为600x600x800的包装似乎很残忍，这意味着它将是800x600x600，因此可以接受，所以在分配尺寸之前要考虑对尺寸进行排序，以便最大尺寸在第一列：

While Not csv.EndOfData
Try
Dim fields = csv.ReadFields()
Dim sizes(2) as Integer
For i as Integer = 0 to 2 
sizes(i) = Convert.ToInt32(fields(i))
Next i
Array.Sort(sizes)
Dim ro = dt.NewRow()
ro(0) = sizes(2) 'the largest after sorting
ro(1) = sizes(1)
ro(2) = sizes(0)
dt.Rows.Add(ro)
Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
MsgBox("Line " & ex.Message & "is not valid and will be skipped.")
End Try
End While

其他要点：

• 我不确定CSV中的哪一列是宽度/高度/长度，但也许这无关紧要
• 请在将组件放在表单上后对其进行重命名。没有什么比帮助编写了一个包含50个标签、50个文本字段和20个按钮的大型表单的人更困难的了，这些标签、字段和按钮都称为Label1..50、TextBox1..50等。重命名需要几秒钟的时间，使您的代码变得有意义

Answer 2

好吧，你可以通过处理DataGridView.CellFormatting事件让DGV为你设置第四列的值，所以你不需要手动点击该按钮。

假设Length、Width和Height列的索引分别为0, 1, 2，我们需要设置索引3的值。

'...
'Declare variables for the max values allowed...
Private ReadOnly MaxLength As Integer = 600
Private ReadOnly MaxWidth As Integer = 400
Private ReadOnly MaxHeight As Integer = 600
'...
Private Sub DataGridView1_CellFormatting(sender As Object, e As DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
Dim s = DirectCast(sender, DataGridView)
Dim length, width, height As Integer
If e.RowIndex > -1 AndAlso
Not Enumerable.Range(0, 2).Any(Function(i) s(i, e.RowIndex).Value Is Nothing) AndAlso
Integer.TryParse(s(0, e.RowIndex).Value.ToString, length) AndAlso
Integer.TryParse(s(1, e.RowIndex).Value.ToString, width) AndAlso
Integer.TryParse(s(2, e.RowIndex).Value.ToString, height) Then
s(3, e.RowIndex).Value = If(length <= MaxLength AndAlso width <= MaxWidth AndAlso height <= MaxHeight,
"Suitable", "Unsuitable")
End If
End Sub

仅此而已。

但是，由于任何未知原因，您需要通过单击按钮手动执行此操作，然后您需要按如下方式处理Click事件：

Dim dgv = DataGridView1
Dim length, width, height As Integer
For Each r In dgv.Rows.Cast(Of DataGridViewRow).
Where(Function(x) Not Enumerable.Range(0, 2).Any(Function(i) x.Cells(i).Value Is Nothing))
If Integer.TryParse(r.Cells(0).Value.ToString, length) AndAlso
Integer.TryParse(r.Cells(1).Value.ToString, width) AndAlso
Integer.TryParse(r.Cells(2).Value.ToString, height) Then
r.Cells(3).Value = If(length <= MaxLength AndAlso width <= MaxWidth AndAlso height <= MaxHeight,
"Suitable", "Unsuitable")
End If
Next