Freeze and Split Panes in Excel Files

When working with a lot of data, it can be difficult to compare cell values in a spreadsheet. To overcome the problem you can freeze rows or columns, and split a worksheet into panes. That will allow you to see the values side by side. Panes can be frozen, split, and frozen/split.

The following example shows how to create and manipulate worksheet panes in your Excel files using C# and VB.NET.

Excel frozen and split panes set with GemBox.Spreadsheet
Screenshot of Excel frozen and split panes set with GemBox.Spreadsheet
View on GitHub 
using GemBox.Spreadsheet;

class Program
{
    static void Main()
    {
        // If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        var workbook = new ExcelFile();

        // Frozen Rows (first 2 rows are frozen).
        var worksheet1 = workbook.Worksheets.Add("Frozen rows");
        worksheet1.Panes = new WorksheetPanes(2, 0);

        // Frozen Columns (first column is frozen).
        var worksheet2 = workbook.Worksheets.Add("Frozen columns");
        worksheet2.Panes = new WorksheetPanes(0, 1);

        // Frozen Rows and Columns (first 2 rows and first 3 columns are frozen).
        var worksheet3 = workbook.Worksheets.Add("Frozen rows and columns");
        worksheet3.Panes = new WorksheetPanes(2, 3);

        // Split pane.
        var worksheet4 = workbook.Worksheets.Add("Split pane");
        worksheet4.Panes = new WorksheetPanes(PanesState.Split, 2310, 1500, "D7", PanePosition.BottomRight);

        workbook.Save("Freeze or Split Panes.%OutputFileType%");
    }
}
Imports GemBox.Spreadsheet

Module Program

    Sub Main()

        ' If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

        Dim workbook As New ExcelFile()

        ' Frozen Rows (first 2 rows are frozen).
        Dim worksheet1 = workbook.Worksheets.Add("Frozen rows")
        worksheet1.Panes = New WorksheetPanes(2, 0)

        ' Frozen Columns (first column is frozen).
        Dim worksheet2 = workbook.Worksheets.Add("Frozen columns")
        worksheet2.Panes = New WorksheetPanes(0, 1)

        ' Frozen Rows and Columns (first 2 rows and first 3 columns are frozen).
        Dim worksheet3 = workbook.Worksheets.Add("Frozen rows and columns")
        worksheet3.Panes = New WorksheetPanes(2, 3)

        ' Split pane.
        Dim worksheet4 = workbook.Worksheets.Add("Split pane")
        worksheet4.Panes = New WorksheetPanes(PanesState.Split, 2310, 1500, "D7", PanePosition.BottomRight)

        workbook.Save("Freeze or Split Panes.%OutputFileType%")
    End Sub
End Module

Note that some values of PanePosition enumeration are invalid if the pane doesn't exist in the split.

See also

Grouping | GemBox.Spreadsheet Example

Grouping | GemBox.Spreadsheet Example

Next steps

GemBox.Spreadsheet is a .NET component that enables you to read, write, edit, convert, and print spreadsheet files from your .NET applications using one simple API.

Download Buy
Published: December 13, 2018 | Modified: December 19, 2022 | Author: Mario Zorica