Freeze and UnFreeze Panes in Excel Document

14 Oct 202410 minutes to read

Freeze Panes

Freezing panes allows you to keep a portion of the worksheet visible while you scroll through the rest of the sheet. The FreezePanes method of the IRange interface can be used to achieve this.

You can set the first visible row and the first visible column in the non-frozen area through the FirstVisibleRow and FirstVisibleColumn properties.

NOTE

FirstVisibleColumn and FirstVisibleRow indexes are “zero-based”.

Freeze Rows

The following code example illustrates how to freeze rows in the worksheet.

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	FileStream inputStream = new FileStream(@Path.GetFullPath(@"Data/InputTemplate.xlsx"), FileMode.Open, FileAccess.Read);
	IWorkbook workbook = application.Workbooks.Open(inputStream);
	IWorksheet worksheet = workbook.Worksheets[0];

	//Applying freeze rows to the sheet by specifying a cell
	worksheet.Range["A3"].FreezePanes();

	//Set first visible row in the bottom pane
	worksheet.FirstVisibleRow = 3;

	//Saving the workbook as stream
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/Output.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);

	//Dispose streams
	outputStream.Dispose();
	inputStream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Open("SourceWorkbookTemplate.xlsx");
    IWorksheet worksheet = workbook.Worksheets[0];

    //Applying freeze rows to the sheet by specifying a cell
    worksheet.Range["A3"].FreezePanes();

    //Set first visible row in the bottom pane
    worksheet.FirstVisibleRow = 3;

    //Saving the workbook
    workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
    Dim application As IApplication = excelEngine.Excel
    application.DefaultVersion = ExcelVersion.Xlsx
    Dim workbook As IWorkbook = application.Workbooks.Open("SourceWorkbookTemplate.xlsx")
    Dim worksheet As IWorksheet = workbook.Worksheets(0)

    'Applying freeze rows to the sheet by specifying a cell
    worksheet.Range("A3").FreezePanes()

    'Set first visible row in the bottom pane
    worksheet.FirstVisibleRow = 3

    'Saving the workbook
    workbook.SaveAs("Output.xlsx")
End Using

A complete working example to freeze rows in C# is present on this GitHub page.

Freeze Columns

The following code example illustrates how to freeze columns in the worksheet.

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	FileStream inputStream = new FileStream(@Path.GetFullPath(@"Data/InputTemplate.xlsx"), FileMode.Open, FileAccess.Read);
	IWorkbook workbook = application.Workbooks.Open(inputStream);
	IWorksheet worksheet = workbook.Worksheets[0];

	//Applying freeze columns to the sheet by specifying a cell
	worksheet.Range["C1"].FreezePanes();

	//Set first visible column in the right pane
	worksheet.FirstVisibleColumn = 4;

	//Saving the workbook as stream
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/Output.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);

	//Dispose streams
	outputStream.Dispose();
	inputStream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Open("InputTemplate.xlsx");
    IWorksheet worksheet = workbook.Worksheets[0];

    //Applying freeze columns to the sheet by specifying a cell
    worksheet.Range["C1"].FreezePanes();

    //Set first visible column in the right pane
    worksheet.FirstVisibleColumn = 4;

    //Saving the workbook
    workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
    Dim application As IApplication = excelEngine.Excel
    application.DefaultVersion = ExcelVersion.Xlsx
    Dim workbook As IWorkbook = application.Workbooks.Open("InputTemplate.xlsx")
    Dim worksheet As IWorksheet = workbook.Worksheets(0)

    'Applying freeze columns to the sheet by specifying a cell
    worksheet.Range("C1").FreezePanes()

    'Set first visible column in the right pane
    worksheet.FirstVisibleColumn = 4

    'Saving the workbook
    workbook.SaveAs("Output.xlsx")
End Using

A complete working example to freeze columns in C# is present on this GitHub page.

Unfreeze Panes

Unfreezing panes allows you to remove any previously frozen sections in an Excel worksheet using the RemovePanes method of the IWorksheet interface.

The following code example illustrates how to unfreeze panes in the worksheet.

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	FileStream inputStream = new FileStream(Path.GetFullPath(@"Data/InputTemplate.xlsx"), FileMode.Open, FileAccess.Read);
	IWorkbook workbook = application.Workbooks.Open(inputStream);
	IWorksheet worksheet = workbook.Worksheets[0];

	//Unfreeze panes in the worksheet
	worksheet.RemovePanes();

	//Saving the workbook as stream
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/Output.xlsx"), FileMode.Create, FileAccess.ReadWrite);
	workbook.SaveAs(outputStream);

	//Dispose streams
	outputStream.Dispose();
	inputStream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Open("InputTemplate.xlsx");
    IWorksheet worksheet = workbook.Worksheets[0];

    //Unfreeze panes in the worksheet
    worksheet.RemovePanes();

    //Saving the workbook
    workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
    Dim application As IApplication = excelEngine.Excel
    application.DefaultVersion = ExcelVersion.Xlsx
    Dim workbook As IWorkbook = application.Workbooks.Open("InputTemplate.xlsx")
    Dim worksheet As IWorksheet = workbook.Worksheets(0)

    'Unfreeze panes in the worksheet
    worksheet.RemovePanes()

    //Saving the workbook
    workbook.SaveAs("Output.xlsx")
End Using

A complete working example to unfreeze panes in C# is present on this GitHub page.

Split Panes 

Split panes allow you to divide a worksheet into separate sections, or panes, which can be scrolled independently.

The following code example illustrates how to split the window through the HorizontalSplit and VerticalSplit properties.

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	IWorkbook workbook = application.Workbooks.Create(1);
	IWorksheet sheet = workbook.Worksheets[0];

	#region Split Panes
	//split panes
	sheet.FirstVisibleColumn = 2;
	sheet.FirstVisibleRow = 5;
	sheet.VerticalSplit = 5000;
	sheet.HorizontalSplit = 5000;
	#endregion

	sheet.ActivePane = 1;

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/SplitPanes.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //split panes
  sheet.FirstVisibleColumn = 5;
  sheet.FirstVisibleRow = 11;
  sheet.VerticalSplit = 1100;
  sheet.HorizontalSplit = 1000;
  sheet.ActivePane = 1;

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Split Panes
  sheet.FirstVisibleColumn = 5
  sheet.FirstVisibleRow = 11
  sheet.VerticalSplit = 1100
  sheet.HorizontalSplit = 1000
  sheet.ActivePane = 1

  workbook.SaveAs("Output.xlsx")
End Using

A complete working example to split panes in C# is present on this GitHub page.