How to: Freeze and Unfreeze Rows and Columns

• Jun 07, 2019
• 2 minutes to read

Note

A complete sample project is available at https://github.com/DevExpress-Examples/wpf-spreadsheetcontrol-examples-e4975.

RowAndColumnActions.cs

static void FreezePanes(SpreadsheetControl control)
{
    //Access the active worksheet.
    Worksheet worksheet = control.Document.Worksheets.ActiveWorksheet;

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

    // Access the active cell. 
    Cell activeCell = control.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);
    }
}

RowAndColumnActions.vb

Private Shared Sub FreezePanes(ByVal control As SpreadsheetControl)
    'Access the active worksheet.
    Dim worksheet As Worksheet = control.Document.Worksheets.ActiveWorksheet

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

    ' Access the active cell. 
    Dim activeCell As Cell = control.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