Thursday, May 1, 2008

Datagridview - Sorting Numeric Columns that are not bound to a DataSource

Or more precisely sorting Double (floating point, decimal) Columns that are not bound to a DataSource.

There are a lot of examples out there to sort datagridview columns that are bound to a datasource, but I could only find one example that had a solution to solving the problem of sorting when the data is not bound to a datasource.

This is the data that I have, in a csv file
Dharmit,Male,10.001,First
Lomesha,Female,11.001,Second
Jaymit,Male,7.001,Third
Ambrish,Male,8.001,First
Chanda,Female,172.00101,Second

The third column (lets call it Order) contains data that is not integers, they are double values.  I load it into a datagrid view by reading each line to a String Array, and then loading the array to the datagridview.

When I do a sortascending on the Order column, it sorts it as if it was String/ or Text instead of as a number.  If the Order column had values like 10, 11, 7, 8, 172 it would sort it properly, but the decimal point screws it up into thinking that it is text.  The solution to this problem is to use the SortCompare event, and to manually figure out if values are equal, greater than or less than.  Initially I didn't understand the sortResult values.  What does 1, -1, and 0 mean.  And to be honest I still don't know, just trial and error.  Some things to keep in mind.  The Data must not be be bound, and the VirtualMode must be False, and the SortCompare is called when the Sort is called, in my case I call it programatically and have set it such.  Also the code below assumes the data is numeric.

  Private Sub DataGridView1_SortCompare(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewSortCompareEventArgs) Handles DataGridView1.SortCompare
    If Double.Parse(e.CellValue1) > Double.Parse(e.CellValue2) Then
      e.SortResult = 1
    ElseIf Double.Parse(e.CellValue1) < Double.Parse(e.CellValue2) Then
      e.SortResult = -1
    Else
      e.SortResult = 0
    End If
    e.Handled = True
    Exit Sub
  End Sub



3 comments:

bugs said...

I have found your Datagridview sorting Numeric very helpful..... thanks for posting the code. My only question is how would you call that Sub when the datagridview starts, so the data that was loaded would be sorted in say column "0". thanks for the help

Ted Unnikumaran said...

bugs, I believe the answer to your question is that I call it after I add the rows to my datagridview:


Grid.Sort(Sortable_Column, System.ComponentModel.ListSortDirection.Ascending)


Sortable_Column is the name of the column that I've specified would be sorted Programmatic. I do that with the following line:
Me.Sortable_Column.SortMode = DataGridViewColumnSortMode.Programmatic


One thing I've done since I posted the blog post was to change SortCompare such:

Private Sub Grid_SortCompare( _
ByVal sender As Object, ByVal e As DataGridViewSortCompareEventArgs) _
Handles Grid.SortCompare

e.SortResult = CompareEx(e.CellValue1.ToString, e.CellValue2.ToString)
e.Handled = True
Exit Sub
End Sub


And I added the following function to take care of the manual compare

Private Function CompareEx(ByVal s1 As Object, ByVal s2 As Object) As Integer

Try
' convert the objects to string if possible.
Dim a As String = CType(s1, String)
Dim b As String = CType(s2, String)

' If the values are the same, then return 0 to indicate as much
If s1 = s2 Then Return 0

' Look to see if either of the values are numbers
Dim IsNum1 As Boolean = IsNumeric(a)
Dim IsNum2 As Boolean = IsNumeric(b)

' If both values are numeric, then do a numeric compare
If IsNum1 And IsNum2 Then
If Double.Parse(s1) > Double.Parse(s2) Then
Return 1
ElseIf Double.Parse(s1) < Double.Parse(s2) Then
Return -1
Else
Return 0
End If
' If the first value is a number, but the second is not, then assume the number is "higher in the list"
ElseIf IsNum1 And IsNum2 = False Then
Return -1
' if the first values is not a number, but the second is, then assume the number is higher
ElseIf IsNum1 = False And IsNum2 Then
Return 1
Else
' If both values are non nuermic, then do a normal string compare
Return String.Compare(s1, s2)
End If
Catch ex As Exception
'Console.WriteLine(ex.ToString)
End Try

' If we got here, then we failed to compare, so return 0
Return 0
End Function

Jeff Prouty said...

Thank you! This works for me and is much more elegant than other solutions I've found. Had to add an IsNumeric() check to avoid errors with alpha data, though.