Align DataView with Sheetview

Posted by: bob-booker on 8 September 2017, 1:57 pm EST

  • Posted 8 September 2017, 1:57 pm EST



    Hi, I have the following piece of code that works well at copying selected rows.
    The trouble I'm having is that if I copy after sorting by a column, it is copying what was originally in the selected rows' position.
    I can see that it's copying the rows in the data positions rather than the sheets view positions. How can I remedy this?
    Thanks, Bob

    FarPoint.Win.Spread.Model.IRangeSupport irs; irs = (FarPoint.Win.Spread.Model.IRangeSupport)fpBOM.Sheets[0].Models.Data;
    FarPoint.Win.Spread.Model.CellRange cr;

    cr = fpBOM.Sheets[0].GetSelection(0);
    if (cr != null)


    {
    fpBOM.Sheets[0].Rows.Add(0, cr.RowCount);
    irs.Copy(cr.Row + cr.RowCount, 0, 0, 0, cr.RowCount, fpBOM.Sheets[0].ColumnCount);
    }

  • Replied 8 September 2017, 1:57 pm EST

    Hello Bob,


    Using the latest build of Spread(v4.0.2010.2005), I copied the selected Row after applying a Sort through Columns using your code and it does copy the Row as it is now viewed not  as it was originally.Below is the code that I used,


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


    FpSpread1.ActiveSheet.SetValue(0, 0, "S")


    FpSpread1.ActiveSheet.SetValue(0, 1, "E")


    FpSpread1.ActiveSheet.SetValue(0, 2, "A")


    FpSpread1.ActiveSheet.SetValue(0, 3, "K")


    FpSpread1.ActiveSheet.SetValue(1, 0, "W")


    FpSpread1.ActiveSheet.SetValue(1, 1, "G")


    FpSpread1.ActiveSheet.SetValue(1, 2, "P")


    FpSpread1.ActiveSheet.SetValue(1, 3, "V")


    FpSpread1.ActiveSheet.SetValue(2, 0, "O")


    FpSpread1.ActiveSheet.SetValue(2, 1, "L")


    FpSpread1.ActiveSheet.SetValue(2, 2, "Q")


    FpSpread1.ActiveSheet.SetValue(2, 3, "H")


    End Sub


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click


    Dim irs As FarPoint.Win.Spread.Model.IRangeSupport


    irs = DirectCast(FpSpread1.Sheets(0).Models.Data, FarPoint.Win.Spread.Model.IRangeSupport)


    Dim cr As FarPoint.Win.Spread.Model.CellRange


    cr = FpSpread1.Sheets(0).GetSelection(0)


    If cr IsNot Nothing Then


    FpSpread1.Sheets(0).Rows.Add(0, cr.RowCount)


    irs.Copy(cr.Row + cr.RowCount, 0, 0, 0, cr.RowCount, FpSpread1.Sheets(0).ColumnCount)


    End If


    End Sub


    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click


    FpSpread1.ActiveSheet.SortColumns(0, False)


    End Sub


     


    Hope it will help you.


    Regards,

  • Replied 8 September 2017, 1:57 pm EST

    Hi, I've managed to reproduce my problem using your code.
    If you add the extra code into the form load as follows.


    When you start the the App, the 1st column reads S, W, O downwards.
    If I click the sort indicator in the 1st column it now reads O, S, W.
    Then I copy the first row ("O") it adds another ("S") rather than the ("O").


    Thanks Bob


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


    FpSpread1.ActiveSheet.SetValue(0, 0, "S")


    FpSpread1.ActiveSheet.SetValue(0, 1, "E")


    FpSpread1.ActiveSheet.SetValue(0, 2, "A")


    FpSpread1.ActiveSheet.SetValue(0, 3, "K")


    FpSpread1.ActiveSheet.SetValue(1, 0, "W")


    FpSpread1.ActiveSheet.SetValue(1, 1, "G")


    FpSpread1.ActiveSheet.SetValue(1, 2, "P")


    FpSpread1.ActiveSheet.SetValue(1, 3, "V")


    FpSpread1.ActiveSheet.SetValue(2, 0, "O")


    FpSpread1.ActiveSheet.SetValue(2, 1, "L")


    FpSpread1.ActiveSheet.SetValue(2, 2, "Q")


    FpSpread1.ActiveSheet.SetValue(2, 3, "H")


    Dim col As FarPoint.Win.Spread.Column


    col = FpSpread1.ActiveSheet.Columns(0)


    col.SortIndicator = FarPoint.Win.Spread.Model.SortIndicator.Descending


    col.ShowSortIndicator = True


    col.AllowAutoSort = True


    End Sub

  • Replied 8 September 2017, 1:57 pm EST

    Bob,

    I tested the provided snippet and could not reproduce the issue as described by you. On using the above snippet, if I run the application the column reads as "S","W","O" (without double quotes), upon clicking on the sort indicator it sorts the column and it now reads as "O","S","W" and if I copy the first row/cell and it is not adding "S" instead it adds the "O" in the list. Also be noted that If 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.Sorting executed by clicking column headers sorts only the displayed data and does not affect the order of actual data in the data model.

    Hope this helps, Thanks

  • Replied 8 September 2017, 1:57 pm EST

    OK, Thanks Suresh,


    Strange that I'm getting different results.
    Could it be because I'm running version v4.0.3510.2008

  • Replied 8 September 2017, 1:57 pm EST

    I understand it a bit more now, I changed this line to the following code and it now works. 


    irs.Copy(cr.Row + cr.RowCount, 0, 0, 0, cr.RowCount, FpSpread1.Sheets(0).ColumnCount)


    changed to:


    irs.Copy(FpSpread1.Sheets(0).GetModelRowFromViewRow(cr.Row + cr.RowCount), 0, 0, 0, cr.RowCount, FpSpread1.Sheets(0).ColumnCount)


    Thanks
    Bob

Need extra support?

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

Learn More

Forum Channels