Sorting mixing column data

Posted by: davewill-fp on 8 September 2017, 1:55 pm EST

  • Posted 8 September 2017, 1:55 pm EST

    Version = 4.0.2010.2005


    When performing


    Call sheet.SortRange(-1, -1, -1, -1, True, mysortinfo)


    ,where mysortinfo is for 2 columns Units/Bulk and Product,

    the grid is not keeping the column data for a row together.


    See the attached Before.bmp and After.bmp.

  • Replied 8 September 2017, 1:55 pm EST



    With Spread 4.0.2010.2005 for Windows Forms I am not able to replicate the issue I tried this code and it didn't move the column data for corresponding rows.Please have a look and let me know if I am missing something.

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            FpSpread1.ActiveSheet.SetValue(0, 0, "C")
            FpSpread1.ActiveSheet.SetValue(1, 0, "A")
            FpSpread1.ActiveSheet.SetValue(2, 0, "E")
            FpSpread1.ActiveSheet.SetValue(3, 0, "B")
            FpSpread1.ActiveSheet.SetValue(0, 1, "3")
            FpSpread1.ActiveSheet.SetValue(1, 1, "5")
            FpSpread1.ActiveSheet.SetValue(2, 1, "11")
            FpSpread1.ActiveSheet.SetValue(3, 1, "22")
            FpSpread1.ActiveSheet.SetValue(0, 2, "32")
            FpSpread1.ActiveSheet.SetValue(1, 2, "51")
            FpSpread1.ActiveSheet.SetValue(2, 2, "13")
            FpSpread1.ActiveSheet.SetValue(3, 2, "21")
        End Sub
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim mysortinfo() As FarPoint.Win.Spread.SortInfo = New FarPoint.Win.Spread.SortInfo() {New FarPoint.Win.Spread.SortInfo(1, True), New FarPoint.Win.Spread.SortInfo(2, True)}
            FpSpread1.ActiveSheet.SortRange(-1, -1, -1, -1, True, mysortinfo)
        End Sub





  • Replied 8 September 2017, 1:55 pm EST

    If the spread has an AddHandler for the DataChanged event and in that event the logic is to look up "A", "B", "C", or "E" and populate column 1 with "3", "5", "11", "22" then when the SortRange fires it fires the DataChanged for each column and row combination.


    In the DataChanged event handler is something like


    Dim alphaColValue As System.String = CType(sheet.GetValue(e.Row, 0), System.String)

    'lookup alphaColValue in database to get the proper values for columns 1 and 2 and populate colums 1 and 2 with code like


    sheet.SetValue(e.Row, 1, DBValueForAlphaColValue)


    It looks like what is happening is that if the DataChanged logic is allowed to execute while the sort is in progress then the data gets askew.  I'm guessing it has to do with the concept of the visible sort being different from the data sort ... i.e. SortRow method versus SortRange method .... since in the DataChanged event the code is reading and writing using GetValue and the sister SetValue I'm not sure why they get out of sync.


    I don't know that the spread could do anything about it without removing the raising of the DataChanged event.


    There is still something more that I don't understand in the DataChanged event when using GetValue and SetValue.  Please provide any thoughts you have.

  • Replied 8 September 2017, 1:55 pm EST


    Yes your observation withe SortRow and SortRange methods being different are correct.If you you use the automatic sorting by clicking the column header or you call the SortRows method of the sheet, then the data model is not sorted, just the data that is displayed to the user. In this case, any data that is hidden before the sort is hidden after the sort, since FarPoint Spread moves any hidden rows automatically. If you use the SortRange method, the data is sorted in the data model and data that is hidden may become visible and vice versa using this method. When you sort data, only the data model is getting sorted. The SelectionModel does not get sorted.

    Another thing to note is the SortRange method is for unbound data only. This method sorts the data in a range of cells by moving the data around in the data model and moving the cell-level styles along with it. This method is not intended for bound data, as it moves data (not necessarily by entire row or column) and has the effect of moving the data around in the data source.


  • Replied 8 September 2017, 1:55 pm EST

    Ok.  So the SelectionModel = what the user sees


    So when my code does a Sheet.GetValue or Sheet.SetValue then it is operating against the SelectionModel and matches what the user sees.  Do the reads/updates to the SelectionModel coorelate properly with the underlying data model internal to the Spread or do I need to somehow keep mappings between the 2?


    I have noticed that if I use Sheet.SortRange(-1,-1,-1,-1,true,sortinfo) then both the DataModel and the SelectionModel reflect the sortinfo.  In your statement above it states that the SortRange method only affects the DataModel.  That conflicts with what I'm seeing.  Unless ... it only affects the datamodel except when using the -1 parameters ... but then you last statement leads me to believe that SortRange affects both DataModel and SelectionModel for -1 parameters and non -1 parameters.

  • Replied 8 September 2017, 1:55 pm EST


    Following information is from the documentation of the spread control and we have detailed description about the different models of Spread in the documentation. About the selection model, it includes any of the settings related to ranges of selected cells. This may
    include methods such as counting the number of selected ranges, adding and
    removing selections, clearing selections and finding whether a cell is selected.

    About your other question,The data model includes the contents of the cells, whether it is the value or
    the formula in a cell, or the cell notes or cell tags. This includes the Value
    properties for cells in the data area of the spreadsheet, the database
    properties for data-bound spreadsheets, and anything having to do with the
    contents in the cells. The data model is an object that supplies the cell values being displayed in the
    sheet.The SetModelDataColumn is different from AddColumn in that you can specify
    which data field you want bound to which column in the data model.

    If you add columns to the model, then they are added to the sheet. The row
    and column in the GetValue and SetValue methods of the data model have the same
    indexes as that of the columns in the sheet as long as the sheet is not sorted.
    If the sheet's rows or columns are sorted, then the view coordinates must be
    mapped to the model coordinates with these SheetView.GetModelRowFromViewRow and
    SheetView.GetModelColumnFromViewColumn methods.The SheetView.GetValue and SheetView.SetValue methods always get and set the
    data in the data model. (It is the same as calling
    SheetView.Models.Data.GetValue and SheetView.Models.Data.SetValue)



Need extra support?

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

Learn More

Forum Channels