FarPoint Forums

Hi there!

Currently, I have set anchors so that the spread clings to the left side,
right side and to the bottom.
The problem is: The scroll bars are so far away, so I would like to adapt
the size of the spread so that it fits perfectly to the number of rows and columns.
How can I do that?

Currently, it looks like this:
http://www.schwortschik.de/temp/spread.gif

---

Prince of Persia World

See if this helps you get started...

FpSpread1.ActiveSheet.ColumnCount = 4

FpSpread1.ActiveSheet.RowCount = 4

Dim w As New Integer, h As New Integer

Dim c As FarPoint.Win.Spread.Column

Dim r As FarPoint.Win.Spread.Row

For Each c In FpSpread1.Sheets(0).RowHeader.Columns

w = w + c.Width

Next

For Each c In FpSpread1.Sheets(0).Columns

w = w + c.Width

w = w + 1 'for gridlines

Next

For Each r In FpSpread1.Sheets(0).ColumnHeader.Rows

h = h + r.Height

Next

For Each r In FpSpread1.Sheets(0).Rows

h = h + r.Height

h = h + 1 'for gridlines

Next

Dim hs, vs As Integer

hs = SystemInformation.HorizontalScrollBarHeight

vs = SystemInformation.VerticalScrollBarWidth

 

FpSpread1.Width = w + vs

FpSpread1.Height = h + hs

---

à bientôt.

Thanks! That works quite good!

There are only two issues:

1. Columns/rows that are set "visible=false" are not taken into consideration.
So if I have e.g. one invisible column, there is a space between the
last column and the vertical scrollbar.

2. If there are more rows/columns than can be shown in the spread, there are
no scrollbars displayed (although I have set the ScrollbarPolicies to "AsNeeded")!

Greetings
RobinSword

---

Prince of Persia World

RobinSword,

1) All you need to do is to check the c.Visible property in your for loop through the columns to see if the column is hidden or not.

2) I am not able to reproduce this problem. The scrollbar should always show when there are more columns/rows then can show in one view of the spreadsheet with these settings. Make sure the SuspendLayout method has not been called on the Spread without a ResumeLayout to start painting. If this is not the problem, could you post a small zipped project reproducing the problem you are seeing for us to debug?

---

Scott S.
FarPoint Technologies, Inc.

1) Ok, it works!

2) Here an example:
In the Form_Load-event I do the following:

FpSpread1.HorizontalScrollBarPolicy = FarPoint.Win.Spread.ScrollBarPolicy.AsNeeded
FpSpread1.VerticalScrollBarPolicy = FarPoint.Win.Spread.ScrollBarPolicy.AsNeeded
FpSpread1.ActiveSheet.ColumnCount = 10
FpSpread1.ActiveSheet.RowCount = 50

So there are displayed 10 columns and 50 rows.
Since the 50 rows canot be displayed alltogether in the spread,
there is a vertical scrollbar display.

Picture1: http://www.schwortschik.de/temp/pic1.gif

Now I press the button "Adjust size" that executes the code above
posted by aqualung.
Now the size is adjusted (no GrayArea can be seen anymore)
but unfortunately the vertical scrollbar also disappears.

Picture2: http://www.schwortschik.de/temp/pic2.gif

---

Prince of Persia World

RobinSword,

The code posted by aqualung always sizes the spreadsheet control large enough so that the entire sheet can be displayed without scroll bars.  This could result in a spreadsheet control that is taller or wider than the form.  In your example, the bottom of the spreadsheet control is beyond the viewable area of the form.  You need to modify aqualung's code to constraint the final size of the spreadsheet to the available space on the form.

    FpSpread.Width = Math.Min(w + vs, availableWidth);
    FpSpread.Height = Math.Min(h + hs, availableHeight);

Remember that aqualung provided the code as a starting point, not as final code.

Great! Thanks to the Farpoint-Gurus! 

Now I only need one little thing to make it perfect:
I have to check whether the vertical and horizontal scrollbars
are currently displayed. Because if not, I don't have to add their
size to the spread's width/height.

RobinSword

---

Prince of Persia World

RobinSword,

You could loop the children of the Spread control to find the scrollbar control.

    Dim s As ScrollBar
    For Each c As Control In FpSpread1.Controls
      If TypeOf c Is ScrollBar Then
        s = CType(c, ScrollBar)
        If s.Bounds.Width > s.Bounds.Height Then
          MessageBox.Show("Horizontal ScrollBar")
        Else
          MessageBox.Show("Vertical ScrollBar")
        End If
      End If
    Next
 

---

Scott S.
FarPoint Technologies, Inc.

Well, with this code I always get "Horizontal ScrollBar" and
"Vertical Scrollbar" and if I check s.Visible, it is always true,
even if the scrollbar is not displayed. :(

---

Prince of Persia World

Hello,

It looks as if we are not destroying the child control when the scrollbar is removed. We just set the height or width to 0 depending on the control. Try this code instead.

    Dim s As ScrollBar
    For Each c As Control In FpSpread1.Controls
      If TypeOf c Is ScrollBar Then
        s = CType(c, ScrollBar)
        If s.Bounds.Width > s.Bounds.Height And s.Bounds.Height > 0 Then
          MessageBox.Show("Horizontal ScrollBar")
        End If
        If s.Bounds.Height > s.Bounds.Width And s.Bounds.Width > 0 Then
          MessageBox.Show("Vertical ScrollBar")
        End If
      End If
    Next
 

---

Scott S.
FarPoint Technologies, Inc.

Now it works! Great!

Thanks a lot!

---

Prince of Persia World