How to Realize FreezePane Function in Excel with C#

Excel Freezepane is used to fix one row or column to be convenient for viewing other contents which cannot be displayed completely. In this post, I want to introduce a method about how to use freeze pane in Excel by using C#.  

Actually, one worksheet just can display certain rows and columns. If data range is over range which can be displayed by Excel, users need to move scroll bar to view. However, the header will move as well. That will make users to be confused with which category or item the data information belongs to. While, FreezePane can solve this problem. Freeze header row/column. Then, when we move scroll bar, froze row/column will not moved. And we can learn data information clearly. 

In my example, I prepare an Excel file and freeze header row and column. Also, the C# .NET Excel component, Spire.XLS for .NET is used for realize this function more easily. 

If you want to use the following code, please download Spire.XLS and install on your system. Then, add Spire.XLS dll file as reference in your project.

Detailed Steps Shown as Following:

  1. Create a new workbook and initialize worksheet.
  2.  Because the worksheets are empty, so I should create data. I invoke CreateSampleData(sheet) method which has been declared to import data in worksheet.
  3. Freeze panes by using sheet.FreezePanes() method. Two parameters should be passed to this method, row index and column index.
  4. Save and launch file.

Use the following code:
using System;
using Spire.Xls;
using System.Drawing;

namespace FreezePane
{
    class Program
    {
        static void Main(string[] args)
        {

            //Create Workbook
            Workbook workbook = new Workbook();
            workbook.CreateEmptySheets(1);
            Worksheet sheet = workbook.Worksheets[0];

            //Writes sample data
            CreateSampleData(sheet);
            sheet.FreezePanes(2, 1);

            workbook.SaveToFile(“FreezePane.xlsx”, ExcelVersion.Version2010);
            System.Diagnostics.Process.Start(workbook.FileName);
        }

        static void CreateSampleData(Worksheet sheet)
        {
            //Customer Name
            sheet.Range[“A1”].Value = “Name”;
            sheet.Range[“A2”].Value = “Austin Brown”;
            sheet.Range[“A3”].Value = “Cathy Mill”;
            sheet.Range[“A4”].Value = “Daniel Short”;
            sheet.Range[“A5”].Value = “Fiona Hard”;

            //Customer Country
            sheet.Range[“B1”].Value = “Country”;
            sheet.Range[“B2”].Value = “Italy”;
            sheet.Range[“B3”].Value = “United Kingdom”;
            sheet.Range[“B4”].Value = “Argentina”;
            sheet.Range[“B5”].Value = “Germany”;

            //Customer Contact
            sheet.Range[“C1”].Value = “E-mail”;
            sheet.Range[“C2”].Value = “Austin.B23@gmail.com”;
            sheet.Range[“C3”].Value = “CM0927331@yahoo.com”;
            sheet.Range[“C4”].Value = “DanShortsss@hotmail.com”;
            sheet.Range[“C5”].Value = “G.Fional.H@gmail.com”;

            //Style
            sheet.Range[“A1:C1”].Style.Font.IsBold = true;
            sheet.Range[“A1:C1”].Style.Font.Size = 14;
            sheet.Range[“A1:C5”].Style.Font.FontName = “Calibri”;
            sheet.Range[“A2:C5”].Style.Font.Size = 12;
            sheet.Range[“A2:C2”].Style.KnownColor = ExcelColors.LightGreen;
            sheet.Range[“A3:C3”].Style.KnownColor = ExcelColors.Aqua;
            sheet.Range[“A4:C4”].Style.KnownColor = ExcelColors.LightOrange;
            sheet.Range[“A5:C5”].Style.KnownColor = ExcelColors.YellowCustom;

            //Border
            sheet.Range[“A1:C5”].Style.Borders[BordersLineType.EdgeTop].Color = Color.Brown;
            sheet.Range[“A1:C5”].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
            sheet.Range[“A1:C5”].Style.Borders[BordersLineType.EdgeBottom].Color = Color.Brown;
            sheet.Range[“A1:C5”].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
            sheet.Range[“A1:C5”].Style.Borders[BordersLineType.EdgeLeft].Color = Color.Brown;
            sheet.Range[“A1:C5”].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
            sheet.Range[“A1:C5”].Style.Borders[BordersLineType.EdgeRight].Color = Color.Brown;
            sheet.Range[“A1:C5”].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
        }
    }
}

After running, we can get the result as following.

Download Spire.XLS for .NET Here

Advertisements

Author: janewdaisy

.NET Program Beginner. Share methods about how to use C#/VB.NET to export data, operate Word, Excel, PDF and other useful skills.

2 thoughts on “How to Realize FreezePane Function in Excel with C#”

    1. Yeah, it is available everywhere. I use this DLL because I didn’t use Microsoft Excel. If you don’t want to use my DLL, you need to add Microsoft Excel 11.0 Library as reference to realize this function.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s