How do I prevent the spread from scrolling in certain situations

Posted by: j2associates on 8 September 2017, 1:13 pm EST

  • Posted 8 September 2017, 1:13 pm EST

    Hello all,


    In certain situations, I need to suppress Vertical scroll bar movement. I am reacting to the TopChange event and manually resetting the spread using SetViewPortTopRow. Is this the preferred way to Cancel the scrolling because it causes another TopChange event to be fired? Thanks in advance for any ideas and/or suggestions!

  • Replied 8 September 2017, 1:13 pm EST

    Thanks for the speedy reply. That does indeed allow me to use my custom mousewheel functions without the flickering but freezing the rows creates another problem. When I freeze the rows, the viewport moves to the top of the spreadsheet so the first row is visible. Calling SetViewportTopRow to send it back to where it was doesn't seem to do anything after the rows are frozen (and if it did, might cause the flickering again). In most cases, I can reset the top row after I unfreeze the rows but occasionally the viewport goes all the way to the bottom. I'm probably going about it the wrong way so I've included the code I'm using to freeze and unfreeze the rows below:

     

    private int _lastTopRow = 0; // private class variable.

     ...

    /* Disable spreadsheet scrolling */

    _lastTopRow =  FpSpread1.GetViewportTopRow(0);

    int currentBottomRow = FpSpread1.GetViewportBottomRow(0);

    FpSpread1.ActiveSheet.FrozenRowCount = currentBottomRow; // Freeze everything up to the bottom visible row.

    // If I call GetViewportTopRow(0) again here, it returns the same value as currentBottomRow for some reason.

    FpSpread1.SetViewportTopRow(0, _lastTopRow); // Appears to have no effect.

    ...

    /* Re-enable spreadsheet scrolling */

    FpSpread1.ActiveSheet.FrozenRowCount = 0;

    FpSpread1.SetViewportTopRow(0, _lastTopRow); // Works most of the time but occasionally the viewport goes to the very bottom.

     

    Andrew

  • Replied 8 September 2017, 1:13 pm EST

    Hello Andrew,

    Making the Rows Frozen is the solution I see, to make the Rows to not to Flicker.Because Scroll is happening even if you are setting the ViewPort.

    I used the following code to freeze the Rows and to set the ViewPort,

            Dim currentbottomrow As Integer
            currentbottomrow = FpSpread1.GetViewportBottomRow(0)

            FpSpread1.Sheets(0).FrozenRowCount = currentbottomrow
            FpSpread1.ShowCell(0, 0, 0, 0, FarPoint.Win.Spread.VerticalPosition.Top, FarPoint.Win.Spread.HorizontalPosition.Left)

    And if I call the GetViewportTopRow(0)  at the position you mentioned , it doesn't return the same value as currentBottomRow.


    Thanks,

  • Replied 8 September 2017, 1:13 pm EST

    Hello,


    This is the preferred method. There is no Cancel parameter for an event that is happening while scrolling.

  • Replied 8 September 2017, 1:13 pm EST

    Thanks ScottS!

  • Replied 8 September 2017, 1:13 pm EST

    This method causes the top two rows to flicker as they move and then
    move back (I have a lot of data and custom rendering, which I think
    slows it down enough to notice). Is there any way to prevent the
    spreadsheet from scrolling in the first place? I tried temporarily
    freezing the visible rows but that makes it impossible to determine
    what cell the mouse is in (frozen cells return -1 for row and column
    index) and I need that information because only certain columns
    override the mouse wheel functionality in certain situations.
    Currently, different key combinations trigger the different mouse wheel
    modes so I need to be able to disable scrolling completely when a key
    combination is pressed, have the user mouse wheel as much as they want
    and then restore scrolling when the keys are released.

     

    Andrew

     
  • Replied 8 September 2017, 1:13 pm EST

    Andrew,


    Freezing the visible rows will be the best for you. You can use the HitTest property to determine the row you are over which will tell you the row even if it is frozen.


      Dim htInfo As FarPoint.Win.Spread.HitTestInformation
      htInfo = FpSpread1.HitTest(e.X, e.Y)
      If Not htInfo.ViewportInfo Is Nothing Then
       MessageBox.Show(htInfo.ViewportInfo.Row)
      Else
       If Not htInfo.HeaderInfo Is Nothing Then
        MessageBox.Show(htInfo.HeaderInfo.Row)
       End If
      End If

  • Replied 8 September 2017, 1:13 pm EST

    Sorry, I've been working on other stuff lately and haven't been tinkering around with this too much. I did try using ShowCell but that had the same problem as SetViewportTopRow. I also found out that calling GetViewportTopRow after freezing the rows returned the FrozenRowCount, which in my example is the same as currentBottomRow. I tested using FpSpread1.ActiveSheet.FrozenRowCount = currentBottomRow - 1;The funny thing is, that isn't the row that ends up being at the top of the viewport so I have no idea what's going on there.

     

    I'm using version 4.0.3510.2008, which is a couple updates behind. I know there's been some scrolling fixes in the newer versions but I'm not sure if they're for the same problem. The only issue with updating to the latest version is that I don't know for sure that updating would fix the problem. If it does, I would have to redo all of our manual tests, as well as I&T for two other applications that make heavy use of the spreadsheet, just to make sure everything still works. That would take weeks, assuming there are no problems, and we just don't have the time for that right now. We would much rather have a fix or workaround that works with the current version.

  • Replied 8 September 2017, 1:13 pm EST

    Hello,


    There is not a way to keep the Spread from scrolling when the Spread is already scrolled to a particular posistion. That is why when you freeze rows, they have to be at the top and the Spread scrolls up. You can try turning off painting and then Spread will not scroll, but this could cause other painting issues if the user starts clicking around the Spread while this is turned off.


     Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
      'Turn off painting
      FpSpread1.SuspendLayout()
      holdrow = FpSpread1.GetViewportTopRow(0)


     End Sub


     Private Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
      'Turn on painting
      FpSpread1.SetViewportTopRow(0, holdrow)
      FpSpread1.ResumeLayout()


     End Sub

  • Replied 8 September 2017, 1:13 pm EST

    This actually works pretty well if the SuspendLayout and ResumeLayout are both in the MouseWheel event handler. Previously, I was putting the ResumeLayout in the TopChange handler but that was never getting called since the layout was suspended. I also found out that I can remove the calls the save and restore the top row while the layout is suspended. Logically, I don't know why this works since I don't even think the rows are moving in the short time the layout is suspended (and I'm restoring them after the layout is resumed) but somehow it does. I left it in just to be safe, though. I've included a simplified version of my code below that uses a custom spreadsheet inheriting from FpSpread. Thanks for your patience and help guys. Much appreciated.

     

     

     public enum MouseWheelMode

    {

    Default,

    Pan,

    Zoom,

    Navigation

    }

     

    public class MyFpSpread : FpSpread

    {

    private MouseWheelMode _currentMouseWheelMode = MouseWheelMode.Default;

    private int _lastTopRow;

    private bool _isRestoringTopRow = false;

     

    public MyFpSpread ()

    {

    this.KeyDown += MyFpSpread_KeyDown;

    this.KeyUp +=  MyFpSpread_KeyUp;

    this.MouseWheel += MyFpSpread_MouseWheel;

    this.TopChange += MyFpSpread_TopChange;

    }

     

    private void MyFpSpread_KeyDown (object sender, KeyEventArgs e)

    {

     if ( e.Shift && !e.Alt )

    {

    _currentMouseWheelMode = MouseWheelMode.Pan; // Shift + MouseWheel = pan

    }

    else if ( e.Shift && e.Alt )

    {

    _currentMouseWheelMode = MouseWheelMode.Zoom; // Shift + Alt + MouseWheel = zoom

    }

    else if ( e.KeyCode == Keys.Space )

    {

    _currentMouseWheelMode = MouseWheelMode.Navigation; // Spacebar + MouseWheel = navigation.

    }

    }

     

    private void MyFpSpread_KeyUp (object send, KeyEventArgs e)

    {

    if ( e.KeyData == Keys.ShiftKey || e.KeyData == Keys.AltKey || e.KeyCode == Keys.Space )
    {
    // simple case, not handling switching from zoom to pan by releasing just the alt key.
    _currentMouseWheelMode = MouseWheelMode.Default;
    }

    }

     

    private void MyFpSpread_MouseWheel (object sender, MouseEventArgs e)

    {

    if (_currentMouseWheelMode != MouseWheelMode.Default)

    {

     int topRow = GetViewportTopRow(0);
     int bottomRow = GetViewportBottomRow(0);
     bool willScroll = e.Delta > 0 && topRow != 0;
     willScroll |= e.Delta < 0 && bottomRow != fpSpread_Sheet1.RowCount - 1;

      if ( willScroll )
      {

    _lastTopRow = this.GetViewportTopRow( 0 );

              this.SuspendLayout();
      }

    }

    switch ( _currentMouseWheelMode )

    {

    case MouseWheelMode.Pan:

    // Pan logic here.

    break;

    case MouseWheelMode.Zoom:

    // Zoom logic here.

    break;

    case MouseWheelMode.Navigation:

    // Navigation logic here.

    break;

    }

     

    if ( IsLayoutSuspended )

    {

    this.SetViewportTopRow( 0, _lastTopwRow );

    this.ResumeLayout();

    this.PerformLayout();

    }

    }

     

    private void FpSpread_TopChange ( object sender, TopChangeEventArgs e)

    {

    if ( _currentMouseWheelMode != MouseWheelMode.Default && !_isRestoringTopRow)

    {

    _isRestoringTopRow = true; // So the TopChange event doesn't continuously fire.

    this.SetViewportTopRow( 0, e.OldTop );

    _isRestoringTopRow = false;

    }

    }

    }

Need extra support?

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

Learn More

Forum Channels