Sorting a ComboBox column - by Text instead of Value

Sorting a ComboBox column - by Text instead of Value

Posted by: dgeier on 8 September 2017, 8:49 am EST

• 

  dgeier

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 8 September 2017, 8:49 am EST

  Hello,
  
  
  
  I have a column which is a Combo Box that has been assigned a list of items containing numeric Values and alphanumeric Text.  e.g. a person's id and a name. 
  
  
  
  When I use auto sorting and click on this column, it sorts by the Value, not by the Text.  Is there an easy way to tell it to sort by the Text?
  
  
  
  Thanks!

• 

  scotts-fp

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 8:49 am EST

  Hello,
  
  
  
  You would need to handle the SortColumnCommand event and programatically sort the data. I would suggest calling the SortRows method passing in your own IComparer object that you can use to compare the values that are being passed in.

• 

  dgeier

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 8:49 am EST

  Scott,
  
  
  
  Do you have any sample code you can provide?  I’m not sure how to set up the IComparer object, or how to get it to use my Combobox Text items.
  
  
  
  Thanks!

• 

  scotts-fp

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 8:49 am EST

  Hello,
  
  
  
  The IComparer interface is a very powerful tool to use to do custom sorting. Here is a sample showing you how to get the information you are looking for.
  
  

  
  
  Private Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
  If IsPostBack Then Return
  Dim g As New FarPoint.Web.Spread.ComboBoxCellType
  g.Items = New String() {“S”, “J”, “R1”, “R2”, “D”}
  g.Values = New String() {“34”, “32”, “5”, “4”, “0”}
  FpSpread1.Sheets(0).Columns(1).CellType = g
  FpSpread1.Sheets(0).AllowSort = True
  End Sub
  Private Sub FpSpread1_SortColumnCommand(ByVal sender As Object, ByVal e As FarPoint.Web.Spread.SpreadCommandEventArgs) Handles FpSpread1.SortColumnCommand
  Dim c As FarPoint.Web.Spread.ComboBoxCellType
  Dim asc As Boolean = Session(“SaveAsc”)
  If e.CommandArgument = 1 Then
  asc = Not asc
  e.Handled = True
  c = CType(FpSpread1.Sheets(0).GetCellType(-1, e.CommandArgument), FarPoint.Web.Spread.ComboBoxCellType)
  FpSpread1.Sheets(0).SortRows(1, asc, True, New ComboComparer(c))
  Session(“SaveAsc”) = asc
  End If
  End Sub
  
  
  Public Class ComboComparer
  Implements System.Collections.IComparer
  Dim combo As FarPoint.Web.Spread.ComboBoxCellType
  Public Function Compare(ByVal x As Object, ByVal y As Object) As Integer Implements System.Collections.IComparer.Compare
  Dim cVal1 As String = combo.Items(Array.IndexOf(combo.Values, x.ToString))
  Dim cVal2 As String = combo.Items(Array.IndexOf(combo.Values, y.ToString))
  Return System.Collections.Comparer.Default.Compare(cVal1, cVal2)
  End Function
  Public Sub New(ByVal c As FarPoint.Web.Spread.ComboBoxCellType)
  combo = c
  End Sub
  End Class
  
  
   

• 

  dgeier

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 8:49 am EST

  Thanks Scott!  Your example was easy to follow.  I noticed that you use a session variable to store the sort indication.  Is this because there is still a bug with using the GetSortIndicator, as I’ve read about in previous posts?

• 

  scotts-fp

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 8:49 am EST

  Hello,
  
  
  
  That paticular bug has been fixed.

• 

  klgrube

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 8:49 am EST

  I have the same issue, and I would hope by now that I wouldn’t have to code in the combobox text sort. Is that really still the case? The sample doesn’t help much with sorting from a data-connected combobox. I’m not sure how to do that. I hope you can help.

• 

  deepaksharma14

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 8:49 am EST

  Hi,

  There is no direct way to sort the combobox column based on the display text. However I have created sample application using the code above which works for data bound combobox column as well. Please refer to the attachment.

  Regards,
  
  Deepak Sharma
  
  2017/05/ComboSortSpread.zip

• 

  klgrube

  • Post Options:
  • Link
    Copy

  Posted 25 May 2018, 5:45 am EST

  Well, it’s May, 2018, and I’m now trying to get this to work again. It just isn’t working. I’ve copied in the code from the sample project, and it seems to be sorting by the wrong column. Besides that, I can’t tell from the sample how to get this to work with the four combo box columns (columns 1 - 4) on this spread. I strongly believe this should be a parameter when creating the combobox - whether you want to sort by the data item itself or the text displayed. This makes it really difficult to use combo boxes reasonably.