How to: Freeze and Unfreeze Rows and Columns

  • 3 minutes to read

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.

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.
    CellRange 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);
    }
}

The image below shows how rows and columns are frozen relative to the active cell.

SpreadsheetControl_FreezeRowsColumnsExample

To custom paint frozen pane borders in the SpreadsheetControl, handle the SpreadsheetControl.CustomDrawFrozenPaneBorder event.

See Also