vb.net sorting mixed integer and string column in ListView

Question

I faced quiet a really big problem (for me). I cannot find any solution for it. I have four columns in my ListView:

ID = integer
Name = string
Response = boolean
Memory = mixed integer with string (1'000 KB)

After [ColumnClick] I can sort (asc/desc) first 3 columns "normally", but when i am trying to sort the fouth one, instead of

1 KB / 5 KB / 1'000 KB

I do receive something like this:

1 KB / 1'000 KB / 5 KB

The fourth column is printed like this:

ListView1.Items(Count).SubItems.Add(FormatNumber(pMem, 0) & " KB")

I was thinking about this:

    If e.Column.ToString = 3 Then
        Dim final As Integer
        For Each value In ListView1.Items
            Replace(value.SubItems(3), "'", "")
            Replace(value.SubItems(3), " KB", "")
            final = value
        Next
    Else
    ...

Then sort integers same way as ID was, and then put them back, to the ListView somehow. But I cannot figure out how.

For sorting in Form:

Private Sub ListView1_ColumnClick(sender As Object, e As ColumnClickEventArgs) Handles ListView1.ColumnClick
    Dim ListViewSorter As New ListViewSorter

    With ListViewSorter
        .SortingOrder = 1
        .ColumnIndex = e.Column
    End With

    ListView1.ListViewItemSorter = ListViewSorter
End Sub

And my ListViewSorter.vb

Public Class ListViewSorter
Implements IComparer

Private ColumnId As Integer
Private SortOrder As SortOrder
Private ItemComparer As CaseInsensitiveComparer

Public Sub New()
    ColumnId = 0
    SortOrder = 0
    ItemComparer = New CaseInsensitiveComparer()
End Sub

Public Property ColumnIndex() As Integer
    Get
        Return ColumnId
    End Get
    Set(Value As Integer)
        ColumnId = Value
    End Set
End Property

Public Property SortingOrder() As SortOrder
    Get
        Return SortOrder
    End Get
    Set(Value As SortOrder)
        SortOrder = Value
    End Set
End Property

Public Function Compare(x As Object, y As Object) As Integer Implements IComparer.Compare
    Dim myResults As Integer
    Dim strX As String = DirectCast(x, ListViewItem).SubItems(ColumnId).Text
    Dim strY As String = DirectCast(y, ListViewItem).SubItems(ColumnId).Text
    Dim num As Point

    If Integer.TryParse(strX, num.X) And Integer.TryParse(strY, num.Y) Then
        myResults = ItemComparer.Compare(num.X, num.Y)
    Else
        myResults = ItemComparer.Compare(strX, strY)
    End If

    If SortOrder = 1 Then
        Return myResults
    ElseIf SortOrder = 2 Then
        Return -myResults
    Else
        Return 0
    End If
End Function
End Class

Answer 1

I had the same problem and none of the answers seemed to work. Finally, I did a sort which converted each lv SubItem to a string array, then converted the value to be sorted to a string, then converted the value to an Integer, set the ascending/descending direction and, if swapping was necessary, packed each of the items from string array to the opposite LV subitem. Here is the Code I used. It works well for my application.

Private Sub SortStandings()
    'Sorts Records by won/loss Percentage for Year
    'Listview Items to be worked with
    Dim LVItemX As ListViewItem
    Dim LVItemY As ListViewItem
    'String Copy of Value to Compare from List View Item
    Dim Strx As String
    Dim Stry As String
    'Integer Value of Value to Compare From List (Cannot Cast LV Value directly to String
    Dim XX As Integer
    Dim YY As Integer
    'Sort Direction Indicator
    Dim J As Integer

    Dim ITemX(ColLst.Pct) As String
    Dim ItemY(ColLst.Pct) As String
    Dim Altx As Integer
    Dim Alty As Integer
    'For each List View Item except the Last one
    For x = 0 To lvDisplay.Items.Count - 2
        LVItemX = lvDisplay.Items(x)
        'Compare it to each lvItem below it
        For y = x + 1 To lvDisplay.Items.Count - 1
            LVItemY = lvDisplay.Items(y)
            'Convert values to Sort to String (Cannot go directly to Integer
            Strx = LVItemX.SubItems.Item(ColLst.Pct).Text
            Stry = LVItemY.SubItems.Item(ColLst.Pct).Text
            'Take Care of Possible Discrepancy in Format
            If Strx = "" Then Strx = "0%"
            If Stry = "" Then Stry = "0%"
            'Convert Strings to Integer
            XX = CInt(Microsoft.VisualBasic.Left(Strx, Len(Strx) - 1))
            YY = CInt(Microsoft.VisualBasic.Left(Stry, Len(Stry) - 1))
            'Set up a tie Breaker Based on as Different Column
            Altx = CInt(LVItemX.SubItems.Item(ColLst.YW).Text)
            Alty = CInt(LVItemY.SubItems.Item(ColLst.YW).Text)
            'Set up  Comparison for Descending ( Change > to < for Ascending )
            J = 0
            If XX < YY Then
                J = -1
            End If
            If XX = YY And Altx < Alty Then
                J = -1
            End If
            'rebuild Both LV Items in New Locations
            If J = -1 Then
                For n = 0 To ColLst.Pct
                    ITemX(n) = LVItemX.SubItems.Item(n).Text
                    ItemY(n) = LVItemY.SubItems.Item(n).Text
                Next
                For n = 0 To ColLst.Pct
                    LVItemX.SubItems.Item(n).Text = ItemY(n)
                    LVItemY.SubItems.Item(n).Text = ITemX(n)
                Next
            End If
        Next
    Next
End Sub

Answer 2

The optimum solution would be to use a DataGridView. Using a DataSource, rather than creating and adding ListViewItems, then ListViewSubItems, you could populate the control with one line of code. It also uses typed columns, so a column of KBs or KGs would sort like numbers. A simple linq expression could then be used to reorder the DataSource.

It would be worth considering adding the "KB" designation in the ColumnHeader so it doesn't repeat over and over in the text and have to be stripped off for sorting.

Since the ListView stores only text, you can have other issues: "9 KB" will sort higher than "1000 KB" because there are no integers there - they are numerals. In many cases, you can use a NaturalSort for strings containing numerals ("just like explorer"), but for whatever reason it didn't like the ' number group separator.

Which leaves us with a ListViewItemSorter. You probably already have one for sorting column 1 and 2. A little more logic is needed to extract an actual numeric value from the KB column for sorting:

Public Class ListViewKBItemComparer
    Implements IComparer
    Private mySortFlipper As Int32 = 1
    Public Property SortColumn As Int32 = 0

    Public Sub New()

    End Sub

    Public Sub New(column As Int32, sort As SortOrder)
        mySortFlipper = If(sort = SortOrder.Ascending, 1, -1)
        SortColumn = column
    End Sub

    Public Function Compare(x As Object, y As Object) As Integer Implements IComparer.Compare
        Dim result As Int32
        Dim lvX = CType(x, ListViewItem)
        Dim lvY = CType(y, ListViewItem)

        If SortColumn = 3 Then
            result = KBCompare(lvX, lvY)
        Else
            result = String.Compare(lvX.SubItems(SortColumn).Text,
                                    lvY.SubItems(SortColumn).Text)
        End If

        Return (result * mySortFlipper)
    End Function


    Private Function KBCompare(x As ListViewItem, y As ListViewItem) As Int32
        ' strip the text
        Dim xs = x.SubItems(SortColumn).Text.Replace(" KB", "")
        Dim ys = y.SubItems(SortColumn).Text.Replace(" KB", "")

        ' convert to decimal
        Dim decX As Decimal = -1
        Decimal.TryParse(xs, decX)

        Dim decY As Decimal = -1
        Decimal.TryParse(ys, decY)
        ' return comparison result
        Return decX.CompareTo(decY)

    End Function
End Class

I used Decimal in case some of the strings could also include fractions. If not, just change Decimal to Int32.

Usage:

myLV.ListViewItemSorter = New ListViewKBItemComparer(e.Column, SortAsc)
myLV.Sort()

e.Column is a param from the ColumnClick event; and SortAsc is a SortOrder to indicate Asc or Desc.

Answer 3

Yes you should use your given example, to remove the text in your array, after tweaking it a bit.

If it's a String Array, which I suspect it is, you should use your replace method to remove the apostrophe, but you should replace them before you add them to the ListBox.

You can do this by:

For Each value In FormatNumber
        FormatNumber(count) = Replace(value, "'", "")
        count +=1
Next

I am guessing that FormatNumber is a string array, so you need to convert it to an integer. To do that, I just copied the example from the first response to this question.

Add the following line of code after the For loop above.

Dim intArray = Array.ConvertAll(FormatNumber, Function(str) Int32.Parse(str))

Now add each value of intArray after sorting it to the Listbox and see if it works.