Sorting multiple columns

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

  • Posted 8 September 2017, 1:22 pm EST

    <P>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.</P>
    <P> Btw, the gender column is a hidden column.</P>
    <P> </P>
  • Replied 8 September 2017, 1:22 pm EST

    <P>Hi! </P>
    <P>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.</P>
    <P>Any help please?</P>
  • Replied 8 September 2017, 1:22 pm EST

    <P>Hello,</P>
    <P>You can use the SortRows method to implement this.</P><FONT size=2>
    <P></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Dim</FONT></FONT><FONT size=2> si() </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> FarPoint.Win.Spread.SortInfo = </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>New</FONT></FONT><FONT size=2> FarPoint.Win.Spread.SortInfo() {</FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>New</FONT></FONT><FONT size=2> FarPoint.Win.Spread.SortInfo(3, </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>False</FONT></FONT><FONT size=2>), </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>New</FONT></FONT><FONT size=2> FarPoint.Win.Spread.SortInfo(0, </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>True</FONT></FONT><FONT size=2>)}</P>
    <P>FpSpread1.Sheets(0).SortRows(0, FpSpread1.Sheets(0).RowCount, si)</P></FONT>
  • Replied 8 September 2017, 1:22 pm EST

    <P>Hello scotts,</P>
    <P>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 <STRONG>using Gender column as my primary key in Descending order and Lastname column as my secondary key in Ascending order</STRONG>. 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. </P>
    <P>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.</P>
    <P>Column A -->Ukey</P>
    <P>Column B -->IDNo</P>
    <P>Column C -->LastName</P>
    <P>Column D -->FirstName</P>
    <P>Column E -->MiddleName</P>
    <P>Column F-->Gender</P>
    <P>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?</P>
    <P>here is the part of the code i have right now</P><FONT size=2>
    <P></FONT><FONT color=#0000ff size=2>Private</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>Sub</FONT><FONT size=2> btnDisplay_Click(</FONT><FONT color=#0000ff size=2>ByVal</FONT><FONT size=2> sender </FONT><FONT color=#0000ff size=2>As</FONT><FONT size=2> System.Object, </FONT><FONT color=#0000ff size=2>ByVal</FONT><FONT size=2> e </FONT><FONT color=#0000ff size=2>As</FONT><FONT size=2> System.EventArgs) </FONT><FONT color=#0000ff size=2>Handles</FONT><FONT size=2> btnDisplay.Click</P>
    <P></FONT><FONT size=2>SuspendLayout()</P>
    <P>cnn.ConnectionString = </FONT><FONT color=#800000 size=2>"Data Source=.\sqlexpress;Initial Catalog=NDDU-IBED;Integrated Security=True"</P></FONT><FONT size=2>
    <P></FONT><FONT color=#0000ff size=2>If</FONT><FONT size=2> cnn.State = ConnectionState.Closed </FONT><FONT color=#0000ff size=2>Then</FONT><FONT size=2> cnn.Open()</P>
    <P>cmdGrade = cnn.CreateCommand</P>
    <P>cmdGrade.CommandText = </FONT><FONT color=#800000 size=2>"SELECT DISTINCT * FROM tblEnrol WHERE SectionName = '"</FONT><FONT size=2> & cboSection.Text & </FONT><FONT color=#800000 size=2>"' AND SubjectName = '"</FONT><FONT size=2> & cboSubjectName.Text & </FONT><FONT color=#800000 size=2>"' AND YearLevel = '"</FONT><FONT size=2> & cboYearLevel.Text & </FONT><FONT color=#800000 size=2>"'"</P></FONT><FONT size=2>
    <P>daGrade.MissingSchemaAction = MissingSchemaAction.AddWithKey</P>
    <P>dsGrade.Clear()</P>
    <P>dsSection.Clear()</P>
    <P>dsSubject.Clear()</P>
    <P>daGrade.SelectCommand = cmdGrade</P>
    <P>daGrade.Fill(dsGrade, </FONT><FONT color=#800000 size=2>"tblEnrol"</FONT><FONT size=2>)</P>
    <P>cnn.Close()</P>
    <P></FONT><FONT color=#0000ff size=2>Dim</FONT><FONT size=2> si() </FONT><FONT color=#0000ff size=2>As</FONT><FONT size=2> FarPoint.Win.Spread.SortInfo = </FONT><FONT color=#0000ff size=2>New</FONT><FONT size=2> FarPoint.Win.Spread.SortInfo() {</FONT><FONT color=#0000ff size=2>New</FONT><FONT size=2> FarPoint.Win.Spread.SortInfo(1, </FONT><FONT color=#0000ff size=2>True</FONT><FONT size=2>), </FONT><FONT color=#0000ff size=2>New</FONT><FONT size=2> FarPoint.Win.Spread.SortInfo(2, </FONT><FONT color=#0000ff size=2>True</FONT><FONT size=2>)}</P>
    <P></FONT><FONT color=#008000 size=2>''HideSheets()</P></FONT><FONT size=2>
    <P></FONT><FONT color=#008000 size=2>'bind FpSpread2 to datasource</P></FONT><FONT size=2>
    <P></FONT><FONT color=#008000 size=2>'1st Grading</P></FONT><FONT size=2>
    <P>FpSpread2.Sheets(0).DataSource = dsGrade</P>
    <P>FpSpread2.Sheets(0).DataMember = </FONT><FONT color=#800000 size=2>"tblEnrol"</P></FONT><FONT size=2>
    <P></FONT><FONT color=#008000 size=2>'Create CellType and specify number decimal for 1st grading sheet</P></FONT><FONT size=2>
    <P></FONT><FONT color=#0000ff size=2>With</FONT><FONT size=2> FpSpread2.Sheets(0)</P>
    <P>.DataAutoCellTypes = </FONT><FONT color=#0000ff size=2>False</P></FONT><FONT size=2>
    <P></FONT><FONT color=#0000ff size=2>Dim</FONT><FONT size=2> num </FONT><FONT color=#0000ff size=2>As</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>New</FONT><FONT size=2> FarPoint.Win.Spread.CellType.NumberCellType</P>
    <P>num.DecimalPlaces = 0</P>
    <P>.Columns(12, 81).CellType = num </FONT><FONT color=#008000 size=2>'column 12 to 81</P></FONT><FONT size=2>
    <P>.Columns(82).CellType = num</P>
    <P>.Columns(86).CellType = num</P>
    <P>.Columns(90).CellType = num</P>
    <P>.Columns(94).CellType = num</P>
    <P>.Columns(98).CellType = num</P>
    <P>.Columns(102).CellType = num</P>
    <P>.Columns(106).CellType = num</P>
    <P>.Columns(84, 85).CellType = num</P>
    <P>.Columns(88, 89).CellType = num</P>
    <P>.Columns(92, 93).CellType = num</P>
    <P>.Columns(96, 97).CellType = num</P>
    <P>.Columns(100, 101).CellType = num</P>
    <P>.Columns(104, 105).CellType = num</P>
    <P>.Columns(109).CellType = num</P>
    <P></FONT><FONT color=#008000 size=2>'sort multi-column</P></FONT><FONT size=2>
    <P>.SortRows(0, FpSpread2.Sheets(0).RowCount, si)</P>
    <P></FONT><FONT color=#0000ff size=2>End</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>With</P></FONT><FONT size=2>
    <P>FormatSpread()</P></FONT>
    <P>   :</P>
    <P>   :</P>
    <P>End Sub</P>
    <P>Thanks for your reply.</P>
  • Replied 8 September 2017, 1:22 pm EST

    <P>Hello,</P>
    <P>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.</P>
    <P><FONT color=#0000ff size=2>Dim</FONT><FONT size=2> si() </FONT><FONT color=#0000ff size=2>As</FONT><FONT size=2> FarPoint.Win.Spread.SortInfo = </FONT><FONT color=#0000ff size=2>New</FONT><FONT size=2> FarPoint.Win.Spread.SortInfo() {</FONT><FONT color=#0000ff size=2>New</FONT><FONT size=2> FarPoint.Win.Spread.SortInfo(4, </FONT><FONT color=#0000ff size=2>False</FONT><FONT size=2>), </FONT><FONT color=#0000ff size=2>New</FONT><FONT size=2> FarPoint.Win.Spread.SortInfo(2, </FONT><FONT color=#0000ff size=2>True</FONT><FONT size=2>)}</P></FONT>
  • 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