Worksheet.FreezePanes(Int32, Int32, Range) Method

Freezes the specified number of rows and columns below and to the right of the specified cell, including the row and column containing this cell.

Namespace: DevExpress.Spreadsheet

Assembly: DevExpress.Spreadsheet.v19.1.Core.dll

Declaration

C#

void FreezePanes(
    int rowOffset,
    int columnOffset,
    Range topLeft
)

VB.NET

Sub FreezePanes(
    rowOffset As Integer,
    columnOffset As Integer,
    topLeft As Range
)

Parameters

Name	Type	Description
rowOffset	Int32	An integer value that specifies the zero-based offset of the last row to be frozen relative to the row that contains the specified cell (the topLeft parameter).
columnOffset	Int32	An integer value that specifies the zero-based offset of the last column to be frozen relative to the column that contains the specified cell (the topLeft parameter).
topLeft	Range	A Range object that specifies a cell (or top left cell of the cell range) from where freezing rows and columns shall start.

Remarks

The FreezePanes method locks panes at the top and on the left side of the worksheet to keep the specified rows and columns visible when the remaining area of the worksheet is scrolled. The top pane contains rows starting from the row that contains the topLeft cell and ending with the row specified by rowOffset relative to the first frozen row. The left pane contains columns starting from the column that contains the topLeft cell and ending with the column specified by columnOffset relative to the first frozen column.

To freeze worksheet rows only or columns only, use the Worksheet.FreezeRows or Worksheet.FreezeColumns method.

To unfreeze panes in a worksheet, use the Worksheet.UnfreezePanes method.

Example

This example demonstrates how to freeze visible rows and columns above and to the left of the cell that is currently active in a worksheet.

The following cases are handled:

• If an active cell is outside the visible range of cells or it matches the top left visible cell, no rows and columns are frozen.
• If an active cell is located in the leftmost visible column, only visible rows above the active cell are frozen. The Worksheet.FreezeRows method is called.
• If an active cell is located in the topmost visible row, only visible columns to the left of the active cell are frozen. The Worksheet.FreezeColumns method is called.
• Otherwise, both visible rows and columns above and to the left of the active cell are frozen. The Worksheet.FreezePanes method is called.

To get the range of cells that is currently visible, use the SpreadsheetControl.VisibleRange property. To access a cell that is currently active in a worksheet, use the SpreadsheetControl.ActiveCell property.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/how-to-freeze-and-unfreeze-rows-and-columns-e4844

Form1.cs

private void buttonFreezePanes_Click(object sender, EventArgs e) {
    //Access the active worksheet.
    Worksheet worksheet = workbook.Worksheets.ActiveWorksheet;

    // Access the cell range that is currently visible.
    Range visibleRange = spreadsheetControl1.VisibleRange;

    // Access the active cell. 
    Cell activeCell = spreadsheetControl1.ActiveCell;

    int rowOffset = activeCell.RowIndex - visibleRange.TopRowIndex - 1;
    int columnOffset = activeCell.ColumnIndex - visibleRange.LeftColumnIndex - 1;

    // If the active cell is outside the visible range of cells, no rows and columns are frozen.
    if (!visibleRange.IsIntersecting(activeCell)) {
        return;
    }

    if (activeCell.ColumnIndex == visibleRange.LeftColumnIndex) {
        // If the active cell matches the top left visible cell, no rows and columns are frozen.
        if (activeCell.RowIndex == visibleRange.TopRowIndex) { return; }
        else
            // Freeze visible rows above the active cell if it is located in the leftmost visible column.
            worksheet.FreezeRows(rowOffset, visibleRange);
    }

    else if (activeCell.RowIndex == visibleRange.TopRowIndex) {
        // Freeze visible columns to the left of the active cell if it is located in the topmost visible row.
        worksheet.FreezeColumns(columnOffset, visibleRange);
    }

    else {
        // Freeze both rows and columns above and to the left of the active cell.
        worksheet.FreezePanes(rowOffset, columnOffset, visibleRange);
    }
}

Form1.vb

Private Sub buttonFreezePanes_Click(ByVal sender As Object, ByVal e As EventArgs) Handles buttonFreezePanes.Click
    'Access the active worksheet.
    Dim worksheet As Worksheet = workbook.Worksheets.ActiveWorksheet

    ' Access the cell range that is currently visible.
    Dim visibleRange As Range = spreadsheetControl1.VisibleRange

    ' Access the active cell. 
    Dim activeCell As Cell = spreadsheetControl1.ActiveCell

    Dim rowOffset As Integer = activeCell.RowIndex - visibleRange.TopRowIndex - 1
    Dim columnOffset As Integer = activeCell.ColumnIndex - visibleRange.LeftColumnIndex - 1

    ' If the active cell is outside the visible range of cells, no rows and columns are frozen.
    If (Not visibleRange.IsIntersecting(activeCell)) Then
        Return
    End If

    If activeCell.ColumnIndex = visibleRange.LeftColumnIndex Then
        ' If the active cell matches the top left visible cell, no rows and columns are frozen.
        If activeCell.RowIndex = visibleRange.TopRowIndex Then
            Return
        Else
            ' Freeze visible rows above the active cell if it is located in the leftmost visible column.
            worksheet.FreezeRows(rowOffset, visibleRange)
        End If

    ElseIf activeCell.RowIndex = visibleRange.TopRowIndex Then
        ' Freeze visible columns to the left of the active cell if it is located in the topmost visible row.
        worksheet.FreezeColumns(columnOffset, visibleRange)

    Else
        ' Freeze both rows and columns above and to the left of the active cell.
        worksheet.FreezePanes(rowOffset, columnOffset, visibleRange)
    End If
End Sub