How to Freeze Rows and Columns in Excel Using C#

When you’re working with large Excel files, it can be tricky to keep track of key data, especially when you need to scroll through many rows or columns. Freezing rows and columns ensures that important data, like headers, remains visible while you navigate through your worksheet. This feature is useful for keeping key information in view when working with large datasets.

In this blog, we’ll show you how to freeze rows and columns in Excel using C# and Spire.XLS .

What Does Freezing Rows and Columns Do?

Freezing rows and columns helps you keep certain parts of the spreadsheet visible, even when scrolling. The typical scenarios for freezing include:

• Freezing the top row: Keep the column headers visible as you scroll down.
• Freezing the first column: Keep row identifiers visible while scrolling horizontally.
• Freezing both the top row and the first column: Keep both headers and identifiers visible while scrolling in both directions.

Prerequisites

Before you begin, make sure you have the following:

1. Spire.XLS for C#: This library is used to manipulate Excel files in C#. You can download it from E-iceblue's website.
2. Visual Studio: A C# development environment for creating the project.

Step-by-Step Guide to Freezing Rows and Columns

Step 1: Set Up Your Project

Start by creating a new C# project in Visual Studio. Add a reference to Spire.XLS . You can install it via NuGet:

Install-Package Spire.XLS

Step 2: Initialize the Workbook and Worksheet

In this step, we will create a new Excel workbook and add some sample data.

// Create a new workbook
Workbook workbook = new Workbook();

// Access the first worksheet
Worksheet sheet = workbook.Worksheets[0];
// Add sample data for demonstration
sheet.Range["A1"].Text = "Name";
sheet.Range["B1"].Text = "Age";
sheet.Range["C1"].Text = "Department";
sheet.Range["A2"].Text = "John";
sheet.Range["B2"].Text = "28";
sheet.Range["C2"].Text = "HR";

Step 3: Freeze Rows and Columns

Now that we've created the workbook, the next step is to apply the freezing functionality. You can freeze the top row, the first column, or both, but only one freezing operation can be applied at a time.

Option 1: Freeze the Top Row Only

To freeze the top row (for example, to keep column headers visible while scrolling down), you can use the following code:

sheet.FreezePanes(2, 1);  // Freeze the top row (row 1)

Option 2: Freeze the First Column Only

To freeze the first column (for example, to keep row identifiers visible while scrolling horizontally), use this code:

sheet.FreezePanes(1, 2);  // Freeze the first column (column A)

Option 3: Freeze Both the Top Row and the First Column

To freeze both the top row and the first column, use this code:

sheet.FreezePanes(2, 2);  // Freeze the first column (column A)

Step 4: Save the Workbook

After applying the freezing operation, save the workbook to a new file:

workbook.SaveToFile("FrozenRowsColumns.xlsx", ExcelVersion.Version2013);

This code saves the workbook after the freezing operation has been applied.

Complete Example Code

Here is the complete code showing how to create an Excel file, freeze the rows/columns, and save the final file:

using Spire.Xls;

class Program
{
    static void Main(string[] args)
    {
        // Create a new workbook
        Workbook workbook = new Workbook();

        // Access the first worksheet
        Worksheet sheet = workbook.Worksheets[0];

        // Add sample data for demonstration
        sheet.Range["A1"].Text = "Name";
        sheet.Range["B1"].Text = "Age";
        sheet.Range["C1"].Text = "Department";
        sheet.Range["A2"].Text = "John";
        sheet.Range["B2"].Text = "28";
        sheet.Range["C2"].Text = "HR";

        // Apply freezing operation (for example, freezing both the top row and first column)
        sheet.FreezePanes(2, 2);  // Freeze both the top row and first column

        // Save the workbook after applying freezing
        workbook.SaveToFile("FrozenRowsColumns.xlsx", ExcelVersion.Version2013);
    }
}

Conclusion

Freezing rows and columns in Excel is a powerful feature for navigating large datasets. With Spire.XLS and C# , you can easily apply this functionality. Here's a summary of the freezing options:

• Freeze the top row only: sheet.FreezePanes(2, 1);
• Freeze the first column only: sheet.FreezePanes(1, 2);
• Freeze both the top row and the first column: sheet.FreezePanes(2, 2);

Each of these methods helps you keep critical information visible, improving the user experience while working with Excel files.