Align DataView with Sheetview

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

  • Posted 8 September 2017, 1:57 pm EST



    <P>Hi, I have the following piece of code that works well at copying selected rows.<BR>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.<BR>I can see that it's copying the rows in the data positions rather than the sheets view positions. How can I remedy this?<BR>Thanks, Bob<BR><BR>FarPoint.Win.Spread.Model.IRangeSupport irs; irs = (FarPoint.Win.Spread.Model.IRangeSupport)fpBOM.Sheets[0].Models.Data;<BR>FarPoint.Win.Spread.Model.CellRange cr;<BR><BR>cr = fpBOM.Sheets[0].GetSelection(0);<BR>if (cr != null)</P>
    <P>{ <BR>fpBOM.Sheets[0].Rows.Add(0, cr.RowCount);<BR>irs.Copy(cr.Row + cr.RowCount, 0, 0, 0, cr.RowCount, fpBOM.Sheets[0].ColumnCount);<BR>}</P>
  • Replied 8 September 2017, 1:57 pm EST

    <P>Hello Bob,</P>
    <P>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, </P><FONT color=#0000ff size=2><FONT color=#0000ff size=2>
    <P>Private</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Sub</FONT></FONT><FONT size=2> Form1_Load(</FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>ByVal</FONT></FONT><FONT size=2> sender </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> System.Object, </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>ByVal</FONT></FONT><FONT size=2> e </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> System.EventArgs) </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Handles</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>MyBase</FONT></FONT><FONT size=2>.Load</P>
    <P>FpSpread1.ActiveSheet.SetValue(0, 0, </FONT><FONT color=#800000 size=2><FONT color=#800000 size=2>"S"</FONT></FONT><FONT size=2>)</P>
    <P>FpSpread1.ActiveSheet.SetValue(0, 1, </FONT><FONT color=#800000 size=2><FONT color=#800000 size=2>"E"</FONT></FONT><FONT size=2>)</P>
    <P>FpSpread1.ActiveSheet.SetValue(0, 2, </FONT><FONT color=#800000 size=2><FONT color=#800000 size=2>"A"</FONT></FONT><FONT size=2>)</P>
    <P>FpSpread1.ActiveSheet.SetValue(0, 3, </FONT><FONT color=#800000 size=2><FONT color=#800000 size=2>"K"</FONT></FONT><FONT size=2>)</P>
    <P>FpSpread1.ActiveSheet.SetValue(1, 0, </FONT><FONT color=#800000 size=2><FONT color=#800000 size=2>"W"</FONT></FONT><FONT size=2>)</P>
    <P>FpSpread1.ActiveSheet.SetValue(1, 1, </FONT><FONT color=#800000 size=2><FONT color=#800000 size=2>"G"</FONT></FONT><FONT size=2>)</P>
    <P>FpSpread1.ActiveSheet.SetValue(1, 2, </FONT><FONT color=#800000 size=2><FONT color=#800000 size=2>"P"</FONT></FONT><FONT size=2>)</P>
    <P>FpSpread1.ActiveSheet.SetValue(1, 3, </FONT><FONT color=#800000 size=2><FONT color=#800000 size=2>"V"</FONT></FONT><FONT size=2>)</P>
    <P>FpSpread1.ActiveSheet.SetValue(2, 0, </FONT><FONT color=#800000 size=2><FONT color=#800000 size=2>"O"</FONT></FONT><FONT size=2>)</P>
    <P>FpSpread1.ActiveSheet.SetValue(2, 1, </FONT><FONT color=#800000 size=2><FONT color=#800000 size=2>"L"</FONT></FONT><FONT size=2>)</P>
    <P>FpSpread1.ActiveSheet.SetValue(2, 2, </FONT><FONT color=#800000 size=2><FONT color=#800000 size=2>"Q"</FONT></FONT><FONT size=2>)</P>
    <P>FpSpread1.ActiveSheet.SetValue(2, 3, </FONT><FONT color=#800000 size=2><FONT color=#800000 size=2>"H"</FONT></FONT><FONT size=2>)</P>
    <P></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>End</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Sub</P></FONT></FONT><FONT size=2>
    <P></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Private</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Sub</FONT></FONT><FONT size=2> Button1_Click(</FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>ByVal</FONT></FONT><FONT size=2> sender </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> System.Object, </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>ByVal</FONT></FONT><FONT size=2> e </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> System.EventArgs) </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Handles</FONT></FONT><FONT size=2> Button1.Click</P>
    <P></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Dim</FONT></FONT><FONT size=2> irs </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> FarPoint.Win.Spread.Model.IRangeSupport</P>
    <P>irs = </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>DirectCast</FONT></FONT><FONT size=2>(FpSpread1.Sheets(0).Models.Data, FarPoint.Win.Spread.Model.IRangeSupport)</P>
    <P></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Dim</FONT></FONT><FONT size=2> cr </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> FarPoint.Win.Spread.Model.CellRange</P>
    <P>cr = FpSpread1.Sheets(0).GetSelection(0)</P>
    <P></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>If</FONT></FONT><FONT size=2> cr </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>IsNot</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Nothing</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Then</P></FONT></FONT><FONT size=2>
    <P>FpSpread1.Sheets(0).Rows.Add(0, cr.RowCount)</P>
    <P>irs.Copy(cr.Row + cr.RowCount, 0, 0, 0, cr.RowCount, FpSpread1.Sheets(0).ColumnCount)</P>
    <P></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>End</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>If</P></FONT></FONT><FONT size=2>
    <P></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>End</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Sub</P></FONT></FONT><FONT size=2>
    <P></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Private</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Sub</FONT></FONT><FONT size=2> Button2_Click(</FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>ByVal</FONT></FONT><FONT size=2> sender </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> System.Object, </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>ByVal</FONT></FONT><FONT size=2> e </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> System.EventArgs) </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Handles</FONT></FONT><FONT size=2> Button2.Click</P>
    <P>FpSpread1.ActiveSheet.SortColumns(0, </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>False</FONT></FONT><FONT size=2>)</P>
    <P></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>End</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Sub</FONT></FONT></P>
    <P><FONT color=#0000ff size=2><FONT color=#0000ff size=2></FONT></FONT> </P>
    <P><FONT color=#0000ff size=2><FONT color=#0000ff size=2><FONT color=#000000>Hope it will help you.</FONT></FONT></FONT></P>
    <P>Regards,<FONT color=#0000ff size=2><FONT color=#0000ff size=2></P></FONT></FONT>
  • Replied 8 September 2017, 1:57 pm EST

    <P>Hi, I've managed to reproduce my problem using your code.<BR>If you add the extra code into the form load as follows.</P>
    <P>When you start the the App, the 1st column reads S, W, O downwards.<BR>If I click the sort indicator in the 1st column it now reads O, S, W.<BR>Then I copy the first row ("O") it adds another ("S") rather than the ("O"). </P>
    <P>Thanks Bob

    <FONT color=#0000ff size=2></P>
    <P>Private</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>Sub</FONT><FONT size=2> Form1_Load(</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> </FONT><FONT color=#0000ff size=2>MyBase</FONT><FONT size=2>.Load</P>
    <P>FpSpread1.ActiveSheet.SetValue(0, 0, </FONT><FONT color=#a31515 size=2>"S"</FONT><FONT size=2>)</P>
    <P>FpSpread1.ActiveSheet.SetValue(0, 1, </FONT><FONT color=#a31515 size=2>"E"</FONT><FONT size=2>)</P>
    <P>FpSpread1.ActiveSheet.SetValue(0, 2, </FONT><FONT color=#a31515 size=2>"A"</FONT><FONT size=2>)</P>
    <P>FpSpread1.ActiveSheet.SetValue(0, 3, </FONT><FONT color=#a31515 size=2>"K"</FONT><FONT size=2>)</P>
    <P>FpSpread1.ActiveSheet.SetValue(1, 0, </FONT><FONT color=#a31515 size=2>"W"</FONT><FONT size=2>)</P>
    <P>FpSpread1.ActiveSheet.SetValue(1, 1, </FONT><FONT color=#a31515 size=2>"G"</FONT><FONT size=2>)</P>
    <P>FpSpread1.ActiveSheet.SetValue(1, 2, </FONT><FONT color=#a31515 size=2>"P"</FONT><FONT size=2>)</P>
    <P>FpSpread1.ActiveSheet.SetValue(1, 3, </FONT><FONT color=#a31515 size=2>"V"</FONT><FONT size=2>)</P>
    <P>FpSpread1.ActiveSheet.SetValue(2, 0, </FONT><FONT color=#a31515 size=2>"O"</FONT><FONT size=2>)</P>
    <P>FpSpread1.ActiveSheet.SetValue(2, 1, </FONT><FONT color=#a31515 size=2>"L"</FONT><FONT size=2>)</P>
    <P>FpSpread1.ActiveSheet.SetValue(2, 2, </FONT><FONT color=#a31515 size=2>"Q"</FONT><FONT size=2>)</P>
    <P>FpSpread1.ActiveSheet.SetValue(2, 3, </FONT><FONT color=#a31515 size=2>"H"</FONT><FONT size=2>)</P>
    <P></FONT><FONT color=#0000ff size=2>Dim</FONT><FONT size=2> col </FONT><FONT color=#0000ff size=2>As</FONT><FONT size=2> FarPoint.Win.Spread.Column</P>
    <P>col = FpSpread1.ActiveSheet.Columns(0)</P>
    <P>col.SortIndicator = FarPoint.Win.Spread.Model.SortIndicator.Descending</P>
    <P>col.ShowSortIndicator = </FONT><FONT color=#0000ff size=2>True</P></FONT><FONT size=2>
    <P>col.AllowAutoSort = </FONT><FONT color=#0000ff size=2>True</FONT></P>
    <P><FONT color=#0000ff size=2>End</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>Sub</P></FONT>
  • Replied 8 September 2017, 1:57 pm EST

    <p>Bob,</p><p>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.</p><p>Hope this helps, Thanks</p>
  • Replied 8 September 2017, 1:57 pm EST

    <P>OK, Thanks Suresh,</P>
    <P>Strange that I'm getting different results. <BR>Could it be because I'm running version v4.0.3510.2008</P>
  • Replied 8 September 2017, 1:57 pm EST

    <P>I understand it a bit more now, I changed this line to the following code and it now works. </P>
    <P>irs.Copy(cr.Row + cr.RowCount, 0, 0, 0, cr.RowCount, FpSpread1.Sheets(0).ColumnCount)</P>
    <P>changed to:</P>
    <P>irs.Copy(<FONT color=#0000cc>FpSpread1.Sheets(0).GetModelRowFromViewRow</FONT><FONT color=#0000ff>(</FONT>cr.Row + cr.RowCount<FONT color=#0000ff>)</FONT>, 0, 0, 0, cr.RowCount, FpSpread1.Sheets(0).ColumnCount)</P>
    <P>Thanks<BR>Bob</P>
Need extra support?

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

Learn More

Forum Channels