Freeze Panes
This article briefly describes what frozen panes are, and how to create and work with them. It contains the following sections:
What are Frozen Panes?
Frozen panes are a method to keep part of the worksheet visible at all times when scrolling.
Figure 1: A document with frozen top rows and first left column
Types of Panes
Panes have the following properties:
There are four types of panes, as marked on the image below:
- Fixed
- Horizontal scrollable
- Vertical scrollable
- Scrollable
Figure 2: Types of panes
When the panes are split only horizontally the panes present are horizontal scrollable and scrollable. When the panes are split vertically, the two panes are vertical scrollable and scrollable.
What Defines the Panes Positioning?
In order to describe fully the state of the frozen panes, the following need to be specified:
Top left cell index of the fixed pane: This property determines the position to which the viewport is scrolled. When this is different from A1, any areas above and to the left of the index become unreachable. In Figure 3 this is C3.
Frozen rows count: The number of visible rows contained by the horizontal scrollable pane. In Figure 3 this value is 1
Frozen columns count: The number of visible columns contained by the vertical scrollable pane. In Figure 3 this value is 4.
Top left cell index of the scrollable pane: This property determines the scroll position of the scrollable pane. In Figure 3 this is I6. Note that this index is different from the topmost and leftmost point of the scrollable pane.
Figure 3: Panes positioning
Freezing Panes
FreezePanes Methods
Panes can be frozen through the ViewState property of the Worksheet. It is of type WorksheetViewState and exposes the following overloads of the FreezePanes method:
- void FreezePanes(int frozenRowsCount, int frozenColumnsCount)
- void FreezePanes(CellIndex fixedPaneTopLeftCellIndex, int frozenRowsCount, int frozenColumnsCount)
- void FreezePanes(CellIndex fixedPaneTopLeftCellIndex, int frozenRowsCount, int frozenColumnsCount, CellIndex scrollableTopLeftCellIndex)
If the top left cell indices of the fixed pane and of the scrollable pane are not specified, it will be assumed that the top left index of the fixed pane is the current top left index of the viewport and that the scrollable pane is not scrolled.
The result illustrated in Figure 3 can be achieved with the code from Example 1.
Example 1: Freezing panes
CellIndex fixedPaneTopLeftCellIndex = new CellIndex(2, 2);
CellIndex scrollableTopLeftCellIndex = new CellIndex(5, 8);
workbook.ActiveWorksheet.ViewState.FreezePanes(fixedPaneTopLeftCellIndex, 1, 4, scrollableTopLeftCellIndex);
Horizontal and Vertical Split
If you would like to create a vertical or horizontal split, all you need to do is specify either the row count or the column count to be equal to zero.
Example 2: Vertical split
CellIndex fixedPaneTopLeftCellIndex = new CellIndex(2, 2);
workbook.ActiveWorksheet.ViewState.FreezePanes(fixedPaneTopLeftCellIndex, 0, 4);
Figure 4: Vertical split
Since the two panes present are only vertical scrollable and scrollable the columns A and B will remain unreachable. However, the user will be able to scroll to the first two rows. Even though there are two rows from the start of the document at the time of freezing, the document will not be split horizontally.
The Pane Class
Another option to freeze the panes in a worksheet is to use the Pane property of type Pane of the WorksheetViewState. The functionality you can achieve is almost identical to the FreezePanes() methods. The Pane class has the following properties:
TopLeftCellIndex: The top left cell index of the scrollable pane
XSplit: The number of visible columns contained by the vertical scrollable pane
YSplit: The number of visible rows contained by the horizontal scrollable pane.
ActivePane: The current active pane.
State: The state of the frozen panes. At the moment only the Frozen state is supported.
The state from Figure 3 can be achieved with the code from Example 3.
Example 3: Freeze panes through the Pane class
CellIndex scrollableTopLeftCellIndex = new CellIndex(5, 8);
Pane pane = new Pane(scrollableTopLeftCellIndex, 4, 1, ViewportPaneType.Scrollable);
workbook.ActiveWorksheet.ViewState.Pane = pane;
Regardless of the method used to freeze the panes of a worksheet, you should take care not to place the top left index of the frozen pane below or to the right of the index determined by the frozen row count and the frozen column count. Doing so may result in an invalid document and unexpected behavior.
Unfreezing Panes
In order to unfreeze the panes of the worksheet, you need to use the same methods as above but specify zero for number of frozen rows and columns.
Example 4: Unfreezing panes
workbook.ActiveWorksheet.ViewState.FreezePanes(0, 0);
Example 4: Unfreezing panes through the Pane class
workbook.ActiveWorksheet.ViewState.Pane = null;