using IronXL; using System; using System.Linq; // Supported for XLSX, XLS, XLSM, XLTX, CSV and TSV WorkBook workBook = WorkBook.Load("sample.xlsx"); // Select worksheet at index 0 WorkSheet workSheet = workBook.WorkSheets[0]; // Get any existing worksheet WorkSheet firstSheet = workBook.DefaultWorkSheet; // Select a cell and return the converted value int cellValue = workSheet["A2"].IntValue; // Read from ranges of cells elegantly. foreach (var cell in workSheet["A2:A10"]) { Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text); } // Calculate aggregate values such as Min, Max and Sum decimal sum = workSheet["A2:A10"].Sum(); // Linq compatible decimal max = workSheet["A2:A10"].Max(c => c.DecimalValue);

using IronSoftware.Drawing; using IronXL; using IronXL.Styles; using System.Linq; WorkBook workBook = WorkBook.Load("sample.xls"); WorkSheet workSheet = workBook.WorkSheets.First(); var range = workSheet["A1:H10"]; var cell = range.First(); // Set background color of the cell with an rgb string cell.Style.SetBackgroundColor("#428D65"); // Apply styling to the whole range. // Set underline property to the font // FontUnderlineType is enum that stands for different types of font underlying range.Style.Font.Underline = FontUnderlineType.SingleAccounting; // Define whether to use horizontal line through the text or not range.Style.Font.Strikeout = false; // Define whether the font is bold or not range.Style.Font.Bold = true; // Define whether the font is italic or not range.Style.Font.Italic = false; // Get or set script property of a font // Font script enum stands for available options range.Style.Font.FontScript = FontScript.Super; // Set the type of the border line // There are also TopBorder,LeftBorder,RightBorder,DiagonalBorder properties // BorderType enum indicates the line style of a border in a cell range.Style.BottomBorder.Type = BorderType.MediumDashed; // Indicate whether the cell should be auto-sized range.Style.ShrinkToFit = true; // Set alignment of the cell range.Style.VerticalAlignment = VerticalAlignment.Bottom; // Set border color range.Style.DiagonalBorder.SetColor("#20C96F"); // Define border type and border direction as well range.Style.DiagonalBorder.Type = BorderType.Thick; // DiagonalBorderDirection enum stands for direction of diagonal border inside cell range.Style.DiagonalBorderDirection = DiagonalBorderDirection.Forward; // Set background color of cells range.Style.SetBackgroundColor(Color.Aquamarine); // Set fill pattern of the cell // FillPattern enum indicates the style of fill pattern range.Style.FillPattern = FillPattern.Diamonds; // Set the number of spaces to intend the text range.Style.Indention = 5; // Indicate if the text is wrapped range.Style.WrapText = true; workBook.SaveAs("stylingOptions.xls");

Published March 26, 2023

How to Freeze a Row in Excel (Beginner Guide)

1. Introduction

Comparing data in your workbook can be challenging when dealing with a large amount of information. Luckily, Excel provides several features to simplify viewing content from different areas within your workbook simultaneously, including the ability to freeze panes. By freezing rows or columns in place, you can ensure that specific rows or columns, especially header cells, are always visible on your worksheet. You can scroll through your content while still being able to see the frozen cells. This can be done using the IronXL library, which is an advanced library for various types of Excel operations. It provides a freeze panes option that we can use.

2. Freeze and Unfreeze Rows and Columns using Excel

2.1 Freeze Rows

In your worksheet, you might want to permanently display a specific row or column, particularly the header cells. You can scroll over your material while still viewing the frozen cells by freezing rows or columns in place.

Step 1: Select the row you want to freeze or, if you want to freeze the top row, select row 1. In this case, we'll choose row 1 because we want to freeze it to see the header at all times.

Step 2: Choose Freeze Panes from the drop-down menu by clicking the Freeze Panes command on the View tab.

Step 3: The gray line indicates that the rows are frozen in place. The frozen rows can still be viewed at the top of the worksheet as you scroll down. In our example, we have scrolled down to row 17, but we can still see row 1 on the screen because it has been frozen as the top row.

2.2 Freeze Columns

Step 1: Choose the column to the right of the column or columns you wish to freeze. In this case, we'll choose column B because we want to freeze column A.

Step 2: Choose Freeze Panes from the drop-down menu by clicking the Freeze Panes command on the View tab.

Step 3: Then, we select the column we want to freeze, which will be shown in a gray line. The gray line indicates where the column will be frozen in place. As we navigate through the worksheet, the frozen column remains visible on the left-hand side. In our example, we have scrolled down to column G and can still see the frozen column on the left.

2.3 Unfreezing Panes

If you want to select a different view option, you may need to reset the spreadsheet by unfreezing the panes. To do this, click the Freeze Panes command and then select Unfreeze Panes from the drop-down menu to unfreeze rows or columns.

3. Freeze and Unfreeze Rows and Columns using IronXL

3.1 IronXL Features

IronXL is a .NET library that enables the reading and editing of Microsoft Excel documents in C#. It is a standalone .NET software library that can read multiple spreadsheet formats. The installation of Microsoft Excel or Interop is not required.

With IronXL's user-friendly C# API, it is easy to read, modify, and create Excel spreadsheet files in the .NET environment. The library provides comprehensive support for .NET Core, .NET Framework, Xamarin, Mobile, Linux, macOS, and Azure, and is among the best Excel spreadsheet libraries for these platforms.

3.2 IronXL Features

Data from XLS, XLSX, CSV, and TSV can be edited, read, and loaded.
It supports storing and exporting data to CSV, TSV, JSON, XLS, and XLSX.
Ranges can be easily utilized with the "WorkSheet["A1:B10"]" syntax. Ranges can be mixed logically.
Columns, rows, and ranges can be sorted.
Examples of styling include cell visual styles, font, size, border, alignment, lock, freeze cells, hide/unhide rows, and number formats can be applied.

3.3 Freeze Panes using IronXL

Freezing rows and columns in Excel allows them to remain fixed in the specified location on the screen while scrolling, making it easier to keep track of columns and refer to them. Below is a sample code for freezing rows:

using IronXL;
WorkBook wb = WorkBook.LoadExcel("sample1.xlsx");
WorkSheet
    ws = wb.GetWorkSheet("Sheet1");
ws.CreateFreezePane(0, 1);
wb.SaveAs("sample1.xlsx");

using IronXL;
WorkBook wb = WorkBook.LoadExcel("sample1.xlsx");
WorkSheet
    ws = wb.GetWorkSheet("Sheet1");
ws.CreateFreezePane(0, 1);
wb.SaveAs("sample1.xlsx");

Imports IronXL
Private wb As WorkBook = WorkBook.LoadExcel("sample1.xlsx")
Private ws As WorkSheet = wb.GetWorkSheet("Sheet1")
ws.CreateFreezePane(0, 1)
wb.SaveAs("sample1.xlsx")

VB C#

The above code is a sample that freezes the top row of the Excel sheet. In the first step, we load the existing Excel sheet using the file location and name. The method WorkBook.LoadExcel allows us to load the existing file into the object web, which helps us perform various types of Excel processes. Then, we select the Excel sheet by specifying the worksheet name. The method GetWorksheet helps us to get the sheet name as a parameter.

Next, we use the worksheet function called CreateFreezePane, which has two parameters - one for the column position and the other for the row position. By passing two values, we can freeze columns and rows. Using this function, we can freeze multiple columns and as many rows as we want. This will pass the freeze panes command to automatically freeze the first column as shown in the code. In the above code, if we change the value from 0 to 1, it will keep the leftmost column visible when we scroll horizontally, and it will freeze multiple rows on the selected cell. That is, it will freeze all the rows of the first column in the Excel sheet.

3.4 Unfreezing Panes using IronXL

We can unfreeze panes using the RemovePane function. This will allow us to unfreeze rows and columns in the Excel sheet.

4. Conclusion

The IronXL library is a development library that provides all the advanced capabilities required for a complex Excel application. One great thing about it is that it offers developers and users a free trial, making it easy to determine if it meets their needs. IronXL is one of the fastest libraries available, and with just a few lines of code, a developer can easily learn how to create an Excel document and perform a variety of Excel operations. To learn more about IronXL, click on this link, or follow this link for additional examples.