基于与其他行的值比较，突出显示DataGridViewRows [英] Highlight DataGridViewRows based on value comparison with other rows

问题描述

我在下面的代码中有一个包含字段列表的Part类。我有一个DataGridView控件，我正在使用NUGET中的高级DGV（ADGV）DLL进行过滤。我必须将ADGV包含在我的winform中。我目前有一个DataGridView，一个搜索框在窗体上，一个按钮运行以下功能。我需要查看所有可见的行，收集最新版本的零件编号的唯一列表，然后通过根据最多的列表检查每行的零件号和转数，对DataGridView中过期的行进行着色。在DataGridView中显示的45,000个条目，这需要17秒。对于〜50个条目，需要〜1.2秒。这是非常低效的，但我看不到减少时间的方法。

  Sub highlightOutdatedParts（）
'目的：使用datagridview控件中的结果，查找每个部分和
'突出显示所有过时的部分相对于他们各自的最新版本
'按部分排序，然后由REV 
如果resultsGrid.ColumnCount = 0或resultsGrid.RowCount = 0然后退出Sub 
 Dim秒表作为新秒表
 stopwatch.Start（）
 resultsGrid.Sort（resultsGrid.Columns（PartNumber），ListSortDirection.Ascending）
 Dim iBag As New ConcurrentBag的部分）
 Dim sortedList As Generic.List（Of Part）
对于每一行作为DataGridViewRow在resultsGrid.Rows 
如果row.Visible = True然后
 Dim iPart作为新的部分（）
尝试
 iPart.Row = row.Cells（0）.Value 
 iPart.Workbook = CStr（row.Cells（1）.Value）
 iPart.Worksheet = CStr（row.Cells（2）.Value） 
 iPart.Product = CStr（row.Cells（3）.Value）
 iPart.PartNumber = CStr（row.Cells（4）.Value）
 iPart.ItemNo = CStr（row。单元格（5）.Value）
 iPart.Rev = CStr（row.Cells（6）.Value）
 iPart.Description = CStr（row.Cells（7）.Value）
 iPart .Units = CStr（row.Cells（8）.Value）
 iPart.Type = CStr（row.Cells（9）.Value）
 iPart.PurchCtgy = CStr（row.Cells（10））。值）
 iPart.Qty = CDbl（row.Cells（11）.Value）
 iPart.TtlPerProd = CDbl（row.Cells（12）.Value）
 iPart.Hierarchy = CStr row.Cells（13）.Value）
 iBag.Add（iPart）
捕获冰作为InvalidCastException 
捕获nre作为NullReferenceException 
结束尝试
结束如果
 Next 
 sortedList =（从c在iBag中由c.PartNumber，c.Rev）.ToList（）'排序并转换为列表
 Dim mostUTDRevList As New Generic.List（Of Part）'最新部件列表，由Rev信件
对于sl As Integer = sortedList.Count  -  1 To 0 Step -1'从列表和工作结束开始开始
 Dim query =从条目在mostUTDRevList'检查在最新的列表中是否存在部件号列表
其中entry.PartNumber = sortedList（sl）.PartNumber 
选择条目
如果query.Count = 0那么'如果这个部分在列表中不存在，添加。 
 mostUTDRevList.Add（sortedList（sl））
结束如果
下一个
'HIGHLIGHT DATAGRIDVIEW ROWS其中零件编号由DATE 
每行作为DataGridViewRow在resultsGrid .Rows 
'如果该版本的该部分不存在于列表中，则必须已过期
尝试
 Dim rowPN As String = CStr（row.Cells（4）.Value ）.ToUpper'获取部件号
 Dim rowR As String = CStr（row.Cells（6）.Value）.ToUpper'get Rev 
 Dim query = From entry在mostUTDRevList中检查该部件号是否与那个Rev在列表中。 
其中entry.PartNumber.ToUpper.Equals（rowPN）AndAlso 
 entry.Rev.ToUpper.Equals（rowR）
选择条目
如果query.Count = 0那么'如果部分是过时突出显示其行
 row.DefaultCellStyle.BackColor = Color.Chocolate 
结束If 
捕获ex As NullReferenceException 
捕捉冰作为InvalidCastException 
结束尝试
 Next 
 resultsGrid.Select（）
 stopwatch.Stop（）
如果没有BackgroundWorker1.IsBusy（）Then timertextbox.Text = stopwatch.Elapsed.TotalSeconds.ToString& secs
 MessageBox.Show（突出显示已成功完成）
 End Sub 
  

解决方案

使用数据比控件几乎总是更快。该控件只是将数据的视图（在网格）中呈现给用户的方法。使用来自那里的数据需要太多的转换才能有效。然后，使用DGV事件突出显示行

很难说出您正在做的所有细节，但它看起来像你正在将数据与其自身进行比较（与某些具体表格相反，其中定义了最新修订版本代码）。为什么数据源是集合，ConcurrentBags等也不清楚。关键是使用为作业优化的集合。

为了演示，我有一个75,000行的表;产品代码是从25,000的池中随机选择的，修订代码是一个随机整数（1-9）。在构建DGV数据源之后（一个 DataTable ），一个 LookUp 是从ProductCode-Revision对创建的。这样做一次又一次：

 '表单级别声明
私有PRCodes作为ILookup（Of String，Int32） 
 
'通过表
'组由产品代码
'为每个创建一个anon Name-Value对象，
'存储代码和最高转数
'将结果转换为LookUp 
 PRCodes = dtSample.AsEnumerable。 
 GroupBy（Function（g）g.Item（ProductCode），
函数（键，值）New with {.Name = key.ToString（），.Value = values 
 Max（Of Int32）（Function（j）j.Field（Of Int32）（RevCode））
}）。 
 ToLookup（Of String，Int32）（Function（k）k.Name，Function（v）v.Value）
  

通过秒表经过时间： 81毫秒创建23731个项目的集合。代码使用匿名类型来存储每个产品代码的最大版本代码。也可以使用具体的类。如果您担心混合套管，则在创建 LookUp （而不是$ $ c $）时使用 .ToLowerInvariant（） c> ToUpper - 请参阅

数据视图按照ProductCode进行排序，以使较低RevCode的色彩明显

我们肯定不会从一个小代码片段中剔除系统的所有细节和约束 - 即使数据类型和原始数据源也是我们猜测的。然而，这应该提供一些帮助，更好的查找方法和使用数据的概念，而不是用户的观点。

有一件事是修订代码 - 你们把它们当作一个字符串。如果这是字母数字，可能无法正确比较 - 9排序/比较高于834或1JW。

另请参见：

查找（TKey， TELEment）Class

匿名类型

I have a Part class with the fields list in the code below. I have a DataGridView control, which I am filtering with the Advanced DGV (ADGV) DLL from NUGET. I must include the ADGV in my winform. I currently have a DataGridView, a search box on the form, and a button to run the following function. I need to go through all of the visible rows, collect a unique list of part numbers with their most recent revisions, and then color the rows in DataGridView which are out of date by checking the part number and rev on each row against the mostuptodate list. For 45,000 entries displayed in DataGridView, this take ~17 secs. For ~50 entries, it take ~1.2 seconds. This is extremely inefficient, but I can't see a way to cut the time down.

Sub highlightOutdatedParts()
    'Purpose: use the results in the datagridview control, find the most recent revision of each part, and 
    '           highlight all outdated parts relative to their respective most recent revisions
    'SORT BY PART NUMBER AND THEN BY REV
    If resultsGrid.ColumnCount = 0 Or resultsGrid.RowCount = 0 Then Exit Sub
    Dim stopwatch As New Stopwatch
    stopwatch.Start()
    resultsGrid.Sort(resultsGrid.Columns("PartNumber"), ListSortDirection.Ascending)
    Dim iBag As New ConcurrentBag(Of Part)
    Dim sortedList As Generic.List(Of Part)
    For Each row As DataGridViewRow In resultsGrid.Rows
        If row.Visible = True Then
            Dim iPart As New Part()
            Try
                iPart.Row = row.Cells(0).Value
                iPart.Workbook = CStr(row.Cells(1).Value)
                iPart.Worksheet = CStr(row.Cells(2).Value)
                iPart.Product = CStr(row.Cells(3).Value)
                iPart.PartNumber = CStr(row.Cells(4).Value)
                iPart.ItemNo = CStr(row.Cells(5).Value)
                iPart.Rev = CStr(row.Cells(6).Value)
                iPart.Description = CStr(row.Cells(7).Value)
                iPart.Units = CStr(row.Cells(8).Value)
                iPart.Type = CStr(row.Cells(9).Value)
                iPart.PurchCtgy = CStr(row.Cells(10).Value)
                iPart.Qty = CDbl(row.Cells(11).Value)
                iPart.TtlPerProd = CDbl(row.Cells(12).Value)
                iPart.Hierarchy = CStr(row.Cells(13).Value)
                iBag.Add(iPart)
            Catch ice As InvalidCastException
            Catch nre As NullReferenceException
            End Try
        End If
    Next
    sortedList = (From c In iBag Order By c.PartNumber, c.Rev).ToList()  ' sort and convert to list
    Dim mostUTDRevList As New Generic.List(Of Part)     ' list of most up to date parts, by Rev letter
    For sl As Integer = sortedList.Count - 1 To 0 Step -1   'start at end of list and work to beginning
        Dim query = From entry In mostUTDRevList    ' check if part number already exists in most up to date list
                    Where entry.PartNumber = sortedList(sl).PartNumber
                    Select entry
        If query.Count = 0 Then     ' if this part does not already exist in the list, add.
            mostUTDRevList.Add(sortedList(sl))
        End If
    Next
    'HIGHLIGHT DATAGRIDVIEW ROWS WHERE PART NUMBERS ARE OUT OF DATE
    For Each row As DataGridViewRow In resultsGrid.Rows
        ' if that part with that Rev does not exist in the list, it must be out of date
        Try
            Dim rowPN As String = CStr(row.Cells(4).Value).ToUpper  ' get part number
            Dim rowR As String = CStr(row.Cells(6).Value).ToUpper   ' get Rev
            Dim query = From entry In mostUTDRevList    ' check if that part number with that Rev is in the list.
                        Where entry.PartNumber.ToUpper.Equals(rowPN) AndAlso
                        entry.Rev.ToUpper.Equals(rowR)
                        Select entry
            If query.Count = 0 Then     ' if the part is out of date highlight its' row
                row.DefaultCellStyle.BackColor = Color.Chocolate
            End If
        Catch ex As NullReferenceException
        Catch ice As InvalidCastException
        End Try
    Next
    resultsGrid.Select()
    stopwatch.Stop()
    If Not BackgroundWorker1.IsBusy() Then timertextbox.Text = stopwatch.Elapsed.TotalSeconds.ToString & " secs"
    MessageBox.Show("Highlighting completed successfully.")
End Sub

解决方案

It is almost always faster to work with the data than the control. The control is simply the means to present a view of the data (in a grid) to the users. Working with the data from there requires too much converting to be effieicent. Then, use the DGV events to highlight the rows

Its hard to tell all the details of what you are doing, but it looks like you are comparing the data to itself (as opposed to some concrete table where the lastest revision codes are defined). Nor is it clear why the datasources are collections, ConcurrentBags etc. The key would be to use collections optimized for the job.

To demonstrate, I have a table with 75,000 rows; the product codes are randomly selected from a pool of 25,000 and a revision code is a random integer (1-9). After the DGV datasource is built (a DataTable) a LookUp is created from the ProductCode-Revision pair. This is done once and once only:

' form level declaration
Private PRCodes As ILookup(Of String, Int32)

' go thru table
' group by the product code
' create an anon Name-Value object for each, 
'     storing the code and highest rev number
' convert result to a LookUp
PRCodes = dtSample.AsEnumerable.
    GroupBy(Function(g) g.Item("ProductCode"),
            Function(key, values) New With {.Name = key.ToString(), .Value = values.
                                    Max(Of Int32)(Function(j) j.Field(Of Int32)("RevCode"))
                                                      }).
    ToLookup(Of String, Int32)(Function(k) k.Name, Function(v) v.Value)

Elapsed time via stopwatch: 81 milliseconds to create the collection of 23731 items. The code uses an anonymous type to store a Max Revision code for each product code. A concrete class could also be used. If you're worried about mixed casing, use .ToLowerInvariant() when creating the LookUp (not ToUpper -- see What's Wrong With Turkey?) and again later when looking up the max rev.

Then rather than looping thru the DGV rows use the RowPrePaint event:

If e.RowIndex = -1 Then Return
If dgv1.Rows(e.RowIndex).IsNewRow Then Return

' .ToLowerInvariant() if the casing can vary row to row
Dim pc = dgv1.Rows(e.RowIndex).Cells("ProductCode").Value.ToString()
Dim rv = Convert.ToInt32(dgv1.Rows(e.RowIndex).Cells("RevCode").Value)

Dim item = PRCodes(pc)(0)
If item > rv Then
    dgv1.Rows(e.RowIndex).DefaultCellStyle.BackColor = Color.MistyRose
End If

Notes

• It takes some time to create the DataSource, but 75,000 rows is a lot to throw at a user
• The time to create the LookUp is minimal - barely measurable
• There is no noticeable wait in displaying them because a) the LookUp is made for this sort of thing, b) rows are done as needed when they are displayed. Row # 19,999 may never be processed if the user never scrolls that far.
• This is all geared to just color a row. If you needed to save the Current/NotCurrent state for each row, add a Boolean column to the DataTable and loop on that. The column can be invisible if to hide it from the user.
  • The random data results in 47,000 out of date RevCodes. Processing 75k rows in the DataTable to set the flag takes 591 milliseconds. You would want to do this before you set the DataTable as the DataSource to prevent changes to the data resulting in various events in the control.

In general, the time to harvest the max RevCode flag and even tag the out of date rows is a trivial increment to creating the datasource.

The Result:

The data view is sorted by ProductCode so that the coloring of lower RevCodes is apparent.

We surely cant grok all the details and constraints of the system from a small snippet - even the data types and original datasource are a guess for us. However, this should provide some help with better look-up methods, and the concept of working with the data rather than the user's view.

One thing is the revision code - yours is treating them as a string. If this is alphanumeric, it may well not compare correctly - "9" sorts/compares higher than "834" or "1JW".

See also:
Lookup(Of TKey, TElement) Class
Anonymous Types

这篇关于基于与其他行的值比较，突出显示DataGridViewRows的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！