Sorting multiple columns

Posted by: lg_sony_27 on 8 September 2017, 1:22 pm EST

  • Posted 8 September 2017, 1:22 pm EST

    Hi! How do i sort multiple columns? for example Columns A,B,C,D has the following LastName, FirstName MiddleName, Gender. I would like to separate the ladies from gentlemen but the lastname is arranged alphabetically ascending and the gender - descending.

     Btw, the gender column is a hidden column.


  • Replied 8 September 2017, 1:22 pm EST


    I want to do this programmatically. I accessed the whole table and display it in spread with some columns hidded. Is there a way to sort column A (LastName as secondary key, sort Ascending) and column D (Gender as primary key, sort Descending).  I dont like the user to click the header to sort the column, I want it sorted automatically when i display the records.

    Any help please?

  • Replied 8 September 2017, 1:22 pm EST


    You can use the SortRows method to implement this.

    Dim si() As FarPoint.Win.Spread.SortInfo = New FarPoint.Win.Spread.SortInfo() {New FarPoint.Win.Spread.SortInfo(3, False), New FarPoint.Win.Spread.SortInfo(0, True)}

    FpSpread1.Sheets(0).SortRows(0, FpSpread1.Sheets(0).RowCount, si)

  • Replied 8 September 2017, 1:22 pm EST

    Hello scotts,

    Actually, when I did a searched on sorting I've seen your post and I've tried it and it works but...something is not right with my output. I have a list of names, and I am sorting records alphabetically using Gender column as my primary key in Descending order and Lastname column as my secondary key in Ascending order. I am confused why is it that there are name which are not sorted right. I want all the Male first followed by all Female but there are some Male students still displayed after Female.

    I have 700 plus columns and i put these in datatable. I'll just state here the first few columns (Fields in my table) in order.

    Column A -->Ukey

    Column B -->IDNo

    Column C -->LastName

    Column D -->FirstName

    Column E -->MiddleName

    Column F-->Gender

    A,B and F are hidden columns in the spread. In my understanding (correct me if I'm wrong) your code sorts the spread using Column A and Column B as basis?

    here is the part of the code i have right now

    Private Sub btnDisplay_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDisplay.Click


    cnn.ConnectionString = "Data Source=.\sqlexpress;Initial Catalog=NDDU-IBED;Integrated Security=True"

    If cnn.State = ConnectionState.Closed Then cnn.Open()

    cmdGrade = cnn.CreateCommand

    cmdGrade.CommandText = "SELECT DISTINCT * FROM tblEnrol WHERE SectionName = '" & cboSection.Text & "' AND SubjectName = '" & cboSubjectName.Text & "' AND YearLevel = '" & cboYearLevel.Text & "'"

    daGrade.MissingSchemaAction = MissingSchemaAction.AddWithKey




    daGrade.SelectCommand = cmdGrade

    daGrade.Fill(dsGrade, "tblEnrol")


    Dim si() As FarPoint.Win.Spread.SortInfo = New FarPoint.Win.Spread.SortInfo() {New FarPoint.Win.Spread.SortInfo(1, True), New FarPoint.Win.Spread.SortInfo(2, True)}


    'bind FpSpread2 to datasource

    '1st Grading

    FpSpread2.Sheets(0).DataSource = dsGrade

    FpSpread2.Sheets(0).DataMember = "tblEnrol"

    'Create CellType and specify number decimal for 1st grading sheet

    With FpSpread2.Sheets(0)

    .DataAutoCellTypes = False

    Dim num As New FarPoint.Win.Spread.CellType.NumberCellType

    num.DecimalPlaces = 0

    .Columns(12, 81).CellType = num 'column 12 to 81

    .Columns(82).CellType = num

    .Columns(86).CellType = num

    .Columns(90).CellType = num

    .Columns(94).CellType = num

    .Columns(98).CellType = num

    .Columns(102).CellType = num

    .Columns(106).CellType = num

    .Columns(84, 85).CellType = num

    .Columns(88, 89).CellType = num

    .Columns(92, 93).CellType = num

    .Columns(96, 97).CellType = num

    .Columns(100, 101).CellType = num

    .Columns(104, 105).CellType = num

    .Columns(109).CellType = num

    'sort multi-column

    .SortRows(0, FpSpread2.Sheets(0).RowCount, si)

    End With




    End Sub

    Thanks for your reply.

  • Replied 8 September 2017, 1:22 pm EST


    The columns are 0 based, so if you want to sort column A and then B, then the indexes in the sortinfo array you created should be 0 and 1. However, from your schematic, it looks like the columns you want to sort by are 4 and 2. Also, column 4, you said you wanted to be descending, so you need to pass false into the first SortInfo object in the aray.

    Dim si() As FarPoint.Win.Spread.SortInfo = New FarPoint.Win.Spread.SortInfo() {New FarPoint.Win.Spread.SortInfo(4, False), New FarPoint.Win.Spread.SortInfo(2, True)}

  • Replied 8 September 2017, 1:22 pm EST

    Thanks! this works fine.
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels