Sorting a DGV column ascending while putting null values at the bottom

Question

I need to make it so that the null values in the column are put at the bottom of the DGV column. I need it in ascending order and this puts null values first but I don't want to see that first, I want to see them after my largest value. This is what I have so far but it puts null values first, I am not sure how to approach a custom sort that puts null values last. Example:

    dim display as integer = 0

    dataGridView1.Columns("Weight").DisplayIndex = display
    display += 1
    dataGridView1.Columns("Height").DisplayIndex = display

    dataGridView1.Sort(dataGridView1.Columns("Height"), System.ComponentModel.ListSortDirection.Ascending)

don't worry about why I have null values in height, I'm just using example data for this problem.

Answer 1

String sorting

What you're asking can be done by creating a custom row comparer for your DataGridView (adapted version of MSDN's code):

Public Class DataGridViewColumnComparer
    Implements IComparer

    Private SortOrderModifier As Integer = 1
    Private ColumnName As String

    Public Sub New(ByVal ColumnName As String, ByVal Order As SortOrder)
        Me.ColumnName = ColumnName
        If Order = SortOrder.Descending Then
            SortOrderModifier = -1
        ElseIf Order = SortOrder.Ascending Then
            SortOrderModifier = 1
        End If
    End Sub

    Public Function Compare(ByVal x As Object, ByVal y As Object) As Integer Implements IComparer.Compare
        Dim Row1 As DataGridViewRow = CType(x, DataGridViewRow)
        Dim Row2 As DataGridViewRow = CType(y, DataGridViewRow)

        Dim Row1Value As String = Row1.Cells(ColumnName).Value.ToString()
        Dim Row2Value As String = Row2.Cells(ColumnName).Value.ToString()

        'If CompareResult = 1 that means that Row1 should be placed BELOW Row2.
        'If CompareResult = -1 that means that Row1 should be placed ABOVE Row2.
        Dim CompareResult As Integer = String.Compare(Row1Value, Row2Value)

        If String.IsNullOrEmpty(Row1Value) = True Then
            CompareResult = 1 'Row1 has an empty/null value, place it below Row2.
        ElseIf String.IsNullOrEmpty(Row2Value) = True Then
            CompareResult = -1 'Row2 has an empty/null value, place Row1 above.
        End If

        Return CompareResult * SortOrderModifier
    End Function
End Class

Usage:

DataGridView1.Sort(New DataGridViewColumnComparer("Height", SortOrder.Ascending))

And it works!

---

Numerical sorting

The previous example showed a string-based sorting algorithm. It'll sort each cell character-by-character based on which character comes first in the charset.

If you instead want to sort the rows numerically you'd have to adapt the code a little more:

Public Class DataGridViewNumericComparer
    Implements IComparer

    Private SortOrderModifier As Integer = 1
    Private ColumnName As String

    Public Sub New(ByVal ColumnName As String, ByVal Order As SortOrder)
        Me.ColumnName = ColumnName
        If Order = SortOrder.Descending Then
            SortOrderModifier = -1
        ElseIf Order = SortOrder.Ascending Then
            SortOrderModifier = 1
        End If
    End Sub

    Public Function Compare(ByVal x As Object, ByVal y As Object) As Integer Implements IComparer.Compare
        Dim Row1 As DataGridViewRow = CType(x, DataGridViewRow)
        Dim Row2 As DataGridViewRow = CType(y, DataGridViewRow)

        Dim Row1Value As String = Row1.Cells(ColumnName).Value.ToString()
        Dim Row2Value As String = Row2.Cells(ColumnName).Value.ToString()

        Dim Row1NumVal As Nullable(Of Long) = Nothing
        Dim Row2NumVal As Nullable(Of Long) = Nothing

        Dim Row1Temp As Long = 0
        Dim Row2Temp As Long = 0

        If Long.TryParse(Row1Value, Row1Temp) = True Then Row1NumVal = Row1Temp
        If Long.TryParse(Row2Value, Row2Temp) = True Then Row2NumVal = Row2Temp

        'If CompareResult = 1 that means that Row1 should be placed BELOW Row2.
        'If CompareResult = -1 that means that Row1 should be placed ABOVE Row2.
        Dim CompareResult As Integer

        If Row1NumVal.HasValue = True AndAlso Row2NumVal.HasValue = True Then
            CompareResult = Row1NumVal.Value.CompareTo(Row2NumVal.Value)

        ElseIf Row1NumVal.HasValue = False Then
            CompareResult = 1

        ElseIf Row2NumVal.HasValue = False Then
            CompareResult = -1

        End If

        Return CompareResult * SortOrderModifier
    End Function
End Class

Usage:

DataGridView1.Sort(New DataGridViewNumericComparer("Height", SortOrder.Ascending))

This version will sort all numbers in numerical order and place everything that is not a number at the bottom.

Answer 2

Another alternative could be to handle the DataGridView.SortCompare event:

Private Sub DataGridView1_SortCompare(sender As Object, e As DataGridViewSortCompareEventArgs) Handles DataGridView1.SortCompare
    If e.CellValue1 Is Nothing Then e.SortResult += 1 : e.Handled = True
    If e.CellValue2 Is Nothing Then e.SortResult -= 1 : e.Handled = True
    If e.Column.DataGridView.SortOrder = SortOrder.Descending Then e.SortResult = -e.SortResult
End Sub